return value from stored procedure with vb.net

I have a stored procedure bellow
I want to return the value @errormsg to my asp.net with vb back

in my vb code I have this

        SQLCmd.Parameters.Add("@errormsg")
        SQLCmd.Parameters("@errormsg").Direction = ParameterDirection.ReturnValue

        If SQLCmd.Parameters("@errormsg").Value = 1 Then
            Labelerror.Text = "There was an error inputing your information.  Please contract PiiComm"
        Else
            Labelerror.Text = "Thank you, your data as been submitted."
        End If

what am I doing wrong
new at this
reson I am doing it this way is because I am many statments in the one procedure .
so after each insert, update etc it has
IF @@ROWCOUNT = 0
select @errormsg as errormsg

so if one fails then I know
declare 
@errormsg varchar(50)
set @errormsg = '1'




     DECLARE @MaxID1 INT 
     DECLARE @Nextid1 INT 
     DECLARE @Notify INT 
     SET @MaxID1 = (SELECT MAX(Next_uno) FROM Next_Key where table_name = 'Notify') 
     DECLARE @NextRecord1 VARCHAR(50) 
     SET @Notify = @MaxID1 
     SELECT @Notify
	 SET @Nextid1 =  (@MaxID1 + 1) 
     UPDATE Next_Key SET Next_uno = @Nextid1 where table_name = 'Notify'

IF @@ROWCOUNT = 0
select @errormsg as errormsg

Open in new window

adamn123Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

arnoldCommented:
Here is an article you may find helpful dealing with error detection in SQL SP.
http://technet.microsoft.com/en-us/library/ms187316.aspx
http://www.sommarskog.se/error-handling-II.html

http://www.sql-server-performance.com/articles/dev/stored_procedure_returns_p1.aspx

You have @errormsg set to 1.  What are you getting?

There is an incomplete picture of your setup/interaction between the vbscript and the sql sp.
0
adamn123Author Commented:
I am getting error

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

What I want to do if the statment does not work then it will make @errormsg a 1
return the one to my vb.net code and the if statement will then make a message to the user
0
adamn123Author Commented:
Do you need anything else from me for more help.
The links where great but still unable to do what I want
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

arnoldCommented:
The problem is that you are defining/declaring @errormsg within the stored procedure making it local, you can not pass it back.  An option is that you can pass a variable that is then can be modified within and modified which then can be detected.

There is an @@error  that is set by the sql server and is a global variable check it out.
Alternatively/Additionally use the return option.
http://www.sqlteam.com/article/stored-procedures-returning-data 
0
adamn123Author Commented:
Are you able to help me add the required code to what I have attached?

if there is an error then
label.text  "There is an error"
else
label.text  "all is well"
end if

Protected Function Updatecontract(ByVal reminder As Object, ByVal reminder1 As Object, ByVal reminder4 As Object) As Integer
        OpenDatabaseConnection()
        Dim SQLCmd As New SqlClient.SqlCommand
        SQLCmd.CommandText = "Updatecontract" ' Stored Procedure to Call
        SQLCmd.CommandType = CommandType.StoredProcedure 'Setup Command Type
        SQLCmd.Connection = SQLCon 'Active Connection 
        SQLCmd.Parameters.AddWithValue("reminder", reminder)
        SQLCmd.Parameters.AddWithValue("reminder1", reminder1)
        SQLCmd.Parameters.AddWithValue("reminder4", reminder4)
        Dim remind As Object = SQLCmd.ExecuteScalar()

        CloseDatabaseConnection()
        ' Return Integer.Parse(rowsAffected.ToString())
    End Function

Open in new window

0
adamn123Author Commented:
increasing points as I need help with adding the code
0
Anthony PerkinsCommented:
First of all since @errormsg is a character parameter you cannot define it as ParameterDirection.ReturnValue.   You can only use ReturnValue if it is a numeric data type, typically an integer is used.

Second please post your Stored Procedure Updatecontract as it stands now and what values you wish to pass in and return.
0
Anthony PerkinsCommented:
Also confirm that you are using SQL Server 2005 or 2008.
0
CodeCruiserCommented:
By the way, your .NET code will generate an exception if there is an error in the stored procedure. So you can handle the error like this

