OpenSQL Help

[psyXonova]

Ok, I have a very specific question about a problem that it gets on my nerves

Code: Select all

QSQL = "SELECT Playlists.ParentPlaylist FROM Playlists WHERE Playlists.PlaylistName = " & Chr(34) & "Last 50 Played" & Chr(34)
SDB.Database.OpenSQL(QSQL)

Why the spesific query returns an error? (Too few parameters, expected 1)
This query runs just fine from Inside Access

I know it has to do with the fact that Playlist.PlaylistName is a text field. But even if i replace "=" with "like" i get the same message
On the other hand if the WHERE clause is something like "WHERE Playlists.IDPlaylist = 1) it works just fine....

Plz help me

Thanks in advance

[Bex]

I dont know VBScript but wouldnt this work?

Code: Select all

QSQL = "SELECT Playlists.ParentPlaylist FROM Playlists WHERE Playlists.PlaylistName = 'Last 50 Played'"
SDB.Database.OpenSQL(QSQL) 

I mean why do you have Chr(34) in the code?

Or perhaps this one will work:

Code: Select all

QSQL = "SELECT Playlists.ParentPlaylist FROM Playlists WHERE Playlists.PlaylistName = " & Chr(34) & "'Last 50 Played'" & Chr(34)
SDB.Database.OpenSQL(QSQL) 

[Peke]

Becouse VBScript uses parenthals to determine String(Text) Type and Commands also needs Parenthals in String Parameters.

When Code Is executed it will look like this:

Code: Select all

QSQL = "SELECT Playlists.ParentPlaylist FROM Playlists WHERE Playlists.PlaylistName = "Last 50 Played""
SDB.Database.OpenSQL(QSQL)

and if you set script like that it will end up with Compile error.

[Bex]

But isn't single quotes (') instead of dubble qoutes (") the proper workaround? I got that impression when I studied the sql in Magic Nodes!

/Bex

[Bex]

On the other hand it could be that you dont have a playlist named Last 50 Played. If Last 50 Played always has the IDPlaylist = 2, thats the id in my db, you could use that instead.

/Bex

[trixmoto]

Can't you use...

Code: Select all

QSQL = "SELECT Playlists.ParentPlaylist FROM Playlists WHERE Playlists.PlaylistName = ""Last 50 Played"""
SDB.Database.OpenSQL(QSQL)

?

[Peke]

Trix Too many Delphi?

[Steegy]

In my (humble) opinion, Bex is right.

I always use:

Code: Select all

Set MyInfoNumber = SDB.Database.OpenSQL("SELECT Lists.ID FROM Lists WHERE TextData = '" & Name & "'")

BTW: Use ' ' for field contents, use [ ] for column names.
e.g.:

Code: Select all

... WHERE [COLUMN NAME] = 'just some text' ...

The ' ' obviously means the character is a text (string), so you can't use it with a numeric value (well, there's no need to; there are no spaces in a number).

Cheers
Steegy

[psyXonova]

Steegy, Bex
You are both right, that savved my problem, thank you very much

Trix
Your code produces the same error as mine.... Thanks for helping though

Peke
Your workaround doesnt offer much help to me since that name of the playlist will be a variable, i just mention last 50 played as an example

Thank you all guys, You have made my day!!!