Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 602
  • Last Modified:

Date parameter causing 'data type mismatch' for search of an Access db

I'm writing a pretty simple application to search an Access db. The query is:

Me.cmdSelectEvents.CommandText = "SELECT eventDetails.id, eventDetails.subject, " & _
"eventDetails.title, eventDetails.trainers, eventDetails.type, eventDetails.summary, " & _
"eventDetails.url, eventDates.date " & _
"FROM eventDates INNER JOIN eventDetails ON eventDates.id = eventDetails.id " & _
"WHERE (((eventDetails.subject)=@subject) AND ((eventDetails.type)=@type) AND ((eventDates.date)<@date));"

The parameters @subject and @type are simply strings selected from dropdown values.

The @date parameter is set by using a dropdown to select a timeframe to search in, specifically "Within 1 month", "Within 3 months", "Within 6 months". These options have the values "1", "3" and "6".

When the form is submitted the value of the selected option is passed into the following routine:

Dim valSelectedDate As Double
Dim dateParameter As Date
valSelectedDate = ddlDate.SelectedValue
dateParameter = DateAdd("m", valSelectedDate, (Date.Now))
cmdSelectEvents.Parameters("@date").Value = dateParameter

However I get the error message "Data type mismatch"

Watching the debug, I can see that Date.Now gives the value of #5/9/2005 12:22:33PM# (the time I ran the app of course).

With the value above and timespan criteria set to 6 months, the @date parameter is shown as being:

#11/9/2005 12:22:33PM# {Date}

...everything as it should be, but then as soon as it then tries to fill a dataset I get the error message

cnEventsDB.Open()
daEvents.Fill(dsEvents)
dgEvents.DataSource = dsEvents
dgEvents.DataBind()

Can anyone shed any light on this?
0
chgl
Asked:
chgl
  • 6
  • 6
  • 3
  • +1
1 Solution
 
riyasjefCommented:
Try this

dateParameter = DateAdd("m", Convert.ToDateTime(ddlDate.SelectedValue) , (Date.Now))

RJ
0
 
chglAuthor Commented:
RJ

Thanks - I've tried that but in Visual Studio.Net it shows that line of code as being incorrect. The tooltip message says:

"Overload resolution failed because no accessible 'DateAdd' can be called with these arguments:
'Public function DateAdd(Interval as String, Number as Double, DateValue as Object) As Date: Conversion from 'Date' to 'Double' requires calling the 'Date.ToOADate' method."

This seems to imply that that the procedure is trying to pass out the result as Double value rather than a Date which is the wrong way round.
0
 
b1xml2Commented:
is the dataset strongly typed or has the dataset loaded it schema first??!!!


also to add months, for e.g.
Now.AddMonths(6)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
chglAuthor Commented:
Its an untyped dataset with no schema
0
 
grayeCommented:
Try this...

"WHERE (((eventDetails.subject)=@subject) AND ((eventDetails.type)=@type) AND ((eventDates.date)<#@date#));"
0
 
chglAuthor Commented:
Sorry

Syntax error in date in query expression '(((eventDetails.subject)=@subject) AND ((eventDetails.type)=@type) AND ((eventDates.date)<#@date#))'.

I'm wondering now if its something to do with the date formats. The code passes them through in US format mm/dd/yyyy but in my database they are in UK format dd/mm/yyyy. Access seems to do this by default, I assume because that is the region setting on the machine the db was created on. Is there a .net equivalent of the ASP "session.LCID = " method that I can use to make my code work in the same format? Or will SQL only deal with US formatted dates?
0
 
b1xml2Commented:
web.config
=========
<configuration>
   <system.web>
      <globalization
         requestEncoding="iso-8859-1"
         responseEncoding="iso-8859-1"
       culture="en-GB"
       uiCulture="en-GB"
       />
   </system.web>
</configuration>

The code will now pass in UK Date Formate
0
 
chglAuthor Commented:
cheers but this isn't working either - i'm pulling my hair out at the mo about this.

As far as i can see the globalization config seems to be being ignored. The date is still passed to the parameter in the format mm-dd-yyyy. I've tried all kinds of things to change it to a different format e.g....

dateParameter = Date.Now.AddMonths(valSelectedDate)
dateParameter = dateParameter.ToUniversalTime()

