printmedia
asked on
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!
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Thanks again!
ASKER
Now I get an error at the line:
Open in new window
The errors says: Invalid attempt to call Read when reader is closed.