Please assist

Question

Post reply

Please assist

Mack Khan

SSC Rookie

Points: 39
More actions
August 10, 2004 at 2:02 pm

#108575

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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 1

cmd.Parameters.Append cmd.CreateParameter ("@TableName",adChar,adParamInput,3,"Z30")

vs

CREATE Proc LateResponses(@stableName char(3),@idCount int output,@idList varchar(500) output)

should give a unknown parameter.

Because you dynamicaly define the cursor, your users will have to have tableaccess !

Is your db / table case-sensitive ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 2

Found it !

Define your @sql variable varchar(500).

It is now defined to short !!

Declare @sql varchar(100)

Set @repIds = 'Z30'

Set @recCount = 0

-- Line 777

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

print @sql

gives : Declare MyCursor Cursor For Select reportPeriod,reportYear,reportid FROM Z30_tbl where authorized =