DBU VB code

Our ADO area has samples of ADO code that can be used in Visual Basic. This area concentrates on the VB Data Environment Designer and how to use it.

Anomalies

We have found some anomalies in using the Visual Basic 6 Data Environment against SQL Server 7, using the SQL OLEDB driver. We have not yet tested this using the OLEDB ODBC driver instead.

A recordset returning stored procedure can be done as:

if <Data Environment>.<rsCommand Name>.State <> adStateOpen then
<Data Environment><.Command Name>
end if
if not <Data Environment>.<rsCommand Name>.BOF and not <Data Environment>.<rsCommand Name>.EOF then
<Data Environment><.rsCommand Name>.MoveFirst ' etc

set GridEx1.ADOrecordset =
<Data Environment>.<rsCommand Name>.Clone

at the end, make sure to close the recordset:

<Data Environment>.<rsCommand Name>.Close

However: a non-recordset returning query (action query) does not seem to handle properly through the <Data Environment>.<Command Name> method. Instead, use the <Data Environment>.<Connection> object:

sql = "exec <stored proc name> "
sql = sql & Chr$(34) & param1 & Chr$(34) & "," & param2 & ","
sql = sql & Chr$(34) & param3 & Chr$(34)

' leave out optional parameters 4 to 9

If <Data Environment>.<connection>.State <> adStateOpen Then
<Data Environment>.<connection>.Open
End If
<Data Environment>.<connection>.Execute (sql)

The alternative would require all arguments despite the fact that they are declared as optional in the SQL stored procedure and optional in the command properties under the Data Environment!

<Data Environment>.<Command Name> param1, param2, param3, param4, param5, param6, param7, param8, param9 '

Location Map

Updates

Visual Basic

DBU VB code

Anomalies

Copyright DBU 2000. Last updated 24 May 2004

Copyright DBU 2000.
Last updated 24 May 2004