How to pass selected value from dropdown value into SQL table

Question

1.00/5 (1 vote)

See more:

Hi,
I've two dropdowns one dropdown is databound(site) and another is unbound(warehouse),
second dropdown will change the value based on 1st dropdown
I need to insert the dropdown values to my table, when i select site it is inserting into table but when i select warehouse it isn't inserting into my table.
How should i bind the warehouse dropdown value?

What I have tried:

This is my design code:

<asp:TemplateField HeaderText="Site" SortExpression="SITE">
  <EditItemTemplate>
<asp:textbox ID="TxtsiteEdit" runat="server" Text='<%# Bind("SITE") %>'></asp:textbox>
</EditItemTemplate>
                                                                   <InsertItemTemplate>
    <asp:DropDownList ID="TxtsiteInsert" runat="server" Text='<%# Bind("SITE") %>'
                                                                           TabIndex="6" AutoPostBack="True" DataSourceID="inventsite"
                                                                           DataTextField="SITEID" DataValueField="SITEID"  OnSelectedIndexChanged="sitedropdown_SelectedIndexChanged">
                                                                                                                            </asp:DropDownList>
          </InsertItemTemplate>
    <ItemTemplate>
                                                                       <asp:Label ID="TxtSite" runat="server" Text='<%# Bind("SITE") %>'></asp:Label>
          </ItemTemplate>
          </asp:TemplateField>
            <asp:TemplateField HeaderText="Warehouse" SortExpression="WAREHOUSE">
                                                                   <EditItemTemplate>
                                                                       <asp:TextBox ID="TxtWarehouseEdit" runat="server" Text='<%# Bind("WAREHOUSE") %>'></asp:TextBox>

                                                                   </EditItemTemplate>
                                                                   <InsertItemTemplate>

                                                      <asp:DropDownList ID= "TxtWarehouseInsert" runat="server" AutoPostBack="True"  >

                                                                       </asp:DropDownList>
                                                                   </InsertItemTemplate>
                  <ItemTemplate>
       <asp:Label ID="TxtWarehouse" runat="server" Text='<%# Bind("WAREHOUSE") %>'></asp:Label>
          </ItemTemplate>
     </asp:TemplateField>

This is my vb code:

Protected Sub sitedropdown_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)

       Dim IdTxtsiteInsert As DropDownList = CType(DetailsView3.Rows(0).FindControl("TxtsiteInsert"), DropDownList)
       Response.Cookies("sitedropdownvalue").Value = IdTxtsiteInsert.SelectedValue


       Fillwarehouse(IdTxtsiteInsert.Text)
       'Dim IdTxtWarehouseInsert As DropDownList = CType(DetailsView3.Rows(0).FindControl("TxtWarehouseInsert"), DropDownList)
       'insertwarehouse(IdTxtsiteInsert.Text, IdTxtWarehouseInsert.Text)


   End Sub
   Public Sub Fillwarehouse(ByVal siteid As String)
       'Dim IdTxtWarehouseInsert As DropDownList = CType(DetailsView3.Rows(0).FindControl("TxtWarehouseInsert"), DropDownList)
       Dim ddr As DropDownList = CType(DetailsView3.Rows(0).FindControl("TxtWarehouseInsert"), DropDownList)
       Dim strConn As String = WebConfigurationManager.ConnectionStrings("BMGINC_DYNAX09_ProdConnectionString").ConnectionString
       Dim con As New SqlConnection(strConn)
       Dim cmd As New SqlCommand()
       cmd.Connection = con
       cmd.CommandType = CommandType.Text
       cmd.CommandText = "Select InventLocationId, Name from InventLocation where InventSiteId ='" & siteid & "'"
       cmd.Parameters.AddWithValue("InventSiteId", siteid)
       Dim objDs As New DataSet()
       Dim dAdapter As New SqlDataAdapter()
       dAdapter.SelectCommand = cmd
       con.Open()
       dAdapter.Fill(objDs)
       con.Close()
       If objDs.Tables(0).Rows.Count > 0 Then
           ddr.DataSource = objDs.Tables(0)
           ddr.DataTextField = "InventLocationId"
           ddr.DataValueField = "InventLocationId"
           ddr.DataBind()
           ddr.Items.Insert(0, "-Select-")


       Else

           LblError.Text = "No Warehouse found"

       End If
   End Sub

Posted 16-Jul-18 23:48pm

Member 13914727

Updated 17-Jul-18 3:40am

CHill60

v2

Add a Solution

Comments

Richard Deeming 17-Jul-18 11:28am

cmd.CommandText = "Select InventLocationId, Name from InventLocation where InventSiteId ='" & siteid & "'"

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

You're already adding the parameter; all you need to do is use it:

cmd.CommandText = "Select InventLocationId, Name from InventLocation where InventSiteId = @InventSiteId"

Member 13914727 18-Jul-18 5:42am

Public Sub Fillwarehouse(ByVal siteid As String)
'Dim IdTxtWarehouseInsert As DropDownList = CType(DetailsView3.Rows(0).FindControl("TxtWarehouseInsert"), DropDownList)
Dim ddr1 As DropDownList = CType(DetailsView3.Rows(0).FindControl("TxtWarehouseInsert"), DropDownList)
Dim strConn As String = WebConfigurationManager.ConnectionStrings("BMGINC_DYNAX09_ProdConnectionString").ConnectionString
Dim con As New SqlConnection(strConn)
Dim cmd As New SqlCommand()
cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select InventLocationId, Name from InventLocation where InventSiteId =@InventSiteId"
'cmd.Parameters.AddWithValue("InventSiteId", siteid)
Dim objDs As New DataSet()
Dim dAdapter As New SqlDataAdapter()
dAdapter.SelectCommand = cmd
con.Open()
dAdapter.Fill(objDs)
con.Close()
If objDs.Tables(0).Rows.Count > 0 Then
ddr1.DataSource = objDs.Tables(0)
ddr1.DataTextField = "InventLocationId"
ddr1.DataValueField = "InventLocationId"
ddr1.DataBind()
ddr1.Items.Insert(0, "-Select warehouse-")

