locked
Passing a varable into a SelectCommand under asp:SqlDataSource RRS feed

  • Question

  • User1763161047 posted

    What I have been working on for the past three days to me seems simple but I am just not able to resolve or understand the problem.  Here is what I am attempting to do and what I have tried that is not working.  I have a page "admn-venuelist.aspx that has a GridView with a SELECT link.  The select link when clicked on takes the user to a URL passing the record ID (Int) to the receiving page "Admn-VenueEdit.aspx.  The link is passed by a code behind page "Admn-venuelist.aspx"  Here is that code and it works.

     Private Sub GridView1_RowCommand(sender As Object, e As GridViewCommandEventArgs) Handles GridView1.RowCommand
            Label2.Text = e.CommandArgument
            Response.Redirect("~/admn-VenueEdit.aspx?VenueID=" + e.CommandArgument)
    
        End Sub

    So now on the receiving page I have put the following code in the code behind page attempting to collect the ID (Int) from the sending page and holding that in a variable to be used in my query string.   Here is the code on the receiving page.

    Partial Class Admn_VenueEdit
        Inherits System.Web.UI.Page
    
        Private Sub SqlDataSource1_Load(sender As Object, e As EventArgs) Handles SqlDataSource1.Load
            Dim QueryID As String
            QueryID = CInt(Int(Request.QueryString("VenueID")))
        End Sub
    End Class

    Ok, now I should have the selected record ID from the sending page and it should be stored in the variable QueryID and converted to an Integer.  I have confirmed that the ID is passing ok as I can send the Variable QueryID to a message box and the record ID displays correctly. 

    So here is the problem  I am unable to figure out a proper methodology of inserting the variable into a SelectCommand String.  Here is the code that is not working. 

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataSourceID="SqlDataSource1" DataKeyNames="Id" Width="180">
        <Columns>
            <asp:TemplateField HeaderText="Destination" ItemStyle-Width="80">
                <ItemTemplate>
                    <asp:TextBox ID="txtName" runat="server" Text='<%# Eval("Destintion") %>' />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Company" ItemStyle-Width="100">
                <ItemTemplate>
                    <asp:TextBox ID="txtCountry" runat="server" Text='<%# Eval("Company") %>' />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [tblVenue] Where [ID] = @QueryField" >
        <SelectParameters>
       <asp:QueryStringParameter 
           Name="QueryField" 
           QueryStringField="QueryID" 
           DbType="Int32"/>
        </SelectParameters>
    </asp:SqlDataSource>

    As you can see I tried to put the variable into a QueryStringParameter but l run into the same problem as I do if I try to insert it directly into the SelectCommand String.  I have also tried to insert directly the Request.QueryString("VenueID") but that also failed.  It would seem reasonable that you would be able to add Variables in a QueryStringParameter but I am not understanding how to or what the proper syntax is.  If I remove the @QueryField and insert "4" for record ID number 4 it works perfectly.  So that line is correct.  My ultimate goal is to display the record details in asp:textboxes that the record can be edited and then updated.  So far I have been able to set up an Add Record Page, List Records page, and Edit record in a GridView.  My only part left is the ability to edit a record details in a WebForm from a selection from a gridveiw.  Any help would be greatly appreciated and I think this will help others wishing to create a Webform Edit Record page.  Thank you  

    Monday, February 13, 2017 3:47 PM

Answers

All replies

  • User632428103 posted

    Hello mike,

    never try but looks at this sample i think it can help you http://www.aspsnippets.com/Articles/ASPNet-SqlDataSource-pass-value-to-SelectParameter-using-QueryString-Parameter-Example.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 13, 2017 6:32 PM
  • User-1716253493 posted

    Simple way to pass id using hyperlinkfield

    admn-venuelist.aspx 

            <asp:GridView ID="GridView1" runat="server">
                <Columns>
                    <asp:HyperLinkField 
                         DataNavigateUrlFields="VenueID" DataNavigateUrlFormatString="~/admn-VenueEdit.aspx?VenueID={0}"
                         Text="Select or Edit" />
                </Columns>
            </asp:GridView>

    or templatefield

            <asp:GridView ID="GridView1" runat="server">
                <Columns>
                    <asp:TemplateField>
                        <ItemTemplate>
                            <asp:HyperLink ID="HyperLink1" runat="server" 
                                 NavigateUrl='<%# Eval("VenueID", "~/admn-VenueEdit.aspx?VenueID={0}") %>' 
                                 Text="Select or Edit"></asp:HyperLink>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>

    Admn-VenueEdit.aspx

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        SelectCommand="SELECT * FROM [tblVenue] Where [ID] = @ID" >
        <SelectParameters>
             <asp:QueryStringParameter Name="ID" QueryStringField="VenueID" DbType="Int32"/>
        </SelectParameters>
    </asp:SqlDataSource>

    Tuesday, February 14, 2017 4:10 AM
  • User1763161047 posted

    Jimmy69

    Thank you.  What was not obvious to me and difficult to find documentation was that QueryStringField="VenueID" picks up the variable that is passed in the URL.  Now that I understand that it is easy.  But getting my head wrapped around that was most difficult.  I do have another question.  Is there a way to pass a variable from code into the QueryStringField or does it only pick up the variable from the URL?  But you nailed the answer to my question and you educated me.  Thank you for that.

    Tuesday, February 14, 2017 2:20 PM