Using vb6 to retreive value from sql and place in textbox

Posted on 2007-09-28
Last Modified: 2013-12-25
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 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

Question by:SirNick
    LVL 8

    Expert Comment

    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")

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

    Author Comment

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

    Accepted Solution

    Try with a "Set" in front the assignation:

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

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

    Author Comment

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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now