SQL statement for first letter (VB6, w2000, office xp)

Topic

New Reply

I have a list box that has a through z in it, lstAlpha. I’ve added a thru z in the lstAlpha through additem.

When I click on a letter in lstAlpha, I want my flexgrid (flxClient) to populate with the client’s name that have that first letter in their name from my tblClient.

dim rs as new ADODB.recordset
“SELECT ClientName FROM tblClient WHERE left(ClientName, 1) = lstAlpha.Text ORDER by ClientName”
set flxClient.DataSource = rs
rs.close

I get an error about wrong parameters. I think i’m not putting the right quotes or single quotes in the SQL stmt. Can someone lead me down the correct path. Thanks a log

Reply | Quote

Replies

What’s your back end? SQL Server? If so, I don’t think there’s a Left function. Try Substring instead.

Reply | Quote

or maybe LIKE a*, LIKE z*, etc. (which I have used in a web page tapping an Access database).

Reply | Quote

Regardless of what backend you’re using, you’ve left out some important steps. You dimmed your recordset object, but that’s all you’ve done. You have to set its connection property and then open it, either directly or by using an execute on a command or connection object. Once the recordset actually contains records, you can assign it to the datasource of the flexgrid.

I don’t know why you’re bothering to use a flexgrid though with a straight SQL statement like that. Without a hierarchical recordset, a flexgrid just looks like a datasheet.

Reply | Quote

Hi,
In addition to Charlotte’s points, I think you need to change your SELECT statement to:
“SELECT ClientName FROM tblClient WHERE left(ClientName, 1) = ‘” & lstAlpha.Text & “‘ ORDER by ClientName”
Hope that helps.

Reply | Quote

thanks rory. that worked. it was my single and double quotes. Could you let me know the rationale behind the use of single quotes. thanks

Reply | Quote

Hi,
The single quotes are there because you’re passing a string variable and I use them in preference to double quotes partly because that is, I believe, the ANSI/ISO standard (Oracle certainly doesn’t seem to like double quotes) and partly because I get confused trying to put double quotes within quoted strings (I think it’s “”” to put ” inside a string that’s already in quotes?)
In this case you also needed to take the controlname.text bit out of the SELECT statement so that it gets evaluated – otherwise you’re including the words “controlname.text” in the statement rather than passing whatever the control’s text actually is, if that makes sense?
Hope that helps.

Reply | Quote