Solved

Creating an ADODB recordset in VB.Net

Posted on 2011-09-05
11
815 Views
Last Modified: 2012-08-14
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 (rstest.open) 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
   .MoveFirst()
   txtProduct.Text = rstest.Fields.Item(2).Value
End With
rstest.Close()
rstest = Nothing

Open in new window

0
Comment
Question by:trbaze
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 36485679
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?
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 36485691
And I don't see where you're using "strtest", your connection string, to actually connection to the database.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 36485695
And on this page are connection strings for SQL Server 2005 for a lot of different situations.  http://www.connectionstrings.com/sql-server-2005
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40
ID: 36485917
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.
0
 
LVL 3

Author Comment

by:trbaze
ID: 36505988
James,
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?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36506295
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).
0
 
LVL 3

Author Comment

by:trbaze
ID: 36506317
What about other forms which will be reading and writing directly to the DB?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36506321
Depends on the code. Example?
0
 
LVL 3

Author Comment

by:trbaze
ID: 36506687
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?
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 100 total points
ID: 36507079
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.
0
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 25 total points
ID: 36507108
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.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now