Opening and closing an ADO Connection

My project consists of several forms and it is becoming that I seem to open a new dbConnection in each forms load event. I have read several things on how to do a global connection and how to do it. But when I tried to declare a Global connection, I couldn't compile past the db.Open line. The code below is what I am using in each of my forms currently:

Dim db as Connection
set db = new Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;dsn=MyFileServer;uid=sa;pwd=;database=junk;"



I am using VB6 SP3 and SQL Server 6.5. What could I possibly be doing wrong? Also, do I need to close each connection on each form unload or does it close by itself when another form opens a new connection?

I am also trying to open a connection and execute a SQL statement. The SQL Statement is "UPDATE table SET blah WHERE blah". What do I do after I build my statement? rs.Open? rs.Update? Where can I find some good info on these procedures.

Thanks.

Well, to use a global connection object, i declare it in a public module like this:

public gAdoCN as ADODB.Connection




And then once you build your sql statement, you could execute like this:

sql = "blah blah blah"

gAdoCN.Execute sql, adExecuteNoRecords, adAsyncExecute

While gAdoCN.State = adStateOpen + adStateExecuting
DoEvents
Wend

MsgBox "Command Executed."




That for asyncronous execution, if you want the program to halt completely, just call the execute method and pass the sql statement --

gAdoCN.Execute sql

You could also use the Command object to do things like that, but i'm not sure it's always worth the work of building a new object just do an insert or update. They're great for stored procedures that take parameters and return values/recordsets.

Hope this helps,
John

John Pirkey
MCSD
www.ShallowWaterSystems.com

Once I execute the sql from the line below:

db.Execute sql, adExecuteNoRecords, adAsyncExecute



how do I know that it successfully executed? Also, How do I refresh the list view once I have made the changes?

Thanks.

If the next line of code after the .execute method is executed, then there was no errors generated by the statement. That doesn't mean that any records were affected, that just means that SQL Server, or Access whatever you db is didn't encounter any errors while executing your request.

you should put in an error handler whenever hitting a database to trap for any errors. in it's simplest form the following will at least not kill your app:

Sub Foo()
'declarations

on error Goto ErrorHandler

'your executable code
'be sure you have this line before your error handling label, or your error code will execute when you dont it to.
Exit Sub

ErrorHandler:
MsgBox error
End Sub




For the list view question, you would have to rebuild the list just like you did at form load to get a fresh copy of the data.

John

John Pirkey
MCSD
www.ShallowWaterSystems.com

Dim cnn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim theTable as string
Dim strSQL as string

on error GoTo ErrorHandler

set cnn = new ADODB.Connection
set rs = new ADODB.Recordset

theTable = "BPN0011T"

strSQL = "SELECT COLUMN1,COLUMN2 FROM " &theTable
cnn.Open connectionString
If cnn.State = ADODB.adStateOpen then
With rs
.ActiveConnection = cnn
.Source = strSQL
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open
set .ActiveConnection = nothing
End With
If rs.RecordCount = 0 then
MsgBox "Can't",vbCritical
set rs = nothing
set cnn = nothing
Exit Sub
else
rs.MoveFirst
Do Until rs.EOF
fieldValue1 = rs!FieldName1
fieldValue2 = rs!FieldName2
fieldValue3 = rs!FieldName3
rs.MoveNext
Loop
End If
else
MsgBox "Can't connect to Table", vbCritical, "Connection error"
Exit Sub
End If
Exit Sub
ErrorHandler:
set rs = nothing
set cnn = nothing
Screen.MousePointer = vbDefault
MsgBox Err.Number & " " & Err.Description & " " & Err.Description
End Sub

Dim cnn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim theTable as string
Dim strSQL as string

on error GoTo ErrorHandler

set cnn = new ADODB.Connection
set rs = new ADODB.Recordset

theTable = "BPN0011T"

strSQL = "SELECT COLUMN1,COLUMN2 FROM " &theTable
cnn.Open connectionString
If cnn.State = ADODB.adStateOpen then
With rs
.ActiveConnection = cnn
.Source = strSQL
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open
set .ActiveConnection = nothing
End With
If rs.RecordCount = 0 then
MsgBox "Can't",vbCritical
set rs = nothing
set cnn = nothing
Exit Sub
else
rs.MoveFirst
Do Until rs.EOF
fieldValue1 = rs!FieldName1
fieldValue2 = rs!FieldName2
fieldValue3 = rs!FieldName3
rs.MoveNext
Loop
End If
else
MsgBox "Can't connect to Table", vbCritical, "Connection error"
Exit Sub
End If
Exit Sub
ErrorHandler:
set rs = nothing
set cnn = nothing
Screen.MousePointer = vbDefault
MsgBox Err.Number & " " & Err.Description & " " & Err.Description
End Sub