Prev: NEOPOINT GENERATOR
Next: DHCP VB6 read return broadcast on UDP 68?
From: Howie429 on 3 Feb 2005 04:19
this statement worked in vb5 but in 6 it comes up as an error


strSQL = "Select * from PriceGuide where Title like '" & UnionTitle & "'*' and
number=" & UnionNumber & " order by title"
Call GuideSet.Open(strSQL, MyConnection, _
adOpenStatic, adLockOptimistic, adCmdText)


any help would be appreciated.

howard
From: John Blessing on 3 Feb 2005 04:55
"Howie429" <howie429(a)aol.com> wrote in message
news:20050203041937.13716.00000267(a)mb-m23.aol.com...
> this statement worked in vb5 but in 6 it comes up as an error
>
>
> strSQL = "Select * from PriceGuide where Title like '" & UnionTitle & "'*'
> and
> number=" & UnionNumber & " order by title"
> Call GuideSet.Open(strSQL, MyConnection, _
> adOpenStatic, adLockOptimistic, adCmdText)
>
>
> any help would be appreciated.
>
> howard

Use % instead of * as the wildcard character

--
John Blessing

http://www.LbeHelpdesk.com - Help Desk software priced to suit all
businesses
http://www.room-booking-software.com - Schedule rooms & equipment bookings
for your meeting/class over the web.
http://www.lbetoolbox.com - Remove Duplicates from MS Outlook


From: Jason Keats on 3 Feb 2005 07:09
Howie429 wrote:
> this statement worked in vb5 but in 6 it comes up as an error
>
>
> strSQL = "Select * from PriceGuide where Title like '" & UnionTitle &
> "'*' and number=" & UnionNumber & " order by title"
> Call GuideSet.Open(strSQL, MyConnection, _
> adOpenStatic, adLockOptimistic, adCmdText)
>
>
> any help would be appreciated.
>
> howard

It's never going to work with the single apostrophe in front of the * (not
even in VB5).

If that doesn't fix it, then also replace * with % (depends on database and
data access technology being used).


From: Gregory Dean on 3 Feb 2005 09:15
On 2/3/05 4:19 AM, in article 20050203041937.13716.00000267(a)mb-m23.aol.com,
"Howie429" <howie429(a)aol.com> wrote:

> this statement worked in vb5 but in 6 it comes up as an error
>
>
> strSQL = "Select * from PriceGuide where Title like '" & UnionTitle & "'*' and
> number=" & UnionNumber & " order by title"
> Call GuideSet.Open(strSQL, MyConnection, _
> adOpenStatic, adLockOptimistic, adCmdText)
>
>
> any help would be appreciated.
>
> howard

% is a wildcard on a Microsoft SQL query. The entire like statement
including the wildcard will need to be in single quotes. I also use parens:

strSQL = "SELECT * FROM PriceGuide WHERE Title LIKE('" & UnionTitle & "%')
AND number=" & UnionNumber & " ORDER title"

This will return only those records that begin with the variable
"UnionTitle". If you want the title to contain the text stored in this
variable, use a wild card at each end:

strSQL = "SELECT * FROM PriceGuide WHERE Title LIKE('%" & UnionTitle & "%')
AND number=" & UnionNumber & " ORDER title"

From: Michael B. Johnson on 4 Feb 2005 10:59
On Thu, 3 Feb 2005 23:09:59 +1100, "Jason Keats"
<jkeats(a)melbpcDeleteThis.org.au> wrote:

<snipped/>

>It's never going to work with the single apostrophe in front of the * (not
>even in VB5).
>
>If that doesn't fix it, then also replace * with % (depends on database and
>data access technology being used).

You've got my vote, Jason. You've already given everyone the key, but
I'll say it again, in hopes it will be listened to...

Jet 3.51 uses * (MS Access '97), but Jet 4 requires % (MS Access
2000+). MS SQL requires % for the wildcard, and so on...

Place a stop statement in your code just before the query executes and
use debug.print to review the SQL statement and make sure the quotes
are all balanced.

Or use Clip on http://sf.net/projects/vbutils to convert a SQL query
to a VB string so the computer can make the mistake of not balancing
the quotes, not you. Then you can legitimately blame the computer and
not yourself when something goes wrong, as it will. (Right click app
in system tray, menu sequence: SQL, to VB string.)

Assign that to a string variable for ease of debugging/comprehension.
Then run the query through a cn.Execute, rs.Open, whatever.
_______________________
Michael B. Johnson
 | 
Pages: 1
Prev: NEOPOINT GENERATOR
Next: DHCP VB6 read return broadcast on UDP 68?