• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 905
  • Last Modified:

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
0
SirNick
Asked:
SirNick
  • 2
  • 2
1 Solution
 
MikkkCommented:
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
0
 
SirNickAuthor Commented:
Thanks but I'm getting an error,  'Invalid use of property'..
0
 
MikkkCommented:
Try with a "Set" in front the assignation:

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

Text1.text = r.Fields("name").Value
0
 
SirNickAuthor Commented:
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.........
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now