Skip to main content
Email Password   helpLost your password?

Introduction

This article explains how to use a Stored procedure in Visual Basic 6.0 using Microsoft ActiveX Data Objects(ADO). I tried to make it simple and I think this may be helpful for those who want to try stored procedures in VB6 applications. Here I will explain

ActiveX Data Objects (ADO)

ActiveX Data Objects provide a set of objects, properties, and methods for applications that access and manipulate data. The commonly used ADO objects are Connection, Command, and Recordset.

Connection Object

Connection object establishes a connection to a data source. To create a Connection object, we need to supply the name of either an ODBC data source or an OLE DB provider.

ADO Connection Strings (CP article by Carlos Antollini)

Command object

A Command object is a definition of a specific command that we intend to execute against a data source.

CommandType property is used to specify the type of command used. Example includes adCmdText - Use if the query string is a SQL command. adCmdTable - Use if the query string is the name of a table name. adCmdStoredProc - Use if the query string is the name of a stored procedure.

Recordset object

Recordset represents the entire set of records from a database table or the results of an executed command.

Creating & Testing a simple Stored Procedure

A Stored Procedure is a set of one or more SQL statements that is precompiled and processed as a single task. The stored procedure exists in the database as an object, even after execution has finished. To know more about Stored procedures check out this CP article.

Everything you wanted to know about stored procedures (CP article by Himanshu Khatri)

Example:

In the SQL Enterprise Manager, expand the Northwind database, right click on the Stored Procedures and select New Stored Procedures, then write down the following example and click OK button.

CREATE PROCEDURE empdetails @empid int
AS
SELECT FirstName, Title, Address FROM Employees WHERE EmployeeID=@empid

GO

From the Tools option select SQL Query Analyser and test the stored procedure as below:

exec empdetails 1

Here exec is the command to execute a stored procedure empdetails is the procedure name and 1 is the employee id. Execute the procedure (F5), you can see the record displayed.

Using Stored Procedure in Visual Basic 6 (Example)

1) Create a new Project in Visual Basic.

2) On the form place the following controls in the order as shown in the figure:

3) From the Project menu click References and select Microsoft ActiveX Data Objects 2.5 Library, and click OK.


4) On the General Declaration section (click on top of code window) enter the following code.

Option Explicit
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim str_empid, strconnect As String

Here Option Explicit force you to declare all variables that are used in the program (good programming practice), con is the connection object, rs is the recordset object cmd is the command object, which is used for specifying the command type and procedure name, str_empid is used to store employee id and strconnect for storing the connection string.

5) On the form load event (Form_Load) enter this:

Private Sub Form_Load()
    strconnect = "Provider=SQLOLEDB;Data Source=suj;Initial Catalog=Northwind"
    con.Open strconnect, "sa", ""
End Sub

Here SQLOLEDB is the database provider, suj is the system or computer name (will be different in your case) and Northwind is the database name, strconnect is the connection string, sa is username and password is null (this will be same as above unless you have changed it). con.Open opens the database connection.

6) On the Command button click event (cmd_get_Click) enter this: 

Private Sub cmd_get_Click()

 str_empid = txt_empid.Text

 Set cmd = New ADODB.Command
 cmd.ActiveConnection = con
 cmd.CommandType = adCmdStoredProc
 cmd.CommandText = "empdetails"

 cmd.Parameters.Append cmd.CreateParameter("empid", adVarChar, adParamInput, 6,   str_empid)

 Set rs = cmd.Execute

 If Not rs.EOF Then
  txt_firstname = rs.Fields(0)
  txt_title = rs.Fields(1)
  txt_address = rs.Fields(2)
 End If

 Set cmd.ActiveConnection = Nothing

End Sub

Here we first assign the value of employee id in the Textbox (txt_empid) to str_empid. You should set cmd as a new command object and should specify the connection to be used(con), the command type (adCmdStoredProc � for Strored procedure), command text(empdetails � name of the stored procedure),the parameter and value (empid � parameter name, str_empid � employee id value). After that we execute the cmd and assign the value to rs (ADODB Recordset object). If the recordset (rs) contains some value, those values will be assigned to appropriate textboxes. Finally set the connection of cmd to none.

7) Execute the program (F5). Enter an employee id and click the Get button, you should get a similar result as below.

