Question Inserting date values in Access tables

[pranav_vaidya]

Hi,
I am using Access 97 and VB .Net 2008.
I am tring to insert 3 values in a table and my code always fails for the Date value. I am getting "System.Data.OleDb.OleDbException: Data type mismatch in criteria expression." as exception.

Any help is much appreciated.

Below is my code-
Dim mTempDBCmd As OleDb.OleDbCommand
Dim mStrSQLInsert As String

Try
mStrSQLInsert = "INSERT INTO tbLeaveBooking (LeaveBookingNo,PS_ID,From_Date) VALUES " _
& " (?,?,?)"

If connDB Is Nothing Then Call ConnectToDB() 'Build DB connection
If connDB.State = 0 Then connDB.Open()

mTempDBCmd = New OleDb.OleDbCommand

With mTempDBCmd
.Connection = connDB
.CommandType = CommandType.Text
.CommandText = mStrSQLInsert

With .Parameters
.AddWithValue("lbbo", "10")
.AddWithValue("PS", mPSID)
.AddWithValue("fd", "'+" & mStartDate.ToString & "+'")
End With

.ExecuteNonQuery()
End With

mTempDBCmd = Nothing
connDB.Close()
SaveLeaveToDB = True

Catch ex As Exception
Debug.Print(ex.ToString)
mTempDBCmd = Nothing
connDB = Nothing
SaveLeaveToDB = False
End Try

Thanks,
Pranav
PN-Mh, India

 

[pranav_vaidya]

I have been able to sort it out as

.Add(New OleDb.OleDbParameter("fd", OleDb.OleDbType.Date)).Value = mStartDate

Thanks.

 

[cjard]

would have been easier to sort it out as:

.AddWithValue("fd", mStartDate)


By concatenating strings onto your date, you made the date into a string, so when AddWithValue came to guess the type, it found a string.. Always put the proper .NET type into AddWithValue