Is there an equivalent to this VBA Code in Visual Studio or do I need to create tableadapters etc for every dataset I want to create?

Rob4077
Rob4077 used Ask the Experts™
on
I am accustomed to working with VBA in MS Access. In VBA when I want to work with a dataset to add, delete & edit records all I do is use the following code to create the dataset and then loop through it. With Visual Basic Studio it looks like I need to create a tableadapter in a form for every dataset I want to work with. Is there an equivalent to the following that I can use in Visual Studio?

Dim sSql As String
Dim rs As Recordset
   
sSql = "SELECT ..."
Set rs = CurrentDb.OpenRecordset(sSql)

If rs.EOF And rs.BOF Then
    Exit Sub
End If

rs.MoveFirst
   
Do While Not rs.EOF
     ...
Loop
   
rs.Close
Set rs = Nothing
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2015
Commented:
This should be similar to your post. You can go to www.connectionstrings.com to find the connection string for Access.
Public Sub Demo()
    Dim sSql As String
    Dim cmd As New System.Data.OleDb.OleDbCommand()
    Dim reader As System.Data.OleDb.OleDbDataReader

    cmd.CommandText = "SELECT ..."
    cmd.Connection = New System.Data.OleDb.OleDbConnection("connectionstring")

    Try
        cmd.Connection.Open()
        reader = cmd.ExecuteReader()

        If Not reader.HasRows Then Exit Sub

        While reader.Read()
            ...
        End While

    Catch ex As Exception

    Finally
        reader.Close()
        cmd.Connection.Close()
    End Try
End Sub

Open in new window

Author

Commented:
That's great. I assume I can use that to connect to the same SQL Database as some of my bound forms use.

Author

Commented:
Just clarifying, your suggestion seems to create a whole new connection to a database. In my case I already have a connection to the database. I have used the following code in one section but this involved creating various objects on my form before I could create the following code. Do I need to create the new connection to the database as per your code or can I use an existing connection?
Dim da As New VigilanceDataDataSetTableAdapters.tblIncomingTableAdapter
Dim Incoming As VigilanceDataDataSet.tblIncomingDataTable = da.GetData()
Dim NewRow As VigilanceDataDataSet.tblIncomingRow
NewRow = Incoming.NewtblIncomingRow()
NewRow("pkReceived") = Msg.TimeStamp
NewRow("CellNum_FK") = Msg.Phone
NewRow("MessageReceived") = Msg.Phone
Incoming.AddtblIncomingRow(NewRow)
da.Update(Incoming)
Most Valuable Expert 2011
Top Expert 2015

Commented:
The above was just a "literal" translation of what you posted. I believe you can reuse the existing connection, but you can only have one object using the connection at a time.

Author

Commented:
Thanks very much for the explanation.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial