Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Really simple question about looping through a querry

Posted on 2008-10-22
15
228 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
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 …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 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