displaying message from a database

hello programmers.
me again. just wondering if i could get some assistance over here.
i have successful created a store procedure that check to if a particular record already exits and this works fine. all i need to do is tell the user when the addition is successful or not
thanks in advance
==================sp=============
CREATE PROCEDURE [dbo].[spTest]
(
@MenuItemID int,
@SectionName nvarchar(50)
)
AS
IF EXISTS(SELECT 'TRUE' FROM Sections WHERE SectionName = @SectionName)
BEGIN
SELECT 'This record already exists!'
END
ELSE
BEGIN
SELECT 'Record Added!'
INSERT INTO Sections (SectionName,MenuItemID)
VALUES (@SectionName,@MenuItemID)
END
GO
==================================
===========this is the function that calls my sp== above=============
Public Sub AddSection(ByVal MenuItemID As Integer, ByVal SectionName As String)
            'Calls the Stored Procedure which adds New Sections
            Dim DL As New DataLayer
            Dim sqlparams As New ArrayList
            sqlparams.Add(DL.AddParameter("@SectionName", "SqlDbType.NVarChar", 50, SectionName, ParameterDirection.Input))
            sqlparams.Add(DL.AddParameter("@MenuItemID", "SqlDbType.Int", 4, CInt(MenuItemID), ParameterDirection.Input))
            Try
                DL.ExecuteStoredProc("spTest", sqlparams)
            Catch ex As Exception
                Throw New ArgumentException(ex.Message.ToString())
            End Try
        End Sub
=====================================

===================in my code behind==============
Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Try
            'Add sections  
            Dim AddSec As New Sections
            AddSec.AddSection(CInt(Request.QueryString("ID")), txtAdd.Text)
            'displays a message if input successful
            lblError.Text = "Section has been added.  Thank you"
            BindGrid()
            'throws an error if input unsuccessful
        Catch ex As Exception
            lblError.Text = ex.Message
        End Try
        txtAdd.Text = ""
    End Sub
==============
SirReadAlotAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SweatCoderCommented:
a little vague. . .so what is the problem? does your codebehind not work? it appears that your codebehind outputs text to user.
0
SirReadAlotAuthor Commented:
the code behind works if its successful
0
SirReadAlotAuthor Commented:
the code behind was used when i did not have to check the sp to see if record exits now that i have implemented it i need to display a message to the user.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SirReadAlotAuthor Commented:
i know the sp works cause i check it out manually should i have used this sp
CREATE PROCEDURE [dbo].[spTest2]
(
@MenuItemID int,
@SectionName nvarchar(50)
)
AS
IF EXISTS(SELECT SectionName FROM Sections WHERE SectionName = @SectionName)
RETURN 700
ELSE
INSERT INTO Sections (SectionName,MenuItemID)
VALUES (@SectionName,@MenuItemID)
RETURN @@ERROR
GO
0
KarinLoosCommented:
On what basis are you determining in your code behind if the add was successfull?
In all cases your sp return results (either Record added or record exists)  ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SirReadAlotAuthor Commented:
Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Try
            'Add sections  
            Dim AddSec As New Sections
            AddSec.AddSection(CInt(Request.QueryString("ID")), txtAdd.Text)
            'displays a message if input successful
            lblError.Text = "Section has been added.  Thank you"=======================here  karin
            BindGrid()
            'throws an error if input unsuccessful
        Catch ex As Exception
            lblError.Text = ex.Message
        End Try
        txtAdd.Text = ""
    End Sub
0
SirReadAlotAuthor Commented:
is my approach right?
0
SirReadAlotAuthor Commented:
is it still too vague
0
KarinLoosCommented:
According to above you always display Section is added regardless of whether it was added or not
my question was, how are you determining the difference ie when it is added you have a result set which
contains 'Record Added!'  and when not it contains  'This record already exists" returned from your sp
however your code behind states lblError.Text = .... regardless.
0
SirReadAlotAuthor Commented:
i understand your question thats why i needed some assistance on what to do
0
SirReadAlotAuthor Commented:
i need to deal with this--part
Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click
       
        Try
            'Add sections  
            Dim AddSec As New Sections
            AddSec.AddSection(CInt(Request.QueryString("ID")), txtAdd.Text)
            'displays a message if input successful
            lblError.Text = "Section has been added.  Thank you"------------------------*********************
            BindGrid()
            'throws an error if input unsuccessful
        Catch ex As Exception
            lblError.Text = ex.Message
        End Try
        txtAdd.Text = ""
    End Sub
0
SirReadAlotAuthor Commented:
does anyone know??
0
KarinLoosCommented:
My suggestion would be as follows:
1.  change the SP to have an output parameter which will tell you whether the insert occurred or not:
2.  On the basis of the output paramter within you code behind display the appropriate message:

