VB6 Connecting to a Database

Question

Alright, what i want to do sounds simple. I would like to load a form and on form load call a sql statement that returns 1 item and places it in TextBox1. Here is what i have so far.

 Private Sub Form_Load()

 Call openTheDatabase

 End Sub

 Public Function openTheDatabase() As Boolean

 '-- Here we want to open the database
 Dim sConnectionString As String
 Dim strSQLStmt As String

 '-- Build the connection string
 sConnectionString = "PROVIDER = MSDASQL;driver={SQL Server};database=databasename ;server=servername;uid=;pwd=;"


 strSQLStmt = "SELECT chvDealerName " & _
        "From dbo.tblDealers Where chrVSCAcctNum = '90442001'"

 TextBox1.Text = strSQLStmt

 End Function

EDIT

Ok it was Text1.Text, but now im just getting the string in the textbox, not the actual database entry

Answer 1

You can see a solid example of setting up ADODB connections here: http://www.timesheetsmts.com/adotutorial.htm

Your project needs a reference to the ADODB library before anything. To do this:

1. Open your project
2. Click the Project menu on the top and click References in the dropdown
3. Check the "Microsoft ActiveX Data Objects 2.x Library" (with x being the highest number you see - on my Windows XP Pro SP2 box, it is 2.7"

Example with your project:

Private Sub Form_Load()

 Call openTheDatabase

 End Sub

 Public Function openTheDatabase() As Boolean

 '-- Here we want to open the database
 Dim sConnectionString As String
 Dim strSQLStmt As String

 '-- Build the connection string
 sConnectionString = "PROVIDER = MSDASQL;driver={SQL Server};database=databasename ;server=servername;uid=;pwd=;"


 strSQLStmt = "SELECT chvDealerName " & _
        "From dbo.tblDealers Where chrVSCAcctNum = '90442001'"

'DB WORK
Dim db As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim result As String

db.ConnectionString = sConnectionString 
db.Open 'open connection

With cmd
  .ActiveConnection = db
  .CommandText = strSQLStmt
  .CommandType = adCmdText
End With

With rs
  .CursorType = adOpenStatic
  .CursorLocation = adUseClient
  .LockType = adLockOptimistic
  .Open cmd
End With

If rs.EOF = False Then
    rs.MoveFirst
    Let result = rs.Fields(0)
End If
'close conns
rs.Close
db.Close
Set db = Nothing
Set cmd = Nothing
Set rs = Nothing


'set local box

' TextBox1.Text = strSQLStmt
TextBox1.Text = result


 End Function