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

[aerohn]

I have an error "ERROR [22018] [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression". I am using VBdotNet2005 and MS Access as its database, and I can't really get the hang of this error. I've been trying to google the answer but I cannot find solution for this. What is the possible reason why this error raises? Thanks in advance... If you want me to provide my code, pls tell me. I'm new here in daniweb.com :)

[GeekByChoiCe]

Data type mismatches mostly happens if you try to fill your dataset field with a value that is not of the type as in your database. In example you try to fill an image field into a Integer field.
So please post the table definition and the query you use to fill your dataset.

[aerohn]

Data type mismatches mostly happens if you try to fill your dataset field with a value that is not of the type as in your database. In example you try to fill an image field into a Integer field.
So please post the table definition and the query you use to fill your dataset.

Private Sub btnContinue_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnContinue.Click
        Dim LibDS As DataSet = New DataSet
        Dim LibDA As OdbcDataAdapter = New OdbcDataAdapter("SELECT * FROM tblUserAccount WHERE Uname='" & txtUserName.Text & "'", LibConn)
        LibDS.Tables.Add("tblUserAccount")
        LibDA.Fill(LibDS, "tblUserAccount")

        If LibDS.Tables(0).Rows(0)("Uname").ToString = Me.txtUserName.Text AndAlso LibDS.Tables(0).Rows(0)("Password").ToString = Me.txtPassword.Text Then
            varUserID = LibDS.Tables(0).Rows(0)("UserID")
            UserRole()
        Else
            MessageBox.Show("The Password you supplied is not valid." & vbCrLf & "Click OK to continue.", "INVALID PASSWORD", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
    End Sub

    Private Sub UserRole()
        Dim libDS As DataSet = New DataSet
        Dim LibDA As OdbcDataAdapter = New OdbcDataAdapter("SELECT * FROM tblRoles WHERE UserID='" & varUserID & "'", LibConn)
        libDS.Tables.Add("tblRoles")
        LibDA.Fill(libDS, "tblRoles")

        Dim ActualRole As String = libDS.Tables(0).Rows(0)("tblRoles").ToString
        If ActualRole = "Member" Then
            Me.Close()
            MsgBox("Accepted!")
        End If
    End Sub

I made it precise so that it can be understood clearly - I tried to remove the unnecessary codes... The error occurs n

LibDA.Fill(libDS, "tblRoles")

under the

Private Sub UserRole()

... The

varUserID

variable has been declared as Long type and in my MS-Access datatype, it was

Number(Long Integer)

type.

[GeekByChoiCe]

Your error lays in this line:

Dim ActualRole As String = libDS.Tables(0).Rows(0)("tblRoles").ToString

You are trying to get the value from column "tblRoles". I have my doubts that you have named the column the same as the tablename. So please check what name the column, that holds the role-name, in your table tblRoles have.
Should be something like

Dim ActualRole As String = libDS.Tables(0).Rows(0)("RoleName").ToString

Also this won't work out:

If ActualRole = "Member" Then
	Me.Close()
	MsgBox("Accepted!")
End If

You never will see the messagebox, because you close the window before showing the box. You should switch the two lines.

[aerohn]

Your error lays in this line:

Dim ActualRole As String = libDS.Tables(0).Rows(0)("tblRoles").ToString

You are trying to get the value from column "tblRoles". I have my doubts that you have named the column the same as the tablename. So please check what name the column, that holds the role-name, in your table tblRoles have.
Should be something like

Dim ActualRole As String = libDS.Tables(0).Rows(0)("RoleName").ToString

Also this won't work out:

If ActualRole = "Member" Then
	Me.Close()
	MsgBox("Accepted!")
End If

You never will see the messagebox, because you close the window before showing the box. You should switch the two lines.

Oh, I'm sorry, I was wrong putting that name. I tried to change it and use the name of my column. It's

Role

... The error is still the same, I really don't know where that error lays in. Since you're helping me out and trying to solve my problem, I think it's better to send you my database. I tried to browse your information n your profile and I found this email-address [e-mail] kindly check this out... I'm a newbie in dotNet and please help me solve my problem. This will help me open up to solve the next error I might encounter. I'm trying to solve this as well. My program that I am trying to build is Library Management System and as you could see in my code, I'm still in login(Sad to say). Thanks loads!

[GeekByChoiCe]

Righteo, found the problem...
You save the role in small letters so
If ActualRole = "Member" Then
should become:
If ActualRole = "member" Then
This will work....

Private Sub UserRole()
		Dim libDS As DataSet = New DataSet
		Dim LibDA As  New OleDbDataAdapter("SELECT * FROM tblRoles WHERE UserID=" & varUserID , LibConn)
		libDS.Tables.Add("tblRoles")
		LibDA.Fill(libDS, "tblRoles")

		Dim ActualRole As String = libDS.Tables(0).Rows(0)("Role").ToString
		If ActualRole = "member" Then
			MsgBox("Accepted!")
			Me.Close()
		End If
	End Sub

[aerohn]

Righteo, found the problem...
You save the role in small letters so
If ActualRole = "Member" Then
should become:
If ActualRole = "member" Then
This will work....

Private Sub UserRole()
		Dim libDS As DataSet = New DataSet
		Dim LibDA As  New OleDbDataAdapter("SELECT * FROM tblRoles WHERE UserID=" & varUserID , LibConn)
		libDS.Tables.Add("tblRoles")
		LibDA.Fill(libDS, "tblRoles")

		Dim ActualRole As String = libDS.Tables(0).Rows(0)("Role").ToString
		If ActualRole = "member" Then
			MsgBox("Accepted!")
			Me.Close()
		End If
	End Sub

ahmmm It doesn't work on me... the error lays in the LibDA.Fill(libDS, "tblRoles")
I'm gonna send you my program and debug it yourself. whaaaa I'm going crazy with this error...

[GeekByChoiCe]

alright, awaiting your project.

[aerohn]

alright, awaiting your project.

I got the error... Thanks for trying help me out :)