?
Solved

Unable to read integers thru OleDbDataReader

Posted on 2005-04-17
10
Medium Priority
?
550 Views
Last Modified: 2008-02-07
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
0
Comment
Question by:karthikjay
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 10

Assisted Solution

by:NetworkArchitek
NetworkArchitek earned 200 total points
ID: 13804090
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
 
LVL 4

Author Comment

by:karthikjay
ID: 13805561
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
 
LVL 26

Assisted Solution

by:Rejojohny
Rejojohny earned 80 total points
ID: 13806085
how r u stepping through the records .. pls post the code
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 4

Author Comment

by:karthikjay
ID: 13806870
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
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 220 total points
ID: 13807098
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
 
LVL 4

Author Comment

by:karthikjay
ID: 13807601
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
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 220 total points
ID: 13807717
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
 
LVL 10

Assisted Solution

by:NetworkArchitek
NetworkArchitek earned 200 total points
ID: 13810450
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
 
LVL 4

Author Comment

by:karthikjay
ID: 13858854
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
 
LVL 4

Author Comment

by:karthikjay
ID: 13859138
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Screencast - Getting to Know the Pipeline
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question