stored procedure calling from vb6

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored procedure calling from vb6

Author

Topic

nizguy
Starting Member

37 Posts

Posted - 2010-02-18 : 17:28:46

I am reading this code from my previous vb6 programmer.
he called this stored procedure using ado from vb6
I have two questions:
1) How to return the result from @DB_SUCCESS or @DB_Fail from the stored procedure.
2) If I want to return BOTH result (@DB_fail or @DB_Success AND the result from the select statement) from the stored procedure, how would I write the code in vb6

Thank you

***** Call from vb6
With adoCmd
.ActiveConnection = adoConn
.CommandType = adCmdStoredProc
.CommandText = "Find_zipcode"
.Parameters.Append adoCmd.CreateParameter("FromZip", adVarChar, adParamInput, 5, tempFzip)
.Parameters.Append adoCmd.CreateParameter("ToZip", adVarChar, adParamInput, 5, tempTzip)
End With

With adoRs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.Open adoCmd
End With

If Not adoRs.EOF Then
txtCity.text = adoRs!City
txtState.text = adoRs!State
End If

adoRs.Close

***** Stored procedure *****
CREATE PROCEDURE Find_zipcode
@FromZip char(5),
@ToZip char(5)

AS

Declare @DB_FAIL int
,@DB_SUCCESS int

Select @DB_FAIL = 0
,@DB_SUCCESS = -1

Select
FromZip, ToZip, City, State
From
zipcode
Where
(fromzip <= @FromZip and tozip >= @FromZip)

If (@@rowcount = 0)
return @DB_FAIL

return @DB_SUCCESS

GO

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-02-18 : 17:57:01

make it an output paramater

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 00:16:15

see

http://www.sqlteam.com/article/stored-procedures-returning-data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources