ID Number: Q80635
1.11 4.20
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). 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 that 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 that 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
Once the above steps have been completed, the sample program can be
run in interpretive mode under Visual Basic or compiled to an
executable file run directly from Windows.
Additional reference words: dblib 1.11 4.20