how to insert id of a databound dropdownlist of on

问题:

• 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 create new ProductCategory, ie. product_category_id is auto-incremented
• I need to do an insert statement
• I have the following code

• Question is, how may i ensure that the 'product_category_id' of ProductCategory is inserted into product_category_id of Product after a selection/no selection at the dropdownlist, while displaying product_category_name of ProductCategory? (FKs)

  Dim sql2 As String = "INSERT INTO Product (product_category_id, product_name, product_title, product_desc, product_author, product_author_age, product_author_desc, product_other_detail, product_dimension1, product_dimension2, product_price, product_institution, product_status, product_delivery_time) VALUES (@product_category_id, @product_name, @product_title, @product_desc, @product_author, @product_author_age, @product_author_desc, @product_other_detail, @product_dimension1, @product_dimension2, @product_price, @product_institution, @product_status, @product_delivery_time)"

  cmd.CommandText = sql2 cmd.CommandType = CommandType.Text

'the following statement is incorrect i believe?

cmd.Parameters.Add(New SqlParameter("@product_category_id", (ddlProductCategoryName2.selectedValue)))
                        cmd.Parameters.Add(New SqlParameter("@product_category_name", (ddlProductCategoryName2.SelectedValue)))
                        cmd.Parameters.Add(New SqlParameter("@product_name", (txtProductName2.Text)))
                        cmd.Parameters.Add(New SqlParameter("@product_title", (txtProductTitle2.Text)))
                        cmd.Parameters.Add(New SqlParameter("@product_desc", (txtProductDescription2.Text)))
                        cmd.Parameters.Add(New SqlParameter("@product_author", (txtProductAuthor2.Text)))
                        cmd.Parameters.Add(New SqlParameter("@product_author_age", (ddlProductAuthorAge2.SelectedValue)))
                        cmd.Parameters.Add(New SqlParameter("@product_author_desc", (txtProductAuthorDesc2.Text)))

回答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.

回答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.