Post reply

How to insert multiple records into access table from a stored procedure

  • April 29, 2008 at 10:55 am

    #121931

    Plz i need help on how to insert into an access table from stored procedure in visual studio 2005 or the best practice to go about it.

    I have a parameterized stored procedure executed and i wish to insert the resultant records into an access table. I have two connections, one for sqlClient and the other for OLEDB, I am a sort of confuse after the ExecuteReadercommand.

    Plz how do i go about this. Here is my sample code that inserted only one record and the application hug.

    myCommand = New SqlCommand("dbo.LWC_Pay_Info", ConnString)

    myCommand.CommandType = CommandType.StoredProcedure

    ConnString.Open()

    With myCommand.Parameters

    .AddWithValue("@StartDate", dtpStartDate.SelectedDate)

    .AddWithValue("@EndDate", dtpEndDate.SelectedDate)

    End With

    myReader = myCommand.ExecuteReader

    While myReader.Read

    mTransID = str2Field(myReader("TransID"))

    mPayerAddress = str2Field(myReader("PayerAddress"))

    mPayerFullName = str2Field(myReader("PayerFullName"))

    mRem_Ref = str2Field(myReader("Rem_Ref"))

    mRem_F_ID = str2Field(myReader("Rem_F_ID"))

    mZoneCode = str2Field(myReader("ZoneCode"))

    mBranchName = str2Field(myReader("BranchName"))

    mBankCode = str2Field(myReader("BankCode"))

    mRem_Amt = str2Field(myReader("Rem_Amt"))

    mRem_Pdate = DateValue(str2Field(myReader("Rem_Pdate")))

    sReader = "INSERT INTO tblPaymentDetails01 ( TransID, Rem_Pdate, Rem_Amt, Rem_Ref, Rem_F_ID, ZoneCode, BankCode,BranchName, PayerFullName, PayerAddress ) " _

    & " VALUES ( " & mTransID & ", DateValue('" & mRem_Pdate & "'), " & mRem_Amt & ", '" & mRem_Ref & "', '" & mRem_F_ID & "', '" & mZoneCode & "', '" & mBankCode & "', '" & mBranchName & "', '" & mPayerFullName & "', '" & mPayerAddress & "' ) "

    'lblRecAffected.Text = sReader

    'Exit Sub

    myCom.Connection = ConnStr

    ConnStr.Open()

    myCom = New OleDbCommand(sReader, ConnStr)

    myCom.CommandType = CommandType.Text

    recAffected = myCom.ExecuteNonQuery

    ConnStr.Close()

    End While

  • April 29, 2008 at 11:15 am

    #808557

    I suggest you find a VB.Net forum and post there.

    This is not the correct forum.

    I will add - you should not be re-opening and closing the destination connection in your loop.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply