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,

 

[chrissie1]

these things

.Add(pStartDate)

should have a type added to them so the command knows what type they are suppossed to be and make it work and then you should be able to feed a date into it.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[Crookshanks]

Thanks for your quick reply, but I am still confused. When I change the code to

 .Add(pStartDate, OleDb.OleDbType.DBDate)

then the system does not accept it. At the top of the supplied code I declare the parameter as a certain type.

pStartDate = New OleDb.OleDbParameter("@StartDate", OleDb.OleDbType.DBDate)

So, I am not sure why I should tell it the system again. Perhaps I am overlooking something or think in the wrong way.

Regards (from Holland)

 

[chrissie1]

sorry I didn't see that.
after looking a bit closer I think the problem is here

pStartDate.Value = "#" & Format(txtStartDate.Text, "Short Date") & "#"

you should feed it a date not a string
so more like this

pStartDate.Value = cdate(txtStartDate.Text)

I don't think it cares if it is short or long it will adapt itself to what is in the database.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[Crookshanks]

Thanks again for your quick reaction. But I already tried that. In the catch of the try I wrote a little line to show what data the system is trying to send:

 For Each par As OleDb.OleDbParameter In myCommand.Parameters
            Debug.WriteLine(par.ParameterName.ToString & " " & par.Value.ToString & " " & par.OleDbType.ToString)
Next

This code gives the following output in the outputscreen:
@StartDate 15/01/05 00:00:00 DBDate
@EndDate 20/01/05 00:00:00 DBDate
@LastUpdated 30/01/99 00:00:00 DBDate
System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at ContractManager.Form_Contracts.cmdSubmit_Click(Object sender, EventArgs e) in ......

It beats me. Well, I will keep on trying. If you got other ideas please feel free to let me know. Thanks for your effort so far.

Regards,

 

[earthandfire]

None of your sample dates work in US format, so you could try:

pStartDate.Value = "#" & Format(txtStartDate.Text, "mm/dd/yyyy") & "#"


Hope this helps.

 

[Crookshanks]

Good guess. But another failure:

pStartDate.Value = ("#" & Format(txtStartDate.Text, "mm/dd/yyyy") & "#")
pEndDate.Value = ("#" & Format(txtEndDate.Text, "mm/dd/yyyy") & "#")
pLastUpdated.Value = ("#" & Format(txtLastUpdated.Text, "mm/dd/yyyy") & "#")

The outputbox gives:
@StartDate #mm/dd/yyyy# DBDate
@EndDate #mm/dd/yyyy# DBDate
@LastUpdated #mm/dd/yyyy# DBDate
System.FormatException: The string was not recognized as a valid DateTime. There is a unknown word starting at index 1.
at System.DateTimeParse.Lex(Int32 dps, __DTString str, DateTimeToken dtok, DateTimeRawInfo raw, DateTimeResult result, DateTimeFormatInfo& dtfi)
at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
at System.DateTime.Parse(String s, IFormatProvider provider, DateTimeStyles styles)

Problem must be somehting with the format. It is so frustrating as hardcoded #12/02/04# just works... grrr
Regards,

 

[earthandfire]

The parameter has been typed as a date; 12/02/04 works in both US and European formats - so - just maybe????

pStartDate.Value = CDate(Format(txtStartDate.Text, "mm/dd/yyyy"))


Hope this helps.

 

[Crookshanks]

Tried it. But not a positive result unfortunately.

The system comes with the following error:
Additional information: Cast from string "mm/dd/yyyy" to type 'Date' is not valid.

FYI: I am using the english date notation. Short date format is mm/dd/yy.

 

[earthandfire]

The Format function should format the first parameter in the syle of the second parameter - so I don't understand why my last two suggestions returned:

#mm/dd/yyyy# and "mm/dd/yyyy" respectively.


Silly qustion but ... are you sure that you have valid data in the textbox?

Hope this helps.

 

