• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 696
  • Last Modified:

Passing Input Parameters to Stored Procedure - Syntax Hell.

Hi Experts,

If anyone can help determine where I am going wrong I will give big points, high marks and mucho gratitude.  I am sending input parameters to a stored procedure from an asp page.  Below is my code followed by the error.  Is their a hint in the fact that the post data in the error ends after the 15th parameter? Is there a limit I've exceeded?  Too easy?  I'm a SQL newbie so forgive the complete ignorance.

Code:

<%@ LANGUAGE="VBSCRIPT" %>
      <%response.buffer = true%>

 
            <%
              '*********** request parameters *************

              Dim month, year
              Smonth = request.form("Smonth")
              Syear =  request.form("Syear")
              Emonth = request.form("Emonth")
              Eyear =  request.form("Eyear")
              channel =  request.form("channel")
              region = request.form("region")
              repAttribute = request.form("repAttribute")
              hireSmonth = request.form("hireSmonth")
              hireSyear = request.form("hireSyear")
              hireEmonth = request.form("hireEmonth")
                        hireEyear = request.form("hireEyear")
              churnSmonth = request.form("churnSmonth")
              churnSyear = request.form("churnSyear")
              churnEmonth = request.form("churnEmonth")
              churnEyear = request.form("churnEyear")
              loadactivity = request.form("loadactivity")
              jobFunction = request.form("job")


FUNCTION ListsRes (nums)
if len(nums) = 0 or nums =0 then
response.write "0"
else
response.write nums
end if
End Function

        '********* db connection ***************

      Set objConn = Server.CreateObject("ADODB.Connection")
      objConn.Open "Provider=sqloledb;data source=(local);Initial Catalog=TMreport;User Id=sa;Password=volks"


Set SQLCmd = server.createObject ("ADODB.Command")
SQLCmd.ActiveConnection = objConn
SQLCmd.Commandtext= "UP_product_national "&Smonth&","&Syear&","&Emonth&","&Eyear&",'"&Channel&"','"&Region&"','"&RepAttribute&","&HireSmonth&","&HireSyear&","&HireEmonth&","&HireEyear&","&ChurnSmonth&","&ChurnSyear&","&ChurnEmonth&","&ChurnEyear&",'"&loadActivity&"','"&Job
                        
'SQLCmd.CommandType = 1

SQLCmd.CommandTimeout = 20000
'SQLCmd.Parameters.Append SQLCmd.createParameter("GSA",adFloat,adParamReturnvalue)
Set ress = SQLCmd.execute
'Dim dataSQL (50)
                  
R=0
ress.movefirst
Do while not ress.eof

%>

And here's the error ...

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near ','.
/telusreports/srdb_1.asp, line 58

Page:
POST 231 bytes to /reports/srdb_1.asp

POST Data:
smonth=3&sYear=2004&emonth=&eyear=&channel=%22Independent+Dealers%22&region=&repAttribute=%22Outbound%22&hiresmonth=&hiresYear=&hireemonth=&hireeyear=&churnsmonth=&churnsYear=&churnemonth=&churneyear= . . .


0
Lindsay44
Asked:
Lindsay44
  • 4
  • 2
  • 2
2 Solutions
 
Anthony PerkinsCommented:
Change this:
'SQLCmd.CommandType = 1

To:
SQLCmd.CommandType = 4
0
 
Anthony PerkinsCommented:
>>Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near ','.<<
Since this error is in the Stored Procedure, please post the Stored Procedure.
0
 
laotzi2000Commented:
It seems you missed a ' after RepAttribute
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
laotzi2000Commented:
and after job too
0
 
Anthony PerkinsCommented:
Good catch.
0
 
Lindsay44Author Commented:
Excellent eye laotzi2000.  I think I'd been looking at that for hours!!  BUT, made the fix and still getting the same error.
Here's my oversized Stored Procedure if it helps -- trying to make this report work for me boss tomorrow.  ugh.  Really appreciate your time.

CREATE Procedure UP_Product_NATIONAL
(
@@sMonth int,
@@sYear int,
@@eMonth int,
@@eYear int,
@@CHANNEL NVARCHAR (20),
@@REGION NVARCHAR (5),
@@DDM NVARCHAR (50),
@@REPATTRIBUTE NVARCHAR (30),
@@HIRESMONTH INT,
@@HIRESYEAR INT,
@@HIREEMONTH INT,
@@HIREEYEAR INT,
@@CHURNSMONTH INT,
@@CHURNSYEAR INT,
@@CHURNEMONTH INT,
@@CHURNEYEAR INT,
@@LOADACTIVITY NVARCHAR (10),
@@JOB NVARCHAR (30)
)

AS

DECLARE @FCOMMAND NVARCHAR (2000)
DECLARE @HIRESDATE DATETIME
DECLARE @HIREEDATE DATETIME
DECLARE @CHURNSDATE DATETIME
DECLARE @CHURNEDATE DATETIME

