Problem with executing stored procedures using ADO

Problem with executing stored procedures using ADO

Post by piggi » Thu, 13 Jan 2000 04:00:00



ADO COMMAND (Sample code (1) below) is used in VB6.0 to execute a stored
procedure to return a recordset from SQL Server 7.0 database. However, After
the Command is executed, no data is returned for the recordset and the
recordcount property of the returned recordset is -1.

Not knowing what is going on, I checked the returned recordset's Fields
property in the Watch window. It has all the fields from the underlying
database table, but its CursorType property is set at adOpenForwardOnly,
which doesn't support adBookmark and adHoldRecords.

Also strangely, if a SQL string rather than a stored procedure (Sample code
(2) below) is used to return a recordset, it works only if the CursorType
for the Open method is set at adOpenStatic or adOpenKeyset, and it didn't
work (recordcount = -1 again) if the CursorType is not set (Sample code (3)
below).

I don't know whether the problem is with the ADO object, or with the ODBC
connection, or with the SQL Server 7.0 database settings. Anyone has any
idea?

The sample codes looks like this,

(1)     Using Stored Procedure (Not Working)

   Public cmd1 As ADODB.Command
   Public rstRec As ADODB.Recordset

   Set cmd1= New ADODB.Command
   Set rstRec = New ADODB.Recordset

   cmd1.ActiveConnection = cn
'Connection
   cmd1.CommandText = "SP_SELECT"                      'Stored procedure
   cmd1.CommandType = adCmdStoredProc
   cmd1.CommandTimeout = 10

   Set param1 = cmd1.CreateParameter("Status", adVarChar, adParamInput, 1)
   cmd1.Parameters.Append param1
   param1.Value = "C"

   Set rstRec = cmd1.Execute

(2)     Using SQL string (Working)

   Public rstRec As ADODB.Recordset
   Dim sSQL As String

   Set rstRec = New ADODB.Recordset

   sSQL = "SELECT * FROM STATUS WHERE MS_STATUS = 'C' "

   rstRec.Open sSQL, cn, adOpenStatic, , adCmdText

(3)     Using SQL string (Not Working)

   Public rstRec As ADODB.Recordset
   Dim sSQL As String

   Set rstRec = New ADODB.Recordset

   sSQL = "SELECT * FROM STATUS WHERE MS_STATUS = 'C' "

   rstRec.Open sSQL, cn, , , adCmdText

 
 
 

1. Problem with executing stored procedures using ADO

ADO COMMAND (Sample code (1) below) is used in VB6.0 to execute a stored
procedure to return a recordset from SQL Server 7.0 database. However, After
the Command is executed, no data is returned for the recordset and the
recordcount property of the returned recordset is -1.

Not knowing what is going on, I checked the returned recordset's Fields
property in the Watch window. It has all the fields from the underlying
database table, but its CursorType property is set at adOpenForwardOnly,
which doesn't support adBookmark and adHoldRecords.

Also strangely, if a SQL string rather than a stored procedure (Sample code
(2) below) is used to return a recordset, it works only if the CursorType
for the Open method is set at adOpenStatic or adOpenKeyset, and it didn't
work (recordcount = -1 again) if the CursorType is not set (Sample code (3)
below).

I don't know whether the problem is with the ADO object, or with the ODBC
connection, or with the SQL Server 7.0 database settings. Anyone has any
idea?

The sample codes looks like this,

(1)     Using Stored Procedure (Not Working)

   Public cmd1 As ADODB.Command
   Public rstRec As ADODB.Recordset

   Set cmd1= New ADODB.Command
   Set rstRec = New ADODB.Recordset

   cmd1.ActiveConnection = cn
'Connection
   cmd1.CommandText = "SP_SELECT"                      'Stored procedure
   cmd1.CommandType = adCmdStoredProc
   cmd1.CommandTimeout = 10

   Set param1 = cmd1.CreateParameter("Status", adVarChar, adParamInput, 1)
   cmd1.Parameters.Append param1
   param1.Value = "C"

   Set rstRec = cmd1.Execute

(2)     Using SQL string (Working)

   Public rstRec As ADODB.Recordset
   Dim sSQL As String

   Set rstRec = New ADODB.Recordset

   sSQL = "SELECT * FROM STATUS WHERE MS_STATUS = 'C' "

   rstRec.Open sSQL, cn, adOpenStatic, , adCmdText

(3)     Using SQL string (Not Working)

   Public rstRec As ADODB.Recordset
   Dim sSQL As String

   Set rstRec = New ADODB.Recordset

   sSQL = "SELECT * FROM STATUS WHERE MS_STATUS = 'C' "

   rstRec.Open sSQL, cn, , , adCmdText

2. unable to open the specified server database (229)

3. decomposition vs synthetise

4. PL/SQL

5. executing a stored procedure in oracle using ADO

6. Long connect times to SQL 7.0 thru EM

7. accessing stored procedure return value using ADO (2.0) command execute method

8. SQL 7 -OK, 6.5 using ADO won't execute x-stored procedure

9. Identifying stored procedure errors while executing using ADO

10. problem in executing an Oracle stored procedure by ADO

11. Problems executing executing store procedure on different environments