Change
Dim remind As Object = SQLCmd.ExecuteScalar()

to

Try
SQLCmd.ExecuteNonQuery()
Catch ex As Exception
   lblError.Text = ex.Message
End Try
0
pbehinCommented:
hi
u can define @errormsg  as parameter in store procedure and type of it as OUTPUT

create spname @errormsg varchar(50) output

and in code set Direction of parameter by System.Data.ParameterDirection.Output

then after execute u can find return value in parameter.value in code
0
rajeeshmcaCommented:
Please post ur aspx.vb code and the stored procedure.... This can be done using the output parameter for the stored procedure
0
adamn123Author Commented:
sorry for the delay I been away,

All I want to do is if there is an error and I need to know

currently after running the procedure it goes to a label that has Thank you ....
I need to know if there was an error so I can make the label on the asp.net page change saying there was an error

if error
label.text = say this
else
label.text = say this


puting
IF @@ROWCOUNT > 0
select @errormsg

I am not sure if that is the correct way of doing it.
I am sure there is a way to pass back if there was an error or not.

I am new at all this Thanks


asp.net

        OpenDatabaseConnection()
        Dim SQLCmd As New SqlClient.SqlCommand
        SQLCmd.CommandText = "Updatecontract" ' Stored Procedure to Call
        SQLCmd.CommandType = CommandType.StoredProcedure 'Setup Command Type
        SQLCmd.Connection = SQLCon 'Active Connection 
        SQLCmd.Parameters.AddWithValue("Client_uno", Client_uno)
        SQLCmd.Parameters.AddWithValue("Vendor", Vendor)
        SQLCmd.Parameters.AddWithValue("contract_num", contract_num)
        SQLCmd.Parameters.AddWithValue("manufacturer_part_num", manufacturer_part_num)
        SQLCmd.Parameters.AddWithValue("manufacturer_description", manufacturer_description)
        SQLCmd.Parameters.AddWithValue("quantity", quantity)
        SQLCmd.Parameters.AddWithValue("term", term)
        SQLCmd.Parameters.AddWithValue("part_num", part_num)
        SQLCmd.Parameters.AddWithValue("part_description", part_description)
        SQLCmd.Parameters.AddWithValue("startdate", startdate)
        SQLCmd.Parameters.AddWithValue("enddate", enddate)
        SQLCmd.Parameters.AddWithValue("reminder", reminder)
        SQLCmd.Parameters.AddWithValue("reminder1", reminder1)
        SQLCmd.Parameters.AddWithValue("reminder4", reminder4)
        SQLCmd.Parameters.AddWithValue("fileloc", fileloc)
        SQLCmd.Parameters.AddWithValue("manucature_contact", manucature_contact)
        Dim errormsg As Object = SQLCmd.ExecuteScalar()


stored procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Updatecontract] 
( 
     @Client_uno int, 
     @Vendor int, 
     @contract_num VARCHAR(50),
     @manufacturer_part_num VARCHAR(100),
	 @manufacturer_description VARCHAR(500),
	 @quantity int, 
	 @term int, 
	 @part_num VARCHAR(100),
	 @part_description VARCHAR(500),
	 @startdate datetime,
	 @enddate datetime,
	-- @serial VARCHAR(8000),
     @reminder VARCHAR(50),
     @reminder1 VARCHAR(50),
     @reminder4 VARCHAR(50),
	 @fileloc VARCHAR(200),
	 @manucature_contact int
) 
AS 
BEGIN 

declare 
@errormsg varchar(50)
set @errormsg = '1'




     DECLARE @MaxID1 INT 
     DECLARE @Nextid1 INT 
     DECLARE @Notify INT 
     SET @MaxID1 = (SELECT MAX(Next_uno) FROM Next_Key where table_name = 'Notify') 
     DECLARE @NextRecord1 VARCHAR(50) 
     SET @Notify = @MaxID1 
     SELECT @Notify
	 SET @Nextid1 =  (@MaxID1 + 1) 
     UPDATE Next_Key SET Next_uno = @Nextid1 where table_name = 'Notify'

