This is "really" newbie stuff, but....
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-
Re: This is "really" newbie stuff, but....
I am not very good with SQL...but my ADO open looks like this
VB Code:
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
VB Code:
Do while NOT rsStorage.EOF
If rsStorage.Fields("City") = cstring Then
'Add whatever
End If
rsStorage.MoveNext
Loop
Re: This is "really" newbie stuff, but....
VB Code:
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.
Re: This is "really" newbie stuff, but....
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.
VB Code:
rsData2.Open "SELECT * FROM city where city='" & cString & "';", cnDB, adOpenDynamic, adLockOptimistic, adCmdText
Re: This is "really" newbie stuff, but....
Quote:
Originally Posted by RobDog888
You should have this line of code all on one line
Good point, and I also forgot to put in the ; in my example.
Re: This is "really" newbie stuff, but....
Boooo, bad Hack, *SLAP* :D 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?
Re: This is "really" newbie stuff, but....
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-
Re: This is "really" newbie stuff, but....
You can get around the single quote issue by checking for it and doubling up on it in order to get the SQL correct. ;)
Re: This is "really" newbie stuff, but....
Take a look at #7 Question in the Frequently Asked Questions link in my signature. :)