Solved

DropDownList control error when using it with an Insert statement.

Posted on 2004-09-09
21
238 Views
Last Modified: 2008-01-09
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?
 

0
Comment
Question by:swpa_wnt
  • 8
  • 7
  • 6
21 Comments
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12020880
try putting .value or .text after

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

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

Author Comment

by:swpa_wnt
ID: 12020907
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
 
LVL 7

Expert Comment

by:imsolost
ID: 12020912
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
 
LVL 7

Expert Comment

by:imsolost
ID: 12020930
Im slow =(
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12020938
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
 

Author Comment

by:swpa_wnt
ID: 12020941
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
 

Author Comment

by:swpa_wnt
ID: 12020953
Here's my control:

<asp:dropdownlist id="ddlRequestor" runat="server" Width="234px"></asp:dropdownlist>
0
 
LVL 7

Expert Comment

by:imsolost
ID: 12020962
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
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12020967
put a response.write(ddlRequestor.SelectedItem.Text)

right above where you add the paramater and see what it writes
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12020971
I usually bind my ddls on the init event of the ddl
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:swpa_wnt
ID: 12020979
You mean with DataBind?  If so, where?
0
 

Author Comment

by:swpa_wnt
ID: 12020997
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
 
LVL 7

Expert Comment

by:imsolost
ID: 12021035
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
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12021040
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
 
LVL 7

Expert Comment

by:imsolost
ID: 12021045
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
 
LVL 7

Accepted Solution

by:
imsolost earned 125 total points
ID: 12021062
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
 

Author Comment

by:swpa_wnt
ID: 12021162
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
 

Author Comment

by:swpa_wnt
ID: 12021182
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
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12021193
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
 
LVL 17

Assisted Solution

by:AerosSaga
AerosSaga earned 125 total points
ID: 12021210
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
 

Author Comment

by:swpa_wnt
ID: 12021211
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…
This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now