Click here to Skip to main content
11,648,094 members (61,376 online)
Click here to Skip to main content

How to Use Stored Procedures in VB6

, 27 Mar 2007 CPOL 692.1K 3.8K 46
Rate this:
Please Sign up or sign in to vote.
Describes how to use a simple stored procedure in Visual Basic 6.0

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:

  • The steps for writing a small stored procedure (in SQL Server 2000)
  • How to use that stored procedure in VB 6.0
  • Using Output parameters in stored procedures

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

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

Command Object

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

CommandTypeproperty 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

Recordsetrepresents 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 CodeProject article.

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, empdetailsis the procedure name and 1is 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:
    • Three Labels (First Name, Title, Address)
    • Four Textboxes (txt_empid, txt_firstname, txt_title, txt_address)
    • One Command button (name= cmd_get, Caption=Get)

      Sample screenshot
  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 forces you to declare all variables that are used in the program (good programming practice), conis the connection object, rsis the recordset object, cmdis the command object which is used for specifying the command type and procedure name, str_empid is used to store employee id and strconnectfor 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 SQLOLEDBis the database provider, sujis the system or computer name (will be different in your case) and Northwindis the database name, strconnectis the connection string, sais username and password is null (this will be same as above unless you have changed it). con.Openopens 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 cmdas a new command object and should specify the connection to be used(con), the command type (adCmdStoredProc– for Stored 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 cmdand 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 cmdto 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 OUTPUTis the output parameter, set @result=1 will set a value for the parameter, select @resultstatement 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sujith C Jose
Software Developer HCL Technologies
India India
Sujith C Jose is a Developer at HCL Technologies.
He holds graduate degree in Computer Science and post graduate degree in Computer Applications.

Sujith's Location

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 4 Pin
Ujjval Shukla27-Jun-15 1:32
memberUjjval Shukla27-Jun-15 1:32 
QuestionHow we map Textbox with Stored procedure return values Pin
Member 1071197230-Mar-14 22:28
memberMember 1071197230-Mar-14 22:28 
GeneralMy vote of 1 Pin
prasad_vjp21-Dec-11 2:09
memberprasad_vjp21-Dec-11 2:09 
GeneralMy vote of 5 Pin
ericcisco16-Nov-10 1:11
memberericcisco16-Nov-10 1:11 
GeneralMy vote of 5 Pin
HarishChand12-Sep-10 23:03
memberHarishChand12-Sep-10 23:03 
GeneralMy vote of 5 Pin
darkangel000428-Jul-10 16:26
memberdarkangel000428-Jul-10 16:26 
QuestionHow CreateParameter in cmd_get_Click works ? [modified] Pin
rixan18-Apr-08 0:23
memberrixan18-Apr-08 0:23 
QuestionObject Not Open Pin
okokokokokokok3-Jun-07 21:46
memberokokokokokokok3-Jun-07 21:46 
AnswerRe: Object Not Open Pin
okokokokokokok4-Jun-07 0:22
memberokokokokokokok4-Jun-07 0:22 
QuestionCommand Object [modified] Pin
Kinara4-Apr-07 6:49
memberKinara4-Apr-07 6:49 
AnswerRe: Command Object Pin
Sujith C Jose10-Apr-07 19:18
memberSujith C Jose10-Apr-07 19:18 
Generalcant open db Pin
stivisa21-Dec-06 0:43
memberstivisa21-Dec-06 0:43 
GeneralRe: cant open db Pin
Sujith C Jose21-Dec-06 1:46
memberSujith C Jose21-Dec-06 1:46 
QuestionHow to Pin
gggggg27-Oct-06 7:39
membergggggg27-Oct-06 7:39 
AnswerRe: How to Pin
Jonathan Black30-Nov-07 19:51
memberJonathan Black30-Nov-07 19:51 
GeneralRe: How to Pin
rixan18-Apr-08 0:53
memberrixan18-Apr-08 0:53 
Questionusing dsn Pin
jenni21abc30-Sep-06 17:39
memberjenni21abc30-Sep-06 17:39 
AnswerRe: using dsn [modified] Pin
Sujith C Jose1-Oct-06 21:10
memberSujith C Jose1-Oct-06 21:10 
GeneralMissing Pin
Devlin Schoonraad6-Sep-06 23:58
memberDevlin Schoonraad6-Sep-06 23:58 
The source code (simple_sp_vb6.zip) for this article appears to be missing :->

There's no place like 127.0.0.1

GeneralRe: Missing Pin
Sujith C Jose21-Sep-06 6:03
memberSujith C Jose21-Sep-06 6:03 
GeneralIts been a while Pin
Ennis Ray Lynch, Jr.24-Aug-06 10:00
memberEnnis Ray Lynch, Jr.24-Aug-06 10:00 
Generalreturn value Pin
aldo hexosa23-Aug-06 17:04
memberaldo hexosa23-Aug-06 17:04 
AnswerRe: return value Pin
Sujith C Jose24-Aug-06 9:52
memberSujith C Jose24-Aug-06 9:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150804.4 | Last Updated 27 Mar 2007
Article Copyright 2006 by Sujith C Jose
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid