ADO connection and good practices

[MDA]

Hi all,

I have a small VB app that will connect to different tables in a SQL Server database as the user clicks a NEXT button. In the past, I Have always created an ADO control every time I need to get a new recordset. However, I think this may be bad programming. What is the correct way to do this, and without using ADO objects??

For example:
First screen will connect to TABLE1 and return results.
Next screen will connect to TABLE 2 and return results.
.... TABLE 3.....

This is all in the same DB. My goal is at the end to take the results from each table and shoot them into another Database.

Do I need more then ONE connection?? Or can I use the same connection with different recordsets??

Thanks for any ideas or samples..

Best regards,

Mike

 

[petermeachem]

One connection is fine. Why don't you use the data form wizard and tell it to produce code and not use a control. You should be able to easily cut and paste what it produce. Peter Meachem
peter@accuflight.com

 

[MDA]

Thanks for your reply... However, I am actually looking for the "how to" of using one connection with many different queries. For example... I created the connection in code then a select statement to poulate a data grid. However, if I created a second select statement to populate a second datagrid, it ruins the first select statement.

What is the coorect code to populate many different datagrids, etc, with one connection.

Thanks for your time and assistance.

Regards,
Mike

 

[petermeachem]

I don't see why it ruins the first one?

What I do is :-

Set cnnBasic = New ADODB.Connection
cnnBasic.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & cDataPath & "Letters.mdb"
cnnBasic.Open

just once.

And then

csql = "SELECT * FROM WhereWeAre"
rs.Open csql, cnnBasic, adOpenStatic, adLockOptimistic
If Not (rs.EOF And rs.BOF) Then

all over the place,

or for a grid


If csql = "" Then
csql = "SELECT AddressID, Title, FirstName, LastName, Address, "
csql = csql & " Town, County, PostCode, "
csql = csql & " EmailAddress, HomePhone, WorkPhone, "
csql = csql & " WorkExtension, FaxNumber"
csql = csql & " From Addresses"
csql = csql & " ORDER BY LastName, FirstName;"
End If
adoPrimaryRS.Open csql, cnn, adOpenStatic, adLockOptimistic
Set GridEX1.ADORecordset = adoPrimaryRS

And so on
Peter Meachem
peter@accuflight.com

 

[Terpsfan]

One thing you need to do is when you unload the form is to sever the connection you just created, otherwise the connection persists and you'll be opening other connections as other connections are opened. This taxes the database.

Form_Unload()
objConn.Close
Set objConn = nothing

I find the best option for creating connections is writing the connection into a class and then making it a DLL. Then you simply are making a call to that DLL each time you want to create a connection without having to write the same code over and over again.

 

[petermeachem]

Yes, but if you open the connection when your application starts, then all forms can use it? Peter Meachem
peter@accuflight.com

 

[MelissaEvans]

One connection can have more than one recordset. You might want to try declaring and using different recordset objects for each data grid, but have them all use the same connection object to get their information.

objCon.ConnectionString = <string>
objCon.Open

RS1.Open <SQL>, objCon...
RS2.Open <SQL>, objCon...
RS3.Open <SQL>, objCon..

and so forth. This way you won't overwrite the recordset.

 

[petermeachem]

Exactly so melissa. That's what I meant, except I understand your explanation so much better than my own. Peter Meachem
peter@accuflight.com

 

[MDA]

Thanks all for the great advice... ohh - what would I do without this site :O

Best regards,

Mike

 

[traffen]

Here's another approach that I find simple enough.
I use a class object for my ado with functions for connect, getrecordset,execute, etc. In my form_load()
I pass the connection information to my method in the class object. As mentioned above you will need to close the connection when the form is destroyed and recordsets when they are not needed.

cnAdpenConnection (&quot;server&quot;,&quot;username&quot;,&quot;password&quot

Then under my command buttons I pass the query and the grid by value to a function

'**** SQL specific to button ****
SQL = &quot;SELECT *
SQL = SQL & &quot; FROM yadayada
SQL = SQL & &quot;WHERE lmnop = 'hijkl'&quot;
ProcessRecordset(SQL,dbGrid1)



Public Sub ProcessRecordset(SQL as string,ByVal dbGrid as DBGrid,)

DIM rs as new adodb.recordset

'The following is a method in the ado class object
If cnado.GetConnectedRecordset(SQL,rs) Then
'process recordset here
DBGrid.ADORecordset = rs
'any other processing goes here

End If

rs.close
set rs = nothing
end Sub

This will work is you handle the recordsets and grids the in the same manner. If not, you can pass enum values to process each differently. These would most likely go into a class object in order to separate the business logic. It makes it easier for re-use also.

 

[bigfoot]

I do that also. It's sooooooo easy to make a mistake, so if you can keep your code to a minuim, it's better.

-Gary

 

[CarlosEscobar]

What about an AdoCommand object?

adoCommand.CommandText = sqlStr
adoCommand.CommandType = adCmdUnknown
adoCommand.CommandTimeout = 15
#If AtClientSite Then
adoCommand.ActiveConnection = ConnectObj
#Else
adoCommand.ActiveConnection = ConectObj.ConnectionString
#End If
adoCommand.Execute


can I just change the CommandText and re-Execute without
updating all the other properties? Also, why do i have to code 2 ways for the ActiveConnection? (if I don't, either myself or the client gets an error)

thanks for any help,
cescobar@accela.com

 

[chiph]

Carlos -

I've had problems re-using a command object where I've changed the CommandText. You can do it if you use parameters in your SQL, and just change the parameter values. I.e.:
[tt]
lAppleID = 1
adoComm.CommandText = &quot;SELECT FROM tbl_Apple WHERE PriKey=?&quot;
adoComm.Parameters.Append adoComm.CreateParameter(&quot;PriKey&quot;, adInteger, adParamInput, , lAppleID)

Set adoRS = adoComm.Execute()

' Do things with the recordset

' Now change the value and re-run the query
lAppleID = 2
adocomm.Parameters(1).Value = lAppleID
set adoRS = adoComm.Execute()
[/tt]

Chip H.

 

[BlackBearDude]

I've created a record set in which I include (in the SQL) some additional fields (e.g., 0 AS a, 0 AS b, etc.) with the intention of looping through the recordset and filling in these fields with data from other sources.

The recordset is created fine. The problem is the infamous &quot;multiple-step operation generated errors&quot; when trying to update a field in the recorset.

What issues exist in this situation? I thought that updating fields in this fashion would be relatively straight-forward.