Else

LblError.Text = "No Warehouse found"

End If
'ddr.Text = ddr1.SelectedValue

End Sub

It is showing error as must declare scalar variable

Member 13914727 18-Jul-18 6:04am

Public Sub Fillwarehouse(ByVal siteid As String)
'Dim IdTxtWarehouseInsert As DropDownList = CType(DetailsView3.Rows(0).FindControl("TxtWarehouseInsert"), DropDownList)
Dim ddr1 As DropDownList = CType(DetailsView3.Rows(0).FindControl("TxtWarehouseInsert"), DropDownList)
Dim strConn As String = WebConfigurationManager.ConnectionStrings("BMGINC_DYNAX09_ProdConnectionString").ConnectionString
Dim con As New SqlConnection(strConn)
Dim cmd As New SqlCommand()
cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select InventLocationId, Name from InventLocation where InventSiteId =@InventSiteId"
cmd.Parameters.AddWithValue("@InventSiteId", InventSiteId)
Dim objDs As New DataSet()
Dim dAdapter As New SqlDataAdapter()
dAdapter.SelectCommand = cmd
con.Open()
dAdapter.Fill(objDs)
con.Close()
If objDs.Tables(0).Rows.Count > 0 Then
ddr1.DataSource = objDs.Tables(0)
ddr1.DataTextField = "InventLocationId"
ddr1.DataValueField = "InventLocationId"
ddr1.DataBind()
ddr1.Items.Insert(0, "-Select warehouse-")

Yes done like this

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ziee-M · Answer 1 · 2018-07-17T03:41:00

Solution 1

Hi,

You did not set the event (selected index change) for the second dropdown.

You just have to add it and implment your logic.

Hope it helps.

Posted 17-Jul-18 3:41am

Ziee-M

Comments

Member 13914727 18-Jul-18 5:02am

How could i pass the selected value in that dropdown to my field in selected index change?

Ziee-M 18-Jul-18 5:19am

You should also check the comment posted by Richard, your code is vulnerable to Sql injection, there are many diffrent way to achive this, by using an ORM such as EntityFramework, or search for SqlParameter to insert your parmaeters in the sql query instead of hard coding them

Member 13914727 18-Jul-18 6:04am

Yes changed it

Ziee-M 18-Jul-18 5:14am

First add and generate your event for the second dropdown :
<asp:DropDownList ID= "TxtWarehouseInsert" runat="server" AutoPostBack="True" OnSelectedIndexChanged="TxtWarehouseInsert_SelectedIndexChanged">
Next, in the your new event, save your data to your datbase. You can get the dropdown text/value with TxtWarehouseInsert.SelectedValue/TxtWarehouseInsert.SelectedText

Member 13914727 18-Jul-18 5:38am

This is my insert event
Protected Sub BtnInsert_Click(ByVal sender As Object, ByVal e As EventArgs)

Dim Idtxtsiteinsert As DropDownList = CType(DetailsView3.Rows(0).FindControl("TxtsiteInsert"), DropDownList)
Dim Idtxtwarehouseinsert As DropDownList = CType(DetailsView3.Rows(0).FindControl("TxtWarehouseInsert"), DropDownList)
warehousedropdown = Idtxtwarehouseinsert.SelectedValue
End Sub

Member 13914727 18-Jul-18 5:39am

But I'm getting an error field cannot be empty, wat should i do now

Ziee-M 18-Jul-18 5:47am

We need more of your code(html/codebehind).
A rapid guess with not enought information, your first line of code return null since the Rows(0) is the header row. you have to get data from second row.
You have to use debuging tools to find the problem.

Member 13914727 18-Jul-18 6:50am

I'm getting error in this event:

 Private Sub DetailsView3_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles DetailsView3.ItemInserted
        LblError.Text = ""

        Dim jno As Integer, lno As Integer
        Dim IdLblJnoInsert As Label = CType(DetailsView3.Rows(0).FindControl("LblJnoInsert"), Label)
        Dim IdLblLnoInsert As Label = CType(DetailsView3.Rows(0).FindControl("LblLnoInsert"), Label)

        If (e.Exception IsNot Nothing) Then
            e.ExceptionHandled = True
            e.KeepInInsertMode = True
            LblError.Text = "Please Check your values. Line not inserted" & e.Exception.ToString
        Else
            Try
 jno = Integer.Parse(IdLblJnoInsert.Text)
                lno = Integer.Parse(IdLblLnoInsert.Text)
                
                updatebom(jno, lno, 1)
                UpdateJournalHdr(jno)
                GridView4.DataBind()
                GridView3.DataBind()
            Catch ex As Exception
                LblError.Text = "BOM Lines Insert:" & Err.Description
            End Try
        End If

Member 13914727 18-Jul-18 6:50am

Please Check your values. Line not insertedSystem.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'WAREHOUSE', table 'BMGINC_DYNAX09_Prod.dbo.PRODJOURNALBOMTEMP'; column does not allow nulls. INSERT fails. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) at System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) at System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback)