Stored Procedure call failed in VB.NET

[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!

 

[Cor Ligthert [MVP]]

Hi,

Why don't you try it first with a very simple stored procedure.
That makes it easier to find in what part your problem is.

Cor

 

[Marina Levit [MVP]]

'Stored procedure failed' has no meaning when trying to get people to help
you.

Does that mean there was an exception? If so, what was it? What was the
message?
Does that mean there was no exception but the results were not waht you
expected? What did you expect t happen?

 

[William \(Bill\) Vaughn]

When you call a SP with OUTPUT parameters, you must set the
Parameter.Direction property to output (choose from the list of
enumerations).
Once the query has executed, all of the rows are returned and the DataReader
is closed, the TDS packets with the OUTPUT parameters are sent. Then and
only then can you reference the Parameters marked as output to fetch the
results.

This is discussed in depth in my book "ADO and ADO.NET Examples and Best
Practices".

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Darren Kopp]

Here's the format you want

dbcommand.Parameters.Add("@TanCode", SqlDbType.Int)
dbcommand.Parameters("@TanCode").Value = TanCode

and for out parameters try this

Dim pErrAdd as New SqlParameter("@ErrAdd", SqlDbType.Int)
pErrAdd.Direction = ParameterDirection.Output
dbcommand.Parameters.Add(pErrAdd)

after you have all the params in, you can exectute non-query as you
have, and access the output parameters like so...

dbcommand.ExecuteNonQuery()
Dim Error as Integer = int.Parse(pErrAdd.Value.ToString())

HTH,
Darren Kopp
http://blog.secudocs.com/