Please help. Thanks a bunch 1

[Jaheel22]

Please assist in resoliving the following error. I spend a lot of time but could not. Thanks
Actually i marked line 777 which produces error. If i take off "authorized = 'N' and canvassType = 'M'", then no error.

Error
--------
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near '='.
/mySite/allPages/test5.asp, line 19

ASP Code
--------------
Const adCmdStoredProc = &H0004
Const adInteger = 3
Const adVarChar = 200
Const adChar = 129
Const adParamOutput = &H0002
Const adParamInput = &H0001


Set cmd = Server.CreateObject ("ADODB.Command")
cmd.ActiveConnection = Con
cmd.CommandText = "LateResponses"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter ("@TableName",adChar,adParamInput,3,"Z30")
cmd.Parameters.Append cmd.CreateParameter ("@Number",adInteger,adParamOutput)
cmd.Parameters.Append cmd.CreateParameter ("@List",adVarChar,adParamOutput,500)
Set rs = cmd.Execute

intCount = cmd.Parameters("@Number")
intList = cmd.Parameters("@List")
Response.Write(intCount&" : "&intList)


SQL Code/Stored Procedure
------------------------------------
CREATE Proc LateResponses(@stableName char(3),@idCount int output,@idList varchar(500) output)
As
Declare @repPeriod char(2)
Declare @repYear char(4)
Declare @repId varchar(11)
Declare @repIds varchar(500)
Declare @recCount int
Declare @sql varchar(100)

Set @repIds = ''
Set @recCount = 0
-- Line 777
SELECT @sql = 'Declare MyCursor Cursor For Select reportPeriod,reportYear,reportid FROM ' + @stableName + '_tbl where authorized = ''N'' and canvassType = ''M'' '
EXEC(@sql)
--Select reportPeriod,reportYear,reportid from MyTable where authorized = 'N' and canvassType = 'M'
Open MyCursor
Fetch Next from MyCursor into @repPeriod,@repYear,@repId
While @@Fetch_Status = 0
Begin
if DateDiff(dd, Cast(@repPeriod+'/28/'+@repYear as DateTime), getDate()) >= 60
set @recCount = @recCount + 1
set @repIds = @repIds + @repId + ','
Fetch Next from MyCursor into @repPeriod,@repYear,@repId
End

Set @idCount = @recCount
Set @idList = @repIds

Close MyCursor
DeAllocate MyCursor

Return
GO

 

[Chopstik]

I am assuming that line 777 should read with single quotes, as follows:

SELECT @sql = 'Declare MyCursor Cursor For Select reportPeriod,reportYear,reportid FROM ' + @stableName + '_tbl where authorized = 'N' and canvassType = 'M' '

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[Jaheel22]

Douglas Adams
You are right, they are single quotes.
But if i write then as single quotes, then i get this error

Incorrect syntax near ' and canvassType = '.


Thanks a lot

 

[Chopstik]

I'm also not entirely certain why you have single quotes around your entire statement. Could you not simply leave the quotes off entirely? Like this:

SELECT @sql = Declare MyCursor Cursor For Select reportPeriod,reportYear,reportid FROM ' + @stableName + '_tbl where authorized = 'N' and canvassType = 'M'

If you continue to run into problems with this, I suspect it may be with the SQL-side of things and you may be able to find additional assistance in forum183.

Incidentally, I am not Douglas Adams (since he is dead and I prefer to be alive for the time being).

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[Jaheel22]

Chopstik

Sorry for mis-naming. Thanks for your time.
Problem is still there and quotes are necessary as i'm trying to create an sql string and then assigning it to variable @sql.

 

[Chopstik]

Ok, SQL is not my strong suit (particularly when it comes to SP's or cursors), but should you not be SETting the variable value as opposed to SELECTing it? Otherwise, I would again advise that you might receive better assistance by posting this in forum183.

[COLOR=red]SET[/color] @sql = Declare MyCursor Cursor For Select reportPeriod,reportYear,reportid FROM ' + @stableName + '_tbl where authorized = 'N' and canvassType = 'M'

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[Chopstik]

Another thought would be to encase your statement within double quotes. Try this:

SET @sql = "Declare MyCursor Cursor For Select reportPeriod,reportYear,reportid FROM ' + @stableName + '_tbl where authorized = 'N' and canvassType = 'M' "

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou