ADODB in sub not working

[hedgracer]

I have the following code in a Public Sub:

Public Sub DailyCash_Click()

Dim cmd1 As New ADODB.Command
Dim errtype As String
On Error GoTo ErrorHandler
errtype = "vb"

If (cnnP.State = adStateOpen) Then
Else
ConSrvr
End If

Screen.MousePointer = 11
With cmd1
.ActiveConnection = cnnP
.CommandText = "CreateCash_Sect_Conf_A"
.CommandType = adCmdStoredProc
errtype = "sql"
.Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@StmtDate", adDate, adParamInput, Len(Forms!SysParameters!SysDate), Forms!SysParameters!SysDate)
.Parameters.Append .CreateParameter("@CashCharge", adNumeric, adParamInput, Forms!CashOpenPositionChargeForm!CashCharge)
.Execute


If .Parameters("RETURN_VALUE").Value = 0 Then 'success
ElseIf .Parameters("RETURN_VALUE").Value <> 0 Then 'failure
MsgBox "Cash Report could not be done with date supplied"
End If
End With

Screen.MousePointer = 0
Exit Sub

ErrorHandler:
Screen.MousePointer = 0
If errtype = "sql" Then
DisplayADOError cnnP
ElseIf errtype = "vb" Then
MsgBox err.Description
End If
End Sub

Here it the code in ConSrvr:

Public Function ConSrvr()
Dim txtP, file_name As String
Dim DatabaseName As String
Dim UserName As String
Dim Password As String

On Error GoTo ErrorConnect:

file_name = Trim("C:\BackOfficeSetup.txt")

Close
Open file_name For Input As #1

Line Input #1, txtP
txtP = Trim(txtP)
'servername = Mid(Trim(txtP), 26, 7)
'servername = "EQU-RGS-MQ1\BACKOFFICE"
Line Input #1, servername
servername = Trim(servername)

cnnP.Open txtP

Screen.MousePointer = vbHourglass
Screen.MousePointer = vbDefault
Exit Function

ErrorConnect:
If cnnP.State = adStateOpen Then
errcase = 1
End If

If cnnP.State <> adStateOpen Then
errcase = 2
End If
End Function

The problem is that this is my code (as evidenced by the distinct lack of documentation). This is Access 2003 on a Windows XP system referencing SQL Server 2000 tables and stored procedures. The cnnP is a global ADODB.Command.

The situation is that the code does not execute the stored procedure. The code hits .Execute and immediately goes into the ErrorHandler under Screen.MousePointer = 0. I am pretty confused about what to do at this point after having tried several different ADODB solutions so I am hoping someone has some suggestions. Any will help and thanks in advance.

Dave

 

[Andrzejek]

If you add the BLUE code to your error handler:

Screen.MousePointer = 0
Exit Sub

ErrorHandler:[blue]
MsgBox err.Description[/blue]
Screen.MousePointer = 0
If errtype = "sql" Then
  DisplayADOError cnnP
ElseIf errtype = "vb" Then
  MsgBox err.Description
End If
End Sub

What do you get in the message box?

Have fun.

---- Andy

 

[hedgracer]

The precision is invalid.

 

[hedgracer]

I thought I found the problem but just got a new error message. Here is how I changed the code:

Screen.MousePointer = 11
With cmd1
.ActiveConnection = cnnP
.CommandText = "CreateCash_Sect_Conf_A"
.CommandType = adCmdStoredProc
errtype = "sql"
.Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@StmtDate", adDate, adParamInput, Len(Forms!SysParameters!SysDate), Forms!SysParameters!SysDate)
.Parameters.Append .CreateParameter("@CashCharge", adNumeric, adParamInput, Forms!CashOpenPositionChargeForm!CashCharge)
End With
With cmd1
.Parameters("@CashCharge").Precision = 2
.Execute

Now the error messsage reads "Procedure 'CreateCash_Sect_Conf_A' expects parameter '@CashChargeRate', which was not supplied." This rate is .25 in a text box in Forms!CashOpenPositionChargeForm!CashCharge. This is very puzzling.

Dave

 

[PHV]

CashCharge or CashChargeRate ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hedgracer]

Thank you, PHV, for identifying this item. I changed this to reflect the parameter in the stored procedure (@CashChargeRate was the parameter in the stored procedure) so everything matches). Here is the complete new code:

Public Sub DailyCash_Click()

Dim cmd1 As New ADODB.Command
Dim errtype As String
On Error GoTo ErrorHandler
errtype = "vb"

If (cnnP.State = adStateOpen) Then
Else
ConSrvr
End If

' Dim dCashCharge As Double
' dCashCharge = CashCharge.Value
' MsgBox dCashCharge

Screen.MousePointer = 11
With cmd1
.ActiveConnection = cnnP
.CommandText = "CreateCash_Sect_Conf_A"
.CommandType = adCmdStoredProc
errtype = "sql"
.Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@StmtDate", adDate, adParamInput, Len(Forms!SysParameters!SysDate), Forms!SysParameters!SysDate)
.Parameters.Append .CreateParameter("@CashChargeRate", adNumeric, adParamInput, Forms!CashOpenPositionChargeForm!CashCharge)
End With
With cmd1
.Parameters("@CashChargeRate").Precision = 2
.Execute

' Forms!CashOpenPositionChargeForm!CashCharge
If .Parameters("RETURN_VALUE").Value = 0 Then 'success
ElseIf .Parameters("RETURN_VALUE").Value <> 0 Then 'failure
MsgBox "Cash Report could not be done with date supplied"
End If
End With

Screen.MousePointer = 0
Exit Sub

ErrorHandler:
MsgBox err.Description

Screen.MousePointer = 0
If errtype = "sql" Then
DisplayADOError cnnP
ElseIf errtype = "vb" Then
MsgBox err.Description
End If
End Sub

Unfortunately I am still getting the same error message as before (Now the error messsage reads "Procedure 'CreateCash_Sect_Conf_A' expects parameter '@CashChargeRate', which was not supplied." This rate is .25 in a text box in Forms!CashOpenPositionChargeForm!CashCharge.) I set up a double variable to see if the value is actually being read and that is happening. I really am lost on this situation. Anybody have any ideas?

Dave