[RESOLVED] Data Type Mismatch

[Vladamir]

I am hacking through another tutorial this morning on how to use VB 2010 Express to add a new record to an existing MS Access database. The database has several fields in it including one which is a date field.

I found the following code at some website and it looked simple enough for my needs, that is to just learn how to add a new record to Access.

Imports System.Data.OleDb

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim myConnection As OleDbConnection
        Dim myCommand As OleDbCommand

        Dim mySQLString As String
        myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFiles\db1.mdb;")
        myConnection.Open()

        mySQLString = "INSERT INTO Table1 VALUES('','06/26/2012','','','FLG','33221','HATCH APD 38 x 42','Alum','Q1234','','Approved')"
        myCommand = New OleDbCommand(mySQLString, myConnection)
        myCommand.ExecuteNonQuery()

    End Sub
End Class

When I run this I get an error message which says "Data type mismatch in criteria expression." I assume it has something to do with the date field...but I'm still so new to this stuff.

[DataMiser]

MSAccess uses # around date values

#06/26/2012# rather than '06/26/2012'

[Vladamir]

Thanks...one of our DBA's came over and offered some assistance with this. We had to search google and even then it took us a couple of practice SQL commands inside of Access itself to figure out the syntax.

mySQLString = "INSERT INTO Table1 ( [Date] ) Values('06/21/2012');"

This is kind of abbreviated version of the previous statement as at this point it's all testing anyway. But it worked. I will try the #'s next to insure we have this right.

[Shaggy Hiker]

You also have single quotes around 33221. If the field is a string, then that would be correct. If the field is numeric, then you should remove the single quotes.

[dminder]

You should actually use parameters in all of your queries.

MyCommand.Parameters.Add("@ParameterName", OLEDBTYpe).Value = MyValue

This stops you from having to remember all the syntax and will also allow you more flexibility in your application.

HTH!!!

D