Two Queries in VB

[andrewsmd]

Can anyone help me with this. I have the following code

Function pay_membership(ByVal PaymentID As Integer)
'update the users table to set the user to a member
Dim userid As Integer = Convert.ToInt16(userDD.SelectedValue.ToString)
Dim memberSql As String = "update tblUsers set ismember = true where userid = " & userid
Dim memberCommand As New Data.OleDb.OleDbCommand(memberSql, myConnection)
'make an entry in the membership table
Dim success As Boolean = True
Dim membershipSQL As String = "insert into tblRegistrations (UserID, PaymentID, AmountDue, DateRegistered) values (" & userDD.SelectedValue & ", " & PaymentID & ", " & paymentAmountTBox.Text & ", #" & Today.Date.ToString & "#)"
Dim membershipCommand As New Data.OleDb.OleDbCommand(membershipSQL, myConnection)

Try
myConnection.Open()
membershipCommand.ExecuteNonQuery()
Catch ex As Exception
messageLabel.Text = "There has been an error.<br /><Br />" & ex.ToString & "<br /><Br />" & membershipSQL
success = False
Finally
myConnection.Close()
End Try
'Try
' myConnection.Open()
' memberCommand.ExecuteNonQuery()
'Catch ex As Exception
' messageLabel.Text = "There has been an error.<br /><Br />" & ex.ToString & "<br /><Br />" & membershipSQL
' success = False
'Finally
' myConnection.Close()
'End Try

Return success
End Function

TherSee the part that is commented out. If I run the page with the commented part like it is it works fine. As soon as I try to run the second query I get this error

there has been an error.

System.Data.OleDb.OleDbException: No value given for one or more required parameters. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at manualpayments.pay_membership(Int32 PaymentID) in \\intellicom05\Company\Customers\NebraskaStateReadingAssociation\Web\nereads.org\Site\www\private\admin\manualpayments.aspx.vb:line 195

insert into tblRegistrations (UserID, PaymentID, AmountDue, DateRegistered) values (4199, 3841, 20, #7/21/2009 12:00:00 AM#)

What I don't understand is why the first query errors when I add the second. Is it because I am adding two queries? I know both queries are correct because I have printed them on the page and tested them manually. Thanks,

[Ocean10000]

Dim memberSql As String = "update tblUsers set ismember = true where userid = " & userid

with

Dim memberSql As String = "update tblUsers set ismember = true where userid = " & userDD.SelectedValue

[andrewsmd]

Thanks for the reply, once again, the problem existed between the chair and the computer. I had made up update to our testing sql server database, but had not copied the change over to the live server. Therefore, when I was trying to add the value, that column didn't exist. It would have been solved a lot faster if the error reporting wasn't so cryptic. Thanks,

[Ocean10000]

Been there Done that myself many of time.

I learned to save Sql Changes after every time I knowingly change the DB on the development server, and then play this changes back on the production. I also wrote a custom program to do a schema dump of the tables, their structure, relations, and stored procedures. I use this to dump the exact layout of the development DB to be later compared against the production. Doing this has saved me a lot of problems because it makes it easier determining what I missed if anything.