ie  Stored proc
CREATE PROCEDURE [dbo].[spTest]
(
@MenuItemID int,
@SectionName nvarchar(50)
@Inserted  bit  OUTPUT
)
AS
BEGIN
  SET NOCOUNT ON
   IF EXISTS(SELECT 'TRUE' FROM Sections WHERE SectionName = @SectionName)
      BEGIN
        SET @Inserted = 1
      END
   ELSE
      BEGIN
          INSERT INTO Sections (SectionName,MenuItemID)
          VALUES (@SectionName,@MenuItemID)
          SET @Inserted = 1
      END
end
GO

ie Code behind

Public Sub AddSection(ByVal MenuItemID As Integer, ByVal SectionName As String)
            'Calls the Stored Procedure which adds New Sections
            Dim DL As New DataLayer
            Dim sqlparams As New ArrayList
            sqlparams.Add(DL.AddParameter("@SectionName", "SqlDbType.NVarChar", 50, SectionName, ParameterDirection.Input))
            sqlparams.Add(DL.AddParameter("@MenuItemID", "SqlDbType.Int", 4, CInt(MenuItemID), ParameterDirection.Input))

            Try
                DL.ExecuteStoredProc("spTest", sqlparams)
            Catch ex As Exception
                Throw New ArgumentException(ex.Message.ToString())
            End Try
        End Sub

0
SjoerdVerweijCommented:
Try replacing

SELECT 'This record already exists!'

with

RaisError('This record already exists!', 16, 1)

You might have to peel the actual message out of the nested exception. Set breakpoints on your Catch lines and examine the ex objects.
0
KarinLoosCommented:
oops pressed submit button to fast ignore above code behind

Public Sub AddSection(ByVal MenuItemID As Integer, ByVal SectionName As String) as Boolean
            'Calls the Stored Procedure which adds New Sections
            Dim DL As New DataLayer
Dim bnInserted as  boolean
            Dim sqlparams As New ArrayList
            sqlparams.Add(DL.AddParameter("@SectionName", "SqlDbType.NVarChar", 50, SectionName, ParameterDirection.Input))
            sqlparams.Add(DL.AddParameter("@MenuItemID", "SqlDbType.Int", 4, CInt(MenuItemID), ParameterDirection.Input))
            sqlparams.Add(DL.AddParameter("@Inserted", "SqlDbType.Boolean", 1, bnInserted, ParameterDirection.InputOutput))
           
            Try
                DL.ExecuteStoredProc("spTest", sqlparams)
            Catch ex As Exception
                Throw New ArgumentException(ex.Message.ToString())
            End Try
        End Sub


Then call the a

Not sure of the vb.net code off hand, but its close that much I know (air code here), but you can get the picture

then
Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click
       
        Try
            'Add sections  
            Dim AddSec As New Sections
            If AddSec.AddSection(CInt(Request.QueryString("ID")), txtAdd.Text) = true then
            'displays a message if input successful
            lblError.Text = "Section has been added.  Thank you"------------------------*********************
            BindGrid()
            'throws an error if input unsuccessful
             else
                lblError.Test = " section not added"
              end if
        Catch ex As Exception
            lblError.Text = ex.Message
        End Try
        txtAdd.Text = ""
    End Sub
0
SirReadAlotAuthor Commented:
right i will test this tomorrow at the office, its 8pm uk time

thanks all
0
SirReadAlotAuthor Commented:
still need more input pls

thanks

0
KarinLoosCommented:
1. The sp needs to be changed  to give an output parameter which will indicate whether or not the insert succeeded.
ie  Stored proc
CREATE PROCEDURE [dbo].[spTest]
(
@MenuItemID int,
@SectionName nvarchar(50)
@Inserted  bit  OUTPUT
)
AS
BEGIN
  SET NOCOUNT ON
   IF EXISTS(SELECT 'TRUE' FROM Sections WHERE SectionName = @SectionName)
      BEGIN
        SET @Inserted = 1
      END
   ELSE
      BEGIN
          INSERT INTO Sections (SectionName,MenuItemID)
          VALUES (@SectionName,@MenuItemID)
          SET @Inserted = 0
      END
end
GO

