Ad:

VB.NET Discussion Thread
Marked Solved
Views: 18222

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Jun 27th, 2005

Parameter error for executeNonQuery()

Expand Post »

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:

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

npasma

Reputation Points: 14

Solved Threads: 0

Light Poster

Offline

41 posts
since Jun 2005

Permalink

Jun 27th, 2005

Re: Parameter error for executeNonQuery()

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?

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"

npasma

Reputation Points: 14

Solved Threads: 0

Light Poster

Offline

41 posts
since Jun 2005

Permalink

Jun 28th, 2005

Re: Parameter error for executeNonQuery()

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

(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:

(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!. **

Quote 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?
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"

Paladine

Team Colleague

Reputation Points: 211

Solved Threads: 27

Master Poster

Offline

793 posts
since Feb 2003

Permalink

Jun 28th, 2005

Re: Parameter error for executeNonQuery()

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.

Quote 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?
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"

Paladine

Team Colleague

Reputation Points: 211

Solved Threads: 27

Master Poster

Offline

793 posts
since Feb 2003

Permalink

Jun 29th, 2005

Re: Parameter error for executeNonQuery()

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

earlofroberts

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

13 posts
since May 2005

Permalink

Jun 29th, 2005

Re: Parameter error for executeNonQuery()

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.

npasma

Reputation Points: 14

Solved Threads: 0

Light Poster

Offline

41 posts
since Jun 2005

Permalink

Jun 30th, 2005

Re: Parameter error for executeNonQuery()

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

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!

npasma

Reputation Points: 14

Solved Threads: 0

Light Poster

Offline

41 posts
since Jun 2005

Permalink

Jun 30th, 2005

Re: Parameter error for executeNonQuery()

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

Paladine

Team Colleague

Reputation Points: 211

Solved Threads: 27

Master Poster

Offline

793 posts
since Feb 2003

Permalink

Jul 6th, 2005

Re: Parameter error for executeNonQuery()

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:

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.

npasma

Reputation Points: 14

Solved Threads: 0

Light Poster

Offline

41 posts
since Jun 2005

Permalink

Jul 6th, 2005

Re: Parameter error for executeNonQuery()

Well thanks for the compliments, I am just glad you figured it out (I should have noticed the quotes), and glad we were of any assistance!

Paladine

Team Colleague

Reputation Points: 211

Solved Threads: 27

Master Poster

Offline

793 posts
since Feb 2003

Page 1
2
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: Need an assistance

Next Thread in VB.NET Forum Timeline: handle buttons

DaniWeb Message

Cancel Changes

User Name
Password