IF @@HIRESMONTH=''
BEGIN
SET @HIRESDATE =''
END
ELSE
BEGIN
SET @HIRESDATE = CONVERT(DATETIME, ((CONVERT(CHAR,@@HIRESMONTH))+'/1/'+(CONVERT(CHAR,@@HIRESYEAR))))
END

IF @@HIREEMONTH=''
BEGIN
SET @HIREEDATE =@HIRESDATE
END
ELSE
BEGIN
SET @HIREEDATE = CONVERT(DATETIME, ((CONVERT(CHAR,@@HIREEMONTH))+'/1/'+(CONVERT(CHAR,@@HIREEYEAR))))
END

IF @@CHURNSMONTH=''
BEGIN
SET @CHURNSDATE =''
END
ELSE
BEGIN
SET @CHURNSDATE = CONVERT(DATETIME, ((CONVERT(CHAR,@@CHURNSMONTH))+'/1/'+(CONVERT(CHAR,@@CHURNSYEAR))))
END

IF @@CHURNEMONTH=''
BEGIN
SET @CHURNEDATE = @CHURNSDATE
END
ELSE
BEGIN
SET @CHURNEDATE = CONVERT(DATETIME, ((CONVERT(CHAR,@@CHURNEMONTH))+'/1/'+(CONVERT(CHAR,@@CHURNEYEAR))))
END
SET @FCOMMAND = N'SELECT ul.salesrepid,channels,mike,pcs,tdata,inputdate,nccssalespersonid,effectivedt,jobfunction,prov,attribute,expirationdate,us.DDMFullname,US.SALESREPFULLNAME from user_loads
      as ul join user_salesrep as us on ul.salesrepid = us.salesrepid
      WHERE 1=1 '

IF @@CHANNEL<>''
BEGIN
SET @FCOMMAND = @FCOMMAND + N' AND CHANNELS = '''+@@CHANNEL+''''
END

IF @@DDM<>''
BEGIN
SET @FCOMMAND = @FCOMMAND + N' AND DDMFullname = '''+@@DDM+''''
END

IF @@REPATTRIBUTE<>''
BEGIN
SET @FCOMMAND = @FCOMMAND + N' AND ATTRIBUTE = @REPATTRIBUTE'
END

IF @HIRESDATE<>''
BEGIN
SET @FCOMMAND = @FCOMMAND + N' AND DATEDIFF(MM,effectivedt,@HIRSD)<=0 AND DATEDIFF(MM,effectivedt,@HIRED)>=0'
END

IF @CHURNSDATE<>''
BEGIN
SET @FCOMMAND = @FCOMMAND + N' AND DATEDIFF(MM,ExpirationDate,@CHURNSD)<=0 AND DATEDIFF(MM,ExpirationDate,@CHURNED)>=0'
END

IF @@LOADACTIVITY='ACTIVE'
BEGIN
SET @FCOMMAND = @FCOMMAND + N' AND (MIKE+PCS+TDATA)>0 '
END

IF @@LOADACTIVITY='INACTIVE'
BEGIN
SET @FCOMMAND = @FCOMMAND + N' AND (MIKE+PCS+TDATA)=0'
END

IF CHARINDEX('ALL',@@JOB)>0
BEGIN
      IF @@JOB = 'All (excluding Dealer Principals)'
      BEGIN
      SET @FCOMMAND = @FCOMMAND + N' AND AND jobfunction <>''Dealer Principals'''
      END