2. Within your AddSection  coding you need to create  a SQL parm for this parameter as well (noting the direction being Output (not inputoutput)
3. The value returned from the the parameter (once you have executed the sp  with .ExecuteStoredProc("spTest", sqlparams)) needs to be retrieved
    and it will contain either the value 0 (success) or 1 (record already existed)
4. The entire function AddSection (note it is now a sub, make it a function) must thus return either true or false (depending on the value of the output parm).
5. In your btnAdd_Click sub  you can then call the function using the IF then Else
  ie  IF AddSections (.....)  then
               lbllMessage.Text = "Record Added"
       Else
               lblMessage.Text = "Record already existed"
       End if

I hope thats a bit clearer
Karin
0
SirReadAlotAuthor Commented:
is this wat u mean in step 2

Public Sub AddSection(ByVal MenuItemID As Integer, ByVal SectionName As String)
            'Calls the Stored Procedure which adds New Sections
            Dim DL As New DataLayer
            Dim sqlparams As New ArrayList
            Dim bnInserted As Boolean
            sqlparams.Add(DL.AddParameter("@Inserted", "SqlDbType.Boolean", 1, bnInserted, ParameterDirection.Output))
            sqlparams.Add(DL.AddParameter("@SectionName", "SqlDbType.NVarChar", 50, SectionName, ParameterDirection.Input))
            sqlparams.Add(DL.AddParameter("@MenuItemID", "SqlDbType.Int", 4, CInt(MenuItemID), ParameterDirection.Input))

            Try
                DL.ExecuteStoredProc("spTest3", sqlparams)
            Catch ex As Exception
                Throw New ArgumentException(ex.Message.ToString())
            End Try
        End Sub
0
SirReadAlotAuthor Commented:
make it a function
Public Function AddSection(ByVal MenuItemID As Integer, ByVal SectionName As String) As Boolean
            'Calls the Stored Procedure which adds New Sections
            Dim DL As New DataLayer
            Dim sqlparams As New ArrayList
            Dim bnInserted As Boolean
            sqlparams.Add(DL.AddParameter("@Inserted", "SqlDbType.Boolean", 1, bnInserted, ParameterDirection.Output))
            sqlparams.Add(DL.AddParameter("@SectionName", "SqlDbType.NVarChar", 50, SectionName, ParameterDirection.Input))
            sqlparams.Add(DL.AddParameter("@MenuItemID", "SqlDbType.Int", 4, CInt(MenuItemID), ParameterDirection.Input))

            Try
                DL.ExecuteStoredProc("spTest3", sqlparams)
            Catch ex As Exception
                Throw New ArgumentException(ex.Message.ToString())
            End Try
        End Function
0
SirReadAlotAuthor Commented:
hi there, i kinda have this error "Error converting data type bit to nvarchar." and its pointing to this

Public Function AddSection(ByVal MenuItemID As Integer, ByVal SectionName As String) As Boolean
            'Calls the Stored Procedure which adds New Sections
            Dim DL As New DataLayer
            Dim sqlparams As New ArrayList
            Dim bnInserted As Boolean
            sqlparams.Add(DL.AddParameter("@Inserted", "SqlDbType.Boolean", 1, bnInserted, ParameterDirection.Output))
            sqlparams.Add(DL.AddParameter("@SectionName", "SqlDbType.NVarChar", 50, SectionName, ParameterDirection.Input))
            sqlparams.Add(DL.AddParameter("@MenuItemID", "SqlDbType.Int", 4, CInt(MenuItemID), ParameterDirection.Input))

            Try
                DL.ExecuteStoredProc("spTest3", sqlparams)
            Catch ex As Exception
                Throw New ArgumentException(ex.Message.ToString())
            End Try
        End Function

do u thin k i should do this==(ByVal MenuItemID As Integer, ByVal SectionName As String, ByVal Inserted As Boolean) As Boolean
0
KarinLoosCommented:
no, you should remember that in the stored procedure the parameter Inserted is LAST, thus you must also put it last in the code behind.

            sqlparams.Add(DL.AddParameter("@SectionName", "SqlDbType.NVarChar", 50, SectionName, ParameterDirection.Input))
            sqlparams.Add(DL.AddParameter("@MenuItemID", "SqlDbType.Int", 4, CInt(MenuItemID), ParameterDirection.Input))
           sqlparams.Add(DL.AddParameter("@Inserted", "SqlDbType.Boolean", 1, bnInserted, ParameterDirection.Output))


0
KarinLoosCommented:
Also you need to return the function itself as true or false depending on the value of the output parm
function ...

 
 
  AddAsection = bnInserted
end Function
0
SirReadAlotAuthor Commented:
sorry, this is the first time writing this sort of code,

this is what i have==
 Public Function AddSection(ByVal MenuItemID As Integer, ByVal SectionName As String) As Boolean
            'Calls the Stored Procedure which adds New Sections
            Dim DL As New DataLayer
            Dim sqlparams As New ArrayList
            Dim bnInserted As Boolean
            sqlparams.Add(DL.AddParameter("@SectionName", "SqlDbType.NVarChar", 50, SectionName, ParameterDirection.Input))
            sqlparams.Add(DL.AddParameter("@MenuItemID", "SqlDbType.Int", 4, CInt(MenuItemID), ParameterDirection.Input))
            sqlparams.Add(DL.AddParameter("@Inserted", "SqlDbType.Boolean", 1, bnInserted, ParameterDirection.Output))
            AddSection = bnInserted
            Try
                DL.ExecuteStoredProc("spTest3", sqlparams)--------------------------pointing here
            Catch ex As Exception
                Throw New ArgumentException(ex.Message.ToString())
            End Try
        End Function

but i still receive this error==Error converting data type bit to nvarchar.

thanks
0
SirReadAlotAuthor Commented:
also in the table design i haven't got Inserted as a record, then again it is an out parameter

thanks
0
KarinLoosCommented:
noticed a small typo
CREATE PROCEDURE [dbo].[spTest]
(
@MenuItemID int,
@SectionName nvarchar(50),  <-----------------------------  (the comma)
@Inserted  bit  OUTPUT
)
AS
BEGIN
  SET NOCOUNT ON
   IF EXISTS(SELECT 'TRUE' FROM Sections WHERE SectionName = @SectionName)
      BEGIN
        SET @Inserted = 1
      END
   ELSE
      BEGIN
          INSERT INTO Sections (SectionName,MenuItemID)
          VALUES (@SectionName,@MenuItemID)
          SET @Inserted = 0
      END
end
GO
0
SirReadAlotAuthor Commented:
yeah, i fix that bit


thanks.


I believe we are close and my error still pops pop

thanks
0
SirReadAlotAuthor Commented:
i can't see anything wrong with this

Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Try
            'Add sections
            Dim AddSec As New Sections
            If AddSec.AddSection(CInt(Request.QueryString("ID")), txtAdd.Text) = True Then
                lblError.Text = "Record Added"
                BindGrid()
            Else
                lblError.Text = "Record already existed"
            End If

        Catch ex As Exception
            lblError.Text = ex.Message
        End Try
        txtAdd.Text = ""
    End Sub
0
SirReadAlotAuthor Commented:
should @Inserted  bit  OUTPUT be @Inserted  boolean  OUTPUT ?
0
SirReadAlotAuthor Commented:
ignore the post above!!!!
0
SirReadAlotAuthor Commented:
anyone?
0
SirReadAlotAuthor Commented:
got this error, Error converting data type bit to nvarchar.  does anyone know how to get around it??

thanks
0
SirReadAlotAuthor Commented:
hey karin, does the sp wotk for u??
0
KarinLoosCommented:
yes i tested the sp  (in Query Analyzer) and it works, it returns the output parm 1 if record already existed and 0 if it inserted it.
0
SirReadAlotAuthor Commented:
i have changed it slightly to this and and not getting the errror
CREATE PROCEDURE [dbo].[spTest3]
(
@MenuItemID int,
@SectionName nvarchar(50)

)
AS
Set Declare @Inserted bit
BEGIN
  SET NOCOUNT ON
   IF EXISTS(SELECT 'TRUE' FROM Sections WHERE SectionName = @SectionName)
      BEGIN
        SET @Inserted = 1
      END
   ELSE
      BEGIN
          INSERT INTO Sections (SectionName,MenuItemID)
          VALUES (@SectionName,@MenuItemID)
          SET @Inserted = 0
      END
return @Inserted
end
GO
0
KarinLoosCommented:
OK now you have a return code  parameter which you need to retrieve in your code behind as opposed to an output parameter
basically same principal applies, determine the value of the return code parameter (1 - false,  0 = true, and set the function accordingly)

0
SirReadAlotAuthor Commented:
don't undrstand, so far if i addcorrectly it says record already exists
0
KarinLoosCommented:
lol at the true false 0 1 syndrome, try changing that around in the stored proc

0
SirReadAlotAuthor Commented:
i have still no joy. can't believe this!!!
CREATE PROCEDURE [dbo].[spTest3]
(
@MenuItemID int,
@SectionName nvarchar(50)

)
AS
Set Declare @Inserted bit
BEGIN
  SET NOCOUNT ON
   IF EXISTS(SELECT 'TRUE' FROM Sections WHERE SectionName = @SectionName)
      BEGIN
        SET @Inserted = 1
      END
   ELSE
      BEGIN
          INSERT INTO Sections (SectionName,MenuItemID)
          VALUES (@SectionName,@MenuItemID)
          SET @Inserted = 0
      END
return @Inserted
end
GO
0
SirReadAlotAuthor Commented:
CREATE PROCEDURE [dbo].[spTest3]
(
@MenuItemID int,
@SectionName nvarchar(50)

)
AS
Set Declare @Inserted bit
BEGIN
  SET NOCOUNT ON
   IF EXISTS(SELECT 'TRUE' FROM Sections WHERE SectionName = @SectionName)
      BEGIN
        SET @Inserted = 0
      END
   ELSE
      BEGIN
          INSERT INTO Sections (SectionName,MenuItemID)
          VALUES (@SectionName,@MenuItemID)
          SET @Inserted = 1
      END
return @Inserted
end
GO
0
KarinLoosCommented:
Set Declare @Inserted bit
 is not valid sql !
 should be
Declare @Inserted bit

when you run this procedure in query analyser :

declare @return  int
Execute @return = spTest3  1, 'SomSection'
select @return

what is the value of @return if you pass in a valid MenuItemID ?
0
SirReadAlotAuthor Commented:
value is 0
0
SirReadAlotAuthor Commented:
Declare @Inserted bit


done it but no luck
0
SirReadAlotAuthor Commented:
hi there am gonna pop out for 45min, am getting a bit pissed off. i will be back

thanks
0
KarinLoosCommented:
right so in your code behind you need to pick up the ReturnValue parameter and this will hold the value 0 if it couldnt insert, and 1 if it could.
Then simply code
If .... = 0 then
     AddSection = False
Else
     AddSection = True
End if
the variable holding the result of the  SqlParamater (with direction ReturnValue)
0
SirReadAlotAuthor Commented:
am back will try this
0
SirReadAlotAuthor Commented:
its no doubt that its not working....
Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Try
            'Add sections
            'If Not txtAdd.Text = Nothing Then
            Dim AddSec As New Sections
           If AddSec.AddSection(CInt(Request.QueryString("ID")), txtAdd.Text) = 0 Then----------i can see record added weather or not record already exist
                lblError.Text = "Record Added"
                BindGrid()
            Else
                lblError.Text = "Record already existed"(with = 1 i see Record already existed, weather it adds or not
            End If

        Catch ex As Exception
            lblError.Text = ex.Message
        End Try
        txtAdd.Text = ""
    End Sub
0
SirReadAlotAuthor Commented:
its always returning false
0
KarinLoosCommented:
have you adjusted your coding in the AddSection to retrieve the Returnvalue of the stored procedure?
0
SirReadAlotAuthor Commented:

Public Function AddSection(ByVal MenuItemID As Integer, ByVal SectionName As String) As Boolean
            'Calls the Stored Procedure which adds New Sections
            Dim DL As New DataLayer
            Dim sqlparams As New ArrayList
            Dim bnInserted As Boolean
            sqlparams.Add(DL.AddParameter("@SectionName", "SqlDbType.NVarChar", 50, SectionName, ParameterDirection.Input))
            sqlparams.Add(DL.AddParameter("@MenuItemID", "SqlDbType.Int", 4, CInt(MenuItemID), ParameterDirection.Input))
            '   sqlparams.Add(DL.AddParameter("@Inserted", "SqlDbType.Boolean", 1, bnInserted, ParameterDirection.Output))---
            AddSection = bnInserted
            Try
                AddSection = DL.ReturnSingleValue(sqlparams, "spTest3")
                Return AddSection
            Catch ex As Exception
                Throw New ArgumentException(ex.Message.ToString())
            End Try
        End Function
0
SirReadAlotAuthor Commented:
how?
0
SirReadAlotAuthor Commented:
right fixed it

Public Function AddSection(ByVal MenuItemID As Integer, ByVal SectionName As String) As Integer
            'Calls the Stored Procedure which adds New Sections
            Dim DL As New DataLayer
            Dim sqlparams As New ArrayList
            Dim bnInserted As Boolean
            Dim ad As Integer
            sqlparams.Add(DL.AddParameter("@SectionName", "SqlDbType.NVarChar", 50, SectionName, ParameterDirection.Input))
            sqlparams.Add(DL.AddParameter("@MenuItemID", "SqlDbType.Int", 4, CInt(MenuItemID), ParameterDirection.Input))
            ad = sqlparams.Add(DL.AddParameter("RETURN_VALUE", "SqlDbType.Integer", 1, bnInserted, ParameterDirection.ReturnValue))
            Try
                AddSection = DL.ExecuteStoredProc("spTest3", sqlparams, True)
                Return AddSection
            Catch ex As Exception
                Throw New ArgumentException(ex.Message.ToString())
            End Try

thanks

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.