Sending Dates with ADO.NET to MS-Access 1

[Crookshanks]

Hello,

I've got a litte problem with sending dates to my MS-Access Database. It just keeps comming up with the message 'Data Type Mismatch in expression". With trial and error I found out that the date field cause the problem.

....
 pStartDate = New OleDb.OleDbParameter("@StartDate", OleDb.OleDbType.DBDate)
pEndDate = New OleDb.OleDbParameter("@EndDate", OleDb.OleDbType.DBDate)
pLastUpdated = New OleDb.OleDbParameter("@LastUpdated", OleDb.OleDbType.DBDate)
....
'myCommand.CommandText = "UPDATE tblContracts SET [CounId] = @CounId, [DepId] = @DepId, [Desc] = @Description, " & _
                '          "[StartDate] = @StartDate, [EndDate] = @EndDate, [TermOfNotice] = @TermOfNotice, [Administrator] = @Administrator, " & _
                '          "[Version] = @Version, [LastUpdated] = @LastUpdated, [Period1] = @Period1, [Period2] = @Period2, " & _
                '          "[Notify] = @Notify, [Path] = @Path, [Notes] = @Notes, [ValueCon] = @ValueCon WHERE [ConId] = " & selectedContract
.....
pStartDate.Value = "#" & Format(txtStartDate.Text, "Short Date") & "#"
pEndDate.Value = "#" & Format(txtEndDate.Text, "Short Date") & "#"
pLastUpdated.Value = "#" & Format(txtLastUpdated.Text, "Short Date") & "#"
....
with mycommand.parameters 
...
.Add(pStartDate)
.Add(pEndDate)
.Add(pLastUpdated)
...
End with

With the code above the execution of the UPDATE Query fails. When I type the data hardcoded like #12/02/02# in the Query it works. But I just can't get it to work with the parameters.

Q1: Is the query incorrect or is it just not en sync with the underlying database?
Q2: I supplied a small part of my code. The fields in the Access database are of the type 'Date Time' and subtype 'General Date' which is de default value. Does anybody see something that could be wrong.

Well Thanks in advance. I will keep up stuggling
Regards,

 

[Crookshanks]

Problems solved!!...

After all the struggling I finally read somewhere dat oledbcommands do not support names in parameters (sqldbcommand do!).

This implecates that the order of the collection parameters is of vital importance, this should be 'n sync with the query definition in the commandtext. That is why I got mixed up with all those data type conversion errors.

Learning all the time.

 

[Takis]

A suggestion, may be silly,
When try
pStartDate.Value = CDate(txtStartDate.Text, "Short Date"))
test the value with
Console.writeLine(pStartDate.Value)

You could also check the format of the field in the database.I believe that you must be sure that the DATE FIELD is a short date

regards,

 

[Crookshanks]

The actual data format was not wrong, just the order of the parameters.