Formatting a SQL query

5 Apr 2005 1:16 AM

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

5 Apr 2005 3:28 AM

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

Show quoteHide quote

"Roshawn Dawson" <udr***@bellsouth.net> wrote in message
news:ezjNr0XOFHA.576@TK2MSFTNGP15.phx.gbl...
> 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

5 Apr 2005 5:42 AM

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/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbparameterclasstopic.asp

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

Show quoteHide quote

"Roshawn Dawson" <udr***@bellsouth.net> wrote in message
news:ezjNr0XOFHA.576@TK2MSFTNGP15.phx.gbl...
> 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

5 Apr 2005 3:39 PM

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

5 Apr 2005 4:41 PM

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.

5 Apr 2005 6:28 PM

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

Jag wrote:
Show quoteHide quote

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

6 Apr 2005 12:09 AM

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

--
Val Mazur
Microsoft MVP

http://xport.mvps.org

Show quoteHide quote

"Roshawn Dawson" <udr***@bellsouth.net> wrote in message
news:ezjNr0XOFHA.576@TK2MSFTNGP15.phx.gbl...
> 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