Solved

Really simple question about looping through a querry

Posted on 2008-10-22
15
214 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

867 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

21 Experts available now in Live!

Get 1:1 Help Now