Is there a way to trace /find the root cause of a Vb runtime error 91 2

[HunterW]

Here's a copy of a simple application that opens a remote informix data base for the purpose of executing a store procedure that creates a record set. When I compile and run the application the connection gets opened successfully but the application fails upon execution of :
Set prm05 = cmdtmsSQL.CreateParameter("from_acct_yr_mon", adVarChar, adParamInput, from_acct_yr_mon).

I have attached the code. What I need is the cause or a way to determine the cause.

I'm new to VB - I'm sure it shows. Thanks
---------------
Option Explicit

Private Sub Form_Load()

On Error GoTo Err_Execute

' Dim parameters to be passed to the stored procedure
Dim comp As Integer
Dim v_co As Integer
Dim stor As Integer
Dim v_st As Integer
Dim from_acct_yr_mon As String
Dim from_acct_day As Integer
Dim to_acct_yr_mon As String
Dim to_acct_day As Integer

'Set up parameters for use in by stored procedure
Dim prm01 As ADODB.Parameter
Dim prm02 As ADODB.Parameter
Dim prm03 As ADODB.Parameter
Dim prm04 As ADODB.Parameter
Dim prm05 As ADODB.Parameter
Dim prm06 As ADODB.Parameter
Dim prm07 As ADODB.Parameter
Dim prm08 As ADODB.Parameter

' Dimension connections, commands, and recordset variables
' Dim ADODB.Connections
Dim Cnxntms As ADODB.Connection
'Dim Cnxnsds As ADODB.Connection

' Dim ADODB.Commands
Dim cmdtmsSQL As ADODB.Command
'Dim cmdsds As ADODB.Command

' Dim ADODB.Recordsets
Dim adotmsRS As ADODB.Recordset
'Dim adosdsRS As ADODB.Recordset

' Define area to catch errors
Dim Err As ADODB.Error

' Dim strSQLChange As String
Dim strSQLProc As String
Dim strtmsCnxn As String
'Dim strsdsCnxn As String
'Dim SQLProc As String

'Set up Proc String to accept the procedure and parameter fields
strSQLProc = "informix.sp_dly_rpt_sls"

' Set up trial parameters to pass
comp = 1
v_co = 1
stor = 962
v_st = 962
from_acct_yr_mon = "12/2004"
from_acct_day = 1
to_acct_yr_mon = "12/2004"
to_acct_day = 31

' Set connection strings
strtmsCnxn = "PROVIDER=MSDASQL;dsn=tms_rdb;uid=apple;pwd=pie;database=tms_rdb;"
'strsdsCnxn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\ta\sales\sds.mdb;uid=admin;pwd=admin"

' Set Cnxn = New ADODB.Connection
Set Cnxntms = New ADODB.Connection
'Set Cnxnsds = New ADODB.Connection

' Create command object
' Set cmdChange = New adodb.Command
Set cmdtmsSQL = New ADODB.Command
'Set cmdsds = New ADODB.Command

' Cnxn.Open strCnxn
Cnxntms.Open strtmsCnxn
'Cnxnsds.Open strsdsCnxn

MsgBox "Connection opened"

'Set Command type and load with procedure name
cmdtmsSQL.CommandType = adCmdStoredProc
cmdtmsSQL.CommandText = strSQLProc

' Set cmdChange.ActiveConnection = Cnxn
Set cmdtmsSQL.ActiveConnection = Cnxntms
'Set cmdsds.ActiveConnection = Cnxnsds

'Set recordsets = New ADODB.Recordset
Set adotmsRS = New ADODB.Recordset
'Set adosdsRS = New ADODB.Recordset



'Create 8 input parameters for the stored procedure to be called
Set prm01 = cmdtmsSQL.CreateParameter("comp", adSmallInt, adParamInput, comp)
Set prm02 = cmdtmsSQL.CreateParameter("v_co", adSmallInt, adParamInput, v_co)
Set prm03 = cmdtmsSQL.CreateParameter("stor", adSmallInt, adParamInput, stor)
Set prm04 = cmdtmsSQL.CreateParameter("v_st", adSmallInt, adParamInput, v_st)
Set prm05 = cmdtmsSQL.CreateParameter("from_acct_yr_mon", adVarChar, adParamInput, from_acct_yr_mon)

' *******The preceeding "Set prm05 =...." causes the Runtime Error '91' message. I used the Vb debugger to isolate to the execution of this statement. Stop at Set prm05 - no error. Continue to >Set prm06 and you get the error. (the line rap in this statement is not in the source)

Set prm06 = cmdtmsSQL.CreateParameter("from_acct_day", adInteger, adParamInput, from_acct_day)
Set prm07 = cmdtmsSQL.CreateParameter("to_acct_yr_mon", adVarChar, adParamInput, to_acct_yr_mon)
Set prm08 = cmdtmsSQL.CreateParameter("to_acct_day", adInteger, adParamInput, to_acct_day)

cmdtmsSQL.Parameters.Append prm01
cmdtmsSQL.Parameters.Append prm02
cmdtmsSQL.Parameters.Append prm03
cmdtmsSQL.Parameters.Append prm04
cmdtmsSQL.Parameters.Append prm05
cmdtmsSQL.Parameters.Append prm06
cmdtmsSQL.Parameters.Append prm07
cmdtmsSQL.Parameters.Append prm08

' ExecuteCommand the procedure and return a complete record set from the stored procedure
ExecuteCommand cmdtmsSQL, adotmsRS

' Print report of new data
' Debug.Print _
' "Data in Titles table after executing the query"
Debug.Print "Records retrieved > " & adotmsRS.RecordCount
Debug.Print "Fields retrieved > " & adotmsRS.Fields.Count


Debug.Print "Stored procedure worked! Hopefully!"


