Stored Procedure, ADO.NET (VB) Error handling and generating error messages.

Stored Procedure, ADO.NET (VB) Error handling and generating error messages.

Post by Ali Eghteb » Sat, 18 Jan 2003 02:35:22



Hi,

I'm using VB .NET and SQL Server 2000.
I'm using the SqlDataAdapter control with the Stored Procedures
generated by its Wizard to Select, Insert, Delete and Update rows in
the database tables.


an output parameter to the client VB application and also to manage
transactions.
Now what I wonder is how can I in a smart way handle the errors and
generate user friendly error messages in the VB client application
since I couldn't succeed generating any messages in the stored
procedure with Raiserror/Print.

Would appreciate an example code regarding the code below.

I also wonder what IS the returned value in intRetVal. Is it the
number of rows effected?

Thanks for any help in advance
Ali Eghtebas, Sweden

******************* In SQL Server 2000 ********************
CREATE PROCEDURE dbo.usp_Ins_ArtikelKat
(


)
AS
        SET NOCOUNT OFF;


Begin Tran -- Start a transaction


--SELECT tintCategory_ID, strCategory FROM tbl_ProdCat WHERE



-- If an Error has occurred
  Begin
    -- Could I show an error msg here? Raiserror or Print did't work
    Rollback Tran -- Cancel the transaction
  End
Else
-- If there is no errors
  Begin
    Commit Tran -- Commit the transaction
  End

-- Moving the Select statement down here makes the datarow in the
dataset to
-- have null as the key value if the transaction is rolled back. That
makes it
-- easy to find the rows in the dataset that could not be updated in
the
-- database table.
SELECT tintCategory_ID, strCategory FROM tbl_ProdCat


parameter
GO
*******************************************************

********* In my VB client application *********
'Adds an Product Category to the DB(tbl_ProdCat)
Private Sub btnArt_AddCat_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnArt_AddKat.Click
        Dim dsProdCat As New DataSet()
        Dim drNewProdCat As DataRow
        Dim intRetVal%,intError%

        SqlDataAdapter1.Fill(dsProdCat, "tbl_ProdCat")
        drNewProdCat = dsProdCat.Tables("tbl_ProdCat").NewRow
        drNewProdCat.Item("strCategory") = txtCategory.Text
        dsProdCat.Tables("tbl_ProdCat").Rows.Add(drNewProdCat)
        intRetVal = SqlDataAdapter1.Update(dsProdCat.Tables("tbl_ProdCat"))

End Sub
***********************************************

 
 
 

Stored Procedure, ADO.NET (VB) Error handling and generating error messages.

Post by Mary Chipma » Sat, 18 Jan 2003 04:27:32


What you want to do is also have an output parameter for an error

0. Your client code then tests the value that is returned by the
stored procedure to see if it's 0 or not. Having an additional output
parameter for an error message lets you send a more informative string
back so it can be displayed in a message box or whatever. If you're
using output parameters, then you don't need raiserror or print--just
send the appropriate values back.

-- Mary
MCW Technologies
http://www.mcwtech.com


>Hi,

>I'm using VB .NET and SQL Server 2000.
>I'm using the SqlDataAdapter control with the Stored Procedures
>generated by its Wizard to Select, Insert, Delete and Update rows in
>the database tables.


>an output parameter to the client VB application and also to manage
>transactions.
>Now what I wonder is how can I in a smart way handle the errors and
>generate user friendly error messages in the VB client application
>since I couldn't succeed generating any messages in the stored
>procedure with Raiserror/Print.

>Would appreciate an example code regarding the code below.

>I also wonder what IS the returned value in intRetVal. Is it the
>number of rows effected?

>Thanks for any help in advance
>Ali Eghtebas, Sweden

>******************* In SQL Server 2000 ********************
>CREATE PROCEDURE dbo.usp_Ins_ArtikelKat
>(


>)
>AS
>    SET NOCOUNT OFF;


>Begin Tran -- Start a transaction


>--SELECT tintCategory_ID, strCategory FROM tbl_ProdCat WHERE



>-- If an Error has occurred
>  Begin
>    -- Could I show an error msg here? Raiserror or Print did't work
>    Rollback Tran -- Cancel the transaction
>  End
>Else
>-- If there is no errors
>  Begin
>    Commit Tran -- Commit the transaction
>  End

>-- Moving the Select statement down here makes the datarow in the
>dataset to
>-- have null as the key value if the transaction is rolled back. That
>makes it
>-- easy to find the rows in the dataset that could not be updated in
>the
>-- database table.
>SELECT tintCategory_ID, strCategory FROM tbl_ProdCat


>parameter
>GO
>*******************************************************

>********* In my VB client application *********
>'Adds an Product Category to the DB(tbl_ProdCat)
>Private Sub btnArt_AddCat_Click(ByVal sender As System.Object, ByVal e
>As System.EventArgs) Handles btnArt_AddKat.Click
>        Dim dsProdCat As New DataSet()
>        Dim drNewProdCat As DataRow
>        Dim intRetVal%,intError%

>        SqlDataAdapter1.Fill(dsProdCat, "tbl_ProdCat")
>        drNewProdCat = dsProdCat.Tables("tbl_ProdCat").NewRow
>        drNewProdCat.Item("strCategory") = txtCategory.Text
>        dsProdCat.Tables("tbl_ProdCat").Rows.Add(drNewProdCat)
>        intRetVal = SqlDataAdapter1.Update(dsProdCat.Tables("tbl_ProdCat"))

>End Sub
>***********************************************


 
 
 

1. ADO.NET via VB.NET error with stored procedures using Temp tables

I have an issue with the SQLDataAdapter returning an error
'Invalid Object #P1" .#P1 is a temp table. for a better
under standing  of what the stored procedure is structured
like see the attached file. I have used the temp table
method for a very long time and the sql 7 and 2000 engine
can handle the temp table structure just fine . Why would
MS violate their own standards in ADO.net and the
SQLDataAdapter Control ?

THis is very frustrating as I am trying to migrate a
system written in vb6 to .Net and these stored procedures
worked fine with the older ado 2.6 under vb6.

Jon O Roberts

2. Connection/Recordset replacement in .NET?

3. (Encrypted) "Stored Procedure does not exist" -- error returned when called by ADO.NET

4. Make Money Fast!

5. Error when sending large image to SQL Server via ADO.NET & Stored Procedure

6. CAN YOU WRITE ABOUT PREPRESS?

7. Handling transactions in ADO.NET vs in a Stored Procedure

8. apple in shared border

9. stored procedure with params returns error (using vb.net)

10. VB.NET/ADO.NET to Oracle 9i Stored Procedure?

11. Can't get Stored Procedure RaiseError message in VB.NET

12. Throwing errors from RAISERROR events generated from ExecuteNonQuery stored procedure calls

13. error handling using C# and stored procedures