Solved

Really simple question about looping through a querry

Posted on 2008-10-22
15
201 Views
Last Modified: 2012-05-05
I have the following querry
Dim strSQLLoop As String
        strSQLLoop = "SELECT description,unitPrice,extendedAmount,vehicleNumber,qty from imtbl_templineitem"
        Dim DBCommandLoop As New SqlDataAdapter(strSQLLoop, "myconnectionstring")
        Dim rs As New DataSet
        DBCommandLoop.Fill(rs)


Please give me a example of how I can loop through this record set and insert into another table with each iteration
0
Comment
Question by:soccerman777
  • 6
  • 4
  • 2
  • +1
15 Comments
 
LVL 7

Accepted Solution

by:
the_bachelor earned 500 total points
ID: 22778619
once you have your dataset, you can loop through it by doing
for each row as DataRow in myDataset.Tables(0).Rows
   'Write you code to perform inserts
   'Access column using row.Item("ColumnName")
next

helps?
0
 
LVL 10

Expert Comment

by:Bane83
ID: 22778660
Could you just explain why you need to loop through each one and then copy each row to a new table?

The reason I ask this is that you can make the copy without doing the loop:
Dim dt as DataTable = rs.Tables(0).Copy
0
 

Author Comment

by:soccerman777
ID: 22778698
I am inserting each row in a temp table into a diferent live table. The table structures are different so I assumed I have to loop through each row and then insert the currect row into the table
0
 
LVL 10

Expert Comment

by:Bane83
ID: 22778726
Ah, yes, you do.  You can use the method that the_bachelor mentioned
0
 

Author Comment

by:soccerman777
ID: 22778771
bachelor,

How do I reference the data fields from the querry in the loop. Please give me a example of the insertsql statement.

0
 

Author Comment

by:soccerman777
ID: 22778781
I do now how to do a insert I jjust need a example of how to reference the var from the recordset at the current row
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 10

Expert Comment

by:Bane83
ID: 22778824
You can use row("ColumnName") replacing ColumnName with whatever the columns name is.
0
 
LVL 7

Expert Comment

by:the_bachelor
ID: 22778880
sure i tried to accomplish that on my 1st post
you can build a sql statement like the folowing inside the for loop

Dim sSql as String = "INSERT INTO myNewTable (col1, col2) VALUES ('" & row("columnName1").ToString & "', " '" & row("ColumnName2").ToString & "')"
0
 
LVL 37

Expert Comment

by:samtran0331
ID: 22778900
Keep in mind that with this method, for every row in the temp table, you are executing a separate insert statement and for, say 1000 rows, that's 1000 separate db calls from your code...

If your goal is simply to update one table with data from another table, you can do it all in sql with something like:
INSERT INTO RealTable(RealField1,RealField2,RealField3,RealField4,RealField5)
SELECT description,unitPrice,extendedAmount,vehicleNumber,qty from imtbl_templineitem

Which will insert into "RealTable" a row for every row that the select query returns...same as coding a row by row insert through code except exponentially more efficient because the database server is handling it....

While it would best be done in a stored procedure...you can probably do the below:


Dim StrSQL As String = "INSERT INTO RealTable(RealField1,RealField2,RealField3,RealField4,RealField5) SELECT description,unitPrice,extendedAmount,vehicleNumber,qty from imtbl_templineitem" 

           Using MyConn As New SqlConnection("myconnectionstring")

                MyConn.Open()

                Dim MyTrans As SqlTransaction = MyConn.BeginTransaction

                Dim MyCmd As New SqlCommand(StrSQL, MyConn, MyTrans)

                Try

                    MyCmd.ExecuteNonQuery()

                    MyTrans.Commit()

                Catch ex As Exception

                    MyTrans.Rollback()

                Finally

                    If MyConn.State = ConnectionState.Open Then MyConn.Close()

                End Try

            End Using

Open in new window

0
 

Author Comment

by:soccerman777
ID: 22779053
samtran0331 I like your method but I have already used the simple soultion. I will post a question in a min asking how to do this in one sql statment because I think it is only fair you sould get points for giveing me such a nice soultion.
0
 
LVL 10

Expert Comment

by:Bane83
ID: 22779085
Sorry, you accepted the wrong person for the solution.  Hopefully you can undo that.
0
 

Author Closing Comment

by:soccerman777
ID: 31508860
Sorry You put that in your comments in your first soultion.  I should have read them.
0
 

Author Comment

by:soccerman777
ID: 22781435
I asked that the points for this soultion would go to the _bachelor
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now