StarQuest Technical Documents

Sample Visual Basic ADO Code to Issue a SELECT to a DB2 Host Binding a Parameter

Last Update: 26 June 2008
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ042

Abstract

Below is sample code written in Visual Basic 6.0 which demonstrates how to connect to your DB2 host via StarSQL using ADO, issue a SELECT against a table passing a parameter to the WHERE clause. Results are displayed in a listbox.

Solution

To use this code, create a standard EXE in VB 6.0 and perform the following steps:

1. Create a form named Form1.
2. Add command button named Command1.
3. Add a listbox named List1.
4. Add "Microsoft ActiveX Data Objects 2.0 Library" to Project | References.
5. Add the code below to the command button's click event.
6. Modify the connect string to match your datasource name and uid and pwd.
7. If you do not create a sample table on your host using the SQL data definition language statement below, you will need to modify the field names and input data to match the chosen table on your host. Sample SQL DDL to create table:

CREATE TABLE MYLIB.MYTABLE (COL_ONE CHAR(10))

which you may populate with the following INSERT statements:

INSERT INTO MYLIB.MYTABLE VALUES ('TEST1')INSERT INTO MYLIB.MYTABLE VALUES ('TEST2')
INSERT INTO MYLIB.MYTABLE VALUES ('TEST1')
INSERT INTO MYLIB.MYTABLE VALUES ('TEST3')

Sample Code

Private Sub Command1_Click()

'Initialize objects
On Error GoTo Command3_ErrorProc:
Dim adoConn As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim objErr As ADODB.Error
Dim adoParm As ADODB.Parameter
Dim adoRs As ADODB.Recordset
Dim RecordsAffected As Long
Dim SqlSent As String
Dim i As Integer
List1.Clear

'Set cursor location to client, so that record count does not return -1
'See Microsoft Knowledge Base article Q194973 for discussion
adoConn.CursorLocation = adUseClient
Me.MousePointer = 11

'Select statement with WHERE criteria on first column
'Modify this statement if you are not using the provided sample table
SqlSent = "SELECT * FROM MYLIB.MYTABLE WHERE COL_ONE = ?"

'Connection string using StarSQL DSN
adoConn.Open "DSN=MY_DB2;UID=MY_UID;PWD=MY_PASS"

'Command brproperties set<>adoCmd.Name = "TestParm"
Set adoCmd.ActiveConnection = adoConn
adoCmd.CommandText = SqlSent
adoCmd.CommandType = adCmdText
adoCmd.CommandTimeout = 15

'Parameter properties set
Set adoParm = New ADODB.Parameter
adoParm.Name = "MYPARM"
adoParm.Type = adVarChar
adoParm.Size = 10
adoCmd.Parameters.Append adoParm
adoParm.Value = "TEST1"

'result set defined; execute SQL statement
Set adoRs = adoCmd.Execute(RecordsAffected)

'Return first column of each record in result set to List box
For i = 1 To adoRs.RecordCount
List1.AddItem adoRs(0)
adoRs.MoveNext
Next

'Clear connection
Set adoCmd = Nothing
Set adoConn = Nothing
Me.MousePointer = 0
Exit Sub

'Error handling
Command3_ErrorProc:
Me.MousePointer = 0
If Not adoCmd Is Nothing Then
Set adoCmd = Nothing
End If
If Not adoConn Is Nothing Then
Set adoConn = Nothing
End If
MsgBox Err.Description

Exit Sub

End Sub