Type mismatch in expression error (DataSet)

Question

User505252109 posted

So i am a newbie in ASP and still learning, but I got this error when trying to show data from table named 'blogposts' and 'userinfo'.

Error message : OleDbException was unhandled by user code : Type mismatch in expression.

Here's the code:

Imports System.Data
Imports System.Data.OleDb

Partial Class Blog_index
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack() Then
            Dim DBConn As OleDbConnection
            Dim DBCommand As OleDbDataAdapter
            Dim DSPosts As New DataSet
            DBConn = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
                & "DATA SOURCE=" _
                & Server.MapPath("~/app_data/dblogin.mdb;"))
            DBCommand = New OleDbDataAdapter _
                ("Select WebLogPostID, TitleText, ThePost, " _
                & "DatePosted, UserName, Email " _
                & "from blogposts " _
                & "Left Join userinfo On " _
                & "blogposts.UserID = " _
                & "userinfo.userID Where " _
                & "DatePosted = #" & Today() & "#" _
                , DBConn)
            DBCommand.Fill(DSPosts, _
                "ThePosts") /* Here is where the error occured */
            repPosts.DataSource = _
                DSPosts.Tables("ThePosts").DefaultView
            repPosts.DataBind()
            calDateToUse.SelectedDate = Today()
            If DSPosts.Tables("ThePosts").Rows.Count = 1 Then
                lblmessage.Text = "1 Web Log post found for " _
                    & Today() & "."
            Else
                lblmessage.Text = DSPosts.Tables("ThePosts").Rows.Count _
                    & " Web Log posts found for " _
                    & Today() & "."
            End If
        End If
    End Sub

please help me :(

Answer 1

User-821857111 posted

You don't show what the Today() method returns but it is most likely not a value that JET recognises as a a valid Datetime. 

Answer 2

User-760709272 posted

Try something like

& "DatePosted = #" & DateTime.Today.ToString("dd MMM yyyy") & "#" _

Answer 3

User753101303 posted

Hi,

Or ToString("MM/dd/yyyy") as Access uses the #MM/dd/yyyy# format if I remember.

Else I would suggest to use parameters as shown here: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters(v=vs.110).aspx

The point is that as you are turning your data into a string to construct a SQL statement you have to provide a value that is syntactically correct which is not as easy as it sound (it could even appear to be correct but fail later if you change language settings etc...). When using parameters you just provide the value using its native type (ie a date, a boolean, a string, whatever etc...) and taking care of transmitting this to a suitable format that always works is done for you.

Answer 4

User-1199946673 posted

You can use the builld in Date function to get the current date

Replace

                & "DatePosted = #" & Today() & "#" _

With

& "DatePosted = Date()" _

Answer 5

User505252109 posted

Still not working, maybe the problem isn't in the Date?

You don't show what the Today() method returns but it is most likely not a value that JET recognises as a a valid Datetime. 

Try something like
& "DatePosted = #" & DateTime.Today.ToString("dd MMM yyyy") & "#" _

Hi,

Or ToString("MM/dd/yyyy") as Access uses the #MM/dd/yyyy# format if I remember.

Else I would suggest to use parameters as shown here: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters(v=vs.110).aspx

The point is that as you are turning your data into a string to construct a SQL statement you have to provide a value that is syntactically correct which is not as easy as it sound (it could even appear to be correct but fail later if you change language settings etc...). When using parameters you just provide the value using its native type (ie a date, a boolean, a string, whatever etc...) and taking care of transmitting this to a suitable format that always works is done for you.

You can use the builld in Date function to get the current date

Replace

dzakyalfajr

                & "DatePosted = #" & Today() & "#" _

With

& "DatePosted = Date()" _

Answer 6

User-821857111 posted

Still not working

What did you try?

Answer 7

User505252109 posted
I tried to change it to String and also tried the other format that you guys told me.

Answer 8

User-821857111 posted

What data type is the DatePosted column? And can you provide some sample values from it?

Answer 9

User505252109 posted
short date like 11/20/2014

Answer 10

User505252109 posted
oh i forgot to tell one thing. The DatePosted is from table blogposts so it's not for inserting new data

Answer 11

User753101303 posted

To change what to a string? According to http://msdn.microsoft.com/en-us/library/bb221200(v=office.12).aspx it seems that a date literal in Access definitively look like :

WHERE ShippedDate = #5/10/96#;

What if try this value? Make sure you have the same error at the same place and tell us if this is not the case...  so WHERE Col=#"+Value.ToString("MM/dd/yyyy")+"#" should really work.

Or don't bother and use parameters as I suggested earlier...

Answer 12

User-821857111 posted

dzakyalfajr

short date like 11/20/2014

So is the data type a datetime or string? Try changing the delimiters to apostrophes:

DatePosted = '" & Today() & "'"

although you should use parameters: http://www.mikesdotnetting.com/article/26/parameter-queries-in-asp-net-with-ms-access

Answer 13

User505252109 posted
its datetime in the database.. tried the apostrophes but still the same error.

Answer 14

User-1199946673 posted

Still not working, maybe the problem isn't in the Date?

Although this seems to be the most likely cause, it might not be the date. Remove the where clause to see if the select statement is correct....

Answer 15

User505252109 posted
hans_v : i tried that as well

Answer 16

User-821857111 posted

This is like pulling teeth...

hans_v : i tried that as well

And what happened?

Answer 17

User505252109 posted
guys, i dont know how to put this..
I just found our that blogspot.UserID has different field type than userinfo.UserID, its text and autonumber. I didn't know if that could be a problem.
Im so sorry for such a beginner mistake.. and thank you guys for putting so much effort helping me