Double Verification in Access


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 12 of 12

Thread: Double Verification in Access

  1. #1
    Join Date
    Feb 2005
    Posts
    34

    Double Verification in Access

    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

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Which line causes the error? I don't see an AND statement in your code.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Feb 2005
    Posts
    34
    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

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    Quote Originally Posted by jbricher
    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'"
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  5. #5
    Join Date
    Feb 2005
    Posts
    34
    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

  6. #6
    Join Date
    Dec 2003
    Posts
    2,750
    I think you need to use the CInt function to convert the data type value returned from ExecuteScalar:

    intCount = CInt(cmd.ExecuteScalar())
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  7. #7
    Join Date
    Feb 2005
    Posts
    34
    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

  8. #8
    Join Date
    Dec 2003
    Posts
    2,750
    OK, after looking at your code again I noticed you have two calls to ExecuteScalar. Which one is failing? First call or second call?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  9. #9
    Join Date
    Feb 2005
    Posts
    34
    The second call is the one which is giving me the error.

  10. #10
    Join Date
    Dec 2003
    Posts
    2,750
    ExecuteScalar does not apply to an Update query. It's for returning a single value rowset. You should use ExecuteNonQuery().

    cmd.ExecuteNonQuery()
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  11. #11
    Join Date
    Feb 2005
    Posts
    34
    Should I change both to cmd.ExecuteNonQuery() or just the last one?

  12. #12
    Join Date
    Feb 2005
    Posts
    34
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center