Solved

Creating an ADODB recordset in VB.Net

Posted on 2011-09-05
11
805 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 82

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 82

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 82

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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 82

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

13 Experts available now in Live!

Get 1:1 Help Now