March 4, 2004 at 11:03 pm
I tryout this program(SP) from VB, i am not getting the ouput values from SP.I don't know what is happening.Below i had sent the Vb and sp program.if u give the solution it will helpfull for me.
My vb Program is
*******************
strsql = "declare @strstatus int EXEC SP_EXPORT_SUPPLIER 'Insert','" & strconcat(0) & "','" & strconcat(1) & "','" & strconcat(2) & "','" & strconcat(3) & "','" & strconcat(4) & "','" & strconcat(5) & "','" & strconcat(6) & "','" & FormatPhoneNumber(strconcat(7)) & "','" & FormatPhoneNumber(strconcat(8)) & "',@strstatus output select @strstatus"
adocm.CommandText = strsql
adocm.ActiveConnection = AdoDREAMConn
Set oRs1 = adocm.Execute
Do Until oRs1.EOF
MsgBox oRs1(0)
oRs1.MoveNext
Loop
oRs1.Close
*******************
My SP is
*******************
Alter PROCEDURE [dbo].[SP_EXPORT_SUPPLIER]
@strOperation as varchar(100),
@strSuppCodeTemp as varchar(30),
@strSuppName as varchar(50),
@strAddress1 as varchar(50),
@strAddress2 as varchar(50),
@strCity as varchar(25),
@strState as varchar(2),
@strZip as varchar(20),
@strPhone as varchar(20),
@strFax as varchar(20),
@strStatus int=3 output
AS
BEGIN
Declare @strSuppCode as varchar(5),@localStatus int
set @localstatus = 3
IF @strOperation='Insert' or @strOperation='Update'
BEGIN
IF NOT EXISTS (SELECT dreamcode from m_interfacemaster where vocode=@strSuppCodeTemp and type = 'Pra')
BEGIN
set @localStatus = 0
select @strSuppCode = 'S' + convert(varchar, max(convert(numeric,substring(suppcode, 2, 4))) + 1) from m_supplier
where IsNumeric(substring(suppcode, 2, 4)) = 1
Insert into m_interfacemaster(vocode, dreamcode,type) values (@strSuppCodeTemp, @strSuppCode,'Pra')
Insert into m_supplier(suppcode , suppname, Address1, Address2, City, State, Zip, phone, fax) Values
(@strSuppCode, @strSuppName, @strAddress1, @strAddress2, @strCity, @strState, @strZip,
@strPhone, @strFax)
End
Else
BEGIN
--set @localStatus = 1
Select @strSuppCode=dreamcode from m_interfacemaster where vocode=@strSuppCodeTemp and type = 'Pra'
Update m_supplier SET suppname=@strSuppName,address1=@strAddress1,address2=@strAddress2,city=@strCity,
state=@strState,zip=@strZip,phone=@strPhone,fax=@strFax WHERE suppcode =
(Select top 1 dreamcode from m_interfacemaster where vocode=@strSuppCodeTemp and type = 'Pra')
--suppcode=@strSuppCode
End
End
Else IF @strOperation='Delete'
BEGIN
set @localStatus = 2
Select @strSuppCode=dreamcode from m_interfacemaster where vocode=@strSuppCodeTemp and type = 'Pra'
Delete from m_interfacemaster where vocode=@strSuppCodeTemp and type = 'Pra'
Delete from m_supplier where suppcode=@strSuppCode
End
IF @strOperation='Insert' and @localStatus <> 0
set @strStatus = 1
else if @localStatus >= 0
set @strStatus = @localStatus
End
*******************
March 4, 2004 at 11:38 pm
Try this
adocm.ActiveConnection = AdoDREAMConn
adocm.CommandType = adCmdStoredProc
adocm.CommandText = "SP_EXPORT_SUPPLIER"
adocm.Parameters.Refresh ' or manually append parameters ....
adocm.Parameters(1) = param1_value
adocm.Parameters(2) = param2_value
.
.
.
adocm.Parameters(n) = param_n_value
MsgBox adocm.Parameters( out_param_number )
MsgBox adocm.Parameters( 0 ) ' for return value of SP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply