smalldatetime

N

Newbie

I have a stored procedure in SQL 2000 that is called with the following code
snippet This works fine from within SQL

When I try to execute the command I get the message:
Run-time error -2147217913
Error converting data type char to smalldatetime

If i strip everything out so that only the date parameters are in the SP it
works fine but as soon as I add another one in I get the above message.

All the variables are set as variant in Access

Here is the code
With cmdQuote
Set .ActiveConnection = cnMIS
.CommandText = "cerro_QuoteFollowUp"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With

' Define stored procedure params and append to command.
params.Append cmdQuote.CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
params.Append cmdQuote.CreateParameter("@Classification", adChar,
adParamInput, 10)
params.Append cmdQuote.CreateParameter("@Customer", adVarChar, adParamInput,
7)
params.Append cmdQuote.CreateParameter("@DateFrom", adDBTimeStamp,
adParamInput, 0)
params.Append cmdQuote.CreateParameter("@DateTo", adDBTimeStamp,
adParamInput, 0)
params.Append cmdQuote.CreateParameter("@ProbFrom", adInteger, adParamInput)
params.Append cmdQuote.CreateParameter("@ProbTo", adInteger, adParamInput)
params.Append cmdQuote.CreateParameter("@Status", adChar, adParamInput, 1)


' Specify input parameter values
params("@DateFrom") = mDateFrom
params("@DateTo") = mDateTo
params("@Customer") = mCustomer
params("@Classification") = mClassification
params("@ProbFrom") = mProbFrom
params("@ProbTo") = mProbTo
params("@Status") = mStatus


' Execute the command
Set rsQuote = cmdQuote.Execute

And fYI here is the stored procedure:

CREATE PROCEDURE dbo.cerro_QuoteFollowUp
@DateFrom smalldatetime = NULL,
@DateTo smalldatetime = NULL,
@Customer varchar(7) = null,
@Classification char (10) = null,
@ProbFrom smallint = null,
@ProbTo smallint = null,
@Status char(1) = null
AS
SELECT QM.Quote,
CASE WHEN QM.QuoteStatus = '0' THEN 'Prep'
when QM.QuoteStatus = '1' then 'Ready for Print'
else 'Printed' end AS Stat,
QM.Classification,
QM.DateTenderReq,
QM.ProbabilityFlag,
QM.Customer,
QM.CustomerName,
QM.DateDeliveryReq,
QN.[Text],
QM.QuoteStatus
FROM QotMaster QM LEFT OUTER JOIN
QUHNotes QN ON QM.Quote = QN.KeyField
where QM.DateDeliveryReq >=@DateFrom and QM.DateDeliveryReq <=
coalesce(@DateTo,@DateFrom) and QM.Customer =
coalesce(@Customer,QM.Customer)
and QM.Classification = coalesce(@Classification,QM.Classification)
and QM.ProbabilityFlag >=@ProbFrom and QM.ProbabilityFlag <=
coalesce(@ProbTo,@ProbFrom)
and ((QM.QuoteStatus in('0','1','2')and @Status is null) or
(QM.QuoteStatus=@Status and @Status is not null))
GO
 
D

Douglas Marquardt

params.Append cmdQuote.CreateParameter("@DateFrom", adDBTimeStamp,
adParamInput, 0)
params.Append cmdQuote.CreateParameter("@DateTo", adDBTimeStamp,
adParamInput, 0)
Click to expand...

I do believe that you do not want to use DBTimeStamp, but rather adDBDate
or adDate


Hope this helps,

Doug.
 
A

Albert Reid

While I am not sure I understand why that error, according to the SQL Server
docs I have, the adDBTimeStamp should be an adTimeStamp. Also, what is the
data type of the mDateFrom and mDateTo? are they String of Date? if they
are string, try :
params("@DateFrom") = CDate(mDateFrom)
params("@DateTo") =CDate( mDateTo)
Click to expand...

Give it a try and post back the results.

---
Al Reid


Newbie said:
I have a stored procedure in SQL 2000 that is called with the following code
snippet This works fine from within SQL

When I try to execute the command I get the message:
Run-time error -2147217913
Error converting data type char to smalldatetime

If i strip everything out so that only the date parameters are in the SP it
works fine but as soon as I add another one in I get the above message.

All the variables are set as variant in Access

Here is the code
With cmdQuote
Set .ActiveConnection = cnMIS
.CommandText = "cerro_QuoteFollowUp"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With

' Define stored procedure params and append to command.
params.Append cmdQuote.CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
params.Append cmdQuote.CreateParameter("@Classification", adChar,
adParamInput, 10)
params.Append cmdQuote.CreateParameter("@Customer", adVarChar, adParamInput,
7)
params.Append cmdQuote.CreateParameter("@DateFrom", adDBTimeStamp,
adParamInput, 0)
params.Append cmdQuote.CreateParameter("@DateTo", adDBTimeStamp,
adParamInput, 0)
params.Append cmdQuote.CreateParameter("@ProbFrom", adInteger, adParamInput)
params.Append cmdQuote.CreateParameter("@ProbTo", adInteger, adParamInput)
params.Append cmdQuote.CreateParameter("@Status", adChar, adParamInput, 1)


' Specify input parameter values
params("@DateFrom") = mDateFrom
params("@DateTo") = mDateTo
params("@Customer") = mCustomer
params("@Classification") = mClassification
params("@ProbFrom") = mProbFrom
params("@ProbTo") = mProbTo
params("@Status") = mStatus


' Execute the command
Set rsQuote = cmdQuote.Execute

