[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2014-08-19
Medium Priority
Last Modified: 2014-08-21
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"


        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)

        End While

Open in new window

Question by:printmedia
LVL 29

Accepted Solution

sammySeltzer earned 1000 total points
ID: 40272390

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

LVL 27

Assisted Solution

MikeToole earned 1000 total points
ID: 40274518
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.
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.

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month18 days, 3 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question