locked
Type mismatch in expression error (DataSet) RRS feed

  • 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 :(

    Friday, November 21, 2014 9:08 PM

Answers

  • 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
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 24, 2014 6:30 PM

All replies

  • 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. 

    Saturday, November 22, 2014 4:38 AM
  • User-760709272 posted

    Try something like

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

    Saturday, November 22, 2014 6:35 AM
  • 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.

    Saturday, November 22, 2014 7:31 AM
  • User-1199946673 posted

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

    Replace

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

    With

    & "DatePosted = Date()" _

    Saturday, November 22, 2014 8:02 AM
  • 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()" _

    Saturday, November 22, 2014 5:44 PM
  • User-821857111 posted

    Still not working

    What did you try?

    Sunday, November 23, 2014 1:55 AM
  • User505252109 posted
    I tried to change it to String and also tried the other format that you guys told me.
    Sunday, November 23, 2014 4:21 AM
  • User-821857111 posted

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

    Sunday, November 23, 2014 4:22 AM
  • User505252109 posted
    short date like 11/20/2014
    Sunday, November 23, 2014 7:53 AM
  • User505252109 posted
    oh i forgot to tell one thing. The DatePosted is from table blogposts so it's not for inserting new data
    Sunday, November 23, 2014 7:59 AM
  • 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...

    Sunday, November 23, 2014 11:44 AM
  • 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

    Sunday, November 23, 2014 1:40 PM
  • User505252109 posted
    its datetime in the database.. tried the apostrophes but still the same error.
    Sunday, November 23, 2014 5:35 PM
  • 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....

    Sunday, November 23, 2014 7:38 PM
  • User505252109 posted
    hans_v : i tried that as well
    Sunday, November 23, 2014 9:44 PM
  • User-821857111 posted

    This is like pulling teeth...

    hans_v : i tried that as well
    And what happened?

    Monday, November 24, 2014 2:05 AM
  • 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
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 24, 2014 6:30 PM