INF: Building Apps with Visual Basic Library for SQL Server

• Microsoft SQL Server version 4.2 for OS/2

SUMMARY

This article provides the basic information necessary to create a simple SQL Server front-end using the Visual Basic Library for SQL Server (VBSQL).

MORE INFORMATION

While considerably more complex applications can be developed using VBSQL, the following steps provide the necessary framework to make a connection with a SQL Server, execute Transact-SQL statements, and process results:

1. Run Visual Basic and choose New Project from the File menu.

2. Add the VBSQL.VBX custom control to the project:

   a. From the File menu, choose Add.

   b. Select VBSQL.VBX from the \SQL\BIN directory.

   c. Once the file has been added to the project, the VBSQL control (an icon resembling a stop sign) should appear on the tool bar.

3. Add the VBSQL include file containing all the necessary function and routine declarations to the project:

   a. From the Window menu, choose Project Window.

   b. Select the GLOBAL.BAS module.

   c. From the Code menu, choose Load Text.

   d. Select the VBSQL.BI file from the \SQL\DBLIB\INCLUDE directory.

4. Declare a global integer variable for the connection to SQL Server by adding the following line of code to the beginning of the GLOBAL.BAS module:

   Global SQLConn%

   NOTE: The handle to the connection is an integer value. While SQLConn% is used in this example, the name of the handle is up to the discretion of the programmer. In addition, while not used in this example, multiple connections could be established by first declaring additional handles (SQLConn2%, SQLConn3%, and so on).

5. Add the VBSQL error/message handler control to the project:

   a. From the Window menu, choose Project Window.

   b. Select Form1.

   c. Choose the VBSQL Control ("stop sign" icon) from the ToolBox and place it on Form1.

   This control must be added to the form in the project prior to calling any VBSQL functions and/or routines. If it is not, unexpected errors may occur when an attempt is made to run the program. In addition, the control must be placed on a form that is always loaded into memory.

6. Add user-defined code to the error/message handler control by clicking the VBSQL control on Form1 and adding the following code to the error and message procedures:

          Sub SQL_Error (SqlConn As Integer, Severity As
                         Integer, ErrorNum As Integer, ErrorStr
                         As String, RetCode As Integer)
   
              MsgBox "Error #:  " + Str$(ErrorNum) + Chr$(13) +
                     Chr$(10) + ErrorStr
   
          End Sub
   
          Sub SQL_Message (SqlConn As Integer, Message As Long,
                           State As Integer, Severity As
                           Integer, MsgStr As String)
   
              MsgBox "Message #:  " + Str$(Message) + Chr$(13)
                     + Chr$(10) + MsgStr
   
          End Sub
   
       These sample error and message handlers represent only the minimum
       amount of code necessary to identify both DB-LIBRARY (db-lib) and
       SQL Server error messages. In practice, you may want to enhance
       the handlers to provide your applications with more robust
       error-handling routines.
   
   

7. Initialize VBSQL by clicking Form1 and adding the following code to the Form Load code window:

   Message$ = SqlInit() If Message$ = "" Then

              Beep
              MsgBox "SqlInit Error"
              End
          Else
              MsgBox Message$
          End If
   
       When the program is run, SqlInit() initializes the user-defined
       error and message handlers. In addition, it returns a string
       containing the version number of the DB-LIBRARY dynamic-link
       library (DLL) that is being used (W3DBLIB.DLL). If an empty string
       is returned, do not attempt to call any other VBSQL functions
       and/or routines and verify that both the W3DBLIB.DLL and
       DBNMP3.DLL files are located in the MS-DOS path. If a version
       string is returned, it must show that DB-LIBRARY 1.16 or later is
       being loaded. If not, unexpected results may occur when the
       program is run.
   
   

8. Add the necessary code to establish a connection with SQL Server:

   a. Click Form1 and add the following code to the Form Load code window:

             Server$ = "servername"
             LoginID$ = "loginID"
             Password$ = "password"
             WorkSta$ = "user"
             AppName$ = "simple"
   
             SqlConn% = SqlOpenConnection(Server$, LoginID$,
                        Password$, WorkSta$, AppName$)
   
             If SqlConn% = FAIL Then
                 MsgBox "Login failed"
                 End
             End If
   
       b. If you choose, you can use the following code to connect to SQL
          Server in place of the preceding code:
   
             Login% = SqlLogin%()
             Results% = SqlSetLUser%(Login%, LoginID$)
             Results% = SqlSetLPwd%(Login%, Password$)
             Results% = SqlSetLApp%(Login%, AppName$)
             Rem  Un-remark the following line if you want to use
             Rem  the BCP APIs:
             Rem  Results% = SqlBCPSetL%(Login%, 1)
             SqlConn% = SqlOpen%(Login%, Server$)
   
             If SqlConn% = FAIL Then
                 MsgBox "Logon failed."
                 End
             End If
   
       Note that the first method is generally preferred because it is
       the simplest. However, if you want to use the BCP APIs for bulk
       copying data into or out of SQL Server, you must use the second
       method.
   
   

9. Add code to the form to select the desired database on SQL Server by clicking Form1 and adding the following code to the Form Load code window:

   Results% = SqlUse(SqlConn%, "pubs")

   This line of code will change the database context from the master database to the pubs sample database.

10. Add code to the form to execute Transact-SQL statements by clicking Form1 and adding the following code to the Form Load code window:

    Cmd$ = "select au_id, au_lname, au_fname from

                   authors"
           MsgBox Cmd$
           Results% = SqlCmd(SqlConn%, Cmd$)
           Results% = SqlExec(SqlConn%)
           Do While (SqlResults%(SqlConn%) <> NOMORERESULTS)
               Do While (SqlNextRow(SqlConn%) <> NOMOREROWS)
                   ID$ =  Sqldata(SqlConn%, 1)
                   LastName$ = Sqldata(SqlConn%,2)
                   FirstName$ = Sqldata(SqlConn%, 3)
                   Print ID$ + ":  " + LastName$ + ", " +
                         FirstName$ + Chr$(13) + Chr$(10)
               Loop
           Loop
    
        This query returns rows from the authors table in the pubs sample
        database. Other Transact-SQL queries could be executed by
        modifying the Transact-SQL statement in Cmd$ and adding additional
        Sqldata() statements as necessary.
    
    

11. Add code to the form to close the connection to SQL Server by clicking Form1 and adding the following code to the Form Load code window:

    SqlClose(SqlConn%)

    This function must be called selectively for each SQL Server connection opened with SQL Server. If not, sleeping processes may be left on the server. For more information, query on the following words in the Microsoft Knowledge Base:

    visual and basic and sleeping and processes

    An alternative method involves calling SqlExit. This procedure may be desirable when working with multiple connections because it effectively closes all open connections.

12. Uninitialize VBSQL prior to exiting the program by clicking Form1 and adding the following code to the Form Unload code window:

    SqlWinExit End

    NOTE: If SqlWinExit is not called before exiting, you will receive the following error message the next time the application is run:

    Error 10001: NULL DBPROCESS pointer encountered