asked on

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

arnold

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.

adamn123

ASKER

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

adamn123

ASKER

Do you need anything else from me for more help.
The links where great but still unable to do what I want

arnold

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

adamn123

ASKER

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

adamn123

ASKER

increasing points as I need help with adding the code

Anthony Perkins

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.

Anthony Perkins

Also confirm that you are using SQL Server 2005 or 2008.

Nasir Razzaq

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

pbehin

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

rajeeshmca

Please post ur aspx.vb code and the stored procedure.... This can be done using the output parameter for the stored procedure

adamn123

ASKER

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

Nasir Razzaq

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

adamn123

ASKER

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"

Anthony Perkins

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

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adamn123

ASKER

Thank you

Anthony Perkins

It looks like I could not have read the question very well, as the author has accepted your solution.