locked
Newbie Needs Help Reading Records RRS feed

  • Question

  • User-415937349 posted

    I'm using Visual Studio 2008 and programing in VB.NET.  I'm reading data from an Access table and inserting the data I read one record at a time into another Access table.  My select statement is returning the correct amount of records, but the DO WHILE LOOP that I'm using only reads 7 records before jumping out.  Can anyone out there help me??? PLEASE!!!!  My code is as follows:

     

    1            Dim sqlSelectInfo As String = "SELECT Registration.School, Registration.SSN, Registration.LastName, Registration.FirstName"
    6 Dim cmdSelectInfo As OleDbCommand, rsSelectInfo As OleDbDataReader
    7
    8 cmdSelectInfo = New OleDbCommand(sqlSelectInfo, cnn)
    9 cmdSelectInfo.CommandType = CommandType.Text
    10
    11 Dim SchoolID As String, StudNum As String, LName As String, FName As String
    15
    16 Try
    17 rsSelectInfo = cmdSelectInfo.ExecuteReader
    18
    19 Do While rsSelectInfo.Read()
    20 SchoolID = CType(rsSelectInfo("
    School"), Long)
    21 StudNum = CType(rsSelectInfo("
    SSN"), String)
    22 LName = CType(rsSelectInfo("LastName"), String)
    23 FName = CType(rsSelectInfo("
    FirstName"), String)
    24
    25 Dim sqlAdminAdd As String = "
    INSERT INTO AdminCustomer ( SchoolID, StudentNumber, LastName, FirstName) " & _
    26 "
    VALUES (" & SchoolID & ",'" & StudNum & "'," & SiteGrdID & ",'" & LName & "','" & FName & "')"
    27 Dim sqlAdminAddCommand As New System.Data.OleDb.OleDbCommand(sqlAdminAdd, cnn)
    28
    29 Try
    30 sqlAdminAddCommand.CommandText = sqlAdminAdd
    31 sqlAdminAddCommand.ExecuteNonQuery()
    32 Catch ex As Exception
    33 Console.WriteLine(ex.ToString)
    34 End Try
    35 Loop
    36 Catch
    ex As Exception
    37 Console.WriteLine(ex.ToString)
    38 End Try
    I'm thinking the problem is with the Do While Loop, but I cant' figure out what's wrong.  Any help would greatly be appreciated, thanks
    Thursday, February 5, 2009 1:34 PM

Answers

  • User-415937349 posted
    Thanks to Mike and Hans_v for all your help.  Hans_v, I did want to insert all the records and the "FROM Registration" is in my code and I left it out by mistake.  As far as the problem, I solved it my self.  It was caused by the SchoolID field.  The SchoolID was enter in as "5100-3" so when I was trying to convert it to a Long, it threw the error.  The "-" was not supposed to be in there.  Thanks again for all your time, consideration and help.  I'm sure you'll be hearing from me again.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 6, 2009 3:18 PM

All replies

  • User-821857111 posted

    I'm pretty sure we have covered the two main problems that you code suffers from before:

    1. Use parameters
    2. Don't write the exception to a Console.

    The first will prevent any possibility of your code failing if one of the text values contains a single quote (Syntax error) and the second will allow you to see if there is an exception.

    To begin to address your problem, get rid of the try...catch blocks.  Let any possible exceptions show their ugly heads!

     

    Thursday, February 5, 2009 5:04 PM
  • User-1199946673 posted

    I'm reading data from an Access table and inserting the data I read one record at a time into another Access table.  My select statement is returning the correct amount of records, but the DO WHILE LOOP that I'm using only reads 7 records before jumping out. 

    In addition to the remarks that Mike has made, Looping through a table and inserting the records into another table is not a very good practice, you could do that in 1 sql statement:

    INSERT INTO AdminCustomer ( SchoolID, StudentNumber, LastName, FirstName)
    SELECT Registration.School, Registration.SSN, Registration.LastName, Registration.FirstName FROM Registration

    Also, I.m missing the "FROM Registration" part in your Select statement! And are you sure you want to insert all records of the table Registration, or did you forget a WHERE clause also? And I don't see you opening the connection anywhere, or is it open already?

    Thursday, February 5, 2009 6:11 PM
  • User-415937349 posted
    Thanks to Mike and Hans_v for all your help.  Hans_v, I did want to insert all the records and the "FROM Registration" is in my code and I left it out by mistake.  As far as the problem, I solved it my self.  It was caused by the SchoolID field.  The SchoolID was enter in as "5100-3" so when I was trying to convert it to a Long, it threw the error.  The "-" was not supposed to be in there.  Thanks again for all your time, consideration and help.  I'm sure you'll be hearing from me again.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 6, 2009 3:18 PM
  • User-1199946673 posted

    Good that you solved it, but it doesn't mean that you're on the right track...

    Concatenating an sql statement is not good practice, so you should use parameters. And in this case, looping through a table and inserting all records one by one is also not the way to go, since you could do it using 1 insert query as I already explained....

    Friday, February 6, 2009 4:03 PM