SQL variables 3

[CptnMike]

I have looked and tried everything I could find in the books, but I can't find any way to pass a VB6 variable to a SQL statement. I'm getting a lot of syntax errors.

Example: "SELECT Name, Address FROM Customers WHERE PhoneNum =" "'"Text1.Text"'""

I'm hoping to fill some other Textboxes on the form with the information that the results of the variable bring back. I'm using ADODB code. (Not binding the Textboxes)

 

[Golom]

You need to concatenate them like this
[blue][tt]
"SELECT Name, Address FROM Customers WHERE PhoneNum ='" & Text1.Text & "'"
[/tt][/blue]
Assuming that "PhoneNum" is a text field. If it's numeric then eliminate the single quotes.

 

[chiph]

Or use ADO parameters like this:

Dim adoConn as New ADODB.Connection
Dim adoComm as New ADODB.Command
Dim adoRS as ADODB.Recordset
Dim sSQL as String

sSQL = sSQL & " SELECT Name,"
sSQL = sSQL & "   Address
sSQL = sSQL & " FROM Customers
sSQL = sSQL & " WHERE (PhoneNum = ?"

adoConn.ConnectionString = "your connect string"
adoConn.Open

Set adoComm.ActiveConnection = adoConn
adoComm.CommandType = adCommandText
adoComm.CommandText = sSQL

adoComm.Parameters.Append adoComm.CreateParameter("PhoneNum", adWChar, adDirectionInput, 20, Text1.Text)

Set adoRS = adoComm.Execute
If Not (adoRS.BOF and adoRS.EOF) Then
  ' Process records
End If

You would, of course, need to tweak it for your database (column length, datatype, etc)

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[CptnMike]

Ok, I tried it, and it didn't work... So I'm going to give you the whole thing the way I'm trying to get it to work, and you can tell me I'm nuts.

Private Sub Command4_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "provider = microsoft.jet.oledb.4.0;persist security info = false;data source = c:\pizza project\testpizza1.mdb"
Set rs = cn.Execute ("select LastName, Address, City, State, Notes from Customers Where PhoneNumber ="&Text1.text&"")
Text4.Text = rs.Fields("LastName")
Text6.Text = rs.Fields("Address")
Text7.Text = rs.Fields("City")
Text8.Text = rs.Fields("State")
Text5.Text = rs.Fields("Notes")
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

End Sub

You can probably see where I'm going better now, and tell me if it will work if I can get the execute statment to quit giving me a syntax error "Expected List Seperator or )

 

[Glasgow]

I think you are missing some single quotes. Your

Set rs = cn.Execute ("select LastName, Address, City, State, Notes from Customers Where PhoneNumber ="&Text1.text&"")


should be

Set rs = cn.Execute ("select LastName, Address, City, State, Notes from Customers Where PhoneNumber ='" & Text1.text & "'")

 

[Glasgow]

Sorry I realise my suggestion is more or less like Golom's and I have assumed SQL Server - not sure what delimiter is for jet so perhaps it should be

Set rs = cn.Execute ("select LastName, Address, City, State, Notes from Customers Where PhoneNumber =" & chr(34) & Text1.text & chr(34))

where chr(34) is a double quote.

 

[CptnMike]

Got it figured out... It's the language used in Access 2000 that's been the problem. Golom, you had it almost right. and Glasgow is close. Chiph, yours worked up until the parameters, then crapped on me. Anyway, the one that worked is:
"SELECT Name, Address FROM Customers WHERE PhoneNum ='" + Text1.Text + "'"

Had to replace the ampersand with a +...! Anyway, Thanks for the help guys!

 

[chiph]

Access (JET/ODBC) uses a different character to indicate parameters, IIRC. Instead of the "?", it uses the "@" symbol. Try that instead.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[happyabc]

The remark is especially significant as it implies that there is an "Access SQL" as distinct from "Jet SQL" ( however minor the differences may be ). How true is this?

This question of how parameters are to be specified in the various Jet contexts is one that I have been trying to resolve for a long time. Can you point me to documentation that covers it?

 

[chiph]

It should be somewhere in the Access docs (I don't have them loaded, sorry).

The thing that trips people up is that MS-Access does not use ANSI standard SQL -- it has it's own peculiar variation.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first