dropdown list Query String Parameter

Question

Hello everyone how you guys doing? I have a dropdown list that won't populate data values from database using sql datasource. When i use the code behind, i was able to populate the data to the dropdown list. I dont know how to pass the Query String Parameter using code behind since i am new in asp.net.

This is the code behind:

Imports System.Data.SqlClient
Partial Class PhotoAlbum
    Inherits System.Web.UI.Page

    Dim oConn As New SqlConnection("Data Source=.\SQLEXPRESS;" & _
"AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;" & _
"Integrated Security=True;User Instance=True")

    Dim oCmd As New SqlCommand()
    Dim oDR As SqlDataReader

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        oConn.Open()
        oCmd.CommandText = "SELECT [CategoryID], [Name]  FROM Categories  ORDER BY [Name]"
        oCmd.Connection = oConn
        oDR = oCmd.ExecuteReader()

        Me.categories.DataSource = oDR
        Me.categories.DataTextField = "Name"
        Me.categories.DataValueField = "CategoryID"
        Me.categories.DataBind()

        oDR.Close()
        oConn.Close()
    End Sub
End Class

I will like to include the following information from sqlDatasource to the codebehind:

SelectCommand="SELECT [CategoryID], [Name] FROM [Categories] WHERE ([UserId] = @UserId) ORDER BY [Name]"> 
    <SelectParameters> 
         <asp:QueryStringParameter Name="UserId" QueryStringField="ID"/>

As you can see from the code behind, i was able to add :

"SELECT [CategoryID], [Name]  FROM Categories  ORDER BY [Name]".

But i will like to add all of this:

SelectCommand="SELECT [CategoryID], [Name] FROM [Categories] WHERE ([UserId] = @UserId) ORDER BY [Name]"> 
        <SelectParameters> 

Thank you guys in advance

Answer 1

Give this a try. I haven't tested it, but the "Request.QueryString" property and the "oCmd.Parameters.AddWithValue()" function are key pieces that you need to utilize.

Imports System.Data.SqlClient
Partial Class PhotoAlbum
    Inherits System.Web.UI.Page

    Dim oConn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")

    Dim oCmd As New SqlCommand()
    Dim oDR As SqlDataReader

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'TODO: probably want to make sure you have an "Id" in the query string
        If Request.QueryString("Id") Is Nothing Then
            ' TODO: handle this scenerio (no "Id" query string parameter)
        Else
            Dim userId As Integer = Nothing
            If Not Integer.TryParse(Request.QueryString("Id"), userId) Then
                ' TODO: handle this scenerio ("Id" query string parameter is not an integer)
            Else
                ' we have a good Id, use a parameterized statement to avoid SQL injection
                ' HINT: can use the "Using" statement of ensure your sql connection is disposed of when finished
                Using oConn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")
                    Dim oCmd As New SqlCommand("SELECT [CategoryID], [Name] FROM [Categories] WHERE UserId = @UserId ORDER BY [Name]", oConn)
                    ' provide a value for the @userId parameter using the "parameters.addwithvalue" function
                    oCmd.Parameters.AddWithValue("@UserId", userId)

                    oConn.Open()
                    Dim oDR As SqlDataReader = oCmd.ExecuteReader()

                    Me.categories.DataSource = oDR
                    Me.categories.DataTextField = "Name"
                    Me.categories.DataValueField = "CategoryID"
                    Me.categories.DataBind()

                    oDR.Close()
                    oConn.Close()
                End Using
            End If
        End If
    End Sub
End Class