Creating a RecordSet

[admdev]

Hi all,

Hope someby can help me.

I am starting to design in Visual Studio 2008 and I am designing a web application that connects to a SQL database. In VB6 I could create a record set and read its contents as follows.

SQLCommand = "SELECT * FROM Tablename WHERE field = '" & data & "'"

Recordset.Open SQLCommand, Database, adOpenDynamic

If Recordset.EOF = True Then
MsgBox "Record does not exist!", , "Record does not exist"
End If

Then I would Bind the fields data to, say a text box like this

Textbox.Text=Recordset("Field1")

I am finding dificult to do this in Visual Studio 2008. Does anybody know how to create a recordset in Visual Basic 2008 as described above.

I have been reading a lot, watching traing videos, and researching on the Internet and I have not found any source which will explain how to do it.

Any help would be appreciated.

Thanks.

[techgnome]

That's because Recordsets don't exist in .NET... what you should be looking for are DataSets, DataTables, and DataAdaptors...

-tg

[jmcilhinney]

You can create Recordsets in VB 2008 but you have to add a reference to the COM-based ADODB library. I strongly recommend against that. You should do as tg says and use ADO.NET. Follow the Data Access link in my signature for some code examples.

You'll find that the main differences between ADO and ADO.NET are:

1. ADO.NET implements a disconnected model, so you work on a local copy of the data while disconnected from the actual data source. ADO maintains a live connection to the data as you manipulate it.

2. The ADO Recordset basically does everything. In ADO.NET there are multiple classes that each have a specific job. This provides for greater flexibility and best performance under all conditions.

[admdev]

Thanks both for the reply!

I have been reading some articles about the ADO.NET, but it seems kind of confusing. If I use a DataSet, how would I access the data from it and assign one of the fields to a textbox.

Thanks.

[jmcilhinney]

It's all very logical. The DataSet has Tables property that is a collection of DataTables. Each DataTable has a Rows property that is a collection of DataRows. Each DataRow has an Item property to get the field values. I'm on a phone right now so writing code is a pain so I'll post an example when I get to work.

[techgnome]

In short, it's like this:

myDataTable("tblTableFor1").Rows(0).Item(0) 'Accesses the first col of the first row in the table
myDataTable("tblTableFor1").Rows(0).Item("MyColName") 'Accesses the collumn "MyColName" of the first row in the table

-tg

[jmcilhinney]

That would be more like:

myValue = myDateSet.Tables("TableName").Rows(rowIndex)(columnIndex)

If you want to draw that out it would be like this:

Dim table As DataTable = myDataSet.Tables("TableName") 'or myDataSet.Tables.Item("TableName")
Dim row As DataRow = table.Rows(rowIndex) 'or table.Rows.Item(rowIndex)
Dim value As Object = row(columnIndex) 'or row.Item(columnIndex)

[admdev]

Thanks for your help!

I finally got it to work. This is my code.

Dim dsData As New DataSet

adapt.TableMappings.Add("Table", "dsData")
adapt.Fill(dsData)

TextBox1.Text = dsData.Tables("dsData").Rows.Item(0).Item(1).ToString

My God! It drove me crazy!


Thanks again for your great help.