DropDownList control error when using it with an Insert statement.

I'm getting the following error when I try to use a dropdownlist web control to update my SQL Server database through the web form:

Prepared statement '(@requestor nvarchar(4000),@request_date nvarchar(4000),@work_or' expects parameter @requestor, which was not supplied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Prepared statement '(@requestor nvarchar(4000),@request_date nvarchar(4000),@work_or' expects parameter @requestor, which was not supplied.

Source Error:
Line 25:                         cmdInsert.ExecuteNonQuery()
Source File: c:\inetpub\wwwroot\change_request\create_cr.aspx    Line: 25

Stack Trace:

[SqlException: Prepared statement '(@requestor nvarchar(4000),@request_date nvarchar(4000),@work_or' expects parameter @requestor, which was not supplied.]
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194
   ASP.create_cr_aspx.Page_Load() in c:\inetpub\wwwroot\change_request\create_cr.aspx:25
   System.Web.Util.ArglessEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +10
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +731





Here's my code:

                  Sub Page_Load
                        Dim conRequests As SqlConnection
                        Dim cmdInsert As SqlCommand
                        Dim cmdRequestor As SqlCommand
                        Dim strInsert As String
                        Dim dtrRequestor As SqlDataReader
                        Dim dtmDate As DateTime

                        conRequests = New SqlConnection( "***" )
                        strInsert = "Insert change_requests ( requestor, request_date, work_orders ) values ( @requestor, @request_date, @work_orders )"
                        cmdInsert = New SqlCommand( strInsert, conRequests )
                        cmdInsert.Parameters.Add( "@requestor", ddlRequestor.SelectedItem )
                        cmdInsert.Parameters.Add( "@request_date", txtDateRequested.Text )
                        cmdInsert.Parameters.Add( "@work_orders", txtWorkOrders.Text )
                        
                        conRequests.Open()
                        
                        cmdInsert.ExecuteNonQuery()
                        
                        cmdRequestor = New SqlCommand( "select name from wnt_analysts", conRequests )
                        dtrRequestor = cmdRequestor.ExecuteReader()
                        ddlRequestor.DataSource = dtrRequestor
                        ddlRequestor.DataTextField = "name"
                        ddlRequestor.DataBind()
                        dtrRequestor.Close()
                        
                        conRequests.Close()
                  End Sub


The HTML is:
<asp:dropdownlist id="ddlRequestor" runat="server" Width="234px"></asp:dropdownlist>



Does anyone have any idea how to fix this?
 

swpa_wntAsked:
Who is Participating?
 
Fred GoodwinVP of Software DevelopmentCommented:
You will need to move the ConRequests out of the IF.  Either solution corrects the same problem which is the DDL's have no values when you are calling the values
0
 
AerosSagaCommented:
try putting .value or .text after

 cmdInsert.Parameters.Add( "@requestor", ddlRequestor.SelectedItem.text)

 cmdInsert.Parameters.Add( "@requestor", ddlRequestor.SelectedItem.Value )
0
 
swpa_wntAuthor Commented:
That changes it to this error:

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:


Line 16:                         strInsert = "Insert change_requests ( requestor, request_date, work_orders ) values ( @requestor, @request_date, @work_orders )"
Line 17:                         cmdInsert = New SqlCommand( strInsert, conRequests )
Line 18:                         cmdInsert.Parameters.Add( "@requestor", ddlRequestor.SelectedItem.Text )
Line 19:                         cmdInsert.Parameters.Add( "@request_date", txtDateRequested.Text )
Line 20:                         cmdInsert.Parameters.Add( "@work_orders", txtWorkOrders.Text )
 

Source File: c:\inetpub\wwwroot\change_request\create_cr.aspx    Line: 18

Stack Trace:


[NullReferenceException: Object reference not set to an instance of an object.]
   ASP.create_cr_aspx.Page_Load() in c:\inetpub\wwwroot\change_request\create_cr.aspx:18
   System.Web.Util.ArglessEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +10
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +731

 
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Fred GoodwinVP of Software DevelopmentCommented:
I have a few ideas for you.  Change this