' Clean up
' Cnxn.Close
Cnxntms.Close
' Cnxnsds.Close


Set Cnxntms = Nothing
' Set Cnxnsds = Nothing
Exit Sub

Err_Execute:
' Notify user of any errors that result from
' executing the query
If adotmsRS.ActiveConnection.Errors.Count >= 0 Then
For Each Err In adotmsRS.ActiveConnection.Errors
MsgBox "Error number: " & Err.Number & vbCr & _
Err.Description
Next Err
End If

' Clean up
If Not adotmsRS Is Nothing Then
If adotmsRS.State = adStateOpen Then adotmsRS.Close
End If
Set adotmsRS = Nothing


If Not Cnxntms Is Nothing Then
If Cnxntms.State = adStateOpen Then Cnxntms.Close
End If
Set Cnxntms = Nothing

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub

Public Sub ExecuteCommand(cmdTemp As ADODB.Command, rstTemp As ADODB.Recordset)

Dim Err As Error

' Run the specified Command object and trap for
' errors, checking the Errors collection
On Error GoTo Err_Execute
cmdTemp.Execute
On Error GoTo 0

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query
If rstTemp.ActiveConnection.Errors.Count > 0 Then
For Each Err In rstTemp.ActiveConnection.Errors
MsgBox "Error number: " & Err.Number & vbCr & _
Err.Description
Next Err
End If

Resume Next

End Sub

Public Sub PrintOutput(rstTemp As ADODB.Recordset)
' Enumerate Recordset
Do While Not rstTemp.EOF
Debug.Print " " & rstTemp!Title & _
", " & rstTemp!Type
rstTemp.MoveNext
Loop

End Sub
'EndExecuteVB


Private Sub Text1_Change()

End Sub

Private Sub txtEndDate_Change()

End Sub

Private Sub txtStartDate_Change()

End Sub



Private Sub txtCorporateName_Change()
End Sub

 

[vladk]

HunterW,
I found this in your code:

' Define area to catch errors
Dim Err As ADODB.Error

"Err" is the reserved word. Don't dim it.

vladk

 

[vladk]

You might want to restructure your code. Your procedure does too many different things. It is better to operate with procedures that would do just one thing. Wrap 'Set up trial parameters to pass' into one function, establishing connection into another, setting pars into the third one, appending them into the next sub and so on. Each procedure should have its own error handling with Err object to analize. You might want to use "With..." statement. If you right click on your code then in the menu you will find "Toggle". Experiment with this feature: it helps identify errors.

vladk

 

[CassandraR]

Hi HunterW:

I don't know whether this is relevant, but I offer it as a suggestion.

The error appears to stopping on the first use adVarChar. I have always used a value with this. For example, one might use adVarChar(255) to define the column's width.

I would suggest, if it is possible, to temporary change the adVarChar to adSmallInt and see whether the error moves to the next adVarChar.

Cassandra

 

[CassandraR]

Hi HunterW:

I have looked up the information on CreateParameter for ADO 2.8. It appears that you have not provided the correct parameters.

Per MSDN:

Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)

Per your code:

Set prm05 = cmdtmsSQL.CreateParameter("from_acct_yr_mon", adVarChar, adParamInput, from_acct_yr_mon)

The matching up leads to the following:

Name: "from_acct_yr_mon"
Type: adVarChar
Direction: adParamInput
Size: from_acc_yr_mon
Value: <no value given>

I make another suggestion:

Set prm05 = cmdtmsSQL.CreateParameter("from_acct_yr_mon", adVarChar, adParamInput, 255, from_acct_yr_mon)

(adding the Size value.)

Cassandra

 

[CassandraR]

By the way, if one wants to skip an optional parameter, one still needs to indicate the parameter's position. For example:

Set prm05 = cmdtmsSQL.CreateParameter("from_acct_yr_mon", adVarChar, adParamInput, , from_acct_yr_mon)

(added a comma to denote the unspecified optional parameter.)

Cassandra

 

[HunterW]

My thanks to everyone. The problem appeared to have been the missing parameters in the CreateParameters area, some use of reserved words, amature coding, etc..

All of your advice and kind words were a very big help!

I'm now down to debugging why I get a "-1" in my recordcount. I suspect it has to do with Informix not liking how I'm passing parameters. I've change the the command strings and command type from:

strSQLProc = "informix.sp_dly_rpt_sls" < Old

strSQLProc = " {CALL informix.sp_dly_rpt_sls( ?, ?, ?, ?, ?, ?, ?, ?)}" < New

cmdtmsSQL.CommandType = adCmdStoredProc
to
cmdtmsSQL.CommandType = adCmdText

The reason for the latter change has to do with the Execute prefixing an "Exec" on the command string.
Now I've got to figure out what to replace the "?" (default parameters ?) with. Maybe something cleaver like the names used in the original stored procedure?
The following trace points me in that direction:

a2c-3b8 ENTER SQLExecDirectW
HSTMT 046435F0
WCHAR * 0x05453C88 [ -3] "{ CALL informix.sp_dly_rpt_sls( ?, ?, ?, ?, ?, ?, ?, ?) }\ 0"
SDWORD -3

VB6 a2c-3b8 EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
HSTMT 046435F0
WCHAR * 0x05453C88 [ -3] "{ CALL informix.sp_dly_rpt_sls( ?, ?, ?, ?, ?, ?, ?, ?) }\ 0"
SDWORD -3

DIAG [S1C00] [DataDirect][ODBC Informix driver]Default parameters values are not suppported by this database. (0) (Different drivers MS, Informix, DataDirect produce the same errors)

Has anyone come across a definitive article on parameter passing to stored procedures that require "Calls" in vb6?

 

[CassandraR]

Hi:

Thanks for the star!

Cassandra