Formatting a SQL query

  • Thread starter Thread starter Roshawn Dawson
  • Start date Start date
R

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
 
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
 
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
 
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.
 
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
 
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
 
Back
Top