ddlRequestor.SelectedItem

TO
ddlRequestor.SelectedItem.text or ddlRequestor.SelectedItem.value depending on what you need.

Also when you call your txtbox's make sure you replace all ' 's so as to not get SQL errors.  Like this.

Replace(DateRequested.Text , "'","''")
Replace(txtWorkOrders.Text, "'","''")

See if that helps
0
 
Fred GoodwinVP of Software DevelopmentCommented:
Im slow =(
0
 
AerosSagaCommented:
Line 18:                     cmdInsert.Parameters.Add( "@requestor", ddlRequestor.SelectedItem.Text )

its not getting the value from the ddlRequestor control then, are you sure there is a control named that and that it has a protected with events?
0
 
swpa_wntAuthor Commented:
I bet .text and .value won't work because my dropdownlist doesn't have any <asp:listitem>'s associated to it.  The dropdown is being dynamically populated from another table in the database.
0
 
swpa_wntAuthor Commented:
Here's my control:

<asp:dropdownlist id="ddlRequestor" runat="server" Width="234px"></asp:dropdownlist>
0
 
Fred GoodwinVP of Software DevelopmentCommented:
the .text pulls from anything that is visible.  make sure you bind the ddl in the page_Load inside of

If Not Page.ispostback then
'bind ddl
end if
0
 
AerosSagaCommented:
put a response.write(ddlRequestor.SelectedItem.Text)

right above where you add the paramater and see what it writes
0
 
AerosSagaCommented:
I usually bind my ddls on the init event of the ddl
0
 
swpa_wntAuthor Commented:
You mean with DataBind?  If so, where?
0
 
swpa_wntAuthor Commented:
Here's the current code:

Sub Page_Load
                        Dim conRequests As SqlConnection
                        Dim cmdInsert As SqlCommand
                        Dim cmdRequestor As SqlCommand
                        Dim strInsert As String
                        Dim dtrRequestor As SqlDataReader

                        conRequests = New SqlConnection( "***" )
                        strInsert = "Insert change_requests ( requestor, request_date, work_orders ) values ( @requestor, @request_date, @work_orders )"
                        cmdInsert = New SqlCommand( strInsert, conRequests )
                        cmdInsert.Parameters.Add( "@requestor", ddlRequestor.SelectedItem.Text )
                        cmdInsert.Parameters.Add( "@request_date", txtDateRequested.Text )
                        cmdInsert.Parameters.Add( "@work_orders", txtWorkOrders.Text )
                        
                        conRequests.Open()
                        
                        cmdInsert.ExecuteNonQuery()
                        
                        cmdRequestor = New SqlCommand( "select name from wnt_analysts", conRequests )
                        dtrRequestor = cmdRequestor.ExecuteReader()
                        ddlRequestor.DataSource = dtrRequestor
                        ddlRequestor.DataTextField = "name"
                        ddlRequestor.DataBind()
                        dtrRequestor.Close()
                        
                        conRequests.Close()
                  End Sub
0
 
Fred GoodwinVP of Software DevelopmentCommented:
Your DDL does not have any values when you are calling it.  Try this.

Sub Page_Load
                    Dim conRequests As SqlConnection
                    Dim cmdInsert As SqlCommand
                    Dim cmdRequestor As SqlCommand
                    Dim strInsert As String
                    Dim dtrRequestor As SqlDataReader

                    conRequests = New SqlConnection( "***" )
                    strInsert = "Insert change_requests ( requestor, request_date, work_orders ) values ( @requestor, @request_date, @work_orders )"
                    cmdInsert = New SqlCommand( strInsert, conRequests )
                    cmdInsert.Parameters.Add( "@requestor", ddlRequestor.SelectedItem.Text )
                    cmdInsert.Parameters.Add( "@request_date", txtDateRequested.Text )
                    cmdInsert.Parameters.Add( "@work_orders", txtWorkOrders.Text )
                   
                    conRequests.Open()
                   
                    cmdInsert.ExecuteNonQuery()
                   
If Not Page.IsPostBack then
                    cmdRequestor = New SqlCommand( "select name from wnt_analysts", conRequests )
                    dtrRequestor = cmdRequestor.ExecuteReader()
                    ddlRequestor.DataSource = dtrRequestor
                    ddlRequestor.DataTextField = "name"
                    ddlRequestor.DataBind()
                    dtrRequestor.Close()
                   
                    conRequests.Close()
End If
               End Sub
0
 
AerosSagaCommented:
This part needs to be in the Init event of the DDL, how can you insert a value before the ddl is populated with the results?
cmdRequestor = New SqlCommand( "select name from wnt_analysts", conRequests )
                    dtrRequestor = cmdRequestor.ExecuteReader()
                    ddlRequestor.DataSource = dtrRequestor
                    ddlRequestor.DataTextField = "name"
                    ddlRequestor.DataBind()
0
 
Fred GoodwinVP of Software DevelopmentCommented:
also you are trying to insert on page load?  You might do this

Sub Page_Load
                    Dim conRequests As SqlConnection
                    Dim cmdInsert As SqlCommand
                    Dim cmdRequestor As SqlCommand
                    Dim strInsert As String
                    Dim dtrRequestor As SqlDataReader


                   
If Not Page.IsPostBack then
                    cmdRequestor = New SqlCommand( "select name from wnt_analysts", conRequests )
                    dtrRequestor = cmdRequestor.ExecuteReader()
                    ddlRequestor.DataSource = dtrRequestor
                    ddlRequestor.DataTextField = "name"
                    ddlRequestor.DataBind()
                    dtrRequestor.Close()
                   
                    conRequests.Close()
Else
     If Page.IsValid
                    conRequests = New SqlConnection( "***" )
                    strInsert = "Insert change_requests ( requestor, request_date, work_orders ) values ( @requestor, @request_date, @work_orders )"
                    cmdInsert = New SqlCommand( strInsert, conRequests )
                    cmdInsert.Parameters.Add( "@requestor", ddlRequestor.SelectedItem.Text )
                    cmdInsert.Parameters.Add( "@request_date", txtDateRequested.Text )
                    cmdInsert.Parameters.Add( "@work_orders", txtWorkOrders.Text )
                   
                    conRequests.Open()
                   
                    cmdInsert.ExecuteNonQuery()
     End If
End If
               End Sub
0
 
swpa_wntAuthor Commented:
No luck...

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:


Line 14:                     Dim dtrRequestor As SqlDataReader
Line 15:
Line 16:                               conRequests.Open()
Line 17:                    
Line 18:                         If Not Page.IsPostBack then
 

Source File: c:\inetpub\wwwroot\change_request\create_cr.aspx    Line: 16

Stack Trace:


[NullReferenceException: Object reference not set to an instance of an object.]
   ASP.create_cr_aspx.Page_Load() in c:\inetpub\wwwroot\change_request\create_cr.aspx:16
   System.Web.Util.ArglessEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +10
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +731

 






Here's the code:

                        Sub Page_Load
                    Dim conRequests As SqlConnection
                    Dim cmdInsert As SqlCommand
                    Dim cmdRequestor As SqlCommand
                    Dim strInsert As String
                    Dim dtrRequestor As SqlDataReader

                        If Not Page.IsPostBack then
                    cmdRequestor = New SqlCommand( "select name from wnt_analysts", conRequests )
                    dtrRequestor = cmdRequestor.ExecuteReader()
                    ddlRequestor.DataSource = dtrRequestor
                    ddlRequestor.DataTextField = "name"
                    ddlRequestor.DataBind()
                    dtrRequestor.Close()
                   
                    conRequests.Close()
                        Else
                              If Page.IsValid
                    conRequests = New SqlConnection( "***" )
                    strInsert = "Insert change_requests ( requestor, request_date, work_orders ) values ( @requestor, @request_date, @work_orders )"
                    cmdInsert = New SqlCommand( strInsert, conRequests )
                    cmdInsert.Parameters.Add( "@requestor", ddlRequestor.SelectedItem.Text )
                    cmdInsert.Parameters.Add( "@request_date", txtDateRequested.Text )
                    cmdInsert.Parameters.Add( "@work_orders", txtWorkOrders.Text )
                   
                    conRequests.Open()
                   
                    cmdInsert.ExecuteNonQuery()
                              End If
                        End If
                        End Sub
0
 
swpa_wntAuthor Commented:
Whoops, sorry, wrong error.

ExecuteReader: Connection property has not been initialized.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: ExecuteReader: Connection property has not been initialized.

Source Error:


Line 16:                         If Not Page.IsPostBack then
Line 17:                     cmdRequestor = New SqlCommand( "select name from wnt_analysts", conRequests )
Line 18:                     dtrRequestor = cmdRequestor.ExecuteReader()
Line 19:                     ddlRequestor.DataSource = dtrRequestor
Line 20:                     ddlRequestor.DataTextField = "name"
 

Source File: c:\inetpub\wwwroot\change_request\create_cr.aspx    Line: 18

Stack Trace:


[InvalidOperationException: ExecuteReader: Connection property has not been initialized.]
   System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean executing) +251
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +80
   System.Data.SqlClient.SqlCommand.ExecuteReader() +41
   ASP.create_cr_aspx.Page_Load() in c:\inetpub\wwwroot\change_request\create_cr.aspx:18
   System.Web.Util.ArglessEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +10
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +731

 
0
 
