CODEWITHSUNDEEP
vb.net
Aaron
Aaron

Reputation: 1989

ERROR [22018] [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression

I have an error ERROR [22018] [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. and I've been trying to solve this but unfortunately I couldn't get the hack of this error. I'm a newbie in dotNet.

Public Sub LogInContinue(ByVal senter As Object, ByVal e As EventArgs)
    Dim LibDS As DataSet = New DataSet
    Dim LibDA As OdbcDataAdapter = New OdbcDataAdapter("SELECT * FROM tblUserAccount WHERE Uname='" & txtUserName.Text & "'", LibConn)
    Dim A As Integer
    LibDS.Tables.Add("tblUserAccount")
    A = LibDA.Fill(LibDS, "tblUserAccount")

    If A = 0 Then
        MessageBox.Show("Username you have supplied is invalid!" & vbCrLf & "Please click OK button to try again.", "INVALID USERNAME", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Else
        If LibDS.Tables(0).Rows(0)("Uname") = Me.txtUserName.Text AndAlso LibDS.Tables(0).Rows(0)("Password") = Me.txtPassword.Text Then
            NewUserID.UUserID = LibDS.Tables(0).Rows(0)("UserID")
            UserAcceptedRole()
        Else
            MessageBox.Show("Password you have supplied is invalid." & vbCrLf & "Please try again!", "PASSWORD MISMATCHED", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
    End If
End Sub

Private Sub UserAcceptedRole()
    Dim ActualRole As String
    Dim LibDSa As DataSet = New DataSet
    Dim LibDAa As OdbcDataAdapter = New OdbcDataAdapter("SELECT * FROM tblRoles WHERE UserID='" & NewUserID.UUserID & "'", LibConn)
    LibDSa.Tables.Add("tblRoles")
    LibDAa.Fill(LibDSa, "tblRoles")

    ActualRole = LibDS.Tables(0).Rows(0)("Role").ToString
    If ActualRole = "Member" Then
        lblWelcome.Text = "Welcome " & txtUserName.Text
        NewObject.NewLabel(lblID, "You are logged in as " & ActualRole, "Calibri", 12, FontStyle.Regular, 666, 40)
        btnLogIn.Text = "Logout"
    End If
End Sub

The error is in the code LibDAa.Fill(LibDSa, "tblRoles") under the Private Sub UserAcceptedRole(). Kindly tell me what this error is all about? and if you have some advice for the improvement of my code(style), kindly tell me. Thanks loads.

Upvotes: 0

Views: 8707

Answers (2)

Niranjan Singh
Niranjan Singh

Reputation: 18260

The User ID may be Integer type so try to remove '' from the query that surround the user id as '232' etc.. The error may be in the following statement:

Dim LibDAa As OdbcDataAdapter = New OdbcDataAdapter("SELECT * FROM tblRoles WHERE UserID='" & NewUserID.UUserID & "'", LibConn)

Check this: LibDS is local to this SUB

Public Sub LogInContinue(ByVal senter As Object, ByVal e As EventArgs)
    Dim LibDS As DataSet = New DataSet   /// local to this sub

How can you access this in another SUB UserAcceptedRole() 

ActualRole = LibDS.Tables(0).Rows(0)("Role").ToString

Best approach is that you pass the role as parameter.

Upvotes: 2

KV Prajapati
KV Prajapati

Reputation: 94653

Always use Parameters (Parameterized SQL statement). Read more on Parameters and SQL Injection.

Private Sub UserAcceptedRole()
    Dim ActualRole As String
    Dim LibDt As New DataTable
    Dim Cmd As New OdbcCommand
    Cmd.CommandText="SELECT * FROM tblRoles WHERE UserID=@UserID"
    Cmd.Connection=LibConn

    MsgBox("Verifying the value of : " & NewUserID.UUserID)

    Cmd.Parameters.Add("@UserID",System.Data.Odbc.OdbcType.Int).Value=NewUserID.UUserID

    Dim LibDAa As OdbcDataAdapter = New OdbcDataAdapter(Cmd)
    LibDAa.Fill(LibDt)

    If LibDt.Rows.Count<>0 Then

      ActualRole = LibDt.Rows(0)("Role").ToString

      If ActualRole = "Member" Then
        lblWelcome.Text = "Welcome " & txtUserName.Text
        ....
      End If
    End If
End Sub

PS: You may use DataSet too.

Upvotes: 1

Related Questions