data type mismatch in criteria expression

Question

0.00/5 (No votes)

See more:

Hi,

I have made a program using vb.net and mysql. But i decided to convert it using access so i can make .exe trial version just in case.

But upon converting i got this error in one specific function

Database
Table Name: Tbl_Attendance
Specific Field Name: F_Date

Objects

Date time picker name: txt_from.Text
Date time picker name: txt_to.Text
Format: yyyy-MM-dd (i.e. 2012-09-05)

I am creating a specific function that once a click a button it will delete records from txt_from.Text to txt_to.Text

This are my codes:

VB

Private Sub btn_ok_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_ok.Click
      If MessageBox.Show("WARNING: Be very careful when deleting records. You cannot undo this procedure!" + vbCrLf + "Do you still want to proceed?", "Attention!!!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
          open_connection1()
          cmd.CommandText = "delete from tbl_attendance where F_date >= '" & txt_from.Text & "' and F_date <= '" & txt_to.Text & "'"
          cmd.ExecuteNonQuery()
          MessageBox.Show("Time table now is clear from  " + txt_from.Text + " to " + txt_to.Text, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
          cn.Close()
      End If
  End Sub

Error: "data type mismatch in criteria expression"

I am very frustrated with this error. please help

Posted 5-Sep-12 5:21am

JMAM

Add a Solution

Comments

[no name] 5-Sep-12 11:27am

"txt_from.Text" is a string not a datetime.

JMAM 5-Sep-12 11:35am

But the code above works perfectly when u used vb.net and mysql

2 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)

BheemG · Answer 1 · 2012-09-05T18:27:00

Try this.

cmd.CommandText = "delete from tbl_attendance where F_date >= #" & Convert.toDatetime(txt_from.Text) & "# and F_date <= #" & Convert.toDatetime(txt_to.Text) & "#"

cmd.CommandText = "delete from tbl_attendance where F_date >= #" & txt_from.Text & "# and F_date <= #" & txt_to.Text & "#"

Dnt use '' for date in ms access only ## will work.

Both will work if not second command will work for sure.(Tested)

Hope This Helps.

hitesh_tech · Answer 2 · 2012-09-05T05:31:00

Solution 1

Trying using # for access dates like below

VB

cmd.CommandText = "delete from tbl_attendance where F_date >= '#" & txt_from.Text & "#' and F_date <= '#" & txt_to.Text & "#'"

Posted 5-Sep-12 5:31am

hitesh_tech

Comments

JMAM 5-Sep-12 11:35am

Thanks for the reply unfortunately it didn't work

BheemG 6-Sep-12 0:34am

Dnt use both '# for date its wrong.