I
itsolutionsfree
Hi,
I have a stored procedure on SQLServer2000 version (8.00.194) and 'm
executing a stored procedure from vb.net.
-------------------------------------------------------------------------------------------------------------------------
stored procedure in server
-------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE STP_ECLAT_FBT_TAN_MST_ADD(
@TanCode int,
@TanNo Varchar(50),
@Active bit,
@Remarks Varchar(500),
@cmpid int,
@ERRADD AS INT OUTPUT,
@MAXIC AS INT OUTPUT)
AS
DECLARE @INTCNT AS INTEGER -- For the Unique Check
DECLARE @ERRNO AS INTEGER -- For getting the Error Number
BEGIN TRAN
SET @INTCNT=(SELECT COUNT(TanCode) FROM ECLAT_FBT_TAN_MST WHERE
TanCode=@TanCode)
IF @INTCNT<>0
BEGIN
SET @ERRADD=50001
ROLLBACK TRANSACTION
RETURN
END
ELSE
INSERT INTO ECLAT_FBT_TAN_MST (TanCode,TanNo,Active,Remarks,cmpid)
VALUES
(@TanCode,@TanNo,@Active,@Remarks, @cmpid);
SELECT @ERRNO=@@ERROR
IF @ERRNO<> 0
BEGIN
SET @ERRADD=@ERRNO
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
SET @ERRADD=0
SELECT @MAXIC=MAX(TanId) FROM ECLAT_FBT_TAN_MST
END
COMMIT TRAN
GO
-------------------------------------------------------------------------------------------------------------------------
In VB.net
-------------------------------------------------------------------------------------------------------------------------
Dim dbcommand As New SqlCommand
Dim BytActive As Integer
If rbtActive.Checked = True Then
BytActive = 1
Else
BytActive = 0
End If
dbcommand.CommandText = "STP_ECLAT_FBT_TAN_MST_ADD"
dbcommand.CommandType = CommandType.StoredProcedure
dbcommand.Connection = gConnection
dbcommand.Parameters.Add(New SqlParameter("@TanCode", TanCode))
dbcommand.Parameters.Add(New SqlParameter("@TanNo", TanNo))
dbcommand.Parameters.Add(New SqlParameter("@Active", BytActive))
dbcommand.Parameters.Add(New SqlParameter("@Remarks", Remarks))
dbcommand.Parameters.Add(New SqlParameter("@cmpid", cmpid))
dbcommand.ExecuteNonQuery()
I d'not know how to run stored procedure from vb.net and pass input
/get output parameters, but i have excetued store procedure in vb6.0.
Any help is appreciated!
I have a stored procedure on SQLServer2000 version (8.00.194) and 'm
executing a stored procedure from vb.net.
-------------------------------------------------------------------------------------------------------------------------
stored procedure in server
-------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE STP_ECLAT_FBT_TAN_MST_ADD(
@TanCode int,
@TanNo Varchar(50),
@Active bit,
@Remarks Varchar(500),
@cmpid int,
@ERRADD AS INT OUTPUT,
@MAXIC AS INT OUTPUT)
AS
DECLARE @INTCNT AS INTEGER -- For the Unique Check
DECLARE @ERRNO AS INTEGER -- For getting the Error Number
BEGIN TRAN
SET @INTCNT=(SELECT COUNT(TanCode) FROM ECLAT_FBT_TAN_MST WHERE
TanCode=@TanCode)
IF @INTCNT<>0
BEGIN
SET @ERRADD=50001
ROLLBACK TRANSACTION
RETURN
END
ELSE
INSERT INTO ECLAT_FBT_TAN_MST (TanCode,TanNo,Active,Remarks,cmpid)
VALUES
(@TanCode,@TanNo,@Active,@Remarks, @cmpid);
SELECT @ERRNO=@@ERROR
IF @ERRNO<> 0
BEGIN
SET @ERRADD=@ERRNO
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
SET @ERRADD=0
SELECT @MAXIC=MAX(TanId) FROM ECLAT_FBT_TAN_MST
END
COMMIT TRAN
GO
-------------------------------------------------------------------------------------------------------------------------
In VB.net
-------------------------------------------------------------------------------------------------------------------------
Dim dbcommand As New SqlCommand
Dim BytActive As Integer
If rbtActive.Checked = True Then
BytActive = 1
Else
BytActive = 0
End If
dbcommand.CommandText = "STP_ECLAT_FBT_TAN_MST_ADD"
dbcommand.CommandType = CommandType.StoredProcedure
dbcommand.Connection = gConnection
dbcommand.Parameters.Add(New SqlParameter("@TanCode", TanCode))
dbcommand.Parameters.Add(New SqlParameter("@TanNo", TanNo))
dbcommand.Parameters.Add(New SqlParameter("@Active", BytActive))
dbcommand.Parameters.Add(New SqlParameter("@Remarks", Remarks))
dbcommand.Parameters.Add(New SqlParameter("@cmpid", cmpid))
dbcommand.ExecuteNonQuery()
I d'not know how to run stored procedure from vb.net and pass input
/get output parameters, but i have excetued store procedure in vb6.0.
Any help is appreciated!