473,852 Members | 1,868 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Stored procedure return value

Hello -

I need a good example of how to take a return value from a stored procedure
and use it in vb code. I have an app that searches a database by city and
state. If a user makes a typo, the info sent to the stored procedure makes
the stored procedure fail. I want to be able to display a label indicating
they should try again, if this is the case. I can't use row count, because
the stored procedure doesn't get that far.

What exactly do I put in my stored procedure and what do I put in my vb?

Any help will be greatly appreciated!

--
Sandy
Nov 21 '05 #1
5 6862
What kind of typo would cause the SP to fail in this way? and how does the
SP fail? (just curious)

"Sandy" <Sa***@discussi ons.microsoft.c om> wrote in message
news:81******** *************** ***********@mic rosoft.com...
Hello -

I need a good example of how to take a return value from a stored
procedure
and use it in vb code. I have an app that searches a database by city and
state. If a user makes a typo, the info sent to the stored procedure
makes
the stored procedure fail. I want to be able to display a label
indicating
they should try again, if this is the case. I can't use row count,
because
the stored procedure doesn't get that far.

What exactly do I put in my stored procedure and what do I put in my vb?

Any help will be greatly appreciated!

--
Sandy

Nov 21 '05 #2
Hi Sandy ! :O)

Assuming you're using SQL Server you can declare an output parameter like
this (not tested.. and my t-sql is getting rusty) :
--
create procedure my_proc(
@id int,
@return_value varchar(200) output,
)
as
begin

select f1, f2, fn from mytable where id = @id
set @return_value = 'ok'

end
--

you would call the procedure like this (not tested either) :
'***
Dim cmd As New SqlCommand
cmd.CommandText = "sp_test"
cmd.CommandType = CommandType.Sto redProcedure
cmd.Connection = New SqlConnection(" my_connection_s tring")

cmd.Parameters. Add("@id", SqlDbType.Int,
ParameterDirect ion.Input).Valu e = 1
cmd.Parameters. Add("@return_va lue", SqlDbType.Varch ar, 200, Nothing,
ParameterDirect ion.Output)

Dim ds As New DataSet
Dim da As New SqlDataAdapter( cmd)
da.Fill(ds)

Console.WriteLi ne(cmd.Paramete rs("@return_val ue").ToString() )
'***

--
Best Regards
Yanick

"Sandy" <Sa***@discussi ons.microsoft.c om> a écrit dans le message de
news:81******** *************** ***********@mic rosoft.com...
Hello -

I need a good example of how to take a return value from a stored procedure and use it in vb code. I have an app that searches a database by city and
state. If a user makes a typo, the info sent to the stored procedure makes the stored procedure fail. I want to be able to display a label indicating they should try again, if this is the case. I can't use row count, because the stored procedure doesn't get that far.

What exactly do I put in my stored procedure and what do I put in my vb?

Any help will be greatly appreciated!

--
Sandy

Nov 21 '05 #3
Hi Robin:

Actually anything put into City that is not in my database will cause the SP
not to work. The error which I get in my Catch ex is Source=System.D ata,
Message="Cannot find Table 0."

When I debug, the actual error is an IndexOutOfRange Exception. I could
catch this error in a Catch ExORE IndexOutOfRange Exception, but I think it's
better to handle it at the Sql Server level. Don't know if this is correct
thinking or not.
--
Sandy
"Robin Tucker" wrote:
What kind of typo would cause the SP to fail in this way? and how does the
SP fail? (just curious)

"Sandy" <Sa***@discussi ons.microsoft.c om> wrote in message
news:81******** *************** ***********@mic rosoft.com...
Hello -

I need a good example of how to take a return value from a stored
procedure
and use it in vb code. I have an app that searches a database by city and
state. If a user makes a typo, the info sent to the stored procedure
makes
the stored procedure fail. I want to be able to display a label
indicating
they should try again, if this is the case. I can't use row count,
because
the stored procedure doesn't get that far.

What exactly do I put in my stored procedure and what do I put in my vb?

Any help will be greatly appreciated!

--
Sandy


Nov 21 '05 #4
Hi Zoury -

Thanks for your response. I tried your sproc and couldn't get it to work.

The return value I really want is the one automatically returned by Sql
Server. It is my understanding that Sql Server always returns a value; if
the sproc executes successfully, it is zero. Otherwise, I believe it returns
a -1.

For simplicity, say we have the following:

Create procedure spCityState
(
@City char(50),
@State char(2)
)
AS
Select @City, @State From Zips Where City = @City and State = @State

When the procedure hits the @City when a typo is put in, the procedure can
obviously no longer finish. I would like to put language in the SP so it
stops working at that point and returns the value to me. (I'm thinking along
the lines of an If Not Exists(Select ......) RETURN before the actual select
statement.

I then would like to use the value from RETURN in my VB code, i.e. if the
value returned is < something then something is displayed in a label to tell
the user there was a typo made and to try again.

I need to know how to designate that RETURN value in the stored procedure
and then access it from my VB code.

Again, thanks for your response. Any suggestions?
--
Sandy
"Zoury" wrote:
Hi Sandy ! :O)

