ADO syntax and style question 6

[Jdoggers]

Hi,
I just wanted to know what is the difference between the two styles of database code:

Style 1:

Dim adoConnect As ADODB.Connection
Dim adoRecordset As ADODB.Recordset

Private Sub Form_Load()

Dim sql As String

Set adoConnect = New ADODB.Connection
adoConnect.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;" _
& "Data Source=C:\mydb.mdb"

sql = "SELECT SetupTab.SetupName, CellTab.CellName "
sql = sql & "FROM SetupTab INNER JOIN CellTab ON SetupTab.CellID = CellTab.CellID;"

Set adoRecordset = New ADODB.Recordset
adoRecordset.Open sql, adoConnect, adOpenKeyset, adLockOptimistic

'sname and cname are two textboxes on the form
sname.Text = adoRecordset!SetupName
cname.Text = adoRecordset!CellName


End Sub


Second style:


Public Sub Form_Load()

Dim parameter1, parameter2, parameter3 As String

Dim adoconnect As Connection
Set adoconnect = New Connection


adoconnect.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;" _
& "Data Source=C:\mydb.mdb;"

Set adoRecordset1 = New Recordset

parameter1 = "setuptab.setupname, celltab.Cellname"
parameter2 = "setuptab, celltab"
parameter3 = "setuptab.cellid = celltab.CellID"

adoRecordset1.Open "select " & parameter1 & " from " & parameter2 & " where " & parameter3 & " order by setupid", _
adoconnect, adOpenStatic, adLockOptimistic

'sname and cname are text boxes on the form
Set sname.DataSource = adoRecordset1
sname.DataField = "setupname"
Set cname.DataSource = adoRecordset1
cname.DataField = "cellname"


What im really asking in this question is what is the difference between defining a connection as "ADODB.connection" and just defining it as a "connection". Secondly, I was just wondering what the difference between using the two commands:

Set adoRecordset1 = New Recordset
Set sname.DataSource = adoRecordset1
sname.DataField = "setupname"

and

Set adoRecordset = New ADODB.Recordset
sname.Text = adoRecordset!SetupName


can anyone help me because this ado stuff is kind of confusing, thanks

 

[gmmastros]

ADODB.Connection is preferred over Connection

Bad:
Dim adoConnection As Connection

Worse:
Dim adoConnection As New Connection

Best:
Dim adoConnection As ADODB.Connection

By specifying the ADODB. part, you are eliminating any ambiguity within your code. You could, for example, create a class module within your app named Connection, and then you would probably get a compile error (if you only specified Connection without the ADODB).

I hope I explained this well enough. If not, let me know.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[fredericofonseca]

This one is using BOUND controls (WORST)
Set adoRecordset1 = New Recordset
Set sname.DataSource = adoRecordset1
sname.DataField = "setupname"



and

This one is using UNBOUND controls (BEST)
Set adoRecordset = New ADODB.Recordset
sname.Text = adoRecordset!SetupName



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Sheco]

The exclamation point ! syntax is old-school carried into ADO only for compatibility making it easier to upgrade code from pre-1997 to use ADO. It is best not to use it for new code.

Instead of
sname.Text = adoRecordset!SetupName

Do this:
sname.Text = adoRecordset.Fields.Item("SetupName")

Or, since Item is the default method of Fields, you could do:
sname.Text = adoRecordset.Fields("SetupName")

But since Fields is the default property of a recordset, my favorite is:
sname.Text = adoRecordset("SetupName")

They all do the same thing though.

 

[LazyMe]

Even though people all over the world warn against the use of that syntax (relying on default properties) ?

Greetings,
Rick

 

[Jdoggers]

hi,
I dont know if the default properties are bad or good, but i definately am going to try to use the code that you guys recommended. Is there a website or maybe a good book that i can buy to learn how to use ado better? I would like to learn about that alot more, thanks

 

[ArtieChoke]

A book I like is "ADO Examples and Best Practices" by William Vaughn.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[johnwm]

There are samples of various ADO features here:

http://www.eside.deusto.es/grupos/g...ADO/htm/mdmscadocodeexamplesinvisualbasic.htm

There is also a section on ADO and Data Access in the SQL BooksOnLine (which every MSSQL user should have), which is available here:

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[BobRodes]

Here's an FAQ that might be useful: faq222-3670. It explains the differences between different types of recordsets.

The reason to specify ADODB.Connection instead of just Connection is to avoid confusion with the older DAO Connection object. If you don't specify what component a class is in, VB will run through the list of references in order until it finds the first one. If you have a reference to DAO higher up in the list than the ADO reference, you'll get the wrong connection object, and, consequently, errors. (That's why you can change the reference order.) If you don't have such a reference, or another conflicting reference, your code will work without putting ADODB. So, specifying the component is a good defensive programming technique. (Same for default properties, in my opinion.)

As for why it's bad to use the New keyword on the same line as the declaration: VB doesn't actually instantiate the object when it's declared. Instead, every time you referenc the object in your code (use a method or property), VB has to check and see if the object has been instantiated yet, and if not, instantiate it. That process of checking creates unnecessary overhead.

HTH

Bob

p. s. I'd like to write this into an faq, since it seems to come up a lot. johnwm, it might be a better job if you help out with some more low level detail about what exactly happens when an object is created, as in pointers and heaps and references and the like, or show where to read up on it.