[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 920
  • Last Modified:

How to Populate a True DBGrid 7 control using a DSN-LESS ADO Connection to a SQL 2000 Server table using VB6

I am new to SQL Server 2000 and I am building a front end to SQL Server 2000 database using VB6.  I need to connect using a global DSN-Less ado connection   and then connect to a recordset to Populate a TDBGrid 7 control with the data from that recordset.    Can anyone show me how I can do this?
0
pgkdavefdd
Asked:
pgkdavefdd
1 Solution
 
PreeceCommented:
Well, here is a way to connect to the SQL Server 2000 database - dsnless.  Just fill in the 4 variables; you can pass them into the sub if you want, or make them properties of a class, etc...  You said something about a global dsnless connection?

 Be sure to make a reference to ADO 2.1 or 2.5, or whatever is suitable for your implementation.  Would also be a good idea to add some error handling.

I usally put this sub and a few others into a class and call it clsAdoSqlSvr or something like that.  Some other subs and functions I add would be for getting a recordset, executing a stored proc, etc...

1)

Put this in the general declarations of the class:

Option Explicit
Private cnnAdo As ADODB.Connection
Private rstAdo As ADODB.Recordset
Private cmdAdo As ADODB.Command

Then add this sub:

Public Sub gsnConnectToDB()
    Dim sAdoConnectString As String
    Set cnnAdo = New ADODB.Connection
    Dim sUserName as String
    Dim sPassword as String
    Dim sServer as String
    Dim sDatabase as String

    sUserName = ""
    sPassword = ""
    sServer = ""
    sDatabase = ""

    sAdoConnectString = "uid=" & sUserName & ";pwd=" & sPassword & ";driver={SQL Server};server=" & sServer & ";database=" & sDatabase & ";dsn=,,connection=adConnectAsync"
       
    cnnAdo.ConnectionString = sAdoConnectString
'    cnnAdo.Provider = "MSDASQL"
'    cnnAdo.Provider = "SQLOLEDB"
    cnnAdo.ConnectionTimeout = 10
    cnnAdo.CommandTimeout = 30
    cnnAdo.Open

End Sub

In that same class file, add the following Function:

Public Function gfGetRS() As ADODB.Recordset
    Set rstAdo = New ADODB.Recordset
    With rstAdo
        Set .ActiveConnection = cnnAdo
        .CursorType = adOpenForwardOnly
        .Open pvsProc
    End With
    Set gfGetRS = rstAdo
End Function



2)  In your main mod file, declare a global variable:

Public oAdoSqlSvr As clsAdoSqlSvr

3)  In your sub main, do this:

    set oAdoSqlSvr = new clsAdoSqlSvr
    oAdoSqlSvr.gsnConnectToDB

Also, when exiting the program, be sure to do some cleanup:
    Set oAdoSqlSvr = Nothing

4)  Slap a dbgrid control onto a form; we'll call it grdMain.

5)  In the form_load, do this (assuming at this point that you are not using stored procs to get data, update the db, etc...):

    psnLoadGrid

6)  Here is the code for psnLoadGrid:
Private Sub psnLoadGrid()
    Dim oRS as ADODB.Recordset
    Dim sSql as String
    Dim lX as long

    sSql = "select * from tblSomeTable"
    Set oRS = oAdoSql.gfGetRS(sSql)
    If Not oRS.BOF And Not oRS.EOF Then
        ' this is probably not the proper grid code for the True DBGrid 7, but it is typical of grids that don't use record binding,
        ' which I think DBGrid does...I can dig around later for some more code specific to DBGrid
        lX = 1
        With grdMain
            Do while not oRS.EOF
                .Row = lX

                .Col = 0
                .Text = oRS!Field1

                .Col = 1
                .Text = oRS!Field2

                lX = lX + 1
                oRS.MoveNext
            Loop
        End With
    End If
   
    oRS.Close
    Set oRS = Nothing    
End Sub

7)  To do an update or delete:

Add this to the clsAdoSqlSvr:

Public Sub gsnExecSql(sSql As String)
    Set cmdAdo = New ADODB.Command
    With cmdAdo
        .CommandTimeout = 10800
        Set .ActiveConnection = cnnAdo
        .CommandText = sSql
        '.CommandType = adCmdStoredProc
        .Execute
    End With
End Sub

Build a string, then make a call to the sub passing in the string:

    sSql = "delete from tblTable where ID = 1"
    oAdoSqlSvr.gsnExecSql sSql


So, anyways, this should help you get started on it!  
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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