[RESOLVED]MS Access 2003+VB.Net 2005+Data type mismatch

[Wulfgur]

Hey,

I'm currently using Visual Basic 2005, MS Access 2003 and OleDb Connection.
This is my database module i use:

Module Database

    Public Connection As New OleDb.OleDbConnection
    Public ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database.mdb"
    Public Command As OleDb.OleDbCommand
    Public Reader As OleDb.OleDbDataReader

End Module

This is the code i am trying to execute.

            Dim i
            Dim Customer As String
            Dim items As String
            Dim price As Double

            Command = Connection.CreateCommand

            For i = 0 To Checkoutbox.Items.Count - 1
                Customer = Customerlist.SelectedItem.ToString

                items = items + "and " + Checkoutbox.Items.Item(i).text

                price = price + Checkoutbox.Items.Item(i).SubItems(1).Text()

Command.CommandText = "INSERT INTO Transactions VALUES ('" & "" & "','" & Customer & "','" & items & "','" & price & "','" & System.DateTime.Today & "');"
              
            Next
            
  Command.ExecuteNonQuery()

The Error is: Data type mismatch in criteria expression.

The first field in my database is an autonumber field.
When inserting data into the table i want the autonumber field to just fill itself in. I don't know what to put into the database statement.

Command.CommandText = "INSERT INTO Transactions VALUES ('" & "" & "','" & Customer & "','" & items & "','" & price & "','" & System.DateTime.Today & "');"

The Fields for the database are:
Transaction_ID: Data Type: Autonumber - Primary Key
Transaction_Customer: Data Type: Text
Transaction_Items: Data Type: Text
Transaction_Price: Data Type: Currency
Transaction_Date: Data Type: Date/Time

Hope that makes sense.

[RobDog888]

Waht is your last field's data type? You are inserting a date/time value but as a string? Also since you are not listing out your fields you need to make sure you are inserting the correct values in the correct fields as it goes by order when not listed.

[Wulfgur]

Waht is your last field's data type?

The last fields data type is: Date/Time

Yes i am inserting the values in the correct order i have checked.

I am trying to find out what i have to put into the statement if the data type is: Autonumber

Command.CommandText = "INSERT INTO Transactions VALUES ('" & "What do i put in here?" & "','" & Customer & "','" & items & "','" & price & "','" & System.DateTime.Today & "');"

What do i put into the section "What do i put in here" If the datatype is an autonumber

[RobDog888]

You leave the autonumber field out of the statement.

Since the last field is date/time you should not be using quotes around the value. Use the # character instead which signifies a date literal.

[techgnome]

Well, that aside, you're getting errors because you didn't list out the fields in the table to insert into.... so it's taking the first value and trying to stuff it into the first field in the table, your autonumber.... clearly that aint' going to work.

it should be a fully qualified insert, like this:

INSERT INTO tblMine (Field2, Field3, Field4,...Fieldx) VALUES ('Field2Val', 1, 3,...'ValueX')

Note, that I SKIPPED Field1.... since it is an autonumber, we do NOT want to insert any data into it.... so we skip it (in effect, this attempts to insert a NULL value and force the DB to recognize that it is an autonumber and generate the next number in the sequence.)


-tg

[Wulfgur]

Edit: trying that now.

[nick2k3]

Edit: trying that now.

since you're using ADO.NET? why don't you use parameters?

[Wulfgur]

Ok thanks all for the help, it seems to be working fine.

[RobDog888]

If everything is solved on this topic now dont forget to Resolve your thread from the Thread Tools menu so other members know its done.