sql parameterized query question mark placement with single quote in code

Wayne Barron
Wayne Barron used Ask the Experts™
on
Hello All;

In the following statement, I need to know how to place the ? for the parameterized Query.
The part we are looking at is
 (CHARINDEX('/?/', '/' + Tracks.LID) > 0)
When run in SSMS, this is the way it will look
 (CHARINDEX('/1/', '/' + Tracks.LID) > 0)
(Replacing the ? with a number)

sqlctLabel.commandtext="SELECT COUNT(DISTINCT Tracks.AlbumID) AS Albums FROM Tracks INNER JOIN Albums ON Tracks.AlbumID = Albums.AlbumID INNER JOIN Artists ON Albums.ArtistID = Artists.ArtistID WHERE (CHARINDEX('/?/', '/' + Tracks.LID) > 0) GROUP BY Artists.ArtistName, Artists.ArtistID"
sqlctLabel.Parameters.Append sqlctLabel.CreateParameter("@LID", adInteger, adParamInput, , strLabelID)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
You need to build an expression:
(CHARINDEX('/' + ? + '/', '/' + Tracks.LID) > 0)

Open in new window

Depending on the SQL object you use you might be able to use the parameter name @LID instead of ?
Wayne BarronAuthor, Web Developer
Top Expert 2009

Author

Commented:
Received the following error.


Microsoft OLE DB Provider for SQL Server error '80040e14'
Argument data type int is invalid for argument 1 of charindex function.

As for the @LID
I tried it using your code, and I get this.


Microsoft OLE DB Provider for SQL Server error '80040e14'
Must declare the scalar variable "@LID".
Wayne BarronAuthor, Web Developer
Top Expert 2009

Author

Commented:
Got it.
Had to change my code around a little from
adInteger
To
adVarChar.

Works now.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Sorry, I should have seen the integer parameter issue ...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial