Solved

Creating an ADODB recordset in VB.Net

Posted on 2011-09-05
11
843 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 3 49
Learning About The VB.NET TableLayoutPanel Control 5 42
Read top line from CSV file 14 60
vb.net searchandselect 12 18
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 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