ASP.net VB: OleDB DataReader.Read not stopping Read when it should

John_Mc

Hi,

I have a DataReader which is sequentially going through records in an Excel spreadsheet. When it has enough information, it should insert a record into a table called posts.

My problem is that only 330 records should be inserted into the DB, but the script never finishes it's tasks(no error message) and the Posts table has exactly 1000 records in it.

I've tried searching for duplicate records because I'm assuming there must be somewhere, however I have been unable to find them.

Anyone see anything wrong with this code?



1 Dim strConnString As String = ExcelConnectionString
2 Dim DBConnection = New OleDbConnection(strConnString)
3 DBConnection.Open()
4 Dim SQLString As String = "SELECT * FROM [Sheet1$]"
5 Dim DBCommand = New OleDbCommand(SQLString, DBConnection)
6 Dim DBReader As Data.IDataReader = DBCommand.ExecuteReader()
7 While DBReader.Read()
8 'Deal with Post table first
9 PostNumber = UtilsObj.CreatePostNumber(UsedPostNumbers)
10 'Add the New Post Number to the Used Numbers List
11 UsedPostNumbers.Add(PostNumber, PostNumber)
12 OldPostNumber = UtilsObj.nullSafeRead(DBReader, DBConnection, "PostNumber")
13 SpecialtyID = CInt(SpecialityList.Item(UtilsObj.nullSafeRead(DBReader, DBConnection, "MajorSpecialty")))
14 HospitalID = CInt(HospitalsList.Item(UtilsObj.nullSafeRead(DBReader, DBConnection, "Hospital")))
15 PostApprovedTraining = UtilsObj.nullSafeRead(DBReader, DBConnection, "PostApprovedTraining")
16 OnRotation = UtilsObj.nullSafeRead(DBReader, DBConnection, "Rotation?")
17 RotationProg = CInt(RotationProgrammesList.Item(UtilsObj.nullSafeRead(DBReader, DBConnection, "RotationProgramme")))
18 RotationStatus = CInt(RotationStatusList.Item(UtilsObj.nullSafeRead(DBReader, DBConnection, "Status re Rotation")))
19 OnCallRota = CInt(OnCallRotas.Item(UtilsObj.nullSafeRead(DBReader, DBConnection, "On Call Rota")))
20 HoursOfWork = CInt(HoursOfWorkList.Item(UtilsObj.nullSafeRead(DBReader, DBConnection, "Hours of work")))
21 If Not UtilsObj.nullSafeRead(DBReader, DBConnection, "hours per week formal edu acts available") = "No answer" Then
22 FormalEduHrs = CInt(UtilsObj.nullSafeRead(DBReader, DBConnection, "hours per week formal edu acts available"))
23 Else
24 FormalEduHrs = 0
25 End If
26 'Insert into Posts Table
27 PostsAdapater.Insert(PostNumber, OldPostNumber, SpecialtyID, HospitalID, PostApprovedTraining, OnRotation, RotationProg, RotationStatus, OnCallRota, HoursOfWork, FormalEduHrs, 0)
28
29 End While
30 DBReader.Close()
31 DBConnection.Close()

Thanks in advance,

John

nialo

your code just does a a loop till the reader is finished. select * from sheet gets 1000 records im assuming and thats what it inserts. You have no filters on your select so your getting all the records.

John_Mc

nialo wrote: »

your code just does a a loop till the reader is finished. select * from sheet gets 1000 records im assuming and thats what it inserts. You have no filters on your select so your getting all the records.

Hi, thanks for the reply. Yes there's something like 90 fields in the spreadsheet and all rows need to be inserted hence the lack of filters in my SQL statement.

I worked around the issue by adding a Bool value check to the while loop, and then checking for certain values within the loop. If those values were missing, I set the Bool to false and then the Loop would exit.

Everything seems to be working fine now