Data type mismatch in criteria expression. MS Access VB

Question

1.00/5 (1 vote)

See more:

, +

VB

' OK button
   Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
       Dim con As New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=C:\Users\Jill\Desktop\saddbase\Sadsystem\Sadsystem\bin\Debug\tenant.mdb")
       Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM info WHERE TN_ID = '" & UsernameTextBox.Text & "' AND Password = '" & PasswordTextBox.Text & "' ", con)
       con.Open()
       Dim sdr As OleDbDataReader = cmd.ExecuteReader()
       ' If the record can be queried, Pass verification and open another form.
       If (sdr.Read() = True) Then
           MessageBox.Show("The user is valid!")

           Me.Hide()
       Else
           MessageBox.Show("Invalid Tenant ID or password!")
       End If

When I run the program there's an error in cmd.ExecuteReader(). *Data type mismatch in criteria expression* please help how to fix this error.

Posted 1-Oct-13 4:11am

GeeJay Luna

Updated 1-Mar-18 17:36pm

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ron Beyer · Answer 1 · 2013-10-01T04:22:00

Solution 1

I'm guessing your username column is not really named TN_ID, but something closer to Username. The TN_ID column is probably your primary key and you are trying to use equality to compare it with a text value, which is a data type mismatch.

Posted 1-Oct-13 4:22am

Ron Beyer

Govindaraj Rangaraj · Answer 2 · 2013-10-01T05:43:00

TN_ID could be a numeric field (possibly ID field as Ron suggested). Check for it's datatype in the database. If it is numeric field then the error makes sense as you are passing string value "UsernameTextBox.Text" into it.

If TN_ID is the username field then
"Integer.Parse(UsernameTextBox.Text)" would do, provided username is combination of numbers, also change it is userid to make more sense.

else if TN_ID is not the username field then
change the TN_ID to right field name (keep in mind the datatype conversion).

else, if TN_ID should hold username which in turns contains alphabets, convert the TN_ID field to varchar (if numeric).

basurajkumbhar · Answer 3 · 2013-10-02T02:37:00

what is the data type of the tn_id and password i don't know
please check the datatypes
If TN_ID is integer and password is string then try
("SELECT * FROM info WHERE TN_ID = " & UsernameTextBox.Text & " AND Password = '" & PasswordTextBox.Text & "' ", con)

if TN_ID is string and password is string then try this
("SELECT * FROM info WHERE TN_ID = '" & UsernameTextBox.Text & "' AND Password = '" & PasswordTextBox.Text & "' ", con)

Member 10474579 · Answer 4 · 2014-11-16T19:50:00

Solution 4

in Access DB, when we use select Query, we have to use TN_ID = " & UsernameTextBox.Text & " instead of TN_ID = '" & UsernameTextBox.Text & "' for number data type.
Because '" & any-value & "' is used for String value and " & any-value & " used for integer or number value from database. (') is used for Sting data. for number we should remove it like this " & any-value & "

Posted 16-Nov-14 19:50pm

Member 10474579

Comments

Russell.BD 24-Oct-15 10:52am

Thanks for your clear explanation. It works for me!

Member 10295316 10-Jan-18 14:28pm

Thanks for your Explanation. I ran into the same problem but your solution work for me

Member 15016268 9-Dec-20 4:41am

Thankyou it works for me .

Data type mismatch in criteria expression. MS Access VB

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0