sql in vb6.0 
Author Message
 sql in vb6.0

hi!
the following code i wrote doesnt work.
can somebody help me with it please!
Dim sqlname As String
Dim dname As String

dname = txtdname.Text
sqlname = "SELECT dvdname FROM disc WHERE dvdname LIKE'%dname%' "
Set RECdisc = DBnero.OpenRecordset(sqlname)
    Do Until RECdisc.EOF
        lstdvd.AddItem RECdisc!dvdName
        RECdisc.MoveNext
    Loop

*** Sent via Developersdex http://www.*-*-*.com/ ***



Tue, 14 Jun 2011 01:05:43 GMT  
 sql in vb6.0

Quote:
> the following code i wrote doesnt work.

"doesn't work" isn't very meaningful
doesn't compile?
reports an error?
doesn't give the expected results?
something else?
if you want help you have to meet people half way...

Quote:
> can somebody help me with it please!
> Dim sqlname As String
> Dim dname As String

> dname = txtdname.Text

using user input directly in a SQL statement opens your app up to sql
injection attacks; at the very least you need to handle ' characters in the
text

Quote:
> sqlname = "SELECT dvdname FROM disc WHERE dvdname LIKE'%dname%' "

there should be a space after LIKE -- it may work without it but makes it
hard to read

Quote:
> Set RECdisc = DBnero.OpenRecordset(sqlname)

you are using DAO?  Is this an Access database?  SQL Server? Oracle? MySQL?
something else?

Quote:
>    Do Until RECdisc.EOF
>        lstdvd.AddItem RECdisc!dvdName

The ! syntax returns a field object and can't be used with numeric indexes
or some column names...  in general I always find it best to use the more
complete syntax for consistency and clarity:
   lstdvd.AddItem RECdisc.Fields("dvdname").Value

Quote:
>        RECdisc.MoveNext
>    Loop

but without know what "doesn't work" all anybody can do is guess at the real
problem

Quote:
> *** Sent via Developersdex http://www.developersdex.com ***

do yourself a favor and use a real newsreader; even Outlook Express or
Windows Mail are better than the web interfaces and developersdex.com has a
pretty low reputation


Tue, 14 Jun 2011 01:17:58 GMT  
 sql in vb6.0


Quote:

> hi!
> the following code i wrote doesnt work.
> can somebody help me with it please!
> Dim sqlname As String
> Dim dname As String

> dname = txtdname.Text
> sqlname = "SELECT dvdname FROM disc WHERE dvdname LIKE'%dname%' "
> Set RECdisc = DBnero.OpenRecordset(sqlname)
>    Do Until RECdisc.EOF
>        lstdvd.AddItem RECdisc!dvdName
>        RECdisc.MoveNext
>    Loop

You need to explain "doesn't work".  That doesn't tell us a thing and so
gives us practically nothing to go on to be able to help you. Are you
getting an error message (or messages).  What are they? If no errors, then
exactly what's not working (not getting any data, not getting the data you
expect, etc.)?

You also need to provide details.  It appears you're using DAO but that's
even not for certain. What data access library are using (DAO, ADO)?  What
is the backend database (Access, SQL Server, something else)?

--
Mike



Tue, 14 Jun 2011 01:28:03 GMT  
 sql in vb6.0

Quote:

>Dim sqlname As String
>Dim dname As String

>dname = txtdname.Text
>sqlname = "SELECT dvdname FROM disc WHERE dvdname LIKE'%dname%' "

I guess that OpenRecordset will fire an error with this statement,
right?

Try like this:

sqlname = "SELECT dvdname FROM disc WHERE dvdname LIKE '" & dname & "'"

Wolfgang



Tue, 14 Jun 2011 03:25:54 GMT  
 sql in vb6.0

Quote:
> sqlname = "SELECT dvdname FROM disc WHERE dvdname LIKE'%dname%' "

A) Everyone's right: you did not post enough details.

B) If, as others have guessed, you are using DAO, then DAO (Jet) does not
support the ANSI-standard wildcards of % and _, but rather the DOS-like
wildcards * and ?.



Tue, 14 Jun 2011 05:26:59 GMT  
 sql in vb6.0


Quote:


>> sqlname = "SELECT dvdname FROM disc WHERE dvdname LIKE'%dname%' "

> A) Everyone's right: you did not post enough details.

> B) If, as others have guessed, you are using DAO, then DAO (Jet) does not
> support the ANSI-standard wildcards of % and _, but rather the DOS-like
> wildcards * and ?.

My question is if the OP meant to use them as wildcards or meant %dname% to
substitute the value into the string since that's not an unusual syntax.
I'd guess the former but either could be true.


Tue, 14 Jun 2011 06:05:47 GMT  
 sql in vb6.0

Quote:





>>> sqlname = "SELECT dvdname FROM disc WHERE dvdname LIKE'%dname%' "

>> A) Everyone's right: you did not post enough details.

>> B) If, as others have guessed, you are using DAO, then DAO (Jet) does not
>> support the ANSI-standard wildcards of % and _, but rather the DOS-like
>> wildcards * and ?.

> My question is if the OP meant to use them as wildcards or meant %dname%
> to substitute the value into the string since that's not an unusual
> syntax. I'd guess the former but either could be true.

In the lines before the SQL statement, he is declaring "dname" as a String
variable, so it's incorrectly quoted.

For the OP, check this article to see why including the TextBox contents
as-is is not a good idea.

http://en.wikipedia.org/wiki/SQL_injection

If you want to secure your DB, use ADO's Parameter Object.



Wed, 15 Jun 2011 04:09:15 GMT  
 sql in vb6.0

Quote:

<cut>
>> My question is if the OP meant to use them as wildcards or meant %dname%
>> to substitute the value into the string since that's not an unusual
>> syntax. I'd guess the former but either could be true.

> In the lines before the SQL statement, he is declaring "dname" as a String
> variable, so it's incorrectly quoted.

There are a number of languages where syntax like this:
x="abc"
y="put %x% here"

results in assigning a value of "put abc here" to y.  Unless the OP comes
back and clarifies there's no way to know what the intent really was.



Wed, 15 Jun 2011 04:40:52 GMT  
 sql in vb6.0
Hi,

As I have this kind of problems, then I use first a simple SQL string to see
where the error can be, by instance
SELECT dvdname FROM disc

Then I know where the error can be.

Cor


Quote:

> hi!
> the following code i wrote doesnt work.
> can somebody help me with it please!
> Dim sqlname As String
> Dim dname As String

> dname = txtdname.Text
> sqlname = "SELECT dvdname FROM disc WHERE dvdname LIKE'%dname%' "
> Set RECdisc = DBnero.OpenRecordset(sqlname)
>    Do Until RECdisc.EOF
>        lstdvd.AddItem RECdisc!dvdName
>        RECdisc.MoveNext
>    Loop

> *** Sent via Developersdex http://www.developersdex.com ***



Wed, 15 Jun 2011 15:56:06 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. Trouble with SQL in VB6

2. SQL and VB6

3. create table by SQL in VB6

4. Store the current time in SQL from VB6

5. SQL-DMO / VB6 Problem

6. Help: How to use SQL in VB6 to access Informix

7. Basic SQL Problem (VB6)

8. Wants to display data from sql thru vb6

9. Query an access database via SQL in VB6

10. SQL in VB6.0

11. SQL x VB6|

12. Updating Tables with SQL in VB6 using vfoxpro tables

 

 
Powered by phpBB® Forum Software