locked
Need way to loop through records *and* modify them, using .NET , not com RRS feed

  • Question

  • If I add references to Microsoft ADO 2.8 and Microsoft ADO Recordset 2.8, the following code all works:

     

    Dim cnndb As New ADODB.Connection
    cnndb.ConnectionString = sConnectionString
    cnndb.Open()

     

    Dim rs As New ADODB.Recordset

     

    Dim strSQL As String = "Select * From Partner"

    rs.Open(strSQL, cnndb, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

     

    'deletea record

    MsgBox(rs.Fields(

    "PartnerName").Value())

    rs.Delete()

     

    Exit Sub

     

     

    'modify a record

    MsgBox(rs.Fields(

    "PartnerName").Value())

    rs.Fields(

    "PartnerName").Value = "AB"

    rs.Update()

     

    Exit Sub

     

    'add a record

    rs.AddNew()

    rs.Fields(

    "PartnerName").Value = "AA"

    rs.Update()

     

     

    'loop

     

    Do Until rs.EOF

    MsgBox(rs.Fields(

    "PartnerName").Value())

    rs.Fields(

    "PartnerName").Value = "AAA"

    rs.Update()

    rs.MoveNext()

     

    Loop

     

    'close

    rs.Close()

    cnndb.Close()




    Although I know I can do some of these tasks with oledbcommand objects and SQL Updates and Deletes, I really have need to just loop through records from code, make changes, delete items, and add items.

    This was really simple in VB6, and the above code is really simple. There has *got* to be a way using TableAdapters, Databases, and BindingControls, or whatever. Does anyone have an answer to this?? I would sure appreciate it.


    Matt Brown
    • Moved by nobugz Monday, June 1, 2009 1:06 AM not a bcl q (From:.NET Base Class Library)
    Monday, June 1, 2009 12:32 AM

Answers

  • Hey Matt,

    I would look at Ado.net and not worry specifically about DataSet's. If all you want to do is open a connection then use some type of DbConnection.
    http://msdn.microsoft.com/en-us/library/system.data.common.dbconnection.aspx

    There are a couple of different types, one for oledb, sqlserver, and others for other providers. Choose the one based on the database provider.

    If you want to get data, you would typically create a DbCommand from the connection, and then set a Sql query and call execute. Its pretty simple. Then if this was a query you can iterate though a reader to get the results. It sounds like you are familiar with Sql so then you would need to craft Sql commands for Updates and Deletes and send them though commands. Below is an msdn article on how to create a command object.
    http://msdn.microsoft.com/en-us/library/tyy0sz6b(VS.71).aspx

    Now where DataSets come in, think of these an sort of an inmemory representation of a database. you can fill this will data from a database, update date it, then and push the updates back via the DataAdapters. If all you are doing is iterating through data and make changes and sending them back using the DBConnection,DbCommand might be all you need. However if you want to bind to objects in the UI you may want to consider using DataSet

    The generated code that you are speaking about essentially creates a TypedDataSet and adapters that can be used to fill and update the various datatables. Below is an msdn article on
    http://msdn.microsoft.com/en-us/library/33y2221y.aspx

    You will have to investigate more on what to due based on your application needs.

    Thanks
    Chris Robinson
    Program Manager - DataSet

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, June 1, 2009 4:51 PM

All replies

  • Hey Matt,

    I would look at Ado.net and not worry specifically about DataSet's. If all you want to do is open a connection then use some type of DbConnection.
    http://msdn.microsoft.com/en-us/library/system.data.common.dbconnection.aspx

    There are a couple of different types, one for oledb, sqlserver, and others for other providers. Choose the one based on the database provider.

    If you want to get data, you would typically create a DbCommand from the connection, and then set a Sql query and call execute. Its pretty simple. Then if this was a query you can iterate though a reader to get the results. It sounds like you are familiar with Sql so then you would need to craft Sql commands for Updates and Deletes and send them though commands. Below is an msdn article on how to create a command object.
    http://msdn.microsoft.com/en-us/library/tyy0sz6b(VS.71).aspx

    Now where DataSets come in, think of these an sort of an inmemory representation of a database. you can fill this will data from a database, update date it, then and push the updates back via the DataAdapters. If all you are doing is iterating through data and make changes and sending them back using the DBConnection,DbCommand might be all you need. However if you want to bind to objects in the UI you may want to consider using DataSet

    The generated code that you are speaking about essentially creates a TypedDataSet and adapters that can be used to fill and update the various datatables. Below is an msdn article on
    http://msdn.microsoft.com/en-us/library/33y2221y.aspx

    You will have to investigate more on what to due based on your application needs.

    Thanks
    Chris Robinson
    Program Manager - DataSet

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, June 1, 2009 4:51 PM
  • Thanks for the anwer. I think I can use it to accomlish my goal, but sometimes I just need to loop through records.

    This is a simple example, I won't bore you with the details of why this is important, but surely it's possible.

    Dol While not Client.eof
       If Client.Fields(Whatever) then Client.Delete
       Client.Fileds(Processed) = True
       Client.Update
       Client.Movenext
    Loop

    A DataReader provides a perfect loop, but cannot update or delete.

    Any more tips?


    Matt Brown
    Monday, June 1, 2009 10:51 PM
  • Hey Matt,

    I would start with my above hints and when you run into more specific issues I would dig in a little bit and figure out how it works, if you can't then feel free to post and ask a specific question. The more specific the question the easier we can help out.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, June 2, 2009 12:14 AM