63856

how to insert id of a databound dropdownlist of one table to another table

Question:

<ul><li>I have 2 tables, Product & ProductCategory</li> <li>1 dropdownlist that is databound programmatically to ProductCategory</li> <li>Am working on Visual Studio 2008, ASP.Net Forms</li> <li>Users may create new ProductCategory, ie. product_category_id is auto-incremented</li> <li>I need to do an insert statement</li> <li>I have the following code</li> <li>

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

</li> </ul>

'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)))

Answer1:

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.

Answer2:

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 <a href="https://stackoverflow.com/questions/9043348/insert-integer-when-dropdownlist-displays-strings-as-datavaluefield/9044442#9044442" rel="nofollow">here</a>, so thought I would give VB solutions this time.

Recommend

  • How to save and retrieve binary image data to picture box in winforms using C#
  • How to Pass List to stored procedure
  • Error “405 Method Not Allow” When Calling Put method in Postman with body parameter
  • Iterate a Linq Expression and convert it into DB parameter
  • Why iis host application do not let log in?
  • Executenonquery return value
  • How to pass the values of user defined datatype variable to MS SQL from java using spring jdbc store
  • Fetch scope_identity value in C# code from stored procedure in 3 tier architecture
  • Update with column name by parameter
  • Approve / reject through dropdownlist
  • why the size of all SqlParameters generated by VS2010 are set to 0?
  • Sorting table row redirects the whole page in my MVC application
  • Report not showing up on report viewer
  • Failed to convert parameter value from a DateTime to a Byte[]
  • Why isn't my method being called?
  • Create Instance of different Object Based on parameter passed to function using C#
  • Stored Function - Sending/Receiving Boolean - BD
  • log4net adding full stack trace to database table via exception parameter
  • Linq to SQL and SQL Injection
  • How to get a return value from a stored procedure in VB.NET
  • gridview on page won't refresh, even when calling databind again
  • Eval versus DataField in ASP:Datagrid
  • How to alias an executable using Powershell permanently?
  • numpy 64bit support in PTVS and numpy System.Int64 casting
  • ng-repeat not working with table but works with list
  • Does for loop open and close a database connection on each iteration?
  • Importing Excel files with a large number of columns header into mysql with c#
  • Cordova Apache wrong module path
  • R sqldf renaming a field in a select statement
  • .NET video play library which allows to change the playback rate?
  • Correct implementation of List Iterator methods
  • How to 'create temp table as select' in Slick?
  • MySQL Order by column = x, column asc?
  • Why doesn't :active or :focus work on text links in webkit? (safari & chrome)
  • Validaiting emails with Net.Mail MailAddress
  • MySQL WHERE-condition in procedure ignored
  • Weird JavaScript statement, what does it mean?
  • Web-crawler for facebook in python
  • trying to dynamically update Highchart column chart but series undefined
  • java string with new operator and a literal