This is "really" newbie stuff, but....

[jPaulB]

Hi Everybody,

I need to scan a rather short mdb table ( It's a list of city names, about 20 records) to see if a city name exists in the table. The searchKey is contained in a combobox.Text property.

Building an SQL request is whats making me crazy because of its string manipulation. (I think)
Here's the code I have worked out so far, along with Visual Basic-6 complaint:

Private Sub SeeCmbCity()
' Finds a record to match city name, or allows for a new record
' to be entered into the city table
Dim rsData2 As ADODB.Recordset
Dim cString As String
cString = Trim(cmbCust(1))
Set rsData2 = New ADODB.Recordset
rsData2.Open "SELECT * FROM city where city=" & cString, cnDB,
adOpenDynamic, adLockOptimistic
'
' VB6 ends with a run-time error. something about too few parameters.
'

End Sub

So I have two questions really...
(1) What have I done wrong with the rsData2.Open statement?

(2) Is it smart to place all data in a single file like mdb does. Clipper would have maintained seperatye databases.

Thanks,
-Paul-

[kfcSmitty]

I am not very good with SQL...but my ADO open looks like this

RsStorage.Open "Splits", cn, adOpenKeyset, adLockPessimistic, adCmdTable

where Splits is the name of the table
and cn is the active connection

then to get the data i do

Do while NOT rsStorage.EOF
     If rsStorage.Fields("City") = cstring Then
          'Add whatever
     End If
rsStorage.MoveNext
Loop

[Hack]

Dim rsData2 As ADODB.Recordset
Dim cString As String
'cString = Trim(cmbCust(1)) i am not sure what cmbCust(1) is.  Do you have
'an array of combo boxes called cmbCust?
Set rsData2 = New ADODB.Recordset
rsData2.Open "SELECT * FROM city where city= '" & cString & "'", cnDB,
adOpenDynamic, adLockOptimistic

One of the things that took a while for me to get used to was that databases like Access and SQL Server have just one database with multiple tables unlike Clipper or dBase that had multiple databases with one table. One database, multiple tables are much, much easier and more efficient to deal with.

[RobDog888]

You should have this line of code all on one line and what is the search criteria? Does it have any illegal or issue causing characters?
Also, if the search criteria is text then you should place single quotes around it.

rsData2.Open "SELECT * FROM city where city='" & cString & "';", cnDB, adOpenDynamic, adLockOptimistic, adCmdText

[Hack]

You should have this line of code all on one line

Good point, and I also forgot to put in the ; in my example.

[RobDog888]

Boooo, bad Hack, *SLAP* Jk

The only other issue is the criteria containing delimiters like apostrophies, astericks, or percent signs. Apostrophies are probably the #1 cause
of criteria issues. Can you post an example of what your passing?

[jPaulB]

Thanks RobDog888, Hack, and kfcSmitty.

It was the quotation mark thing that was killing me. I'l keep working on understanding that stuff.... folks say that SQL is "the way to go" but I couldn't judge anyway.

Thanks again for the solution and the help

-Paul-

[RobDog888]

You can get around the single quote issue by checking for it and doubling up on it in order to get the SQL correct.

[dee-u]

Take a look at #7 Question in the Frequently Asked Questions link in my signature.