locked
Oledbexception data type mismatch in criteria expression RRS feed

  • Question

  • User873497469 posted

    I am trying to retrieve a number from a query in an access database and am getting the followign error:

     

    oledbexception was unhandled by user code

    data type mismatch in criteria expression

     

    The code is as follows:

     

     

        Protected Sub GridView2_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles GridView2.SelectedIndexChanged
    
            Dim maxDay As String
            maxDay = "SELECT [MaxofDayNumber] FROM [qryMaxDay] WHERE [entryID]='" & GridView2.SelectedRow.Cells(5).Text & "'"
    
            'maxDay = "SELECT [balance] FROM [tblEntry] WHERE [entryID]='" & GridView2.SelectedRow.Cells(5).Text & "'"
    
            Dim DataConn1 As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/0715713.mdb"))
    
            Dim MaxDayCommand As New OleDbCommand(maxDay, DataConn1)
            DataConn1.Open()
            Dim MaxDayReader As OleDbDataReader = MaxDayCommand.ExecuteReader()
            MaxDayReader.Read()
    
            Response.Redirect("playCompetition.aspx?entryID=" & GridView2.SelectedRow.Cells(5).Text & "&day=" & MaxDayReader("maxDay"))
    
            MaxDayReader.Close()
            DataConn1.Close()
    
    
        End Sub
      
    Tuesday, April 14, 2009 10:55 AM

Answers

  • User-1199946673 posted

    You're retreiving the [MaxofDayNumber] field, not [maxDay]!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 14, 2009 2:03 PM

All replies

  • User-1199946673 posted

    I suspect that EntryID is a Numeric value? If that's the case,  assume that GridView2.SelectedRow.Cells(5) contains the value 5. The maxday will be:

    SELECT [MaxofDayNumber] FROM [qryMaxDay] WHERE [entryID]='5'

    However, it should be:

    SELECT [MaxofDayNumber] FROM [qryMaxDay] WHERE [entryID]=5

    You should use the quotes for string values only. To avoid any of this, you better learn yourself to use parameter queries:

      

    maxDay = "SELECT [MaxofDayNumber] FROM [qryMaxDay] WHERE [entryID]=?"
    Dim DataConn1 As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=|DataDirectory|0715713.mdb"))
    Dim MaxDayCommand As New OleDbCommand(maxDay, DataConn1)
    MaxDayCommand.Parameters.AddWithValues("", GridView2.SelectedRow.Cells(5).Text)
    .....
     

    http://www.mikesdotnetting.com/Article.aspx?ArticleID=26

    Also not that i changed the connectionstring slightly:

    http://www.mikesdotnetting.com/Article.aspx?ArticleID=78

    Tuesday, April 14, 2009 12:14 PM
  • User873497469 posted

    Oh excellent I didn't spot that :)

     

    Problem with coding is now that I have fixed that another error has occured! argh.

     

    When I click the button I get the following line "Response.Redirect("playCompetition.aspx?entryID=" & GridView2.SelectedRow.Cells(5).Text & "&day=" & MaxDayReader("maxDay"))"  highlighted with the erro:

    IndexOutofRangeException was unhandled by user code
    MaxDay

     


    Tuesday, April 14, 2009 1:00 PM
  • User-1199946673 posted

    You're retreiving the [MaxofDayNumber] field, not [maxDay]!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 14, 2009 2:03 PM
  • User873497469 posted

     Excellent, you have saved my bacon!!

     

    Thanks

    Tuesday, April 14, 2009 2:09 PM
  • User873497469 posted

    I am now getting the error:

    System.Data.OleDb.OleDbException: No value given for one or more required parameters.

    The query is definately functioning.  Code as follows:

      

    1        Protected Sub GridView2_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles GridView2.SelectedIndexChanged
    2    
    3            Dim maxDay As String
    4            maxDay = "SELECT [MaxOfdayNumber], [entryDayID] FROM [qryMaxDay] WHERE [entryID]=" & GridView2.SelectedRow.Cells(5).Text & ""
    5    
    6            Dim DataConn1 As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/0715713.mdb"))
    7    
    8            Dim MaxDayCommand As New OleDbCommand(maxDay, DataConn1)
    9            DataConn1.Open()
    10           Dim MaxDayReader As OleDbDataReader = MaxDayCommand.ExecuteReader()
    11           MaxDayReader.Read()
    12   
    13           'Label3.Text = MaxDayReader("MaxOfdayNumber")
    14           Response.Redirect("playCompetition.aspx?entryID=" & GridView2.SelectedRow.Cells(5).Text & "&entryDayID=" & MaxDayReader("entryDayID") & "&day=" & MaxDayReader("MaxOfdayNumber"))
    15   
    16           MaxDayReader.Close()
    17           DataConn1.Close()
    18   
    19   
    20       End Sub
    
     

     

     

    Wednesday, April 15, 2009 6:39 PM