TSQL giving ADO Error

Question

Post reply

TSQL giving ADO Error

jsucupira

SSC Veteran

Points: 219
More actions
November 24, 2010 at 10:54 am

#229056

Hello,
I am trying to figure out what is wrong with this code. I have a ms sql 2005 as my database and access as the front-end. This code used to work on access 2003, but after upgrading to 2010 it is giving me problems.
Thank you for the help.
Create PROCEDURE TestSP
(
@Course varchar(10) = '%',
@Location varchar(50) = '%',
@Term varchar(41) = '%',
@Type varchar(20) = '%',
@status Varchar(20) = '%'
)
as
set nocount on
SELECT DISTINCT Link, Session, Term, Type
FROM dbo.PFMain
WHERE (Course LIKE @Course) AND (Location LIKE @Location) AND (Term LIKE @Term) AND (Type LIKE @Type) AND (Status LIKE @status)
Return

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

what specific error do you get returned?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jsucupira SSC Veteran Points: 219 More actions · Answer 2

The store procedure works, but when I try to change or even just save without making any change I get the following error: ADO error: Incorrect syntax near the keyword 'PROCEDURE'. Must declare the variable '@Status'.

jsucupira SSC Veteran Points: 219 More actions · Answer 3

jsucupira

SSC Veteran

Points: 219

November 29, 2010 at 8:04 am

#1256275

bump

r.stebbens 78867 SSC-Addicted Points: 407 More actions · Answer 4

type, status etc are key words in SQL you might want to try putting square brackets round those columns. eg.

SELECT DISTINCT [Link], [Session], [Term], [Type]

FROM dbo.PFMain

WHERE ([Course] LIKE @Course) AND ([Location] LIKE @Location) AND ([Term] LIKE @Term) AND ([Type] LIKE @Type) AND ([Status] LIKE @status)

Although the error your getting sounds more like a setup problem in Access :-/

Developer, DBA, Pre-Sales consultant.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 5

What exactly are you trying to do in Access? Are you running VBA code? If so, what is it?

Todd Fifield

jsucupira SSC Veteran Points: 219 More actions · Answer 6

This is just a stored procedure. So I am just trying to change a stored procedure that I already have.

jsucupira SSC Veteran Points: 219 More actions · Answer 7

r.stebbens 78867 (11/30/2010)
type, status etc are key words in SQL you might want to try putting square brackets round those columns. eg.
SELECT DISTINCT [Link], [Session], [Term], [Type]
FROM dbo.PFMain
WHERE ([Course] LIKE @Course) AND ([Location] LIKE @Location) AND ([Term] LIKE @Term) AND ([Type] LIKE @Type) AND ([Status] LIKE @status)
Although the error your getting sounds more like a setup problem in Access :-/

I tried this, but it also gave me the same type of error.

jsucupira SSC Veteran Points: 219 More actions · Answer 8

r.stebbens 78867

I think you are right, it is probably an access settings. I just tried the same code on the SQL server and it worked just fine.