Sample screenshot

Using OUTPUT parameter in Stored Procedures

Output parameters allow the stored procedure to pass a value back to the caller program.
This will be useful for setting some status values that can be used in the programs.

Here is an example that uses an output parameter. 

CREATE PROCEDURE update_empdetails @empid int,@firstname varchar(30),@title varchar(30) ,
@address varchar(100),@result int OUTPUT AS
Begin 
update Employees set FirstName=@firstname,Title=@title,Address=@address WHERE EmployeeID=@empid;
set @result=1;
select @result;
EndGO

Here @result int OUTPUT is the output parameter, set @result=1 will set a value for the parameter select @result statement is needed only if you want to see the result in SQL Query Analyser.

To test the procedure in SQL Query Analyser : 

update_empdetails 1, 'abc','Programmer','Address' , 0

Calling this procedure in VB:

1) Add one more Command button (name= cmd_update, Caption=Update) in your form as below.

Sample screenshot

2) In the button click event enter this code

Private Sub cmd_update_Click()
Dim res As Integer
Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "update_empdetails"
cmd.Parameters.Append cmd.CreateParameter("empid", adVarChar, adParamInput, 6, txt_empid.Text)
cmd.Parameters.Append cmd.CreateParameter("firstname", adVarChar, adParamInput, 30, txt_firstname.Text)
cmd.Parameters.Append cmd.CreateParameter("title", adVarChar, adParamInput, 30, txt_title.Text)
cmd.Parameters.Append cmd.CreateParameter("address", adVarChar, adParamInput, 100, txt_address.Text)
cmd.Parameters.Append cmd.CreateParameter("result", adInteger, adParamOutput)
cmd.Execute
res = cmd("result")
If (res = 1) Then
 MsgBox "Updated Successfully"
End If
Set cmd.ActiveConnection = Nothing
End Sub

Here cmd.Parameters.Append cmd.CreateParameter("result", adInteger, adParamOutput) is for specifying the output parameter.
result � name of the output parameter
adInteger �data type
adParamOutput �Parameter Direction Enum, for output parameter.

res = cmd("result")

This statement will assign the value of the result parameter to res.

3) Execute the program (F5). Enter an employee id and click the Get button, The values will be displayed in textboxes, then enter new values for First Name, Title and Address and click the Update button, the record is updated and you will get a message box saying "Updated Successfully".

ADO Reference:

Distributed Applications for Microsoft Visual Basic 6.0 MCSD Training Kit.

You must Sign In to use this message board.
 
 
Per page   
 FirstPrevNext
GeneralHow CreateParameter in cmd_get_Click works ? [modified] Pin
rixan		1:23 18 Apr '08  
QuestionObject Not Open Pin
okokokokokokok		22:46 3 Jun '07  
AnswerRe: Object Not Open Pin
okokokokokokok		1:22 4 Jun '07  
QuestionCommand Object [modified] Pin
Kinara		7:49 4 Apr '07  
AnswerRe: Command Object Pin
Sujith C Jose		20:18 10 Apr '07  
Generalcant open db Pin
stivisa		1:43 21 Dec '06  
GeneralRe: cant open db Pin
Sujith C Jose		2:46 21 Dec '06  
GeneralHow to Pin
gggggg		8:39 27 Oct '06  
AnswerRe: How to Pin
Jonathan Black		20:51 30 Nov '07  
GeneralRe: How to Pin
rixan		1:53 18 Apr '08  
Questionusing dsn Pin
jenni21abc		18:39 30 Sep '06  
AnswerRe: using dsn [modified] Pin
Sujith C Jose		22:10 1 Oct '06  
GeneralMissing Pin
Devlin Schoonraad		0:58 7 Sep '06  
GeneralRe: Missing Pin
Sujith C Jose		7:03 21 Sep '06  
GeneralIts been a while Pin
Ennis Ray Lynch, Jr.		11:00 24 Aug '06  
Generalreturn value Pin
aldo hexosa		18:04 23 Aug '06  
AnswerRe: return value Pin
Sujith C Jose		10:52 24 Aug '06  
Last Visit: 7:58 22 Nov '09     Last Update: 7:58 22 Nov '09 1


Last Updated 27 Mar 2007 | Advertise | Privacy | Terms of Use | Copyright © CodeProject, 1999-2009