AerosSagaCommented:
You need to attain your ddl values by putting the datareader in the Init event of the ddl and then insert, probably on submit not on page load.  You are never going to get it to work that way.

0
 
AerosSagaCommented:
Private Sub ddlSizes_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlSizes.Init
        Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New OleDb.OleDbCommand
        Dim dr As OleDb.OleDbDataReader
        cmd.CommandText = "SELECT * FROM Products WHERE ProductID = " & Request.QueryString("ProductID")
        cmd.Connection = cnn
        cnn.Open()
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        dr.Read()
        ddlSizes.Items.Insert(0, New ListItem("Please Select a Size...", ""))
        If Not IsDBNull(dr("Size1")) Then
            ddlSizes.Items.Insert(1, New ListItem(dr.Item("Size1"), dr.Item("Size1")))
        End If
        If Not IsDBNull(dr("Size2")) Then
            ddlSizes.Items.Insert(2, New ListItem(dr.Item("Size2"), dr.Item("Size2")))
        End If
        If Not IsDBNull(dr("Size3")) Then
            ddlSizes.Items.Insert(3, New ListItem(dr.Item("Size3"), dr.Item("Size3")))
        End If
        If Not IsDBNull(dr("Size4")) Then
            ddlSizes.Items.Insert(4, New ListItem(dr.Item("Size4"), dr.Item("Size4")))
        End If
        If Not IsDBNull(dr("Size5")) Then
            ddlSizes.Items.Insert(5, New ListItem(dr.Item("Size5"), dr.Item("Size5")))
        End If
        If Not IsDBNull(dr("Size6")) Then
            ddlSizes.Items.Insert(6, New ListItem(dr.Item("Size6"), dr.Item("Size6")))
        End If
        If Not IsDBNull(dr("Size7")) Then
            ddlSizes.Items.Insert(7, New ListItem(dr.Item("Size7"), dr.Item("Size7")))
        End If
        If Not IsDBNull(dr("Size8")) Then
            ddlSizes.Items.Insert(8, New ListItem(dr.Item("Size8"), dr.Item("Size8")))
        End If
        If Not IsDBNull(dr("Size9")) Then
            ddlSizes.Items.Insert(9, New ListItem(dr.Item("Size9"), dr.Item("Size9")))
        End If
        If Not IsDBNull(dr("Size10")) Then
            ddlSizes.Items.Insert(10, New ListItem(dr.Item("Size10"), dr.Item("Size10")))
        End If
        cnn.Close()
        cmd.Dispose()
        cnn.Dispose()
    End Sub
0
 
swpa_wntAuthor Commented:
Gotcha.  Will do.  I've gotta take off for the day.  I'll give that a shot in the morning.  If I have any problems, I'll post them here, if not, I'll split the points evenly between you two.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.