And fYI here is the stored procedure:

CREATE PROCEDURE dbo.cerro_QuoteFollowUp
@DateFrom smalldatetime = NULL,
@DateTo smalldatetime = NULL,
@Customer varchar(7) = null,
@Classification char (10) = null,
@ProbFrom smallint = null,
@ProbTo smallint = null,
@Status char(1) = null
AS
SELECT QM.Quote,
CASE WHEN QM.QuoteStatus = '0' THEN 'Prep'
when QM.QuoteStatus = '1' then 'Ready for Print'
else 'Printed' end AS Stat,
QM.Classification,
QM.DateTenderReq,
QM.ProbabilityFlag,
QM.Customer,
QM.CustomerName,
QM.DateDeliveryReq,
QN.[Text],
QM.QuoteStatus
FROM QotMaster QM LEFT OUTER JOIN
QUHNotes QN ON QM.Quote = QN.KeyField
where QM.DateDeliveryReq >=@DateFrom and QM.DateDeliveryReq <=
coalesce(@DateTo,@DateFrom) and QM.Customer =
coalesce(@Customer,QM.Customer)
and QM.Classification = coalesce(@Classification,QM.Classification)
and QM.ProbabilityFlag >=@ProbFrom and QM.ProbabilityFlag <=
coalesce(@ProbTo,@ProbFrom)
and ((QM.QuoteStatus in('0','1','2')and @Status is null) or
(QM.QuoteStatus=@Status and @Status is not null))
GO
Click to expand...
 
N

Newbie

Thanks for that but I have narrowed it down to the @ProbFrom and @ProbTo
parameters causing the problem - the date error msg was a red herring!

the field that Probability is stored in is a
Decimal
Precision 1
Scale 0

I have the parameter in the stored procedure set to an integer
when I run this in query analyser - there are no problems it returns the
records I expect it to

However when I run the vb code - no records are returned even though there
are

What am I doing wrong?
Albert Reid said:
While I am not sure I understand why that error, according to the SQL Server
docs I have, the adDBTimeStamp should be an adTimeStamp. Also, what is the
data type of the mDateFrom and mDateTo? are they String of Date? if they
are string, try :
params("@DateFrom") = CDate(mDateFrom)
params("@DateTo") =CDate( mDateTo)
Click to expand...

Give it a try and post back the results.

---
Al Reid


Newbie said:
I have a stored procedure in SQL 2000 that is called with the following code
snippet This works fine from within SQL

When I try to execute the command I get the message:
Run-time error -2147217913
Error converting data type char to smalldatetime

If i strip everything out so that only the date parameters are in the SP it
works fine but as soon as I add another one in I get the above message.

All the variables are set as variant in Access

Here is the code
With cmdQuote
Set .ActiveConnection = cnMIS
.CommandText = "cerro_QuoteFollowUp"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With

' Define stored procedure params and append to command.
params.Append cmdQuote.CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
params.Append cmdQuote.CreateParameter("@Classification", adChar,
adParamInput, 10)
params.Append cmdQuote.CreateParameter("@Customer", adVarChar, adParamInput,
7)
params.Append cmdQuote.CreateParameter("@DateFrom", adDBTimeStamp,
adParamInput, 0)
params.Append cmdQuote.CreateParameter("@DateTo", adDBTimeStamp,
adParamInput, 0)
params.Append cmdQuote.CreateParameter("@ProbFrom", adInteger, adParamInput)
params.Append cmdQuote.CreateParameter("@ProbTo", adInteger, adParamInput)
params.Append cmdQuote.CreateParameter("@Status", adChar, adParamInput, 1)


' Specify input parameter values
params("@DateFrom") = mDateFrom
params("@DateTo") = mDateTo
params("@Customer") = mCustomer
params("@Classification") = mClassification
params("@ProbFrom") = mProbFrom
params("@ProbTo") = mProbTo
params("@Status") = mStatus


' Execute the command
Set rsQuote = cmdQuote.Execute

And fYI here is the stored procedure:

CREATE PROCEDURE dbo.cerro_QuoteFollowUp
@DateFrom smalldatetime = NULL,
@DateTo smalldatetime = NULL,
@Customer varchar(7) = null,
@Classification char (10) = null,
@ProbFrom smallint = null,
@ProbTo smallint = null,
@Status char(1) = null
AS
SELECT QM.Quote,
CASE WHEN QM.QuoteStatus = '0' THEN 'Prep'
when QM.QuoteStatus = '1' then 'Ready for Print'
else 'Printed' end AS Stat,
QM.Classification,
QM.DateTenderReq,
QM.ProbabilityFlag,
QM.Customer,
QM.CustomerName,
QM.DateDeliveryReq,
QN.[Text],
QM.QuoteStatus
FROM QotMaster QM LEFT OUTER JOIN
QUHNotes QN ON QM.Quote = QN.KeyField
where QM.DateDeliveryReq >=@DateFrom and QM.DateDeliveryReq <=
coalesce(@DateTo,@DateFrom) and QM.Customer =
coalesce(@Customer,QM.Customer)
and QM.Classification = coalesce(@Classification,QM.Classification)
and QM.ProbabilityFlag >=@ProbFrom and QM.ProbabilityFlag <=
coalesce(@ProbTo,@ProbFrom)
and ((QM.QuoteStatus in('0','1','2')and @Status is null) or
(QM.QuoteStatus=@Status and @Status is not null))
GO
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

no records returned but should be 3
Help with ADO stored procedure 1

Top