SQL Query

I've connected to a SQL DB and I've added records but how would I get info and put it into a text1.text ??

VB Code:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
 
Private Sub Command1_Click()
conn.Open
rs.Open "select * from employee", conn, adOpenStatic, adLockOptimistic
rs.Requery
Text1.Text = rs.Fields("ename")
End Sub
 
Private Sub Form_Load()
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=tiger;Persist Security Info=True;User ID=scott"
End Sub

Why do you requery the recordset just after you opened it. Are you affraid something changed already?

Remove the rs.Requery line, because the only thing it does is hammer the database server.

Also, if you are looking for specific information in specific records there is no need to build a recordset containing every field in your table. Specifiy the fields that you want in your SELECT.

Also, insofar as you are after specific information, a WHERE clause, and possibly some AND clauses would be required in order to drill down to exactly what you want.

If you are going to be displaying the information in a text box, the inference is you are only expecting a one record return.

So can someone show me a sample code the correct way

I dont know SQL Server nor the connection so i left that out ..
also you didnt say if you were retrieving multiple records or not ..
this is a single record .. you would also want to check for a null value.

VB Code:

OpenConnection
    strSQL = "SELECT myField FROM myTable WHERE myId = 1"
    Set objRs = objConn.Execute(strSQL, , adCmdText)
    If Not objRs.EOF Then
        Text1 = objRs("myField")
    End If
    Set objRs = Nothing
    CloseConnection

Quote:

---

Originally Posted by rory

I dont know SQL Server nor the connection so i left that out ..
also you didnt say if you were retrieving multiple records or not ..
this is a single record .. you would also want to check for a null value.

VB Code:

OpenConnection
    strSQL = "SELECT myField FROM myTable WHERE myId = 1"
    Set objRs = objConn.Execute(strSQL, , adCmdText)
    If Not objRs.EOF Then
        Text1 = objRs("myField")
    End If
    Set objRs = Nothing
    CloseConnection

---

Yeah I need about 5 or 6 records... what does it change to then?

VB Code:

If Not objRs.EOF Then
    Do While Not objRs.EOF
        '// do something
        objRs.MoveNext
    Loop
End If