Stored Procedure call failed in VB.NET

2 Mar 2006 10:01 AM

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!

2 Mar 2006 10:22 AM

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

<itsolutionsf***@gmail.com> schreef in bericht
Show quote

news:1141293692.635033.228530@e56g2000cwe.googlegroups.com...
> 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!
>

2 Mar 2006 1:38 PM

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?

<itsolutionsf***@gmail.com> wrote in message
Show quote

news:1141293692.635033.228530@e56g2000cwe.googlegroups.com...
> 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!
>

2 Mar 2006 5:21 PM

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.
__________________________________

<itsolutionsf***@gmail.com> wrote in message

Show quote

news:1141293692.635033.228530@e56g2000cwe.googlegroups.com...
> 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!
>

2 Mar 2006 9:32 PM

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/