Pass smalldatetime parameter to stored procedure 1

[gazzippy]

I have a From_Load event that sets the input parameters and recordsource for a subform.
If I only have the @SEARCH parameter (varchar(50)) in the stored procedure and only pass this parameter it works fine, however if I try to pass the @CREATE_DATE parameter(smalldatetime) I get the following error:-

2757:- There was a problem accessing a property or method of the OLE object.

If I open the stored procedure using ADO Open command and pass parameters this works fine too so I know that the stored procedure works fine.

Below is my Form_Load event

Private Sub Form_Load()
On Error GoTo Error_Handler

'// Set the Input Parameters, set the Subforms recordsource to the stored procedure
With Me.fsubBatch_Track_List.Form
.InputParameters = "@CREATE_DATE = " & gstr_BT_Date & ", @SEARCH = '" & gstr_BT_String & "'"
.RecordSource = "zp_Batch_Track_List"
End With

Exit_Error_Handler:
Exit Sub

Error_Handler:
StdErrMsg
Resume Exit_Error_Handler

End Sub

 

[gazzippy]

Sorted it, forgot to stick quotes around the variable that holds the @CREATE_DATE parameter, also found that identifying the type of variable being passed helps!!

.InputParameters = "@CREATE_DATE smalldatetime = '" & gstr_BT_Date & "', @SEARCH varchar = '" & gstr_BT_String & "'"

 

[davemcdonaldireland]

Cheers gazzippy, I just spent hours wrestling with this, had forgotten to put in the datatype in the .InputParameters string and was getting that generic OLE error. Pity we can't get a meaningful error from Access 2000 for ADO errors. They've fixed it in Access 2002. Let's all pay more monay and upgrade to working software...NOT!

Dave Mc Donald

http://www.mcdonaldit.com