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](http://stackoverflow.com/questions/16217464/trying-to-insert-datetime-now-into-date-time-field-gives-data-type-mismatch-er) — Steve, Mar 11 '14 at 14:16

Steve · Accepted Answer · 2014-03-11T14:37:28.173

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 '14 at 14:37

answered Mar 11 '14 at 14:24

Steve

213,761
22
232
286

Did you try without the quotes around the parameters? – Steve Mar 11 '14 at 14:29
Oh well, glad to be of help, see you again on SO – Steve Mar 11 '14 at 14:34
Poor DB engine was probably all confused receiving `VALUES (''Jeremy', ...` – PsychoData Mar 11 '14 at 14:37

How to write Date to Access DB from VB.net

1 Answers1