IF @@ROWCOUNT > 0
select @errormsg 


     DECLARE @MaxID INT 
     DECLARE @Nextid INT 
     SET @MaxID = (SELECT MAX(Next_uno) FROM Next_Key where table_name = 'Contract') 
	 SET @Nextid =  (@MaxID + 1) 
     UPDATE Next_Key SET Next_uno = @Nextid where table_name = 'Contract'


INSERT INTO Contract( Client_uno, Vendor, contract_num, manufacturer_part_num, manufacturer_description, quantity,  term,  part_num, part_description, startdate, enddate,contract_uno, Notify, manucature_contact)
VALUES ( @Client_uno, @Vendor, @contract_num, @manufacturer_part_num, @manufacturer_description, @quantity,  @term,  @part_num, @part_description, @startdate, @enddate, @MaxID, @Notify, @manucature_contact)

IF @@ROWCOUNT > 0
select @errorms


     INSERT INTO Notify(reminder_uno, reminder, reminder1, reminder4, send_reminders) 
     VALUES ( @Notify, @reminder, @reminder1, @reminder4, 1) 

IF @@ROWCOUNT > 0
select @errorms

declare  
@Client_uno_folder varchar(50)
set  @Client_uno_folder = @Client_uno

DECLARE @SQLda varchar(2000)
SET @SQLda = 'IF OBJECT_ID(''temp'  + @Client_uno_folder + ''') IS NOT NULL DROP TABLE temp' + @Client_uno_folder
EXEC (@SQLda)

IF @@ROWCOUNT > 0
select @errorms

DECLARE @SQLt varchar(2000)
SET @SQLt = 'CREATE TABLE temp'  + @Client_uno_folder +  ' (serial varchar(50))'
EXEC (@SQLt)

IF @@ROWCOUNT > 0
select @errorms

--Bulk insert into Test 
DECLARE @SQL varchar(2000)
SET @SQL = 'BULK INSERT temp'  + @Client_uno_folder +  ' FROM '''  + @fileloc +  ''' WITH (FIELDTERMINATOR = ''\n'')'
EXEC (@SQL)

IF @@ROWCOUNT > 0
select @errorms

DECLARE @SQLi varchar(2000)
SET @SQLi = '
INSERT INTO Serials (serial, Client_uno, contract_num, filelocpath) 
SELECT serial,  ' + @Client_uno_folder + ', ' + @contract_num + ', ''' + @fileloc + '''     FROM  temp'  + @Client_uno_folder
EXEC (@SQLi)

IF @@ROWCOUNT > 0
select @errorms

DECLARE @SQLdb varchar(2000)
SET @SQLdb = 'DROP TABLE temp'  + @Client_uno_folder
EXEC (@SQLdb)

IF @@ROWCOUNT > 0
select @errorms

select @@error
END

Open in new window

0
CodeCruiserCommented:
You dont need to do anything on the stored procedure side. If there is an error, an exception will be thrown.

Try
   'execute stored procedure
Catch Ex as Exception
   lblError.Text = Ex.Message
End Try
0
adamn123Author Commented:
CodeCruiser I think we are on to something

stupid question
how do I do the if within a try

if ex comes back with some then
lblError.Text = Ex.Message
else
lblError.Text =  " all is good"
0
Anthony PerkinsCommented:
>>If there is an error, an exception will be thrown.<<
But that is just the point, look at the Stored Procedure, there is no error triggered.  In order for the user to trigger an error they need to do a RAISERROR() in the Stored Procedure.  Once they ahve done that, then yes it is as simple as checking the Exception in .NET.  Also, there is no longer any need to use ExecuteScalar and is should be replaced by the  ExecuteNonQuery method.
0
CodeCruiserCommented:
@adamn123
Try
'execute stored procedure
lblError.Text = "all is good"
Catch Ex As Exception
lblError.Text = "Error: " & ex.Message
End Try


@acperkins
I may have not followed the conversation properly but do we need to use raiseerror to receive an error? I though we are discussing catching the usual db errors (which would be raised even if you dont want them).
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
adamn123Author Commented:
Thank you
0
Anthony PerkinsCommented:
It looks like I could not have read the question very well, as the author has accepted your solution.
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
.NET Programming

From novice to tech pro — start learning today.