Hi,
     I need help to figure out what to do about this ADO error. The desciption for the error code #
-2147217913 is "Invalid character value for cast specification". I'm sending a date value from a date picker object to a parameterized stored procedure where the date = the date of birth value in the accounts table. That is when I recieve the above error.

The code I use to open a recordset using the stored procedure below is:

dim strDOB as DTPicker

strDOB = me.dtpDOB

'Open the recordset object
objRS.Open "up_parmsel_test (" & strDOB & ")", _
       objConn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

I've tried making the strDOB variable a Date data type and a String data type and I get the same error no matter which data type I use.

The code for the stored procedure is below:

ALTER PROCEDURE dbo.up_parmsel_test
    @DOB_DATE_DT DATETIME AS

-- I've also made the @DOB_Date_DT parameter above a VARCHAR data
-- type and once again, I get the same error.

DECLARE @SQL NVARCHAR(4000),
    @SQLWHERE NVARCHAR(1024)

SET @SQL = ''
SET @SQLWHERE = ''

SET @SQL = 'SELECT ACC_ACCTNUM_VC, ACC_FIRST_NAME_VC, ACC_LAST_NAME_VC
            FROM ACCOUNTS_T'

IF @DOB_Date_DT IS NOT NULL
        BEGIN
                SET @SQLWHERE = @SQLWHERE + '
                    ACCOUNTS_T.ACC_DOB_DATE_DT
                                        = ' + @DOB_Date_DT
        END

-- I've also casted it using the @DOB_Date_DT parameter to be a
-- VARCHAR AND I've converted it like the following
-- "CONVERT(NVARCHAR(10), @DOB_Date_DT,101)", but that also gave me
-- the same error.

IF @SQLWHERE <> ''
    SET @SQL = @SQL + ' WHERE ' + @SQLWHERE

--PRINT @SQL
EXEC sp_executesql @SQL
    WITH RECOMPILE

If I declare the @DOB_Date_DT and set it equal to the date in the DOB_DT field and run the code by itself without running it from VB, the stored procedure pulls up the requested data with no problems. It just doesn't work when calling the stored procedure through VB. I'm using VB 6.0 and SQL Server 2000. Please help! Thanks

Robert

I figured it out. I had to put a single quote in the open recordset code in vb. The following code shows how it should have been , (represented by the bold, red, single quotes), compared to the way it was in my initial post:

'Open the recordset object
objRS.Open "up_parmsel_test ('" & strDOB & "')", _
       objConn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc