Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Creating an ADODB recordset in VB.Net

Posted on 2011-09-05
11
Medium Priority
?
1,170 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 84

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 84

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 84

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 400 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 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 100 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

783 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