It still comes out as #mm-dd-yyyy#

Arrgghhh!!!!
0
 
b1xml2Commented:
in debug mode, the date is seen in us date format!
What is your dateparameter type??

send in the datatype

if the datatype is OleDbType.DateTime, then all you need is to send in the DateTime value
0
 
chglAuthor Commented:
Thanks so much for your help - sorry if its dragging out a bit!

In answer to your question the command is set up as follows:

Me.cmdSelectEvents.CommandText = "SELECT eventDetails.id, eventDetails.subject, " & _
        "eventDetails.title, eventDetails.trainers, eventDetails.type, eventDetails.summary, " & _
        "eventDetails.url, eventDates.date " & _
        "FROM eventDates INNER JOIN eventDetails ON eventDates.id = eventDetails.id " & _
        "WHERE (((eventDetails.subject)=@subject) AND ((eventDetails.type)=@type) AND ((eventDates.date)=@date));"
        Me.cmdSelectEvents.Connection = Me.cnEventsDB
        Me.cmdSelectEvents.Parameters.Add(New System.Data.OleDb.OleDbParameter("@subject", System.Data.OleDb.OleDbType.VarWChar))
        Me.cmdSelectEvents.Parameters.Add(New System.Data.OleDb.OleDbParameter("@date", System.Data.OleDb.OleDbType.Date))
        Me.cmdSelectEvents.Parameters.Add(New System.Data.OleDb.OleDbParameter("@type", System.Data.OleDb.OleDbType.VarWChar))

I thought it might be a good idea to just post up where my code currently is. The sub is now like this:

Dim valSelectedDate As Double
Dim dateParameter As Date
valSelectedDate = ddlDate.SelectedValue
dateParameter = Date.Now.AddMonths(valSelectedDate)

cmdSelectEvents.Parameters("@date").Value = dateParameter
cmdSelectEvents.Parameters("@type").Value = ddlType.SelectedValue
cmdSelectEvents.Parameters("@subject").Value = ddlSubject.SelectedValue
cnEventsDB.Open()
daEvents.Fill(dsEvents)
dgEvents.DataSource = dsEvents
dgEvents.DataBind()


In the Access database the field eventDates.date has datatype of Date/Time with a 'General Date' format

Again - thanks so much fo your help.
0
 
grayeCommented:
So, try this.... change the parameter to accept a string (rather than a Date type), then...

cmdSelectEvents.Parameters("@date").Value = "#" & dateParameter.tostring("MM/dd/yyyy") & "#"
0
 
b1xml2Commented:
absolute rubbish!
>>cmdSelectEvents.Parameters("@date").Value = "#" & dateParameter.tostring("MM/dd/yyyy") & "#"<<


you should never ever be held hostage by the date format. doing what you are suggesting graye is committing CultureInfo suicide!!!

0
 
b1xml2Commented:
plus the OleDbType prevents expects a DateTime object!
0
 
b1xml2Commented:
Me.cmdSelectEvents.CommandText = "SELECT eventDetails.id, eventDetails.subject, " & _
        "eventDetails.title, eventDetails.trainers, eventDetails.type, eventDetails.summary, " & _
        "eventDetails.url, eventDates.date " & _
        "FROM eventDates INNER JOIN eventDetails ON eventDates.id = eventDetails.id " & _
        "WHERE (((eventDetails.subject)= ? AND ((eventDetails.type)= ? ) AND ((eventDates.date)= ?  );"
Me.cmdSelectEvents.Connection = Me.cnEventsDB
With cmdSelectEvents.Parameters
      .Add("subject",OleDbType.VarWChar).Value = ddlSubject.SelectedValue
      .Add("type",OleDbType.VarWChar).Value = ddlType.SelectedValue
      .Add("date",OleDbType.Date).Value = Now.Today.AddMonths(6)
End With

....
0
 
grayeCommented:
Wow, I wasn't expecting an emotional response...

Microsoft Access databases use US date format "behind the scenes"...  regardless of the Culture setting of the PC or the language setting in Access.
0
 
chglAuthor Commented:
Sorry guys it just isn't working - I'm going to move it to an SQL database. I wasted so much time on this already

Thanks for all your sugestions
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now