Read records from sql statement and insert those records into a sql table in vb.net

Hi all.

I want to read records from a sql statement and then insert those records into a sql server 2008 table. I was thinking of using SqlDataReader to do so but don't know how to get it to work, below is some code I've started to work on.

Please let me know how I can finish it off, or if I'm doing it incorrectly then what is the correct way to do so.

Thank you in advance.

Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim dr As SqlDataReader
        Dim  itemnumber1 As String
        Dim qtyordered, qtyshipped, qtybackordered As Decimal

con.ConnectionString = "Data Source=myserver;Initial Catalog=mydb;Integrated Security=True"

        con.Open()

        cmd.Connection = con

cmd.CommandText = "SELECT ItemNumber, QtyOrdered, QtyShipped, QtyBackordered FROM myTableA t1 INNER JOIN myTableB t2 ON t1.InvoiceNumber = t2.InvoiceNumber WHERE (t2.SkipPrintingOfThisComponent <> 'Y') AND (t1.SalesOrderNumber = @SalesOrder3)"
        cmd.Parameters.AddWithValue("@SalesOrder3", txtSalesOrder.Text)

While dr.Read()
            itemnumber1 = dr("ItemNumber")
            qtyordered = dr("QtyOrdered")
            qtyshipped = dr("QtyShipped")
            qtybackordered = dr("QtyBackordered")

            cmd.CommandText = "INSERT INTO tblOrderPicking_Detail(  SalesOrder,ItemNumber,QtyOrderedEaches,QtyShippedEaches,QtyBackorderedEaches) VALUES (@SalesOrder4,@ItemNumber1,@QtyOrderedEaches,@QtyShippedEaches,@QtyBackorderedEaches )"

   
            cmd.Parameters.AddWithValue("@SalesOrder4", txtSalesOrder.Text)
            cmd.Parameters.AddWithValue("@ItemNumber1", itemnumber1)
            cmd.Parameters.AddWithValue("@QtyOrderedEaches", qtyordered)
            cmd.Parameters.AddWithValue("@QtyShippedEaches", qtyshipped)
            cmd.Parameters.AddWithValue("@QtyBackorderedEaches", qtybackordered)

            cmd.ExecuteNonQuery()
        End While
        dr.Close()

Open in new window

printmediaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sammySeltzerCommented:
Hi,

I am assuming that you will be inserting one record at a time.

So, the basic syntax will be to select the records you need based on the WHERE predicate you are passing.

Then if the record exists, insert that into your other table:

Something like:

                    INSERT INTO tblOrderPicking_Detail(  SalesOrder,ItemNumber,QtyOrderedEaches,QtyShippedEaches,QtyBackorderedEaches) 

        SELECT ItemNumber, QtyOrdered, QtyShipped, QtyBackordered FROM myTableA t1 INNER JOIN myTableB t2 ON t1.InvoiceNumber = t2.InvoiceNumber WHERE (t2.SkipPrintingOfThisComponent <> 'Y') AND (t1.SalesOrderNumber = @SalesOrder3)"

Open in new window


You can also use EXISTS function:

            s = "IF NOT EXISTS (SELECT ItemNumber, QtyOrdered, QtyShipped, QtyBackordered FROM myTableA t1 INNER JOIN myTableB t2 ON t1.InvoiceNumber = t2.InvoiceNumber WHERE (t2.SkipPrintingOfThisComponent <> 'Y') AND (t1.SalesOrderNumber = @SalesOrder3")           
 s += " BEGIN " '***Record does not exist, create one***
            s += " INSERT INTO tblOrderPicking_Detail(  SalesOrder,ItemNumber,QtyOrderedEaches,QtyShippedEaches,QtyBackorderedEaches)
            s += " End "
            s += " ELSE "

Record already exists.

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MikeTooleCommented:
Wow, that's such an approach I would never dream of using :-)
Essentially you're trying to perform an operation on a 'Set' - which is exactly what SQL is designed to do.
Either:
1) Write a sql stored procedure that performs the logic you want, and call that from VB
Or, if you don't have access to the sql database to code the stored procedure ...
2) Create the sql to perform the action as a string in your VB code and execute that.

It is never a good idea to iterate a sql reader result set and execute sql  statements based on it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.