Data type mismatch

[Chong]

I have an error Data type mismatch in criteria expression when I run the application. Below are my codes.

Private Shared mdbPath As String = Application.StartupPath & "\Scholarship.mdb"

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbPath
Dim strSQL As String
Dim rdrEdu As OleDbDataReader
Dim cn As New OleDbConnection(strConn)

cn.Open()
strSQL = "SELECT SSN, Status, HghSchoolGPA, ACT, SAT," & _
"TotalCredits, GPALstSemester, GPACumulative, Rank " & _
"FROM Education WHERE Education.SSN=" & "'" & intSSN & "'"

Dim cmdSQL As New OleDbCommand(strSQL, cn)

Try
rdrEdu = cmdSQL.ExecuteReader
Catch dbException As System.Data.OleDb.OleDbException
MessageBox.Show(dbException.Message)
End Try

Please help.

Many thanks in advance.

Chong

[Lunatic3]

If Education.SSN is a numeric filed then omit 's from Education.SSN=" & "'" & intSSN & "'" and let it be :
Education.SSN=" & intSSN

[Chong]

Thanks for trying to help me.

Yes, the Education.SSN is a number. I did remove the quote around intSSN and here's the error I get:

No value given for one or more required parameters.

Thanks!

Chong

[Lunatic3]

check to see if you have spelled all the fields correctly. For example HghSchoolGPA seems to be HighSchoolGPA.

[Chong]

Thanks! Yes, I did misspelled a word. However, because I have changed my SQL query to contain two tables, can I do the following:

Dim strHolder as String

strHolder = "SSN: " & CInt(rdr("Education.SSN")) & " " & rdr("Personal.FstName") & " " & rdr("Personal.MdlName") & " " & rdr("Personal.LstName") & vbCrLf & _
rdr("Personal.Address") & " " & rdr("Personal.StoutCity") & " " & rdr("Personal.StoutState") & " " & rdr("Personal.StoutZip") & vbCrLf & _
"Phone: " & rdr("Personal.StoutPhone") & vbCrLf & _
"Class Standing: " & rdr("Education.Status")

to get the contents out of the tables?

Thanks

[Lunatic3]

I guess you can do it.
There is only one point in your code, that i think its not neccessary, and that is:
CInt(rdr("Education.SSN"))

simply use rdr("Education.SSN") and if you want to change type then it should be CStr(rdr("Education.SSN")) as that filed is a number.

[Chong]

I get the following error:

No data exists for the row/column.

Thanks!

Chong

[Lunatic3]

The error says all. Check your SQL statement and see if you are returning proper fields.

[Chong]

How would I check to see if I'm returning any value?

Chong

[Lunatic3]

Send parts of your code.

[Chong]

I have simplified my code to this:

Private Shared mdbPath As String = Application.StartupPath & "\Scholarship.mdb"

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbPath
Dim strSQL As String
Dim rdr As OleDbDataReader
Dim cn As New OleDbConnection(strConn)

cn.Open()
strSQL = "SELECT Education.SSN, Education.Status FROM Education WHERE Education.SSN=" & intSSN
Dim cmdSQL As New OleDbCommand(strSQL, cn)
Try
rdr = cmdSQL.ExecuteReader
Catch dbException As System.Data.OleDb.OleDbException
MessageBox.Show(dbException.Message)
End Try

Try
schlrHolder = rdr("SSN")
Catch dbException As Exception
MessageBox.Show(dbException.Message)
End Try
rdr.Close()

By I still received the same error, "No data exists for the row/column."

[Lunatic3]

Does this simplifed code throw error? If not send the orginal.

[Chong]

Yes, this simplied version throw an error too. And the error as the same which is "No data exists for the row/column."

Thanks for helping. I'm greatly appreciated!

Chong

[Lunatic3]

Its because you have not called the rdr.Read Method before trying to read the data. Remember to close the datareader when done with it.