#1
  1. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jul 2007
    Posts
    166
    Rep Power
    0

    Insert records in vb.net using loop


    Hi..how to insert records using loop in vb.net...by incrementing id col by '1'.
    Code:
    Dim SqlInsertID As String
                            Dim ProcedureActions As String
                            'If dbConn.State = 1 Then dbConn.Close()
                            dbConn.Open()
                            Dim sqlSelect As String = "Select ........."
                            Dim ObjCmdInsertID As OleDbCommand
                            Dim ObjDRInsertID As OleDbDataReader
                           
                            ObjCmdInsertID = New OleDbCommand(sqlSelect, dbConn)
                            ObjDRInsertID = ObjCmdInsertID.ExecuteReader()
                            'dbConn.Close()
    
                            If ObjDRInsertID.HasRows() Then
                                While ObjDRInsertID.Read()
                                    Dim ncn_ref_id As String
                                    Dim ObjCmdrid As New OleDbCommand("SELECT ISNULL(MAX(Ref_ID),0)+1 as REF_ID from ..", dbConn)
                                    If dbConn.State = 1 Then dbConn.Close()
                                    dbConn.Open()
                                    Dim ObjDRrid As OleDbDataReader
                                    ObjDRrid = ObjCmdrid.ExecuteReader()
                                    If ObjDRrid.Read Then
                                        ncn_ref_id = ObjDRrid("REF_ID")
                                    End If
    
                                    SqlInsertID = "Insert into tNCN_Events(NCN_Ref_Number,NCN_Ref_ID,REQUESTOR,ACTION_BY,PO_NUMBER,REPLACEMENT_PO,STATUS,STATUS_ID,EVENTS_FLAG,ACTIONS) SELECT '" & ReferenceNo & "','" & ncn_ref_id & "','" & Requestor & "','" & NCNActionRef & "','" & PO & "','" & ncnD.NCNReplacementPO & "','" & NCNstatus & "','" & NCNStatusID & "','0',Procedure_Actions from tNCN_ProcedureActions where Fault_Procedure = '" & ProcedureActionInsert & "'"
                                End While
                            End If
    
                            If dbConn.State = 1 Then dbConn.Close()
                            dbConn.Open()
                            Dim ObjCmdID As New OleDbCommand(SqlInsertID, dbConn)
                            ObjCmdID.ExecuteNonQuery()
    Now i get this error.,"invalid attempt to read when reader is closed" on this line for second check
    Code:
    While ObjDRInsertID.Read()
    Because i have this code below
    Code:
    If dbConn.State = 1 Then dbConn.Close()
    But when i comment this,
    Code:
    If dbConn.State = 1 Then dbConn.Close()
    it says ...that the connection is open and fetching ....

    now when i try to close connection as below
    Code:
    ObjCmdInsertID = New OleDbCommand(sqlSelect, dbConn)
                            ObjDRInsertID = ObjCmdInsertID.ExecuteReader()
    dbConn.Close()
    it says, invalid attempt to HasRows() when reader is closed...

    What am trying to do is read records from select query and insert it in table using while loop..
    inside while loop i have a select query which will select max records +1 (increment by 1) and for a particular column each time i insert , it will insert by incrementing one..its a id column...
    is there any other way to do this?
    Thanks to help.
  2. #2
  3. No Profile Picture
    ASPFree Know-It-All
    ASP Adventurer (500 - 999 posts)

    Join Date
    Aug 2004
    Posts
    932
    Rep Power
    334
    You need to create a second connection object to use while the 1st one is busy with ObjDRInsertID.

    The problem is that you're trying to do two operations at once with the same connection object, and you can't do that.

    Alternatively you could store your IDs in an array or something, and do the looping after you're done reading.
  4. #3
  5. Slaprentice of Wolves
    ASP Good Citizen (1000 - 1499 posts)

    Join Date
    Aug 2007
    Location
    Mossville, IL
    Posts
    1,469
    Rep Power
    542
    There appear to be lots of problem with this code.
    First, exactly what asmoran already points out -- you can't close a connection and then attempt to read from it again.

    Second, I'm not sure what your are trying to do here, but it appears you don't execute the INSERT statement until after all the rows returned from your SELECT query have been processed -- wouldn't that give you only the last iteration?
    Also the SELECT MAX() returns only one row -- actually one 1 value! Why not use ExecuteScalar to get the value?

    Third, your update is not atomic. This code may work fine while testing, but once in production 1 process can execute the SELECT MAX()+1 code, but before it can execute the INSERT.. code, process #2 executes the INSERT with the same ID value -- now your throws an exception. This need to be wrappered in a Transaction AND with a TRY..CATCH..FINALLY block. However, RefID appears to be a primary key -- why not make it a Auto Increment field? (You don't mention what db engine you are using)
    Wolffy
    ------------------------
    Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary. Not FDIC insured

Similar Threads

  1. ASP Insert and JS form loop
    By ecoviva in forum ASP Development
    Replies: 5
    Last Post: February 19th, 2007, 06:26 AM
  2. VB.NET, Checkboxlist - update records
    By Rictor in forum .NET Development
    Replies: 1
    Last Post: January 16th, 2007, 04:58 PM
  3. How to insert date into records
    By cougarAsp in forum ASP Development
    Replies: 5
    Last Post: October 19th, 2006, 05:25 AM
  4. How do I insert all records from a repeat region ?
    By jtcarlson in forum ASP Development
    Replies: 2
    Last Post: March 24th, 2005, 02:55 PM
  5. Insert Multiple records from Web Form
    By Judi Secoges in forum Visual Basic Programming
    Replies: 5
    Last Post: May 21st, 2004, 09:44 AM

IMN logo majestic logo threadwatch logo seochat tools logo