Please help...Update statement

Hi, what am I doing wrong here. I get the following error message:

ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 13.

Here is the code

Dim UpdateCMD As Odbc.OdbcCommand

Dim mStream As New MemoryStream()
Dim arrImage() As Byte
Dim sqlString As String

sqlString = "UPDATE ADDRESS SET " & "LastName = @LastName, FirstName = @FirstName, " & _
"DateOfBirth = @DateOfBirth, HomeAddrees = @HomeAddress, " & _
"OfficeAddress = @OfficeAddress, HomePhone = @HomePhone, " & _
"OfficePhone = @OfficePhone, MobilePhone = @MobilePhone, " & _
"EmailAddress = @EmailAddress, Comment = @Comment, " & _
"PictureName = @PictureName, Picture = @Picture " & _
"WHERE Item_ID = " & Me.txtID.Text & ";"

imgPicture.Image.Save(mStream, imgPicture.Image.RawFormat)
arrImage = mStream.GetBuffer
mStream.Close()

UpdateCMD = New Odbc.OdbcCommand

With UpdateCMD
.CommandText = sqlString
.CommandType = CommandType.Text
.Connection = dbConn
.Parameters.Add(New Odbc.OdbcParameter("@LastName", Odbc.OdbcType.NVarChar))
.Parameters.Add(New Odbc.OdbcParameter("@FirstName", Odbc.OdbcType.NVarChar))
.Parameters.Add(New Odbc.OdbcParameter("@DateOfBirth", Odbc.OdbcType.NVarChar))
.Parameters.Add(New Odbc.OdbcParameter("@HomeAddress", Odbc.OdbcType.NVarChar))
.Parameters.Add(New Odbc.OdbcParameter("@OfficeAddress", Odbc.OdbcType.NVarChar))
.Parameters.Add(New Odbc.OdbcParameter("@HomePhone", Odbc.OdbcType.NVarChar))
.Parameters.Add(New Odbc.OdbcParameter("@OfficePhone", Odbc.OdbcType.NVarChar))
.Parameters.Add(New Odbc.OdbcParameter("@MobilePhone", Odbc.OdbcType.NVarChar))
.Parameters.Add(New Odbc.OdbcParameter("@EmailAddress", Odbc.OdbcType.NVarChar))
.Parameters.Add(New Odbc.OdbcParameter("@Comment", Odbc.OdbcType.NVarChar))
.Parameters.Add(New Odbc.OdbcParameter("@PictureName", Odbc.OdbcType.NVarChar))
.Parameters.Add(New Odbc.OdbcParameter("@Picture", Odbc.OdbcType.Image))
.Parameters("@LastName").Value = Me.txtLastName.Text
.Parameters("@FirstName").Value = Me.txtFirstName.Text
.Parameters("@DateOfBirth").Value = Me.txtDateOfBirth.Text
.Parameters("@HomeAddress").Value = Me.txtHomeAddress.Text
.Parameters("@OfficeAddress").Value = Me.txtOfficeAddress.Text
.Parameters("@HomePhone").Value = Me.txtHomePhone.Text
.Parameters("@OfficePhone").Value = Me.txtOfficePhone.Text
.Parameters("@MobilePhone").Value = Me.txtMobilePhone.Text
.Parameters("@EmailAddress").Value = Me.txtEmailAddress.Text
.Parameters("@Comment").Value = Me.txtComment.Text
.Parameters("@PictureName").Value = sPictureName(0)
.Parameters("@Picture").Value = arrImage
.ExecuteNonQuery()
End With

thanks
[3543 byte] By [Croody] at [2007-11-20 0:35:31]
# 1 Re: Please help...Update statement
Edit: Removed. Did not solve issue.
Craig Gemmill at 2007-11-10 3:12:51 >
# 2 Re: Please help...Update statement
Hi Craig, thanks for the reply. I did try it that way but it keeps returning the same error. I did not use single quote on Item_ID because it a number column.
Any ideas of what else that might be causing the error or any other way to do this? Any help will be appreciated.
Croody at 2007-11-10 3:13:51 >
# 3 Re: Please help...Update statement
My initial guess is that it is the EMail Address that is causing the problem. The Expected 13 when there are 12 parameters leads me to believe there is some issue with

myaddress@mysite.com in the parameter. Try changing the @ sign to ? for the email address parameter.

sqlString = "UPDATE ADDRESS SET " & "LastName = @LastName, FirstName = @FirstName, " & _
"DateOfBirth = @DateOfBirth, HomeAddrees = @HomeAddress, " & _
"OfficeAddress = @OfficeAddress, HomePhone = @HomePhone, " & _
"OfficePhone = @OfficePhone, MobilePhone = @MobilePhone, " & _
"EmailAddress = ?EmailAddress, Comment = @Comment, " & _
"PictureName = @PictureName, Picture = @Picture " & _
"WHERE Item_ID = " & Me.txtID.Text & ";"

imgPicture.Image.Save(mStream, imgPicture.Image.RawFormat)
arrImage = mStream.GetBuffer
mStream.Close()

UpdateCMD = New Odbc.OdbcCommand

With UpdateCMD
.CommandText = sqlString
.CommandType = CommandType.Text
.Connection = dbConn
...
.Parameters.Add(New Odbc.OdbcParameter("?EmailAddress", Odbc.OdbcType.NVarChar))
...
.Parameters("?EmailAddress").Value = Me.txtEmailAddress.Text
...
.ExecuteNonQuery()
sotoasty at 2007-11-10 3:14:54 >
# 4 Re: Please help...Update statement
I have tried it but it still does not work. I even tried changing the "@" sign in every parameter to "?". I still believe that there is something missing in the code but I just can't figure it out.
Croody at 2007-11-10 3:15:49 >
# 5 Re: Please help...Update statement
The only other thing I would look at then is the picture. Try removing that whole parameter and see if that is causeing the problem. Or maybe try removing one parameter at a time and see if you can find where the problem is.
sotoasty at 2007-11-10 3:16:49 >
# 6 Re: Please help...Update statement
Oh... ok
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataodbcodbccommandclasscommandtypetopic.asp

So what you have to do is remove all of the @whatever and replace them with a single ?.

Then when you add the parameters, do it in the order as you want them to appear in the statement, and don't name them with a @.

sqlString = "UPDATE ADDRESS SET " & "LastName = ?, FirstName = ?, " & _
...
...

With UpdateCMD
...
.Parameters.Add(New Odbc.OdbcParameter("LastName", Odbc.OdbcType.NVarChar))
.Parameters.Add(New Odbc.OdbcParameter("FirstName", Odbc.OdbcType.NVarChar))
...
.Parameters("LastName").Value = Me.txtLastName.Text
.Parameters("FirstName").Value = Me.txtFirstName.Text


I'm not 100% sure that you can't use the @ part in the parameter collection, so try it both ways. You definitely can not use @ in the statement.
Craig Gemmill at 2007-11-10 3:17:48 >
# 7 Re: Please help...Update statement
Hi Craig, it works now. You are right that the @ sign cannot be used in the statement but it can be used in the parameters collection.

With UpdateCMD
.CommandText = sqlString
.CommandType = CommandType.Text
.Connection = dbConn
.Parameters.Add(New Odbc.OdbcParameter("@LastName", Odbc.OdbcType.NVarChar))
.Parameters.Add(New Odbc.OdbcParameter("@FirstName", Odbc.OdbcType.NVarChar))

.Parameters("@LastName").Value = Me.txtLastName.Text
.Parameters("@FirstName").Value = Me.txtFirstName.Text
.ExecuteNonQuery()
End With

Thank you for the help
Croody at 2007-11-10 3:18:56 >