asked on

Using vb6 to retreive value from sql and place in textbox

Hello All

I have been using vb6 on and off for a few years but am new to using databases in vb6. I am using the code below that I got from the net to connect which works fine however in the last statement I want to retrieve a value from the name column and place it in a textbox but I cant get it to work. Any comments would be appreciated.

'Declaring your database
Public MyDatabase As ADODB.Connection

'Tables into your database
Public MyTable1 As ADODB.Recordset

Private Sub Form_Load()

'Set your variables to nothing (just for stability)
Set MyDatabase = Nothing
Set MyTable1 = Nothing

'Connecting to your DB
Set MyDatabase = New ADODB.Connection
Set MyTable1 = New ADODB.Recordset

'Here's where you're to set all your database options
ConnectionString = "Provider=SQLOLEDB.1" _
& ";User ID=sa" _
& ";Password=theknapp1" _
& ";Initial Catalog=aTable" _
& ";Data Source=mypc\SQLEXPRESS"

'If your database is in another computer in the local network you can replace 127.0.0.1 with it's ip adress
MyDatabase.Open ConnectionString
MyTable1.CursorLocation = adUseClient

Text1.text = MyDatabase.Execute ("SELECT name FROM Table_1")

The above statement is the problem

End sub

Regards
Nick

Mikkk

MyDatabase.Execute returns a recordset object, so it can't be converted to string directly.
In order to be able to move through the results of the SQL do:

Dim r As ADODB.Recordset
r=MyDatabase.Execute ("SELECT name FROM Table_1")
r.MoveFirst

Text1.text = r.Fields("name").Value

SirNick

ASKER

Thanks but I'm getting an error, 'Invalid use of property'..

ASKER CERTIFIED SOLUTION

Mikkk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SirNick

ASKER

I'm sure I tried that, but I just tried again and its worked....so I couldn't have

Thanks for that, you will obviuously get the points.........