Examples of VB6.0 calling stored procedures (preface)

The example of stored procedure VB calling stored procedure preface

(Note: The following code is excerpted from Microsoft MSDN and tested.) )



There are many methods to call stored procedures in VB, such as using ADO object's Recordset.Open method, ADO object's Connection.excute method, etc., can obtain recordset information. This topic discusses using the Parameter object to invoke a stored procedure, and you can get a lot of unexpected information.


First, you need to establish a stored procedure in SQL Server. Make sure that any version of SQL Server 2000 is installed and contains the pubs database.

Open Query Analyzer, start your local SQL Server, and then copy the following SQL statement into the query edit box that you opened. Press the "F5" key. OK, the stored procedure Adotestrpe is generated.



Use Pubs

Go

if exists (select * from sysobjects where id =

OBJECT_ID (' dbo. Adotestrpe ') and Sysstat & 0xf = 4)

drop procedure dbo. Adotestrpe

Go



CREATE PROCEDURE Adotestrpe

(

@SetRtn int=0 OUTPUT,

@R1Num Int=1,

@P1Num Int=1,

@E1Num Int=1,

@R2Num int=2,

@P2Num int=2,

@E2Num int=2

)

As

DECLARE @iLoop INT

DECLARE @PrintText VARCHAR (255)

DECLARE @iErrNum INT



/* Check for no resultsets-needed to get "return value" back * *

IF @R1Num + @R2Num = 0 SELECT NULL



/* Resultset 1 ******************************* * *



IF @R1Num > 0

BEGIN

SET RowCount @R1Num

SELECT ' Resultset 1 ' rsnum, Title

From Pubs. Titles

SET ROWCOUNT 0

End



/* must raise a default error context in which to return the PRINT * *

* Statement * *

/* (if none present) since PRINT statements are a severity level of * *

/*0. */

IF (@P1Num > 0) and (@E1Num = 0) RAISERROR ("RAISERROR. PError1 ",

11, 2)



IF @P1Num > 0

BEGIN

SELECT @iLoop = 0

While @iLoop < @P1Num

BEGIN

SELECT @iLoop = @iLoop + 1

SELECT @PrintText = ' PRINT. Resultset.1:line ' +

CONVERT (char (2), @iLoop)

PRINT @PrintText

End

End



IF @E1Num > 0

BEGIN

SELECT @iLoop = 0

While @iLoop < @E1Num

BEGIN

SELECT @iLoop = @iLoop + 1

SELECT @iErrNum = @iLoop + 201000

RAISERROR ("RAISERROR.") Resultset.1 ", 11, 2)

End

End



/* Resultset 2 ******************************* * *



IF @R2Num > 0

BEGIN

SET RowCount @R2Num

SELECT ' Resultset 2 ' rsnum, Title

From Pubs. Titles

SET ROWCOUNT 0

End



/* must raise a default error context in which to return the PRINT * *

* Statement * *

/* (if none present) since PRINT statements are a severity level of * *

/* 0. */

IF (@P2Num > 0) and (@E2Num = 0) RAISERROR ("RAISERROR. PError2 ",

11, 2)



IF @P2Num > 0

BEGIN

SELECT @iLoop = 0

While @iLoop < @P2Num

BEGIN

SELECT @iLoop = @iLoop + 1

SELECT @PrintText = ' PRINT. Resultset.2:line ' +

CONVERT (char (2), @iLoop)

PRINT @PrintText

End

End



IF @E2Num > 0

BEGIN

SELECT @iLoop = 0

While @iLoop < @E2Num

BEGIN

SELECT @iLoop = @iLoop + 1



SELECT @iErrNum = @iLoop + 202000

RAISERROR ("RAISERROR.") Resultset.2 ", 11, 2)

End

End



/* Return & Output ************************************ * *



Select @SetRtn =-1

Return @SetRtn

Go



When you are finished running, if there are no errors, close Query Analyzer, and then continue with the following actions. Otherwise it may be that your SQL Server 2000 does not have the correct or copy-installed problem.



Open VB6.0, create a new project, default to a form Form1 (if not, add a new form, named Form1), add a CommandButton to the form. Save the project.