SQL string not works in VB and works in Access 2000 
Author Message
 SQL string not works in VB and works in Access 2000

I had some problem with the SQL string in my program. It returned more
records of other type than it searched for. so i changed the OR to AND and
it didn't return anyting. so i turned to Access to work out the problem
there with a query. When i finaly got what i wanted, I copied that SQL
string back to VB test program. But it does not return anything.

Here is a short version of my test program code

Dim SQLCommand As String
Dim adomoredb As New ADODB.Connection
Dim AdoMoreRec As New ADODB.Recordset
If adomoredb.State = adStateClosed Then
    adomoredb.ConnectionString = "DSN=More View Database"
    adomoredb.Open
    AdoMoreRec.ActiveConnection = adomoredb
    AdoMoreRec.CursorType = adOpenStatic
    AdoMoreRec.CursorLocation = adUseClient
End If
SQLCommand = "SELECT bilder.*, Bilder.Bildefil, Bilder.visaar,
Bilder.Plakattype From Bilder WHERE (((Bilder.Bildefil) Like '*h&m*') AND
((Bilder.visaar)='2001') AND ((Bilder.Plakattype)='boards' or
(Bilder.Plakattype)='colorbuss'))"  'all is on one line in VB and this is
the SQL string from Access 2000

AdoMoreRec.Open SQLCommand
Label1.Caption = AdoMoreRec.RecordCount
If adomoredb.State = adStateClosed Then
        Set AdoMoreRec = Nothing
        Set adomoredb = Nothing
    Else
        AdoMoreRec.Close
        adomoredb.Close
        Set AdoMoreRec = Nothing
        Set adomoredb = Nothing
    End If

What i trying to do is to get all occurrence of an item
say you have a database of car models and you want all Ford's and VW's of
2001 with color red.
in my test it ether resultetd in non as the code above. Or I got all of one
type but not the other.

Petter L.



Tue, 17 Feb 2004 17:48:59 GMT  
 SQL string not works in VB and works in Access 2000
Petter

Try
Like ('h&m%') or ('%h&m%')

Sukesh


Quote:
> I had some problem with the SQL string in my program. It returned more
> records of other type than it searched for. so i changed the OR to AND and
> it didn't return anyting. so i turned to Access to work out the problem
> there with a query. When i finaly got what i wanted, I copied that SQL
> string back to VB test program. But it does not return anything.

> Here is a short version of my test program code

> Dim SQLCommand As String
> Dim adomoredb As New ADODB.Connection
> Dim AdoMoreRec As New ADODB.Recordset
> If adomoredb.State = adStateClosed Then
>     adomoredb.ConnectionString = "DSN=More View Database"
>     adomoredb.Open
>     AdoMoreRec.ActiveConnection = adomoredb
>     AdoMoreRec.CursorType = adOpenStatic
>     AdoMoreRec.CursorLocation = adUseClient
> End If
> SQLCommand = "SELECT bilder.*, Bilder.Bildefil, Bilder.visaar,
> Bilder.Plakattype From Bilder WHERE (((Bilder.Bildefil) Like '*h&m*') AND
> ((Bilder.visaar)='2001') AND ((Bilder.Plakattype)='boards' or
> (Bilder.Plakattype)='colorbuss'))"  'all is on one line in VB and this is
> the SQL string from Access 2000

> AdoMoreRec.Open SQLCommand
> Label1.Caption = AdoMoreRec.RecordCount
> If adomoredb.State = adStateClosed Then
>         Set AdoMoreRec = Nothing
>         Set adomoredb = Nothing
>     Else
>         AdoMoreRec.Close
>         adomoredb.Close
>         Set AdoMoreRec = Nothing
>         Set adomoredb = Nothing
>     End If

> What i trying to do is to get all occurrence of an item
> say you have a database of car models and you want all Ford's and VW's of
> 2001 with color red.
> in my test it ether resultetd in non as the code above. Or I got all of
one
> type but not the other.

> Petter L.



Tue, 17 Feb 2004 17:54:33 GMT  
 SQL string not works in VB and works in Access 2000
As Sukesh said ....

Access likes the *
But Ado likes the %

VOrtex


Quote:
> I had some problem with the SQL string in my program. It returned more
> records of other type than it searched for. so i changed the OR to AND and
> it didn't return anyting. so i turned to Access to work out the problem
> there with a query. When i finaly got what i wanted, I copied that SQL
> string back to VB test program. But it does not return anything.

> Here is a short version of my test program code

> Dim SQLCommand As String
> Dim adomoredb As New ADODB.Connection
> Dim AdoMoreRec As New ADODB.Recordset
> If adomoredb.State = adStateClosed Then
>     adomoredb.ConnectionString = "DSN=More View Database"
>     adomoredb.Open
>     AdoMoreRec.ActiveConnection = adomoredb
>     AdoMoreRec.CursorType = adOpenStatic
>     AdoMoreRec.CursorLocation = adUseClient
> End If
> SQLCommand = "SELECT bilder.*, Bilder.Bildefil, Bilder.visaar,
> Bilder.Plakattype From Bilder WHERE (((Bilder.Bildefil) Like '*h&m*') AND
> ((Bilder.visaar)='2001') AND ((Bilder.Plakattype)='boards' or
> (Bilder.Plakattype)='colorbuss'))"  'all is on one line in VB and this is
> the SQL string from Access 2000

> AdoMoreRec.Open SQLCommand
> Label1.Caption = AdoMoreRec.RecordCount
> If adomoredb.State = adStateClosed Then
>         Set AdoMoreRec = Nothing
>         Set adomoredb = Nothing
>     Else
>         AdoMoreRec.Close
>         adomoredb.Close
>         Set AdoMoreRec = Nothing
>         Set adomoredb = Nothing
>     End If

> What i trying to do is to get all occurrence of an item
> say you have a database of car models and you want all Ford's and VW's of
> 2001 with color red.
> in my test it ether resultetd in non as the code above. Or I got all of
one
> type but not the other.

> Petter L.



Tue, 17 Feb 2004 22:23:02 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. HELP: VB 6 works with Access97 file, not Access 2000 file

2. VB DataControl not working with Access 2000

3. ExtCreatePen does not work in Windows 2000 but works in Windows 98

4. Access 2000 VBA code not working Access 2002

5. DLL usage - works in Access 97 but not in Access 2000

6. Code from Access 2.0 not working in Access 2000

7. ApplyFilter not working in Access 2000

8. Step into - F8 not working correctly in Access 2000

9. DAO not working in Access 2000

10. Multiuser adds in Access 2000 not working

11. Access 2000 format not working

12. Correlated subquery in DELETE stmt not work in Access '97 or 2000

 

 
Powered by phpBB® Forum Software