Solved

DropDownList control error when using it with an Insert statement.

Posted on 2004-09-09
21
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Fred Goodwin
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:Fred Goodwin
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:Fred Goodwin
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
 

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:Fred Goodwin
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:Fred Goodwin
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:
Fred Goodwin 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

707 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