asked on

Loop to insert multiple records

Hi all, if I have the following, how would I set this up to loop through and insert multiple rows?? Like in the Select statement I could have a JB_ID with several lines to it, so then I would need the insert to insert multiple lines.. in Visual Studio 2005 VB??

Thanks

Dim BP_Req As Integer
        Dim IT_ID As String
        Dim BP_Desc As String
        Dim BP_Print_Desc As String
        Dim BP_DispOrder As Integer
 
        Dim sql6 As String = "Select BP_Req, IT_ID, BP_Desc, BP_Print_Desc, BP_DispOrder from bomPriEquip where JB_ID = @JB_ID"
        Using conn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("HCISDataCARsConnectionString").ConnectionString)
            Dim cmd As New SqlCommand(sql5, conn)
            cmd.Parameters.AddWithValue("@JB_ID", JB_ID)
            Dim reader As SqlClient.SqlDataReader
            cmd.Connection.Open()
            reader = cmd.ExecuteReader
            reader.Read()
            BP_Req = reader("BP_Req")
            IT_ID = reader("IT_ID")
            BP_Desc = reader("BP_Desc")
            BP_Print_Desc = reader("BP_Print_Desc")
            BP_DispOrder = reader("BP_DispOrder")
            cmd.Connection.Close()
        End Using
 
        Dim sql2 As String = "INSERT  INTO [bomPriEquip] (BP_Req, IT_ID, BP_Desc, BP_Print_Desc, BP_DispOrder) VALUES (@BP_Req, @IT_ID, @BP_Desc, @BP_Print_Desc, @BP_DispOrder)"
        Using conn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("HCISDataCARsConnectionString").ConnectionString)
            Dim cmd As New SqlCommand(sql1, conn)
            cmd.Parameters.Add(New SqlParameter("@BP_Req", BP_Req))
            cmd.Parameters.Add(New SqlParameter("@IT_ID", IT_ID))
            cmd.Parameters.Add(New SqlParameter("@BP_Desc", BP_Desc))
            cmd.Parameters.Add(New SqlParameter("@BP_Print_Desc", BP_Print_Desc))
            cmd.Parameters.Add(New SqlParameter("@BP_DispOrder", BP_DispOrder))
            cmd.Parameters.Add(New SqlParameter("@JB_IDNew", JB_IDNew))
            conn.Open()
            cmd.ExecuteNonQuery()
            cmd.Connection.Close()
        End Using

Open in new window

ASKER CERTIFIED SOLUTION

Rob Farley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ratkinso666

ASKER

I am trying this:
INSERT INTO [bomPriEquipT] (JB_ID, BP_Req, IT_ID, BP_Desc, BP_Print_Desc, BP_DispOrder) values (@JB_IDNew, (Select BP_Req, IT_ID, BP_Desc, BP_Print_Desc, BP_DispOrder from bomPriEquip where JB_ID = @JB_ID))

but am getting this error:
Subqueries are not allowed in this context. Only scalar expressions are allowed.

I have to replace the JB_ID with the new one...

ratkinso666

ASKER

I got it to work like this,
INSERT INTO [bomPriEquipT] (JB_ID, BP_Req, IT_ID, BP_Desc, BP_Print_Desc, BP_DispOrder) Select @JB_IDNew, BP_Req, IT_ID, BP_Desc, BP_Print_Desc, BP_DispOrder from bomPriEquipT where JB_ID = @JB_ID

Thanks,
Randy

ratkinso666

ASKER

Thank you for your EXCELLENT solution!!!

Rob Farley

No worries. I'm pleased it's working for you.

And yes, you can't combine the VALUES and SELECT statements. But you can make a SELECT statement which just includes whatever you need to make it essentially the same as using VALUES.

Rob