Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Probably something really easy but I'm having big problems creating an SQL string.
Data looks like this
ID Player Team Position
1 Jens Lehmann Arsenal Goalkeeper
2 Manuel Almunia Arsenal Goalkeeper
3 Scott Carson Aston Villa Goalkeeper
4 Thomas Sorensen Aston Villa Goalkeeper
My problem is that I need to put a string in the WHERE clause, ie. I need quotes around it so it is not treated as a variable.
SELECT Player.* FROM Player WHERE Player.Position="Goalkeeper";
In Access, this works fine.
A plain SQL query in VB also works fine.
.CommandText = "SELECT * FROM Player;"
Of course when I place the more complicated SQL query in the VB I end up with
.CommandText = "SELECT Player.* FROM Player WHERE Player.Position="Goalkeeper";"
Which of course has screwy quotes. I've no idea how to either escape the quotes, or close the string and concat the variable on to it, as I still need the SQL query to contain quotes, so I can't print quotes anywhere.
Did try and use Chr(34) but that didn't work either.
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
.CommandText = "SELECT Player.* FROM Player WHERE Player.Position='Goalkeeper';"
Try that.
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
Ah hang on that might not work that SQL Server syntax.
2 secs....
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Yeah single quote doesn't work, also fails to open the recordset.
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
.CommandText = "SELECT Player.* FROM Player WHERE Player.Position=\"Goalkeeper\";"
Try that?
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
Is this VB6? VBA? VB.Net?
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Escaping the quotes with a \ gives expected end of statement error.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
VB6 - its in college. I'm going to register the student so the other replies will be from him.
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
Tried doubling up the quotes?
.CommandText = "SELECT Player.* FROM Player WHERE Player.Position=""Goalkeeper"";"
I'm a C# man and I haven't done any VB for ages so i'm clutching at straws really 
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
Working on the basis of something simple like a Access Database...
one thing I don't understand is why you've got Player.Position =?
If I had a Table called Player and I wanted to return all players in a variable 'position' in that table into say a listbox I'd do this in vb.net. This would allow say a droplist box to represent a list of possible positions, then when you click 'find' it - populates the listbox of players from that position.
Listbox1 and Combobox1 exist on form. Assuming Database structure of Table 'Player'.
Public Sub GetPlayerList()
Dim playerposition As string
Dim ocmdVBCommand As OleDbCommand
Dim odtrVBDataReader As OleDbDataReader
playerposition = Combobox1.Text 'This is the selected item text, i.e. Goalkeeper
Try
ocmdVBCommand = New OleDbCommand()
With ocmdVBCommand
.Connection = New OleDbConnection(ConnectionStringtoDB) 'Requires you to set this up previously pointing to your DB
.Connection.Open()
.CommandText = "SELECT * FROM Player WHERE Position='" & playerposition & "'" 'This player position string is currently = to Goalkeeper
odtrVBDataReader = .ExecuteReader()
End With
With ListBox1
.Items.Clear()
.BeginUpdate()
'Adds a player to the list
Do While odtrVBDataReader.Read
ListBox1.Items.Add(odtrVBDataReader.GetString(1))
Loop
.EndUpdate()
End With
odtrVBDataReader.Close() 'Close the reader
ocmdVBCommand.Connection.Close() 'Close connection with DB
Catch
End Try
End Sub
Note the above is in Vb.Net, so I'm not sure on syntax of Vb6...
[Edited on 12-03-2008 by Paul_J]
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
You can do all of that in VB6.
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
"SELECT * FROM Player WHERE Position='" & playerposition & "'"
and ListBox1.Items.Add(odtrVBDataReader.GetString(1))
Just to explain to the kid if he doesn't know, that number in the GetString( ) is the number of the thing I want to use out of what I returned...
So a more precise query such as 'Select player from etc etc etc' - would only return 1 column of results, so you'd just have Getstring(0) instead.
Similarily with querying it all, you'd use that number in the get string to return any info you wanted representive of the column of data you'd want.
If this is a Access database Ian, then the SQL statement I entered will work, I just tried it out. but you'll need to perhaps adjust the code / nick code to work with your VB6 example, as I'm not too hot on VB6 syntax.
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
Your original question too Ian without using a variable position control... and just wanting goal keeper would be.
.CommandText = "SELECT Player FROM Player WHERE Position='Goalkeeper'"
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
quote:
Originally posted by Paul_J
Your original question too Ian without using a variable position control... and just wanting goal keeper would be.
.CommandText = "SELECT Player FROM Player WHERE Position='Goalkeeper'"
Doesn't work - that's SQL Server syntax, Access uses " instead of '.
I think doubling up the quotes like I said earlier should sort it.
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
quote:
Originally posted by James
quote:
Originally posted by Paul_J
Your original question too Ian without using a variable position control... and just wanting goal keeper would be.
.CommandText = "SELECT Player FROM Player WHERE Position='Goalkeeper'"
Doesn't work - that's SQL Server syntax, Access uses " instead of '.
I think doubling up the quotes like I said earlier should sort it.
That's strange, I've just built a Access database and am querying it fine with the syntax above using single quotes.
[Edited on 12-03-2008 by Paul_J]
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
James
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
Maybe it's version of Access or something? like old 95/98 version of Access did something different?
If it does require " " around the thing, then yeh double quotes is how you'd get it to place it in the syntax.
.CommandText = "SELECT * FROM Player WHERE Position=""Goalkeeper"""
Don't think you need the Tablename.Field thing with something so basic... so not Player.Position etc...
But if it's anything like my example the single quotes will do it.
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
Paul, my original suggestion was to use single quotes but Ian said this:
quote:
Originally posted by Ian
Yeah single quote doesn't work, also fails to open the recordset.
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
Hmm weird.  you may be right then for the version he's using or the way it's opening the database.
Impossible to know without having his code / exact details of the stuff he's using i guess.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Its VB6 and Access 97 mdb.
Everything works fantastic when the SQL statement does not contain WHERE.
He is registered now so I'll have him paste in the code, but that is largely unnecessary as it works, string issue aside.
|
