Oledbexception data type mismatch in criteria expression

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

  

Answer 1

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

Answer 2

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

 

Answer 3

User-1199946673 posted

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

Answer 4

User873497469 posted

 Excellent, you have saved my bacon!!

 

Thanks

Answer 5

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