Scanning and Changing Columns in ADO.NET

Hi, Thanks for clicking

We looking to change over our code from classic ADO to ADO.NET/ADO.NET2

We need the equivalent of this code that scans thru each record of a recordset, and adds into another recordset each field (column)

Dim RSTJob As New ADODB.Recordset
Dim RSTQuote As New ADODB.Recordset

(code to open two records to simple tables, Tbl_Quote and Tbl_Job)

                        RSTQuote.MoveFirst()
                        For i = 1 To RSTQuote.RecordCount
                            RSTJob.AddNew()
                            For Each fld In RSTJob.Fields
                                        fld.Value = .Fields(fld.Name).Value                          
                            Next fld                            
                      RSTQuote.MoveNext()                            
                RSTJob.Update()
                        Next i
                    End With

Can anyone help??
RobKearneyAsked:
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.

Jorge PaulinoIT Pro/DeveloperCommented:
Hi RobKearney,

In .NET the recordsets as not used. The new methods are quicker, saffer and even easy to use. Check this link that explains almout everything
http://msdn2.microsoft.com/en-us/library/h43ks021(VS.71).aspx

If you have any problem just say it.

jpaulino
0
Jorge PaulinoIT Pro/DeveloperCommented:
Note: You have examples for SqlConnection and OleDBConnection
0
RobKearneyAuthor Commented:
Hi jpaulino, Thanks for your comments. Just left with one issue

This is where I've got. Just need to update it to the Job Table. Trying to basically do drJob.Update or something similar. Can you help???

Dim col As DataColumn

        ssql = "SELECT * FROM Tbl_QuoteCosting WHERE QuoteID=1"
        Dim tblQuote As New System.Data.DataTable
        Dim daQuote As New System.Data.SqlClient.SqlDataAdapter(ssql, cnnSQL)
        daQuote.Fill(tblQuote)

        ssql = "SELECT * FROM Tbl_JobCosting"
        Dim tblJob As New System.Data.DataTable
        Dim daJob As New System.Data.SqlClient.SqlDataAdapter(ssql, cnnSQL)        
daJob.Fill(tblJob)

        If tblQuote.Rows.Count <> 0 Then
            Dim drJob As DataRow = tblJob.NewRow()
            For i = 0 To tblQuote.Rows.Count - 1
                Dim drQuote As DataRow = tblQuote.Rows(i)
                drJob.BeginEdit()
                For Each col In tblJob.Columns
                    If col.ColumnName <> "JobID" Then
                        If col.ColumnName <> "UniqueIndex" Then
                            drJob.Item(col.ColumnName) = drQuote.Item(col.ColumnName)
                        Else
                            UniqueIndex = UniqueIndex + 1
                            drJob.Item("UniqueIndex") = UniqueIndex
                        End If
                    Else
                        drJob.Item("JobID") = 99
                    End If
                Next
                drJob.EndEdit()
            Next
        End If
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Jorge PaulinoIT Pro/DeveloperCommented:
You can use SqlCommand.ExecuteNonQuery to execute T-SQL statement (UPDATE, DELETE or INSERT). Something like "UPDATE myTable SET myField = " & Me.TextBox1.Text & " WHERE ID = " & MyID

You can read more in here:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(VS.80).aspx

That method that you used it's good depending of the propose that you want to achive but you can do a executereader to do the loop. Check here: http://msdn2.microsoft.com/en-us/library/9kcbe65k(VS.80).aspx

All SqlCommand methods you can check in here but the most used are (ExecuteNonQuery, ExecuteReader and ExecuteScalar) ExecuteReader is used when you need to retrieve only one value.
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand_methods(VS.80).aspx
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
RobKearneyAuthor Commented:
Got it sorted.

Added this at the end of procedure above. Thanks for all your help!
0
Jorge PaulinoIT Pro/DeveloperCommented:
Glad I could help!

jpaulino
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
.NET Programming

From novice to tech pro — start learning today.