Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

Insert multiple records into table from Select statement vb.net

Hi all.

I have the following code below that pulls records from the SQL select statement and inserts them into the ItemQuote_CostDetail table. Unfortunately, when there are more than 1 records returned in the select statement it only inserts the first record into the table. How can I do this? I was thinking a for loop but I don't know how to get started.

Thank you in advance!

cmd.CommandText = "SELECT count(*) As count FROM ItemQuote WHERE Status = 'Active' AND MasterItemNumber = @MasterItemNumberA AND RawMaterialNumber = @RawMaterialNumberA AND CostType = @CostTypeA"
            cmd.Parameters.AddWithValue("@MasterItemNumberA", DataGrid_Pending.SelectedRows(0).Cells("MasterItemNumber").Value)
            cmd.Parameters.AddWithValue("@RawMaterialNumberA", DataGrid_Pending.SelectedRows(0).Cells("RawMaterialNumber").Value)
            cmd.Parameters.AddWithValue("@CostTypeA", DataGrid_Pending.SelectedRows(0).Cells("CostType").Value)

            dr = cmd.ExecuteReader
            dr.Read()
           
            If dr("count") >= 1 Then

                Dim quotenumber As Integer
                cmd.CommandText = "SELECT QuoteNumber FROM ItemQuote WHERE Status = 'Active' AND MasterItemNumber = @MasterItemNumberB AND RawMaterialNumber = @RawMaterialNumberB AND CostType = @CostTypeB"

                cmd.Parameters.AddWithValue("@MasterItemNumberB", DataGrid_Pending.SelectedRows(0).Cells("MasterItemNumber").Value)
                cmd.Parameters.AddWithValue("@RawMaterialNumberB", DataGrid_Pending.SelectedRows(0).Cells("RawMaterialNumber").Value)
                cmd.Parameters.AddWithValue("@CostTypeB", DataGrid_Pending.SelectedRows(0).Cells("CostType").Value)

                dr.Close()
                con.Close()

                con.Open()

                cmd.Connection = con

                dr = cmd.ExecuteReader

                dr.Read()
                quotenumber = dr("QuoteNumber")
                dr.Close()

                cmd.CommandText = "INSERT INTO ItemQuote_CostDetail(QuoteNumber, PriceSheetID, CreateDate) VALUES (@QuoteNumberC, @PriceSheetIDC, @CreateDateC) "
                cmd.Parameters.AddWithValue("@QuoteNumberC", quotenumber)
                cmd.Parameters.AddWithValue("@PriceSheetIDC", pricesheetID)
                cmd.Parameters.AddWithValue("@CreateDateC", DateTime.Now.ToString("MMMM dd, yyyy"))
                cmd.ExecuteNonQuery()

End If

Open in new window

0
printmedia
Asked:
printmedia
  • 2
1 Solution
 
BeartlaoiCommented:
On line 27 change it to

While dr.Read()

Change line 36

End While

Move line 29 to below line 36 so dr does not close until the While loop is finished

Read() returns True when there are rows left.  Currently if your select returned no rows you would get errors.
0
 
printmediaAuthor Commented:
Thank you for your reply Beartlaoi.

Now I get an error at the line:
While dr.Read()

Open in new window


The errors says: Invalid attempt to call Read when reader is closed.
0
 
printmediaAuthor Commented:
Nevermind I figured it out. I can't read and then insert records in the same code body because you can only have one open connection. So instead of doing the insert right below the reader, I created a Sub to do the insert and I simply call it from within the while loop.

Thanks again!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now