VB.NET

VB.NET RSS

Parameter error for executeNonQuery()

Please support our VB.NET advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Jun 2005

Posts: 41

Reputation: npasma is an unknown quantity at this point

Solved Threads: 0

npasma

Offline

Light Poster

Parameter error for executeNonQuery()

Jun 27th, 2005

OK, yeah u guessed it. VBnoob here. Like so many other poor souls, lost in a sea of code...heres the dealio-

I know param's need to be completely done and in the correct order for executeNonQuery() to work, but here's my error:

Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.

Source Error:

Line 88: DBConnection.Open()
Line 89: Dim iCount As Integer
Line 90: iCount = cmdSoftware.ExecuteNonQuery()
Line 91: DBConnection.Close()
Line 92: If iCount > 0 Then

Source File: c:\inetpub\wwwroot\ASPproject\WebApplication2\SoftwareDB.vb Line: 90

Stack Trace:

[OleDbException (0x80040e10): No value given for one or more required parameters.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66
WebApplication2.SoftwareDB.updateEntry(Software Software) in c:\inetpub\wwwroot\ASPproject\WebApplication2\SoftwareDB.vb:90
WebApplication2.WebForm1.btnUpdate_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\ASPproject\WebApplication2\WebForm1.aspx.vb:251
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292

The relevant code is as follows:

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
  Public Shared Function updateEntry(ByVal Software As Software) As Boolean
        Dim sUpdate As String = "UPDATE [SOFTWARE DATABASE] SET " _
                                    & "SoftwareNum=@SoftwareNum, SoftwareName=@SoftwareName, Version=@Version, " _
                                    & "Location=@Location, SoftwareBrand=@SoftwareBrand, DatePurchased=@DatePurchased, " _
& "FirstName=@FirstName, LastName=@LastName, SerialNumber=@SerialNumber, Model=@Model " _
                                    & "WHERE SoftwareNum=@SoftwareNum"
        Dim DBConnection As OleDbConnection = Connection()
        Dim cmdSoftware As New OleDbCommand(sUpdate, DBConnection)
        cmdSoftware.Parameters.Add("@SoftwareNum", Software.SoftwareNum)
        cmdSoftware.Parameters.Add("@SoftwareName", Software.SoftwareName)
        cmdSoftware.Parameters.Add("@Version", Software.Version)
        cmdSoftware.Parameters.Add("@Location", Software.Location)
        cmdSoftware.Parameters.Add("@SoftwareBrand", Software.SoftwareBrand)
        cmdSoftware.Parameters.Add("@DatePurchased", Software.DatePurchased)
        cmdSoftware.Parameters.Add("@FirstName", Software.FirstName)
        cmdSoftware.Parameters.Add("@LastName", Software.LastName)
        cmdSoftware.Parameters.Add("@SerialNumber", Software.SerialNumber)
        cmdSoftware.Parameters.Add("@Model", Software.Model)
        DBConnection.Open()
        Dim iCount As Integer
        iCount = cmdSoftware.ExecuteNonQuery()
        DBConnection.Close()
        If iCount > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
  Public Shared Function updateEntry(ByVal Software As Software) As Boolean
        Dim sUpdate As String = "UPDATE [SOFTWARE DATABASE] SET " _
                                    & "SoftwareNum=@SoftwareNum, SoftwareName=@SoftwareName, Version=@Version, " _
                                    & "Location=@Location, SoftwareBrand=@SoftwareBrand, DatePurchased=@DatePurchased, " _
& "FirstName=@FirstName, LastName=@LastName, SerialNumber=@SerialNumber, Model=@Model " _
                                    & "WHERE SoftwareNum=@SoftwareNum"
        Dim DBConnection As OleDbConnection = Connection()
        Dim cmdSoftware As New OleDbCommand(sUpdate, DBConnection)
        cmdSoftware.Parameters.Add("@SoftwareNum", Software.SoftwareNum)
        cmdSoftware.Parameters.Add("@SoftwareName", Software.SoftwareName)
        cmdSoftware.Parameters.Add("@Version", Software.Version)
        cmdSoftware.Parameters.Add("@Location", Software.Location)
        cmdSoftware.Parameters.Add("@SoftwareBrand", Software.SoftwareBrand)
        cmdSoftware.Parameters.Add("@DatePurchased", Software.DatePurchased)
        cmdSoftware.Parameters.Add("@FirstName", Software.FirstName)
        cmdSoftware.Parameters.Add("@LastName", Software.LastName)
        cmdSoftware.Parameters.Add("@SerialNumber", Software.SerialNumber)
        cmdSoftware.Parameters.Add("@Model", Software.Model)
        DBConnection.Open()
        Dim iCount As Integer
        iCount = cmdSoftware.ExecuteNonQuery()
        DBConnection.Close()
        If iCount > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

I dunno, looks like i have all the needed parameters to me. Am I missing something dumb here?

Last edited by Paladine; Jun 28th, 2005 at 3:31 am. Reason: Adding code blocks

•

Join Date: Jun 2005

Posts: 41

Reputation: npasma is an unknown quantity at this point

Solved Threads: 0

npasma

Offline

Light Poster

Re: Parameter error for executeNonQuery()

Jun 27th, 2005

This may clarify, I changed the UPDATE statement to be more accurate, what you see before each parameter there is the actual column names, many in brackets b/c they would otherwise be illegal in VB code. (I didn't name the little buggers.) The error I get is still the same. Am I allowed to have slightly diff. param names than the corresponding column names?

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
Dim sUpdate As String = "UPDATE [SOFTWARE DATABASE] SET " _
                                    & "[Software #]=@SoftwareNum, [Software Name]=@SoftwareName, Version=@Version, " _
                                    & "Location=@Location, [Soft Brand]=@SoftwareBrand, DatePurchased=@DatePurchased, " _
                                    & "FirstName=@FirstName, LastName=@LastName, SerialNumber=@SerialNumber, Model=@Model " _
                                    & "WHERE SoftwareNum=@SoftwareNum"
Dim sUpdate As String = "UPDATE [SOFTWARE DATABASE] SET " _
                                    & "[Software #]=@SoftwareNum, [Software Name]=@SoftwareName, Version=@Version, " _
                                    & "Location=@Location, [Soft Brand]=@SoftwareBrand, DatePurchased=@DatePurchased, " _
                                    & "FirstName=@FirstName, LastName=@LastName, SerialNumber=@SerialNumber, Model=@Model " _
                                    & "WHERE SoftwareNum=@SoftwareNum"

•

Join Date: Feb 2003

Posts: 793

Reputation: Paladine has a spectacular aura about

Solved Threads: 27

Paladine

Offline

Master Poster

Re: Parameter error for executeNonQuery()

Jun 28th, 2005

Well first off you have the second parameter of the Add Method containing the value you are passing in, and not the datatype. And you have not created any Parameter Objects , to store those parameter values for the SQL statement.

cmdSoftware.Parameters.Add("@SoftwareNum", Software.SoftwareNum)

should be

Help with Code Tags

(Toggle Plain Text)

cmdSoftware.Parameters.Add("@SoftwareNum", SqlDataType.Int)

for example.

Values are not passed into the query with parameters like that, but rather it should be done like this:

Help with Code Tags

(Toggle Plain Text)

 Dim objNum as Parameter
objNum = cmdSoftware.Parameters.Add("@SoftwareNum", SqlDataType.Int) 
  objNum.Value = Software.SoftwareNum

Remember: Add method is a for the Parameters Collection. It is a collection of Objects that hold the values which you want to pass to the SQL Statement. But no where in your code do you pass these collection of Parameter Objects the values you want them to contain. The Add method takes two Parameters (empty string, sql or odbc Datatype). Take a look at the Parameters Collection Class (a blueprint for a parameter object).

Hope this helps.

**Note : You should use msdn.microsoft.com to research the methods & parameters those methods take, when you are coding, and check out the Tutorials on Daniweb....may just help!. **

•

Originally Posted by npasma

This may clarify, I changed the UPDATE statement to be more accurate, what you see before each parameter there is the actual column names, many in brackets b/c they would otherwise be illegal in VB code. (I didn't name the little buggers.) The error I get is still the same. Am I allowed to have slightly diff. param names than the corresponding column names?
Help with Code Tags

VB.NET Syntax (Toggle Plain Text)
Dim sUpdate As String = "UPDATE [SOFTWARE DATABASE] SET " _
                                    & "[Software #]=@SoftwareNum, [Software Name]=@SoftwareName, Version=@Version, " _
                                    & "Location=@Location, [Soft Brand]=@SoftwareBrand, DatePurchased=@DatePurchased, " _
& "FirstName=@FirstName, LastName=@LastName, SerialNumber=@SerialNumber, Model=@Model " _
                                    & "WHERE SoftwareNum=@SoftwareNum"
Dim sUpdate As String = "UPDATE [SOFTWARE DATABASE] SET " _ & "[Software #]=@SoftwareNum, [Software Name]=@SoftwareName, Version=@Version, " _ & "Location=@Location, [Soft Brand]=@SoftwareBrand, DatePurchased=@DatePurchased, " _ & "FirstName=@FirstName, LastName=@LastName, SerialNumber=@SerialNumber, Model=@Model " _ & "WHERE SoftwareNum=@SoftwareNum"

Assistant Manager, Pharmacy Informatics
Wordpress Learning Blog
Updated : ASP.Net Login Code

•

Join Date: Feb 2003

Posts: 793

Reputation: Paladine has a spectacular aura about

Solved Threads: 27

Paladine

Offline

Master Poster

Re: Parameter error for executeNonQuery()

Jun 28th, 2005

FYI

By the way you do not add the [Software #] = @SoftwareNum into your Update Statement. It is your Primary Key value, so you wouldn't be updating it on the fly when using a where clause. Not proper Referential Integrity with that type of SQL update statement.

•

Originally Posted by npasma

This may clarify, I changed the UPDATE statement to be more accurate, what you see before each parameter there is the actual column names, many in brackets b/c they would otherwise be illegal in VB code. (I didn't name the little buggers.) The error I get is still the same. Am I allowed to have slightly diff. param names than the corresponding column names?
Help with Code Tags

VB.NET Syntax (Toggle Plain Text)
Dim sUpdate As String = "UPDATE [SOFTWARE DATABASE] SET " _
                                    & "[Software #]=@SoftwareNum, [Software Name]=@SoftwareName, Version=@Version, " _
                                    & "Location=@Location, [Soft Brand]=@SoftwareBrand, DatePurchased=@DatePurchased, " _
& "FirstName=@FirstName, LastName=@LastName, SerialNumber=@SerialNumber, Model=@Model " _
                                    & "WHERE SoftwareNum=@SoftwareNum"
Dim sUpdate As String = "UPDATE [SOFTWARE DATABASE] SET " _ & "[Software #]=@SoftwareNum, [Software Name]=@SoftwareName, Version=@Version, " _ & "Location=@Location, [Soft Brand]=@SoftwareBrand, DatePurchased=@DatePurchased, " _ & "FirstName=@FirstName, LastName=@LastName, SerialNumber=@SerialNumber, Model=@Model " _ & "WHERE SoftwareNum=@SoftwareNum"

Assistant Manager, Pharmacy Informatics
Wordpress Learning Blog
Updated : ASP.Net Login Code

•

Join Date: May 2005

Posts: 13

Reputation: earlofroberts is an unknown quantity at this point

Solved Threads: 1

earlofroberts earlofroberts is offline

Offline

Newbie Poster

Re: Parameter error for executeNonQuery()

Jun 29th, 2005

I suspect that one of your parameters contains a NULL.
By the way, if you use imbedded code instead of stored procedures you are exposing yourself to hacking by injection.
good luck.

earlofroberts

•

Join Date: Jun 2005

Posts: 41

Reputation: npasma is an unknown quantity at this point

Solved Threads: 0

npasma

Offline

Light Poster

Re: Parameter error for executeNonQuery()

Jun 29th, 2005

Thank you all for your time in replying to my question. I am bogged down today, working on switching some servers from Novell to Windows NT (hooray!),

but hopefully I'll be able to address these issues soon! Man, I'm glad I found this site. :mrgreen: Thanks again.

•

Join Date: Jun 2005

Posts: 41

Reputation: npasma is an unknown quantity at this point

Solved Threads: 0

npasma

Offline

Light Poster

Re: Parameter error for executeNonQuery()

Jun 30th, 2005

OK, I'm back with modified code- but no dice. Same error. I changed the parameter entry stuff according to Paladine's advice, and I got my debugger working, and checked all 10 of my parameters- they all contain values, none of them are null. How then can I be getting this error? :surprised

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
Public Shared Function updateEntry(ByVal prmSoftware As Software) As Boolean
        Dim sUpdate As String = "UPDATE [SOFTWARE DATABASE] SET " _
                                    & "[Software Name]=@SoftwareName, Version=@Version, " _
                                    & "Location=@Location, [Soft Brand]=@SoftwareBrand, DatePurchased=@DatePurchased, " _
                                    & "FirstName=@FirstName, LastName=@LastName, SerialNumber=@SerialNumber, Model=@Model " _
                                    & "WHERE SoftwareNum=@SoftwareNum"
        Dim DBConnection As OleDbConnection = Connection()
        Dim cmdSoftware As New OleDbCommand(sUpdate, DBConnection)
 
        Dim prmSoftwareNum As OleDbParameter = cmdSoftware.Parameters.Add("@SoftwareNum", OleDbType.VarChar)
        Dim prmSoftwareName As OleDbParameter = cmdSoftware.Parameters.Add("@SoftwareName", OleDbType.VarChar)
        Dim prmVersion As OleDbParameter = cmdSoftware.Parameters.Add("@Version", OleDbType.VarChar)
        Dim prmLocation As OleDbParameter = cmdSoftware.Parameters.Add("@Location", OleDbType.VarChar)
        Dim prmSoftwareBrand As OleDbParameter = cmdSoftware.Parameters.Add("@SoftwareBrand", OleDbType.VarChar)
        Dim prmDatePurchased As OleDbParameter = cmdSoftware.Parameters.Add("@DatePurchased", OleDbType.VarChar)
        Dim prmFirstName As OleDbParameter = cmdSoftware.Parameters.Add("@FirstName", OleDbType.VarChar)
        Dim prmLastName As OleDbParameter = cmdSoftware.Parameters.Add("@LastName", OleDbType.VarChar)
        Dim prmSerialNumber As OleDbParameter = cmdSoftware.Parameters.Add("@SerialNumber", OleDbType.VarChar)
        Dim prmModel As OleDbParameter = cmdSoftware.Parameters.Add("@Model", OleDbType.VarChar)
        prmSoftwareNum.Value = prmSoftware.SoftwareNum
        prmSoftwareName.Value = prmSoftware.SoftwareName
        prmVersion.Value = prmSoftware.Version
        prmLocation.Value = prmSoftware.Location
        prmSoftwareBrand.Value = prmSoftware.SoftwareBrand
        prmDatePurchased.Value = prmSoftware.DatePurchased
        prmFirstName.Value = prmSoftware.FirstName
        prmLastName.Value = prmSoftware.LastName
        prmSerialNumber.Value = prmSoftware.SerialNumber
        prmModel.Value = prmSoftware.Model
 
        DBConnection.Open()
        Dim iCount As Integer
        iCount = cmdSoftware.ExecuteNonQuery()
        DBConnection.Close()
        If iCount > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
Public Shared Function updateEntry(ByVal prmSoftware As Software) As Boolean
        Dim sUpdate As String = "UPDATE [SOFTWARE DATABASE] SET " _
                                    & "[Software Name]=@SoftwareName, Version=@Version, " _
                                    & "Location=@Location, [Soft Brand]=@SoftwareBrand, DatePurchased=@DatePurchased, " _
                                    & "FirstName=@FirstName, LastName=@LastName, SerialNumber=@SerialNumber, Model=@Model " _
                                    & "WHERE SoftwareNum=@SoftwareNum"
        Dim DBConnection As OleDbConnection = Connection()
        Dim cmdSoftware As New OleDbCommand(sUpdate, DBConnection)

        Dim prmSoftwareNum As OleDbParameter = cmdSoftware.Parameters.Add("@SoftwareNum", OleDbType.VarChar)
        Dim prmSoftwareName As OleDbParameter = cmdSoftware.Parameters.Add("@SoftwareName", OleDbType.VarChar)
        Dim prmVersion As OleDbParameter = cmdSoftware.Parameters.Add("@Version", OleDbType.VarChar)
        Dim prmLocation As OleDbParameter = cmdSoftware.Parameters.Add("@Location", OleDbType.VarChar)
        Dim prmSoftwareBrand As OleDbParameter = cmdSoftware.Parameters.Add("@SoftwareBrand", OleDbType.VarChar)
        Dim prmDatePurchased As OleDbParameter = cmdSoftware.Parameters.Add("@DatePurchased", OleDbType.VarChar)
        Dim prmFirstName As OleDbParameter = cmdSoftware.Parameters.Add("@FirstName", OleDbType.VarChar)
        Dim prmLastName As OleDbParameter = cmdSoftware.Parameters.Add("@LastName", OleDbType.VarChar)
        Dim prmSerialNumber As OleDbParameter = cmdSoftware.Parameters.Add("@SerialNumber", OleDbType.VarChar)
        Dim prmModel As OleDbParameter = cmdSoftware.Parameters.Add("@Model", OleDbType.VarChar)
        prmSoftwareNum.Value = prmSoftware.SoftwareNum
        prmSoftwareName.Value = prmSoftware.SoftwareName
        prmVersion.Value = prmSoftware.Version
        prmLocation.Value = prmSoftware.Location
        prmSoftwareBrand.Value = prmSoftware.SoftwareBrand
        prmDatePurchased.Value = prmSoftware.DatePurchased
        prmFirstName.Value = prmSoftware.FirstName
        prmLastName.Value = prmSoftware.LastName
        prmSerialNumber.Value = prmSoftware.SerialNumber
        prmModel.Value = prmSoftware.Model

        DBConnection.Open()
        Dim iCount As Integer
        iCount = cmdSoftware.ExecuteNonQuery()
        DBConnection.Close()
        If iCount > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

The error is:

Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.

Source Error:

Line 135: DBConnection.Open()
Line 136: Dim iCount As Integer
Line 137: iCount = cmdSoftware.ExecuteNonQuery()
Line 138: DBConnection.Close()
Line 139: If iCount > 0 Then

Source File: c:\inetpub\wwwroot\ASPproject\WebApplication2\SoftwareDB.vb Line: 137

Stack Trace:

[OleDbException (0x80040e10): No value given for one or more required parameters.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
WebApplication2.SoftwareDB.updateEntry(Software prmSoftware) in c:\inetpub\wwwroot\ASPproject\WebApplication2\SoftwareDB.vb:137
WebApplication2.WebForm1.btnUpdate_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\ASPproject\WebApplication2\WebForm1.aspx.vb:248
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()

This error seems very persistant. I would appreciate any more help with this ongoing problem. I realize the code isn't very secure (vs. injection) yet, but the priority is solving this error first. Thank you!

•

Join Date: Feb 2003

Posts: 793

Reputation: Paladine has a spectacular aura about

Solved Threads: 27

Paladine

Offline

Master Poster

Re: Parameter error for executeNonQuery()

Jun 30th, 2005

Try adding for each parameter, before the value assignment :

objParam.Direction = ParameterDirection.Input

Replace objParam with your parameter variable(s).

That may solve the problem

Last edited by Paladine; Jun 30th, 2005 at 1:32 pm. Reason: Forgot to say one thing

Assistant Manager, Pharmacy Informatics
Wordpress Learning Blog
Updated : ASP.Net Login Code

•

Join Date: Jun 2005

Posts: 41

Reputation: npasma is an unknown quantity at this point

Solved Threads: 0

npasma

Offline

Light Poster

Re: Parameter error for executeNonQuery()

Jul 6th, 2005

Well, after much effort I have stumbled on the solution. The problem was merely that my update statement, composed of a number of string parameters needed to have single quotes around each item. The code looks like this:

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
Dim sUpdate As String = "UPDATE [SOFTWARE DATABASE] SET " _
                                    & "[Software Name]='@SoftwareName', Version='@Version', " _
                                    & "Location='@Location', [Soft Brand]='@SoftwareBrand', DatePurchased='@DatePurchased', " _
                                    & "FirstName='@FirstName', LastName='@LastName', SerialNumber='@SerialNumber' Model='@Model' " _
                                    & "WHERE SoftwareNum='@SoftwareNum'"
Dim sUpdate As String = "UPDATE [SOFTWARE DATABASE] SET " _
                                    & "[Software Name]='@SoftwareName', Version='@Version', " _
                                    & "Location='@Location', [Soft Brand]='@SoftwareBrand', DatePurchased='@DatePurchased', " _
                                    & "FirstName='@FirstName', LastName='@LastName', SerialNumber='@SerialNumber' Model='@Model' " _
                                    & "WHERE SoftwareNum='@SoftwareNum'"

I'm not out of the woods yet, as I still have some other problems to deal with, but hey, another day another thread, right?

Thanks to those who assisted in solving this problem and a special thanks to Paladine, I never would have gotten this far without your help big guy. I can't believe you do this stuff for free! :cheesy:

Happy programming y'all.