VB6 database

[Reiko]

Hi im currently having a problem with taking data from a field in a database and then using it within the form, when supplied with a telephone number by inputting in
to a text box, the number needs to be checked against the database and then if a record matches the telephone number the Name in the record shoudl be inserted into another text box.

I am using visual basic 6 and microsoft access 2000

At the moment i am getting an error of "the connection cannot be used to perform this operation"

Dim CON As Connection
Dim rs As New Recordset
Dim SQLname As String

Private Sub cmd_details_Click()

rs.Open "Select * from customer, CON, adOpenKeyset, adLockOptimistic,"

SQLname = "Select name from Customer where telephone='" & (txtphone.Text)
txtname.Text = SQLname

End Sub

Private Sub form_initialize()

Set CON = New ADODB.Connection      'set the database connection
CON.Open "Provider=Microsoft.jet.oledb.4.0; Data Source=" & App.Path & "\test.mdb"  'connection string to the database

End Sub

thanks in advance

[hawisme000]

try this

SQLname = "Select name from Customer where telephone=' " & nameoftxtbox & "' "

put this on your rs.open even or

like this

rs.source = "Select name from Customer where telephone=' " & nameoftxtbox & "' "
rs.activeconnection = CON
rs.open

your SQLname is a query thats needs to be open,

[Reiko]

I can see how using the new SQL string was improved however when implementing it in this manner

rs.Source = "Select name from Customer where telephone=' " & txtphone.Text & "' "
rs.ActiveConnection = CON
rs.Open

I am getting an error of the ooperation is not allowed when the object is ope

Thanks for the help thus far.

[hawisme000]

have you remove your 1st rs.open event?
and replace it with the 1 gave u

if thats the error , then remove it
the system cannont open two recordset with the same name,

[Programmer666]

Try this:

Dim CON As new ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQLname As String

Private Sub cmd_details_Click()


SQLname = "Select name from Customer where telephone='" & (txtphone.Text)

rs.Open sqlName, CON, adOpenKeyset, adLockOptimistic

txtname.Text = Rs!name
End Sub

Private Sub form_initialize()

Set CON = New ADODB.Connection      'set the database connection
CON.Open "Provider=Microsoft.jet.oledb.4.0; Data Source=" & App.Path & "\test.mdb"  'connection string to the database

End Sub

Reference ADODB in your project

[fkmercado]

Try this...

Dim CON As new ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQLname As String

Private Sub cmd_details_Click()


SQLname = "Select name from Customer where telephone='" & txtphone.Text & "'"

rs.Open sqlName, CON, adOpenKeyset, adLockOptimistic, 1

txtname.Text = rs.fields("name").value
End Sub

Private Sub form_initialize()

Set CON = New ADODB.Connection      'set the database connection
CON.Open "Provider=Microsoft.jet.oledb.4.0; Data Source=" & App.Path & "\test.mdb"  'connection string to the database

[AUGXIS]

If rs.State = adStateOpen Then rs.Close
gSql = "Select name from Customer where telephone='" & txtphone.Text & "'"
rs.Open gSql, CON, adOpenKeyset, adLockOptimistic, 1

txtname.Text = rs.fields("name").value
End Sub

Private Sub form_initialize()

Set CON = New ADODB.Connection 'set the database connection
CON.Open "Provider=Microsoft.jet.oledb.4.0; Data Source=" & App.Path & "\test.mdb" 'connection string to the database

End Sub