7 Replies - 6821 Views - Last Post: 06 April 2012 - 09:28 AM Rate Topic: -----

#1 kitty992  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 06-April 12

Problem - Data type mismatch in criteria expression.

Posted 06 April 2012 - 05:08 AM

Hi,
I'm having trouble inserting data from a visual basic form into MS Access.
I have used sql to connect the database and it works fine.
This is my code for inserting the book information.

"Insert Into tblBooks VALUES ('" & TextBox1.Text & _
                       "','" & TextBox2.Text & _
                       "','" & TextBox3.Text & _
                       "','" & TextBox4.Text & _
                       "','" & TextBox5.Text & _
                       "','" & TextBox6.Text & _
                       "','" & TextBox7.Text & _
                       "','" & dtpicker2.Value.ToShortDateString & _
                       "','" & TextBox10.Text & _
                       "','" & dtpicker1.Value.ToShortDateString & "')"
                    .ExecuteNonQuery()

                End With
                MsgBox("Book Info Added!", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "SUCCESS")


The problem I am having is the data type of the dtpicker1 and dtpicker2 is showing an error saying "Data type mismatch in criteria expression" when the program is executed.
The data type in the access database for dtpicker is date/Time.
I have tried changing all the dtpicker to textboxs to see if the information would be added to the database and it works perfect, therefore I have a feeling that the problem is the datepicker.

I would be very grateful for any help or advice.

Kitty


Is This A Good Question/Topic? 0
  • +

Replies To: Problem - Data type mismatch in criteria expression.

#2 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 206
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Problem - Data type mismatch in criteria expression.

Posted 06 April 2012 - 05:13 AM

Try without the .ToShortDateString. Just dtpicker2.Value
Was This Post Helpful? 1
  • +
  • -

#3 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1728
  • View blog
  • Posts: 5,707
  • Joined: 25-September 09

Re: Problem - Data type mismatch in criteria expression.

Posted 06 April 2012 - 05:18 AM

Also try wrapping dates with # instead of ' in Access
  "','" & TextBox7.Text & _
  "',#" & dtpicker2.Value.ToShortDateString & _
  "#,'" & TextBox10.Text & _
  "',#" & dtpicker1.Value.ToShortDateString & "#)"

Was This Post Helpful? 1
  • +
  • -

#4 kitty992  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 06-April 12

Re: Problem - Data type mismatch in criteria expression.

Posted 06 April 2012 - 05:18 AM

View PostnK0de, on 06 April 2012 - 05:13 AM, said:

Try without the .ToShortDateString. Just dtpicker2.Value



Thank you very much for your help unfortunately the same error has appeared.

Kitty
Was This Post Helpful? 0
  • +
  • -

#5 kitty992  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 06-April 12

Re: Problem - Data type mismatch in criteria expression.

Posted 06 April 2012 - 05:36 AM

Thank you very much for your help and advice, they both paid off.
I made the changes that was suggested and also changed the datatype to text within the database.

Once again thankyou

Kitty
Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1728
  • View blog
  • Posts: 5,707
  • Joined: 25-September 09

Re: Problem - Data type mismatch in criteria expression.

Posted 06 April 2012 - 06:18 AM

kitty992, why would you not store a date as a date? Storing them as a string will result in sorting issues, not to mention if you want to use the datetime structure for calculating against them, you will need to convert them back to an actual date.

The problem you're having is that Access' SQL interpreter handles dates differently so that is why you wrap them in #. To Denote that you are passing a date. Newer versions seem to have taken care of this but I haven't tested it.

What would really make your life easier would be to use parameters in your statements which can handle all of this behind the scenes, makes your statements easier to read, alleviates concatenation errors and most of all protects your database from SQL injection attacks.
Was This Post Helpful? 1
  • +
  • -

#7 kitty992  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 06-April 12

Re: Problem - Data type mismatch in criteria expression.

Posted 06 April 2012 - 08:27 AM

View PostCharlieMay, on 06 April 2012 - 06:18 AM, said:

