Unable to read integers thru OleDbDataReader

Hi,
 I am trying to read data from a MS Access Db. But the problem is the OledbDataReader does not read integer values from the table. gives an error as follows

"The data value could not be converted for reasons other than sign mismatch or data overflow. For example, the data was corrupted in the data store but the row was still retrievable". Any suggestions on how to go about this
LVL 4
karthikjayAsked:
Who is Participating?
 
Arthur_WoodConnect With a Mentor Commented:
ok, you did mention what context the applications was running in.  I was just trying to proveide a means by which you could display the values from the data reader.  However, the code I suggested would appear to have moved you past the error that you were asking about in the original question.  The point being that in the reader2.read loop, you can then use the values from the datareader, as needed.

AW
0
 
NetworkArchitekConnect With a Mentor Commented:
OleDBDataReader can read integers fine. First run "Compact and Repair Database" in Access and then try it, if not post your code and table information.
0
 
karthikjayAuthor Commented:
Hi,
 I tried Compacting and repairing the Db, but of no use. Here is the code

Dim con2 As New OleDbConnection("Data Source=C:\\MyDB\\Data.mdb;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;")
Dim sQuery2 As String = "SELECT Rid,Hrs,Days,Begt,Endt,DayMinutes,EveMinutes,Enrl from tblDataid ORDER BY Rid"
Dim command2 As New OleDbCommand(sQuery2.Trim(), con2)
 con2.Open()
Dim reader2 As OleDbDataReader = command2.ExecuteReader(CommandBehavior.CloseConnection)

In the above code the DayMinutes and Eveminutes columns are in the Integer format. As I step through the code i get the following error during the execution of the code

"The data value could not be converted for reasons other than sign mismatch or data overflow. For example, the data was corrupted in the data store but the row was still retrievable".
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
RejojohnyConnect With a Mentor Commented:
how r u stepping through the records .. pls post the code
0
 
karthikjayAuthor Commented:
The code returns the error message at the line
Dim reader2 As OleDbDataReader = command2.ExecuteReader(CommandBehavior.CloseConnection).
Stepping thru the code means running it one line at a time and not thru the records.
0
 
Arthur_WoodConnect With a Mentor Commented:
try this change:

Dim reader2 As OleDbDataReader = command2.ExecuteReader()

Do While reader2.Read
    MessageBox.Show("Day Minutes = " & reader2.GetInteger(5) & " Eve Minute = " & reader2.GetInteger(6))
Loop


AW
   
0
 
karthikjayAuthor Commented:
Hi Arthur,

I get the following error

It is invalid to show a modal dialog or form when the application is not running in UserInteractive mode. Specify the ServiceNotification or DefaultDesktopOnly style to display a notification from a service application
0
 
NetworkArchitekConnect With a Mentor Commented:
It looks like you are writing a service, in which case a messagebox is not really an option. Also, you may want to use "While(reader2.read()" instead of "do while," in  case there are no records. Anyway, change that line to:

while reader2.Rread()
    Debug.WriteLine("Day Minutes = " & reader2.GetInteger(5) & " Eve Minute = " & reader2.GetInteger(6))
end while

So when you run this in debug mode you will see the output in the little console window.

Are you sure you get the error on that exact line, that seems to point to something else. It absolutely should not matter but try changing the first part to this and see what happens.

Dim con2 As New OleDbConnection("Data Source=C:\\MyDB\\Data.mdb;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;")
Dim sQuery2 As String = "SELECT Rid,Hrs,Days,Begt,Endt,DayMinutes,EveMinutes,Enrl from tblDataid ORDER BY Rid"
Dim command2 As New OleDbCommand()
command2.connection = con2
command2.commandtext = sQuery2
 con2.Open()
Dim reader2 As OleDbDataReader = command2.ExecuteReader(CommandBehavior.CloseConnection)

while reader2.Rread()
    Debug.WriteLine("Day Minutes = " & reader2.GetInteger(5) & " Eve Minute = " & reader2.GetInteger(6))
end while


If this still doesn't work and you haven't tried it, try just SELECTing the RID and see if you get just print that in the Debugger, because if that doesn't work then there is really something else wrong.
0
 
karthikjayAuthor Commented:
Hi,
 I have tried all the suggestions, but in vain. Are you guys sure that there is a .GetInteger method for the reader. I have modified the query a bit now

Dim con2 As New OleDbConnection("Data Source=C:\\MyDB\\Data.mdb;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;")
Dim sQuery2 As String = "select rid ,sum(len(days)) ,(dayminutes+eveminutes)  from tblCourseRegSession group by rid,(dayminutes+eveminutes) having rid = 'XXXX'
Dim command2 As New OleDbCommand()
command2.connection = con2
command2.commandtext = sQuery2
 con2.Open()
Dim reader2 As OleDbDataReader = command2.ExecuteReader(CommandBehavior.CloseConnection)

I found the datatypes for the reader components and this is what i got. for the reader.getstring(1) the datatype reutned was 'DBTYPE_R8' and for reader.getstring(2) it was "DBTYPE_I4". Just wanted to remind u guys that I am using an ACCESS database.
0
 
karthikjayAuthor Commented:
Hi everyone,
I found a solution for the problem. I used the reader.getvalue method to retrieve the value and it works .Thanks everyone for pitching in. I am spliting the points amongst everyone who responded
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.