Parameter error for executeNonQuery()

[npasma]

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:

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? :confused:

[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?

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]

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

[b]cmdSoftware.Parameters.Add("@SoftwareNum", SqlDataType.Int) [/b]

for example.

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

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

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

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?

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]

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.

:cool:

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?

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"

[earlofroberts]

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

[npasma]

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]

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

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! :cool:

[Paladine]

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

[npasma]

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:

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.

[Paladine]

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!

[janet_ss]

cnn.Open()
cmd = New SqlCommand(Sql, cnn)
cmd.ExecuteNonQuery()
cmd.Dispose()
cnn.Close()

janett.

[Comatose]

1. You didn't use code tags.
2. This thread is ancient.... like, year 2005. You think he figured out his issue yet?