Creating an ADODB recordset in VB.Net

I am working on making the switch from Microsoft Access to VB.Net in visual studio 2008.  I am trying to figure out how to create an ADODB recordset connection to the database for record manipulation.  So far i've had no luck.  The debugging of the program stops at the line where I open the recordset ( and I'm not sure why.  Can someone tell me what I'm doing wrong?
Dim rstest As ADODB.Recordset
Dim SQLrs As String
Dim cn As ADODB.Connection
Dim strtest As String

SQLrs = "SELECT tblProductNames.ID, tblProductNames.FName FROM tblProductNames WHERE (((tblProductNames.ID)=3));"

strtest = "DSN=Berry_DatSQL;Integrated Security=True"
cn = New ADODB.Connection
cn.CursorLocation = CursorLocationEnum.adUseClient
cn.Open(OdbcConnection1.ConnectionString, "", "", -1)

rstest = New ADODB.Recordset
rstest.Open(SQLrs, OdbcConnection1.ConnectionString, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic, 1)

With rstest
   txtProduct.Text = rstest.Fields.Item(2).Value
End With
rstest = Nothing

Open in new window

Who is Participating?
Jacques Bourgeois (James Burger)PresidentCommented:
DataSets and DataTables are completely new things. They are nothing similar to a Recordset. You have to forget what you learned.

You do not see real time changes made by other users unless you recreate the dataset or datatable.

The notion of locking a record while it is being edited does not exists.

They use techniques invented by web programmers. But they are the way that Microsoft decided to implement ADO.NET even for Windows applications.

You have to learn new ways of working. And I can tell you that after 30 years of working with cursors and lock, it first hit me straight in the face. But after using them for close to 10 years now, I would not come back to things as they were before. I want to cry when I have to do maintenance on my old code.

If a site like Amazon, that has thousands of simultaneous users, was working as we did before, it would crawl to a halt. With the new techniques, it flies over the cloud, no matter how many transactions come in every second. If working disconnected from the database (that is the big change in ADO.NET) works for Amazon, it should surely work for most of us.

If you are in a rush, go for what you are used to, ADODB. But as soon as you can, learn ADO.NET. There is no way to know for how long Microsoft will support ADOBD in .NET. And after the first few weeks of "what have they done there?" will be over, you will end up with a better way to work with databases.

I do not have time to go into the details now, but in ADO.NET, you do not lock a record when a user is editing it, because he is editing a copy of the record that you have in memory. You are always working on a copy of the data, never on the data itself. The data itself is changed afterward, when you decide to update the database with what you have in memory. You detect changes by other users when you Update the record, and they decide how to act if another user changed the data since you loaded it into memory.
Dave BaldwinFixer of ProblemsCommented:
Since you're using "Integrated Security=True", you must be on the same machine as the SQL Server.  Does your DSN use the same login that you used to create the database in SQL Server?
Dave BaldwinFixer of ProblemsCommented:
And I don't see where you're using "strtest", your connection string, to actually connection to the database.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Dave BaldwinFixer of ProblemsCommented:
And on this page are connection strings for SQL Server 2005 for a lot of different situations.
Jacques Bourgeois (James Burger)PresidentCommented:
Since you are moving to .NET, why not take a pause and learn to do it the .NET way.

ADODB is offered in .NET only for backward compatibility of older application. Since it is not a .NET technology, it tends to give different little problems here and there. There are enough differences between VB.NET and VB6 so that reusing old code is usually more headaches than rewriting.

There is a new ADO, called ADO.NET, wich is almost the same as far as Connection and Command objects are concerned. Recordsets have disappeared however and are replaced by DataSets (small copies of your database in memory) and DataTables (the closest there is to a Recordset, but filled with extra features and possibilities).

Even better, ADO.NET comme with specialized libraries. So you could use the System.Data.SqlClient with SQL Server. Since it is .NET and communicates directly with SQL server, without the need for providers as ADODB does, you gain not only a better compatibility with .NET, but also a huge gain in performance.

You need to learn a new way of working however, specially in a multiuser environment.
trbazeAuthor Commented:
I imagine these are the same datasets that you can use behind a windows form.  How do you see real-time data changes in a multi-user enviroment?
Given that you are only getting the product name from the DB, you can use a simple SQLCommand.ExecuteScalar() to get it and it will be real time (not in memory).
trbazeAuthor Commented:
What about other forms which will be reading and writing directly to the DB?
Depends on the code. Example?
trbazeAuthor Commented:
In an access form that has a table for a record source, when you pull up a record you access locks the current record.  As you type data, it is a direct write to the table.  Does it work the same way with the datasets or are you working with old data?
Dave BaldwinFixer of ProblemsCommented:
In SQL servers, you Never write directly to the tables, you make a request to Update or Insert a row and the server processes it in turn with the other requests it gets.  And it doesn't matter what kind of 'front end' you are using, whether it is Access or ASP or PHP.  All activity goes thru the server that manages the database.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.