END
ELSE
BEGIN
      IF @@JOB<>''
      BEGIN
      SET @FCOMMAND = @FCOMMAND + N' AND jobfunction ='''+@@JOB+''''
      END
END

SET @FCOMMAND = @FCOMMAND + N' group by ul.salesrepid,channels,mike,pcs,tdata,inputdate,nccssalespersonid,effectivedt,jobfunction,prov,attribute,expirationdate,us.DDMFullname,SALESREPFULLNAME'

      SET NOCOUNT ON
      CREATE TABLE ##NATIONALSUM1
      (
      salesrepid NVARCHAR (20),
      channel NVARCHAR (70),
      mike INT,
      pcs INT,
      tdata INT,
      inputdate DATETIME,
      nccssalespersonid NVARCHAR (40),
      effectivedt DATETIME,
      jobfunction NVARCHAR (30),
      prov NVARCHAR (20),
      attribute NVARCHAR (30),
      expirationdate DATETIME,
      DDMFullname NVARCHAR (50),
      SALESREPFULLNAME NVARCHAR (150)
            )
      INSERT INTO ##NATIONALSUM1
            EXEC SP_EXECUTESQL @FCOMMAND,N'@REPATTRIBUTE NVARCHAR (30),@CHURNSD datetime,@CHURNED datetime,@HIRSD DATETIME,@HIRED DATETIME',
                   @REPATTRIBUTE=@@REPATTRIBUTE,
                  @CHURNSD=@CHURNSDATE,
                  @CHURNED=@CHURNEDATE,
                  @HIRSD = @HIRESDATE,
                  @HIRED=@HIREEDATE

DECLARE @sYear int
DECLARE @eYear int
DECLARE @sMonth int
DECLARE @eMonth int

set @sYear = @@sYear
set @sMonth = @@sMonth
-- set @eYear = @@eYear
-- set @eMonth = @@eMonth

IF @@eMonth=''
BEGIN
SET @eMonth = @sMonth
END
ELSE
BEGIN
SET @eMonth = @@eMonth
END

IF @@eYear=''
BEGIN
SET @eYear = @sYear
END
ELSE
BEGIN
SET @eYear = @@eYear
END

DECLARE @PROV VARCHAR (5)
DECLARE @TEMPSTARTDATE DATETIME
DECLARE @TEMPENDDATE DATETIME
DECLARE @YASDATE DATETIME
DECLARE @YAEDATE DATETIME

SET @TEMPSTARTDATE = CONVERT(DATETIME, ((CONVERT(CHAR,@sMonth))+'/1/'+(CONVERT(CHAR,@sYear))))
SET @TEMPENDDATE = CONVERT(DATETIME, ((CONVERT(CHAR,@eMonth))+'/1/'+(CONVERT(CHAR,@eYear))))
SET @YASDATE = CONVERT(DATETIME, ((CONVERT(CHAR,@sMonth))+'/1/'+(CONVERT(CHAR,@sYear-1))))
SET @YAEDATE = CONVERT(DATETIME, ((CONVERT(CHAR,@eMonth))+'/1/'+(CONVERT(CHAR,@eYear-1))))

DECLARE @Q1SDATE DATETIME
DECLARE @Q2SDATE DATETIME
DECLARE @Q3SDATE DATETIME
DECLARE @Q4SDATE DATETIME
DECLARE @Q1EDATE DATETIME
DECLARE @Q2EDATE DATETIME
DECLARE @Q3EDATE DATETIME
DECLARE @Q4EDATE DATETIME

SELECT @Q1SDATE = DBO.QUO_DATE (@SMonth,@sYear,1,'S')
SELECT @Q1EDATE = DBO.QUO_DATE (@SMonth,@sYear,1,'E')
SELECT @Q2SDATE = DBO.QUO_DATE (@SMonth,@sYear,2,'S')
SELECT @Q2EDATE = DBO.QUO_DATE (@SMonth,@sYear,2,'E')
SELECT @Q3SDATE = DBO.QUO_DATE (@SMonth,@sYear,3,'S')
SELECT @Q3EDATE = DBO.QUO_DATE (@SMonth,@sYear,3,'E')
SELECT @Q4SDATE = DBO.QUO_DATE (@SMonth,@sYear,4,'S')
SELECT @Q4EDATE = DBO.QUO_DATE (@SMonth,@sYear,4,'E')

declare @currstart datetime
SET @currstart  = CONVERT(DATETIME, ('1/1/'+(CONVERT(CHAR,@sYear))))

DECLARE @YTDYASDATE DATETIME
SET @YTDYASDATE  = CONVERT(DATETIME, ('1/1/'+(CONVERT(CHAR,@sYear-1))))
DECLARE @YTDYAEDATE DATETIME
SET @YTDYAEDATE  = CONVERT(DATETIME, ((CONVERT(CHAR,@sMonth))+'/1/'+(CONVERT(CHAR,@sYear-1))))

DECLARE @MNUM INT
SET @MNUM= DATEDIFF(mm,@TEMPSTARTDATE,@TEMPENDDATE)+1
TRUNCATE TABLE GENERALTEMP

DECLARE RESALE CURSOR FAST_FORWARD FOR
select prov from user_salesrep where prov is not null
group by prov

OPEN RESALE
FETCH NEXT FROM RESALE INTO @PROV
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC UP_GENERIC_PRODUCTIBITY @currstart,
@TEMPSTARTDATE,
@TEMPENDDATE,
@YASDATE,
@YAEDATE,
@YTDYASDATE,
@YTDYAEDATE,
@Q1SDATE,
@Q2SDATE,
@Q3SDATE,
@Q4SDATE,
@Q1EDATE,
@Q2EDATE,
@Q3EDATE,
@Q4EDATE,
@MNUM,
@PROV

FETCH NEXT FROM RESALE INTO @PROV
END
CLOSE RESALE
DEALLOCATE RESALE
EXEC UP_GENERATEREPORT_NATIONAL

TRUNCATE TABLE GENERALTEMP

DROP TABLE ##NATIONALSUM1

SET NOCOUNT OFF

GO
0
 
Anthony PerkinsCommented:
You are missing the DDM parameter.
0
 
Lindsay44Author Commented:
That's why you should always code with a buddy.
Thank-you both for the eagle-eyed review, I feel a bit dumb, but I'm kinda glad it was so simple.

Cheers, Lindsay.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now