kitty992, why would you not store a date as a date? Storing them as a string will result in sorting issues, not to mention if you want to use the datetime structure for calculating against them, you will need to convert them back to an actual date.

The problem you're having is that Access' SQL interpreter handles dates differently so that is why you wrap them in #. To Denote that you are passing a date. Newer versions seem to have taken care of this but I haven't tested it.

What would really make your life easier would be to use parameters in your statements which can handle all of this behind the scenes, makes your statements easier to read, alleviates concatenation errors and most of all protects your database from SQL injection attacks.



Thank you very much for your advice, unfortunately I dont know how to input the date using datepciker without using text as the data type, i have used the # to wrap the code however it still does not work
Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1728
  • View blog
  • Posts: 5,707
  • Joined: 25-September 09

Re: Problem - Data type mismatch in criteria expression.

Posted 06 April 2012 - 09:28 AM

OK, in SQL it's all just strings. What I mean by that is the SQL statement is nothing more than a string that is formatted in a syntax that the SQL interpreter understands. With access that means that you wrap text with '' (Apostrophes) you wrap dates with ## (Pound Signs) Numbers do not need to be wrapped and spaces in table and field names can confuse the interpreter so either don't use the or always wrap fields and table names with [] Square brackets.

The following statement shows those syntax rules

Insert into [My Table] (FirstName, LastName, HireDate, HoursWorked) VALUES ('Charlie', 'May', #02/27/1987#, 40)
Notice that this has wrapped the table (My Table) to denote that it is not two separate words
The values for FirstName and LastName are enclosed in apostrophes because they are text
the value for HireDate is wrapped in pound signs because it is a date
and the value for hoursworked can just be passed without any notations.
This of course means that HireDate needs to be a date type in the database and Hours worked needs to be a numeric type.

Now that being said. Your datetime picker has to methods of displaying the date, it has .Text which is a string representation what you have displayed in your datetimepicker and it has a .Value property which represents a datetime datatype.

For your statement, you can use the .Text property because you're just concatenating a string to another string (your sql statement) you just have to follow the syntax rules that applies.

Your statement structured like this should work:
"Insert Into tblBooks VALUES ('" & TextBox1.Text & _
                       "','" & TextBox2.Text & _
                       "','" & TextBox3.Text & _
                       "','" & TextBox4.Text & _
                       "','" & TextBox5.Text & _
                       "','" & TextBox6.Text & _
                       "','" & TextBox7.Text & _
                       "',#" & dtpicker2.Text & _
                       "#,'" & TextBox10.Text & _
                       "',#" & dtpicker1.Text & "#)"
                    .ExecuteNonQuery()


Now two other cautions by not specifying the fields side of the insert statement,
1. You need to be careful to ensure that you have put the values in the order that they appear in the table as you are not specifying (field to Value)
2. You have not missed any fields.

A better syntax in my opinion is to specify the fields in the statement so:
INSERT INTO Employee (FirstName, LastName, DOB, HireDate) VALUES ('Charlie', 'May', #10/27/1967#, #2/27/1987#)
This ensures that the interpreter knows what value goes to what field and will save a lot of headaches when trying to debug these types of errors.

The last thing is that I think you should take the time to read up on Parameterized Statments.

Oh yea, and taking the time to name your controls to what they hold will make it a lot easier to follow when you get into large code blocks.
What it TextBox1? For the sake of this explanation let's say that TextBox1 represents a Book Title. Therefore, naming txtBookTitle makes it more clear as to what information its .Text property represents.

I prefer the prefixing like I did above. If I know I have a textbox that holds something and can't remember the actual name, I can type me.txt and the intellisense will show me my list of controls starting with txt. The same thing goes for other controls like Combobox (cbo), ListBox (lb or lst) ListView (lvw) etc... Sure, you can go back to the form and click the control and retrieve the name but that's just an extra step taking you away from your coding.

This post has been edited by CharlieMay: 06 April 2012 - 09:37 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1