Syntax error converting datetime from character

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Syntax error converting datetime from character

Author

Topic

kaus
Posting Yak Master

179 Posts

Posted - 2001-07-13 : 18:33:10

Hi - I'm getting syntax error converting datetime from character string whenever I try updating a date value in my database via a stored procedure called from an ASP page. Below is the relevant code both on ASP and the stored procedure. I've tested the imput on the ASP side with IsDate and it shows as being a date -- any ideas ?? -- I've also tried using "/" as date separator and using datetime as the datatype on both ASP parameter and in stored procedure -- also tried surrounding string with #

'GET VALUES FROM FORM -- ASP PAGE
if request("ContractMonth") <> "" then
ContractDate = request("ContractMonth") & "-" & request("ContractDay") & "-" & request("ContractYear")
else
ContractDate = ""
end if

'SET COMMAND PARAMETERS -- ASP PAGE
Set prm2 = Cmd.CreateParameter ("contract_date", adVarChar, adParamInput, 25)
prm2.value = ContractDate

Cmd.Execute ************** Error occurs here

'STORED PROCEDURE

CREATE procedure sp_update_fund
@strID varchar(5),
@strContractDate varchar(25)

as
DECLARE @strsql varchar(2000)
DECLARE @strsql1 varchar(100)
DECLARE @strsql2 varchar(100)
DECLARE @strsql3 varchar(100)
SELECT @strsql = 'Update Funding Set '
if @strContractDate = ""
BEGIN
SELECT @strsql1 = 'Contract_Date = NULL'
END
else
BEGIN
SELECT @strsql1 = 'Contract_Date = '' + @strContractDate + '''
END
SELECT @strsql = @strsql + @strsql1 + ' where Project_ID = ' + @strID
exec (@strsql)
--print @strsql

Thanks --

Any help greatly appreciated !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources