Formatting a SQL query

[Roshawn Dawson]

Hi,

I have the following query written for MS Access:

PARAMETERS [Enter a keyword to search] Text ( 255 );
SELECT TOP 10 ISBN, Title
FROM Books
WHERE Keywords LIKE "*" & [Enter a keyword to search] & "*"
ORDER BY Title;

This query works as expected. Now, how do I write this query in VB.NET
for an OleDBCommand's text property? I tried this but it doesn't seem
to be working:

"Select Top 10 ISBN, Title FROM Books WHERE Keywords Like '*' & ? & '*'
ORDER BY Title ASC;"

Any thoughts?

Thanks,
Roshawn

 

[Travis Murray]

Try:

"Select Top 10 ISBN, Title FROM Books WHERE Keywords Like '*" &
InputBox("Enter a keyword to search") & "*' ORDER BY Title ASC;"

Travis Murray
Artiem Consulting, Inc.
http://www.artiem.com

 

[W.G. Ryan eMVP]

Roshawn - what do you mean by doesn't seem to be working? Is it throwing an
exception? If so the likely culprit is that you aren't adding the
parameters to your OleDbCommand's paramaters collection ie
http://msdn.microsoft.com/library/d...rfsystemdataoledboledbparameterclasstopic.asp

 

[Roshawn Dawson]

Thanks for your reponses.

Travis - I won't be using an InputBox. The parameter's value will
acutally be a querystring value.

Ryan - I have indeed added a parameter to the OleDBCommand's parameter
collection. No errors are generated at all.

At first I was getting a syntax error, but the current rewrite of the
sql string fixed that.

Any more ideas?

Thanks,
Roshawn

 

[Jag]

Hi Roshawn,

In case you are not getting the desired output, try using % in place of
*.
Wild character is % while going through .NET.

Hope this helps.

 

[Roshawn Dawson]

Thanks, Jag. You are a hair-saver and money-saver (you saved me from
pulling out my remaining hairs, thus saving me from buying a hairpiece).

Be Cool

 

[Val Mazur \(MVP\)]

Hi,

Your could should use parameterized query. It should be something like
(assuming that you have defined OledbConnection connection)

Dim lcSQL as string="SELECT TOP 10 ISBN, Title FROM Books WHERE
Keywords LIKE ? ORDER BY Title"
Dim testCMD As OledbCommand = New OledbCommand (lcSQL , PubsConn)
Dim loLikeParam As OledbParameter = testCMD.Parameters.Add
("@LikeParam", OledbType.VarChar, 20)
loLikeParam.Value="Place my value here"

Now you could execute cxommand to get DataTable or to open reader