Assuming you're using SQL Server you can declare an output parameter like
this (not tested.. and my t-sql is getting rusty) :
--
create procedure my_proc(
@id int,
@return_value varchar(200) output,
)
as
begin

select f1, f2, fn from mytable where id = @id
set @return_value = 'ok'

end
--

you would call the procedure like this (not tested either) :
'***
Dim cmd As New SqlCommand
cmd.CommandText = "sp_test"
cmd.CommandType = CommandType.Sto redProcedure
cmd.Connection = New SqlConnection(" my_connection_s tring")

cmd.Parameters. Add("@id", SqlDbType.Int,
ParameterDirect ion.Input).Valu e = 1
cmd.Parameters. Add("@return_va lue", SqlDbType.Varch ar, 200, Nothing,
ParameterDirect ion.Output)

Dim ds As New DataSet
Dim da As New SqlDataAdapter( cmd)
da.Fill(ds)

Console.WriteLi ne(cmd.Paramete rs("@return_val ue").ToString() )
'***

--
Best Regards
Yanick

"Sandy" <Sa***@discussi ons.microsoft.c om> a écrit dans le message de
news:81******** *************** ***********@mic rosoft.com...
Hello -

I need a good example of how to take a return value from a stored

procedure
and use it in vb code. I have an app that searches a database by city and
state. If a user makes a typo, the info sent to the stored procedure

makes
the stored procedure fail. I want to be able to display a label

indicating
they should try again, if this is the case. I can't use row count,

because
the stored procedure doesn't get that far.

What exactly do I put in my stored procedure and what do I put in my vb?

Any help will be greatly appreciated!

--
Sandy


Nov 21 '05 #5
> The return value I really want is the one automatically returned by Sql
Server. It is my understanding that Sql Server always returns a value; if
the sproc executes successfully, it is zero. Otherwise, I believe it returns a -1.


ok. i've never used it (and i can't test it for now) but I think you need
something like this :
'**
cmd.Parameters. Add("@return_va lue", SqlDbType.Int). Direction =
ParameterDirect ion.ReturnValue
'**

after the call, you should be able to read the value using something like
this :
'**
Console.WriteLi ne(cmd.Paramete rs("@return_val ue").Value)
'**

Note that if you use SqlCommand.Exec uteReader() to query your data, you
won't be able to read the value of any Output, InputOut and ReturnValue
parameters until you call the Close() (or Dispose()) method of the reader.

--
Best Regards
Yanick
Nov 21 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
11356
by: C Kirby | last post by:
In SQL Server 2000, I've got a rather lengthy stored procedure, which creates a lot of temporary tables as it processes down through a few sets of data. When testing it through Query Analyzer, it runs fine (a bit slow though). But when I try to run it through the ade, it doesn't do anything. It runs through the procedure in milliseconds but doesn't seem to ever actually start it. If I change the calling code in the ade VBA to refer to...
0
6709
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft Visual Basic .NET version of this article, see 308049. For a Microsoft Visual C++ .NET version of this article, see 310071. For a Microsoft Visual J# .NET version of this article, see 320627. This article refers to the following Microsoft .NET...
2
5469
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
7
9726
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get notification that it has completed. The main reason for this being to stop the user thinking the application has frozen when in fact it is just waiting for a long SP to complete. Another reason for doing it like this is that I also have had a problem in the past where the SP takes longer than the...
4
3998
by: nishi57 | last post by:
I hope I can get some help regarding this issue, which has been going on for a while. I have a desktop user who is having problem running "Stored Procedures". The DB2 Connect application works fine but when he runs the stored procedure, he gets the following error message. "SYSPROC".CSGCSB54 - Run started. Data returned in result sets is limited to the first 100 rows. Data returned in result set columns is limited to the first 20...
4
4367
by: scparker | last post by:
Hello, We have a stored procedure that does a basic insert of values. I am then able to retrieve the ID number created for this new record. We are currently using ASP.NET 2.0 and use N-Tier Architecture. The Stored Procedures are used through TableAdaptors, which in turn are used by Class Files. I wish to be able to return this new ID value using the Stored
9
4150
by: fniles | last post by:
I am using VB.NET 2003 and SQL2000 database. I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1 parameter (varchar(10)) and returns the identity column value from that table. When calling the stored procedure from VB.NET, in the CommandText, can I just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with "INSERT_INTO_MYTABLE" then do the following : OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10)...
9
2476
by: Frawls | last post by:
Hi I Am am having problems with a stored Procedure that i wrote. Basically whats happening is that the Stored procedure Runs fine when i EXECUTE it in SQL Query analyzer. But when i debug through the application in Visual Studio .NET 2003 the application an exception when it executes the query.
12
5147
by: Dooza | last post by:
I have a stored procedure that takes a number of inputs, does a bulk insert, and then outputs a recordset. When I run the stored procedure in Server Management Studio I also get a return value from the stored procedure which is an INT. I want to access this return value on my ASP/VBScript page, but do not know how to access it. Here is my code so far:
0
11020
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10670
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10735
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9506
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7073
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5934
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4549
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4143
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3180
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.