Using Parameterized Query

[bharanidharanit]

I using this code to get value from database using ADODB connection. But i am getting runtime error in the highlighted field. How to add parameterized query.

Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim tmp As String
Private Sub Combo1_click()
rs.Open "SELECT journeytime from ticket where cityname=' & combo1.text & '", db
rs.MoveFirst
While (rs.EOF = False)
tmp = rs.Fields(0).Value
Combo2.AddItem (tmp)
rs.MoveNext
Wend
rs.Close
End Sub

Private Sub Form_Load()
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb;Persist Security Info=False"
rs.Open "SELECT cityname from ticket", db
rs.MoveFirst
While (rs.EOF = False)
tmp = rs.Fields(0).Value
Combo1.AddItem (tmp)
rs.MoveNext
Wend
rs.Close
End Sub

[gibra]

See this topic
Database - Why should I use Parameters instead of putting values into my SQL string?
http://www.vbforums.com/showthread.php?t=548787

[akhileshbc]

I using this code to get value from database using ADODB connection. But i am getting runtime error in the highlighted field. How to add parameterized query.

Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim tmp As String
Private Sub Combo1_click()
rs.Open "SELECT journeytime from ticket where cityname=' & combo1.text & '", db
rs.MoveFirst
While (rs.EOF = False)
tmp = rs.Fields(0).Value
Combo2.AddItem (tmp)
rs.MoveNext
Wend
rs.Close
End Sub

Private Sub Form_Load()
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb;Persist Security Info=False"
rs.Open "SELECT cityname from ticket", db
rs.MoveFirst
While (rs.EOF = False)
tmp = rs.Fields(0).Value
Combo1.AddItem (tmp)
rs.MoveNext
Wend
rs.Close
End Sub

Change that line to something like this:

rs.Open "SELECT journeytime from ticket where cityname='" & combo1.text & "'", db

....

[bharanidharanit]

ya thankyou that works, i missed that...

[akhileshbc]

If that solved your problem, please mark the Thread as RESOLVED....

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

While akhileshbc's method has worked so far, I strongly recommend that you use parameters as explained in the link gibra posted - because it solves lots of problems that you haven't discovered yet (like having the ' character in the data, which will make your code fail and/or have errors).

[bharanidharanit]

thankyou all....

[bharanidharanit]

I dont know whats the error here, It says "cannot change the ActiveConnection property of a Recordset object which has a Command object as its source"

strSQL = "select journeytime from schedule where cityname=param1"
Dim adoCommand As adodb.Command
Set adoCommand = New adodb.Command
With adoCommand
    .ActiveConnection = adoConnection
    .CommandType = adCmdText
    .CommandText = strSQL
    .Prepared = True
    .Parameters.Append .CreateParameter("param1", adVarChar, adParamInput, Len(Combo1.Text), Combo1.Text)
    .Execute
End With
Set adoRecordset = New adodb.Recordset
    adoRecordset.Open adoCommand, adoConnection, 3, 2

Combo2.Text = "Select Time"
While (adoRecordset.EOF = False)
tmp = adoRecordset.Fields(0).Value
Combo2.AddItem (tmp)
adoRecordset.MoveNext
Wend
Set adoCommand = Nothing
Set adoRecordset = Nothing
Combo2.Enabled = True
Combo2.SetFocus

[techgnome]

strSQL = "select journeytime from schedule where cityname=?" 'If using Access, use ? as the parameter placeholder; for SQL Server use @paramname
Dim adoCommand As adodb.Command
Set adoCommand = New adodb.Command
With adoCommand
    .ActiveConnection = adoConnection
    .CommandType = adCmdText
    .CommandText = strSQL
    .Prepared = True
    .Parameters.Append .CreateParameter("param1", adVarChar, adParamInput, Len(Combo1.Text), Combo1.Text)
Set adoRecordset =   .Execute
End With
'Set adoRecordset = New adodb.Recordset
'    adoRecordset.Open adoCommand, adoConnection, 3, 2

Combo2.Text = "Select Time"
While (adoRecordset.EOF = False)
tmp = adoRecordset.Fields(0).Value
Combo2.AddItem (tmp)
adoRecordset.MoveNext

-tg