[RESOLVED] Weird issue inserting date into Access table

[nbrege]

I'm trying to insert some data into an Access database table that includes a date column. If I hardcode the date in a variable it works just fine. However if I use Date.Now it throws an error. See code below:

            Using con As OleDbConnection = New OleDbConnection(MDBConnString), cmd As New OleDbCommand("", con)
                con.Open()

                Try

                    'Dim myDate As Date = #11/10/2025 04:26:07 PM#    'this works
                    Dim myDate As Date = Date.Now   'this fails

                    'insert the New number
                    cmd.CommandText = "insert into myTable (Type,Num,PartNum,Who,EntryDate) values (?,?,?,?,?)"

                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("Type", type)
                    cmd.Parameters.AddWithValue("Num", newNum)
                    cmd.Parameters.AddWithValue("PartNum", newPartNum)
                    cmd.Parameters.AddWithValue("Who", Environment.UserName.ToLower)
                    cmd.Parameters.AddWithValue("EntryDate", myDate)

                    cmd.ExecuteNonQuery()


                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try

            End Using

The error I'm getting is "Data type mismatch in criteria expression." My hard coded date is the same format as what Date.Now returns, so I can't understand why one works & the other doesn't. Can anyone see what I'm doing wrong?

[dday9]

AddWithValue implicitly sets the parameter type and it sounds like it is failing. Use the .Add("...", OleDbType.{type}).Value = "..." approach instead;

cmd.Parameters.Add("@EntryDate", OleDbType.Date).Value = myDate

[nbrege]

dday ... thanks, I'll give that a try.

[jmcilhinney]

That does seem very strange. You're providing a binary Date value either way, so it is bizarre that one would work while the other would not. What is the data type of the EntryDate column in the database?

[Zvoni]

Irrespective of the weird issue: Why in blazes are you passing the Date as a Param in the first place?
Let Access do it.
Rule of thumb: If you want to insert an information, which the Database itself can create, then never ever pass it from outside
Aircode

cmd.CommandText = "insert into myTable (Type,Num,PartNum,Who,EntryDate) values (?,?,?,?,Date())"

                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("Type", type)
                    cmd.Parameters.AddWithValue("Num", newNum)
                    cmd.Parameters.AddWithValue("PartNum", newPartNum)
                    cmd.Parameters.AddWithValue("Who", Environment.UserName.ToLower)
                    'cmd.Parameters.AddWithValue("EntryDate", myDate)  'Commented out

                    cmd.ExecuteNonQuery()

[nbrege]

That does seem very strange. You're providing a binary Date value either way, so it is bizarre that one would work while the other would not. What is the data type of the EntryDate column in the database?

The column is a Date/Time data type. Ddays suggestion did work. I also found that converting the date to a string & back to a date works:

CDate(Date.Now.ToString)

[nbrege]

Irrespective of the weird issue: Why in blazes are you passing the Date as a Param in the first place?
Let Access do it.
Rule of thumb: If you want to insert an information, which the Database itself can create, then never ever pass it from outside
Aircode

cmd.CommandText = "insert into myTable (Type,Num,PartNum,Who,EntryDate) values (?,?,?,?,Date())"

                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("Type", type)
                    cmd.Parameters.AddWithValue("Num", newNum)
                    cmd.Parameters.AddWithValue("PartNum", newPartNum)
                    cmd.Parameters.AddWithValue("Who", Environment.UserName.ToLower)
                    'cmd.Parameters.AddWithValue("EntryDate", myDate)  'Commented out

                    cmd.ExecuteNonQuery()

Agreed, a better way to do it. I will change my code.


***Edit***

I ended up using Now() instead of Date() because I want the full timestamp:

insert into myTable (Type,Num,PartNum,Who,EntryDate) values (?,?,?,?,Now())

[Zvoni]

Agreed, a better way to do it. I will change my code.


***Edit***

I ended up using Now() instead of Date() because I want the full timestamp:

insert into myTable (Type,Num,PartNum,Who,EntryDate) values (?,?,?,?,Now())

Well, there you go.
Glad i could help