How to write Date to Access DB from VB.net

Question

I'm having trouble writing a date to a database. I've been looking all over the place and seen many conflicting standards.

Basically I keep getting a type mismatch. I'm sure it's something simple that I'm not seeing.

my code is below. Basically, I make a query, add the parameters, and try to run it. Name and phonenumber are text, Product Ordered should be a number (productID more specifically), and date is a date.

Why am I getting a mismatch when command runs?

        Dim sqlCMD
        sqlCMD = "" + _
                       "INSERT INTO Orders " + _
                       "    ([NAME], " + _
                       "    [PhoneNumber], " + _
                       "    [ProductOrdered], " + _
                       "    [OrderDate]) " + _
                       "VALUES      ('@CustomerName', " + _
                       "    '@PhoneNumber', " + _
                       "    '@ProductOrdered', " + _
                       "    '@OrderDate'); "

        Dim connection As OleDb.OleDbConnection = New OleDb.OleDbConnection()
        connection.ConnectionString = ConnectionStringConst

        Dim command As OleDb.OleDbCommand = New OleDb.OleDbCommand(sqlCMD, connection)
        command.Parameters.Add("@CustomerName", OleDb.OleDbType.Char).Value = txtOrderCustName.Text
        command.Parameters.Add("@PhoneNumber", OleDb.OleDbType.Char).Value = txtOrderPhoneNum.Text
        command.Parameters.Add("@ProductOrdered", OleDb.OleDbType.Integer).Value = cmbOrderItem.SelectedIndex
        command.Parameters.Add("@OrderDate", OleDb.OleDbType.DBDate).Value = DateTime.Now


        Try
            connection.Open()   
            command.ExecuteNonQuery()      'Perform our query and insert into the table 
        Catch ex As Exception
            MsgBox("ERROR - Your query may not have completed" + vbNewLine + vbNewLine + _
                   "Error Message: " + ex.Message)
        Finally
            connection.Close()  
        End Try

Hope this helps. It is really frustrating Trying to insert DateTime.Now into Date/Time field gives "Data type mismatch" error — Steve
– Steve, Commented Mar 11, 2014 at 14:16

Steve · Accepted Answer · 2014-03-11 14:37:28Z

2

The reason of your datatype mismatch is the presence of single quotes around the parameter names Basically you pass a string instead of a number or a date

   sqlCMD = "" + _
                   "INSERT INTO Orders " + _
                   "    ([NAME], " + _
                   "    [PhoneNumber], " + _
                   "    [ProductOrdered], " + _
                   "    [OrderDate]) " + _
                   "VALUES      (@CustomerName, " + _
                   "    @PhoneNumber, " + _
                   "    @ProductOrdered, " + _
                   "    @OrderDate); "

This could be the real source of your type mismatch.

edited Mar 11, 2014 at 14:37

answered Mar 11, 2014 at 14:24

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Steve Over a year ago

Did you try without the quotes around the parameters?

Steve Over a year ago

Oh well, glad to be of help, see you again on SO

PsychoData Over a year ago

Poor DB engine was probably all confused receiving VALUES (''Jeremy', ...

Collectives™ on Stack Overflow

How to write Date to Access DB from VB.net

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related