[Crookshanks]

It is not a silly question. My mistake could be anywhere.

But I have checked ...
The values in the textboxes come directly from the database itself. I am actually overwriting the values to be sure it is the correct format in the Accessdatabase.

The values are: 01/12/05, 05/01/09, 01/01/05
Nothing wrong with them seems to me.

The Format function should format the first parameter in the syle of the second parameter..

I am not sure what you mean by this.
Your help is very much appreciated, I am stuck for hours.

 

[earthandfire]

As a simple example: Format(1.234, "0.00") will format the first parameter 1.234 in the style of "0.00" (the second parameter) and return a string "1.23". So what I meant basically was why is the return value from Format, the format string itself ie "mm/dd/yy".

As far as what is going wrong, I'm afraid I've got no more ideas.

 

[Crookshanks]

I see. . Ok, many thanks anyway. I'll fix it eventually.

 

[SqueakinSweep]

Hmmm....shouldnt format be "MM/dd/yy" or "dd/MM/yy".
Capital M for month, little m for minutes?


Sweep
...if it works dont f*** with it
...if its f****ed blame someone else
...if its your fault that its f***ed, say and admit nothing.

 

[Crookshanks]

Hello Sweep, thanks for your suggestion. Does not help although.

The properties of the parameter (Name, Value, Type) are:
@StartDate 15/01/05 00:00:00 DBDate
@EndDate 20/01/05 00:00:00 DBDate
@LastUpdated 30/01/99 00:00:00 DBDate

This seems OK to me.


I am using the following format command (Does not give an error message).

pStartDate.Value = CDate(Format(txtStartDate.Text, "Short Date"))

I am out of ideas :-(. I think I will try a data binding instead of all these parameters.

Q: Is there a way to see the actual QUERY with its filled parameters which is send to the Database?

Regards,

 

[earthandfire]

Final thought:

pStartDate.Value = CType(txtStartDate.Text, DBDate)


Hope this helps.

 

[earthandfire]

Final final thought:

pStartDate.Value = CType(Format(txtStartDate.Text, "short date"), DBDate)


Hope this helps.

 

[Crookshanks]

Hehe, Thanks.
DbDate is not supported by the CType methode. So I tried:

pStartDate.Value = CType(Format(txtStartDate.Text, "short date"), Date)
pEndDate.Value = CType(Format(txtEndDate.Text, "short date"), Date)
pLastUpdated.Value = CType(Format(txtLastUpdated.Text, "short date"), Date)

And it worked!! Thanks to you all
Finally I can focus on another problem... but that's another story .... Cheers!

 

[chrissie1]

could you show us the results of The properties of the parameters (Name, Value, Type)

because then this should work aswell

pStartDate.Value = Cdate(Format(txtStartDate.Text, "short date"))

and I was just wondering why this
pStartDate.Value = Cdate(txtStartDate.Text)

didn't work and what the difference was when you look at the results?




Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[Crookshanks]

The results of the parameter properties were the same. The type of the parameter is not actually changes when you use the CType methode. The Type property is set with

pStartDate = New OleDb.OleDbParameter("@StartDate", OleDb.OleDbType.Date)

It think it is because Access does not know somehting about DBDate as Type... I am still not sure what happens exactly as now I am trying to deal with string variables.

pDesc = New OleDb.OleDbParameter("@Desc", OleDb.OleDbType.Char)

And these give the same error as the dates. No I am sure Access does not recognize Char as Type, but it should map to String Type). So I am still investigating...

To answer your question, here are the properties:

pStartDate.Value = CType(Format(txtStartDate.Text, "short date"), Date)

gives:
@StartDate 15/01/00 00:00:00 Date

pStartDate.Value = Cdate(txtStartDate.Text)

gives:
@StartDate 12/12/05 00:00:00 Date

So, no difference.
Q: Does anybody know about Type diffulcities working with an Access database at the back?