How To Insert Id Of A Databound Dropdownlist Of One Table To Another Table

I have 2 tables, Product & ProductCategory 1 dropdownlist that is databound programmatically to ProductCategory Am working on Visual Studio 2008, ASP.Net Forms Users may creat

Solution 1:

As far as I can tell there is no reason your SQL/VB won't work as required, you just need to change the databinding on the drop down list slightly. In the page load method call something akin to the following (I have had to assume your column names):

dim adapter as new SqlDataAdapter("SELECT * FROM ProductCategory", [connectionstring])
dim table as new DataTable()
adapter.fill(table)
ddlProductCategoryName2.DataSource = table
ddlProductCategoryName2.DataValueField = "Product_Category_ID"
ddlProductCategoryName2.DataTextField = "Product_Category_Name"

This will mean that

ddlProductCategoryName2.selectedValue

will return the product_category_ID rather than the name displayed in the drop down list.

Solution 2:

I'm posting an alternative to my other answer in case for some reason you do not want to databind the product_Category_ID to the drop down list. Add the following function to the code behind your page.

Private Function GetProductCategoryID(ByVal productCategoryName As String) As Int32

    Dim id As Int32
    Dim connectionString As String = "" 'your connection string
    Using connection As New SqlConnection(connectionString)
        connection.Open()
        Using Adapter As New SqlDataAdapter("SELECT Product_Category_ID FROM ProductCategory WHERE Product_Category_Name = @Name", connection)
            Dim table As New DataTable()
            Adapter.SelectCommand.Parameters.AddWithValue("@Name", productCategoryName)
            Adapter.Fill(table)
            If (table.Rows.Count = 0) Then
                Using command As New SqlCommand("INSERT ProductCategory (Proudct_Category_Name) VALUES (@Name) SELECT SCOPE_IDENITTY()", connection)
                    command.Parameters.AddWithValue("@Name", productCategoryName)
                    id = Convert.ToInt32(command.ExecuteScalar())
                End Using
            Else
                id = Convert.ToInt32(table.Rows(0).Item(0))
            End If
        End Using

        connection.Close()
    End Using

    Return id
End Function

Then you would only have to change one line in your current code:

cmd.Parameters.Add(New SqlParameter("@product_category_id", (ddlProductCategoryName2.selectedValue)))

to

cmd.Parameters.Add(New SqlParameter("@product_category_id", GetProductCategoryID(ddlProductCategoryName2.selectedValue)))

There is no reason the logic behind this function can't be done entirely in SQL (i.e. if an entry exists in the table for the selected string use the ID, otherwise insert it and obtain the ID), but I have already given SQL solutions here, so thought I would give VB solutions this time.