Double Verification in Access

[jbricher]

Hi everyone, I am building on some code that was written previously and I was wondering how to verify against 2 fields. The code I will provide has 1 and I thought I would use an AND statement with the specific value listed. This gives me the error "END OF STATEMENT EXPECTED". I would appeciate the help of anyone who can give me advice. Thanks in advance.

- Joe

Sub Page_Load(Sender As Object, e as EventArgs)

End Sub

Sub BtnSendClicked(Sender as Object, e as EventArgs)

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Request.MapPath("CPP_v6.mdb") & ";"
Dim MyConn as New OleDBConnection (strConn)
Dim MySQL As String

Dim intCount as Integer
MYSQL = "SELECT COUNT(*) FROM LotteryEntry WHERE studentID=" & studentID.Text
Dim cmd as New OleDBCommand (MySQL, MyConn)
MyConn.Open ()
intCount = cmd.ExecuteScalar()
MyConn.Close ()
Response.write("<h2>The value for intCount is " & intCount & ". Which means the record exists</h2>")

If NOT intCount > 0 Then
Response.Redirect("WinnersAreaFailurePage.html")
Else
MySQL = "UPDATE WinnersEntry SET VehicleMake = '" & VehicleModel.Text & "', VehicleModel = '" & VehicleMake.Text & "', VehicleYear = '" & VehicleYear.Text & "', VehicleColor = '" & VehicleColor.text & "', VehicleLicenseNumber = '" & VehicleLicenseNumber.text & "' WHERE StudentID = " & StudentID.Text

cmd = New OleDBCommand (MySQL, MyConn)
MyConn.Open ()
intCount = cmd.ExecuteScalar()
MyConn.Close ()
Response.Redirect("WinnersAreaSuccessPage.html")
End If

End Sub

[Phil Weber]

Which line causes the error? I don't see an AND statement in your code.

[jbricher]

Sorry, I took it out, it was the line that contained MYSQL = SELECT COUNT etc. I have re-inserted the line and will re-post the code. Thanks.

Sub BtnSendClicked(Sender as Object, e as EventArgs)

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Request.MapPath("CPP_v6.mdb") & ";"
Dim MyConn as New OleDBConnection (strConn)
Dim MySQL As String

Dim intCount as Integer
MYSQL = "SELECT COUNT(*) FROM LotteryEntry WHERE studentID=" & studentID.Text "AND WHERE Winner.Value" = "1"
Dim cmd as New OleDBCommand (MySQL, MyConn)
MyConn.Open ()
intCount = cmd.ExecuteScalar()
MyConn.Close ()
Response.write("<h2>The value for intCount is " & intCount & ". Which means the record exists</h2>")

If NOT intCount > 0 Then
Response.Redirect("WinnersAreaFailurePage.html")
Else
MySQL = "UPDATE WinnersEntry SET VehicleMake = '" & VehicleModel.Text & "', VehicleModel = '" & VehicleMake.Text & "', VehicleYear = '" & VehicleYear.Text & "', VehicleColor = '" & VehicleColor.text & "', VehicleLicenseNumber = '" & VehicleLicenseNumber.text & "' WHERE StudentID = " & StudentID.Text

cmd = New OleDBCommand (MySQL, MyConn)
MyConn.Open ()
intCount = cmd.ExecuteScalar()
MyConn.Close ()
Response.Redirect("WinnersAreaSuccessPage.html")
End If

End Sub

[pclement]

MYSQL = "SELECT COUNT(*) FROM LotteryEntry WHERE studentID=" & studentID.Text "AND WHERE Winner.Value" = "1"

If the data type of your field is Text, single quotes must enclose the value. If numeric, single quotes are omitted. The below code assumes both are Text. In addition you only need a single WHERE statement:

MYSQL = "SELECT COUNT(*) FROM LotteryEntry WHERE studentID = '" & studentID.Text & "' AND Winner = '1'"

[jbricher]

I think that that will work, the problem is that I get an error on Line 31, which is:

intCount = cmd.ExecuteScalar()

That says Data type mismatch in criteria expression. Did I mess something up somewhere with the syntax or something. If anyone can offer any advice I would greatly appreciate it. Thanks.

- Joe

Code:

Sub BtnSendClicked(Sender as Object, e as EventArgs)

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Request.MapPath("CPP_v6.mdb") & ";"
Dim MyConn as New OleDBConnection (strConn)
Dim MySQL As String

Dim intCount as Integer
MYSQL = "SELECT COUNT(*) FROM LotteryEntry WHERE studentID = '" & studentID.Text & "' AND Winner = '1'"
Dim cmd as New OleDBCommand (MySQL, MyConn)
MyConn.Open ()
intCount = cmd.ExecuteScalar()
MyConn.Close ()
Response.write("<h2>The value for intCount is " & intCount & ". Which means the record exists</h2>")

If NOT intCount > 0 Then
Response.Redirect("WinnersAreaFailurePage.html")
Else
MySQL = "UPDATE WinnersEntry SET VehicleMake = '" & VehicleModel.Text & "', VehicleModel = '" & VehicleMake.Text & "', VehicleYear = '" & VehicleYear.Text & "', VehicleColor = '" & VehicleColor.text & "', VehicleLicenseNumber = '" & VehicleLicenseNumber.text & "' WHERE StudentID = " & StudentID.Text

cmd = New OleDBCommand (MySQL, MyConn)
MyConn.Open ()
intCount = cmd.ExecuteScalar()
MyConn.Close ()
Response.Redirect("WinnersAreaSuccessPage.html")
End If

End Sub

[pclement]

I think you need to use the CInt function to convert the data type value returned from ExecuteScalar:

intCount = CInt(cmd.ExecuteScalar())

[jbricher]

I tried that and still received the same error on the same line. I'm sorry i didn't mention this before, but all fields are text fields. Thanks for your help

- Joe

[pclement]

OK, after looking at your code again I noticed you have two calls to ExecuteScalar. Which one is failing? First call or second call?

[jbricher]

The second call is the one which is giving me the error.

[pclement]

ExecuteScalar does not apply to an Update query. It's for returning a single value rowset. You should use ExecuteNonQuery().

cmd.ExecuteNonQuery()

[jbricher]

Should I change both to cmd.ExecuteNonQuery() or just the last one?

[jbricher]

Thank you for your help, I got it to work. It turns out I had a minor error within the code that was causing the error. I appreciate your help. Thank you.

- Joe