Error passing variable to Stored Procedure

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

Transact-SQL (2000)

Error passing variable to Stored Procedure

Author

Topic

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-08-24 : 16:11:12

I am passing a variable in an ASP page to a stored procedure. It is a datetime field in the database that I am trying to query to.

I keep getting a syantax error on the asp page:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near '/'.

/go resource center/intranet II/roster/mgmt/reports/Protocol.asp, line 70

Here is my stored procedure:

CREATE PROCEDURE Protocol
@PassDate datetime
AS
SELECT *

FROM table where table.date > '"& @PassDate &"'
ORDER BY table.name
GO

Here is how I am calling the SP in the ASP page:

<% Dim PassDate
PassDate = request("PassDate")
%>

<% Set mCon2=server.createobject("ADODB.connection")
mCon2.open "Provider=SQLOLEDB; Data Source=GOMODB; Initial Catalog=xxxxxx; User Id=xxxxxx; Password=xxxxxx;"
set orr2 = server.createobject("adodb.recordset")
Stt2 = "Protocol " & PassDate
orr2.Open Stt2, mCon2%>

Any help or sugguestions on correcting this issue would be greatly appreciated.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 16:16:52

SELECT *
FROM table where table.date > '"& @PassDate &"'
ORDER BY table.name

Don't use SELECT *. It's a performance hit.

And it should be:

SELECT Column1, Column2
FROM table
WHERE date > @PassDate
ORDER BY name

Tara

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-08-24 : 16:28:12

Ok changed SP to this.. and still doesn't work, get the same error.

CREATE PROCEDURE Protocol
@PassDate datetime
AS
SELECT Table.DATE, Table.First_Name, Table.Last_Name, Table.Rank
FROM Table
WHERE Table.DATE > @PassDate
ORDER BY Table.LAST_NAME
GO

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-25 : 00:04:29

Run that in debug. Show us exactly what it's trying to send to the database server. This isn't a SQL error though, it's an ASP error. You might want to check out an ASP forum.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-08-25 : 09:47:50

Ok, I will make the same post in the ASP forum.

Thi is the error on the ASP page.

I keep getting a syantax error on the asp page:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near '/'.

/go resource center/intranet II/roster/mgmt/reports/Protocol.asp, line 70

When I check syntax in the SP it says its fine.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-25 : 10:02:24

i think you need to pass it like this
Stt2 = "Protocol '" & PassDate & "'"
when passing date like that you need to pass it as a string.
becasue your error says Incorrect syntax near '/'. i'm guessing your date format is dd/mm/yyyy.

Go with the flow & have fun! Else fight the flow :)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-08-25 : 11:14:35

That was definately the case:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39038

Duane.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-25 : 11:22:47

yeah... i saw it and you sniped me by mere 20 seconds :)))))

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources