return a value from SQL server SP back to .net

I have a SP code:

select 'nothing' from tableA where userID = '123'
if @@rowcount = 0
return 0
else
return 1

..net code:
Dim myConnection As New SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")
Dim myCommand As New SqlCommand("StoreP", myConnection)
myCommand.Connection.Open()
Returnvalue = myCommand.ExecuteNonQuery()
myCommand.Connection.Close()
Returnvalue shows -1, it doesnt show the return value from SP. how do I fix this problem? thanks
mother be
0
motherbe
8/11/2003 4:10:49 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

9 Replies
1053 Views

Similar Articles

[PageSpeed] 42
Get it on Google Play
Get it on Apple App Store

Hi,

The return value from the ExecuteNonQuery returns the number of rows affected by the query, not the return value from the stored procedure. For a SELECT statement like you're using, it always returns -1.
To get the return value, you have to add a parameter to the ADO.NET command object, and set its Direction property to ReturnValue. Something like this:
myCommand.Parameters.Add("@RetVal", SqlType.Integer).Direction = _

ParameterDirection.ReturnValue
Then you can read the value after you run the query:
Dim i as Int32 = myCommand.Parameters("@RetVal").Value
I've typed the code from memory, so it may need some tweaking.
Don
Don Kiely, MCP, MCSD
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
0
donkiely
8/11/2003 5:37:18 AM
I still have two questions

1. If I want to return 2 value from SP to asp.net, how do I do?
2. when I want to insert same value into PK twice in SP, it will give me a error message 2627, and the code break. How do I do to let SP return me an error message WITHOUT hanging the code. In other word, I am trying to let SP return the error message, but I dont want the asp.net web page to stop.
Thank you
mother be
0
motherbe
8/13/2003 8:24:17 PM
Hi,

1. If I want to return 2 value from SP to asp.net, how do I do?
Then use output parameters. You can define as many of those as you want. For them, use ParameterDirection.Output for the Direction property.
2. when I want to insert same value into PK twice in SP, it will give me a error message 2627, and the code break. How do I do to let SP return me an error message WITHOUT hanging the code. In other word, I am trying to let SP return the error message, but I dont want the asp.net web page to stop.
Probably the best way is to raise an error from the SP using the RAISERROR statement. That will generate a SqlException that you can catch and handle in your page.
Another way is to return one or more output parameters from the SP, one for an error number and another for a message. I don't like this option because it's more work and doesn't hook into the natural exception infrastructure of .NET.
Don
Don Kiely, MCP, MCSD
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
0
donkiely
8/14/2003 11:35:23 PM
Can you tell me how exactly you do it? I try parameterdirection.output it gives me an error "too many argument specified." how do I code in SP to return 2 values? thank you
mother be
0
motherbe
8/16/2003 3:15:55 PM
It sounds like you haven't added the output parameters to the stored procedure, right? You have to do that as well as add the ADO.NET code.

Post the complete sp definition and we'll help you make the changes.
Don
Don Kiely, MCP, MCSD
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
0
donkiely
8/17/2003 6:53:42 PM
the SP code is

CREATE PROCEDURE test @aaa as varchar(10) output, @bbb as varchar(10) output AS
select @aaa = '111'
select @bbb = '222'
return
GO
asp.net code is
Dim objCOmmand As New SqlCommand(strSQL, objConnection)
objConnection.Open()
objCOmmand.CommandType = CommandType.StoredProcedure
objCOmmand.Parameters.Add(New SqlParameter("@aaa", SqlDbType.VarChar))
objCOmmand.Parameters.Add("@aaa", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
objCOmmand.Parameters("@aaa").Value = "aaa"
objCOmmand.Parameters.Add(New SqlParameter("@bbb", SqlDbType.VarChar))
objCOmmand.Parameters.Add("@bbb", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
objCOmmand.Parameters("@bbb").Value = "bbb"
objCOmmand.ExecuteNonQuery()
Label1.Text = objCOmmand.Parameters("@aaa").Value
Label2.Text = objCOmmand.Parameters("@bbb").Value
objConnection.Close()
after SP I should get 111 in stead of aaa in @aaa and 222 instead of bbb in @bbb, but I get aaa and bbb in the result. how do I get the value return from SP?
mother be
0
motherbe
8/19/2003 7:44:36 PM
Your are not using the correct ParameterDirection.  ReturnValue is solely to return the value that appears after the RETURN keyword in your stored procedure.  Valid ParameterDirection values for stored procedure parameters are:

Input
Output
InputOutput
In your case, you should be using InputOutput for @aaa and @bbb since you are supplying data to the stored procedure (input) and are new receiving data back from the stored procedure (output).
Terri
Terri Morton
Engagement Manager, Neudesic

How to ask a question

0
tmorton
8/20/2003 1:34:28 PM
I try:

objCOmmand.Parameters.Add("@bbb", SqlDbType.VarChar).Direction = ParameterDirection.InputOutput
but it gives me an error:
Parameter 1: '@aaa' of type: String, the property Size has an invalid size: 0
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: Parameter 1: '@aaa' of type: String, the property Size has an invalid size: 0
mother be
0
motherbe
8/20/2003 5:46:47 PM
Since you are using a VarChar datatype, you need to specify the length.  And I don't know if it matters, but I usually take 2 lines to add a parameter and set the direction:


objCommand.Parameters.Add("@aaa", SqlDbType.VarChar, 10)
objCommand.Parameters("@aaa").Direction=ParameterDirection.InputOutput
objCommand.Parameters.Add("@bbb", SqlDbType.VarChar, 10)
objCommand.Parameters("@bbb").Direction=ParameterDirection.InputOutput

Terri
Terri Morton
Engagement Manager, Neudesic

How to ask a question

0
tmorton
8/20/2003 9:22:38 PM
Reply:

Similar Artilces:

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

Sql Server Script File(.sql) Execution by Vb.net Code
I have a problem. I m working on "light weight sql server"  project.   and i want to execute .sql file through vb.net code with the help of sqldmo library and sqlns namespace. but i donot know any method to directly execute the .sql file. i am successfully making the full script of select database of sql server.   please help me.... thanks.... Hi, You can use SQL-DMO object in your .net projects by referencing Sqldmo.dll  file which is in  C:\Program Files\Microsoft SQL Server\80\Tools\Binn  folder is default installation parameters are used. I...

Attach SQL DB to SQL Server 2005 using VB.Net
Is there any way to attach a SQL DB to SQL Server 2005 using VB.Net?  If so can you also set security? Can you rephrase your question? Attach SQL DB from which version of SQL server ? Look up books online for sp_attachdb and sp_detachdb.***********************Dinakar NethiLife is short. Enjoy it.*********************** I have a DB that was created in SQL Server 2005.  I have a website that displays data from systems all over the world.  Each system sends it's data to it's own DB.  The customer can then go to a website and see the data at real time.  What ...

creating ssis packagte for sql server to sql serer using C#.net
Hi friendsdoes any one has code or urls which can help me to create the ssis pacakage which will transfer data from sql server to sql server databaseswith some manipulationsthanks so much Lad LaxmikantPune You are probably in the wrong forum.  This forum is for questions relating to ASP.NET Dynamic Data. thanks,David...

sql anywhere 10 .NET provider & sql anywhere 8 server
sql anywhere 10 .NET provider & sql anywhere 8 server are there any major known issues other than test for my scenarios or it is not compatible at all. i was able to connect, add, delete & update records on a simple table & dataset. Env: VS 2005 .NET 2.0 & sql anywhere 10 thanks vsv This configuration should work. But we do not do significant (if any) testing of this configuration, so you should ensure you do thorough testing with your application. -- Ian McHardy (iAnywhere Solutions) Please reply only to the newsgroup. Whitepapers, TechDo...

I cannot stop Visual Studio.NET 2005 from looking for SQL Server 2005 instead of SQL 2000
I would like to know how to set SQL2000 as the default database server when running any webapplication. I have installed Visual Studio.NET and SQL Server 2000 locally in my laptop.  But anytime I want to run a webapplication I am getting the following error:System.Data.SqlClient.SqlException was unhandled by user code  Class=20  ErrorCode=-2146232060  LineNumber=0  Message="An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server doe...

SQL Server 05 & .NET .dll's: executing .dll from T-SQL
Can I manually execute a .dll stored in SQL Server 2005?Is it possible for this .dll to do anything: call a Web Service, process XML, etc?Thanks. ...

SQL Server and .NET
Hi,Our application uses SQL Server as back-end and Access forms as front-end. We are going to migrate front-end from MS Access form to ASP.Net.Our concern is,Do we need to normalize or do any changes the database to work with ASP.NET. I can say that the database is already in well normalized form.Pls help me in this.Regards,Vijay No, you will not have to do anything to the database to get it to work with ASP.NET. Your database is completely separate from the UI  code. Tryst No. .NET is completely compatiable with SQL Server as is.With all the work I've done m...

SQL query that works in SQL Server Management Studio, but doesn't on .NET 2.0 page
SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasourc...

Converting from SQL server 2005 back to sql server express edition?
Hi folks,I was working on MS sql server 2005 evalution where i have built a number of databases. However, i came to discove that the evalution version has expried before i finished my work. Now i have disinstalled the sql server 2005 and installed the Sql express edition.My concern here is how can i keep my databases so they can work with sql express edition?Thank you very much in advance. Since you dont have the option of backup and restore, what you can do is attach the databases. Find the .mdf files of your database in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data ( replace w...

SQL Server 2005 Installation problems with SQL Server Express & SQL Server 2000
Hi Guys, I have had SQL Server Express and Sql Server Management Studio Express installed on my machine for some time and recently tried to install a trial of SQL Server 2005 as well.  (Yes, I'm migrating from Visual Studio Express to Visual Studio Professional, just as in tended!) Everything went fine except that nothing seemed to be installed.  I searched in all the obvious places - both on the Start/Programs menu and on the hard-drive: nothing. A check under Add/Remove Programs showed that Sql Server 2005 Express was installed, but called SQL Server 2005. So after a number o...

A .NET Framework error occurred during execution of user-defined routine or aggregate -While creating a SQL SERVER 2005 Stored prodecure in VS.NET 2005
 Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.Pro...

This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers
When I connect SQL Server 2005 to a remote database i get the error: "This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers". Can I confirm it is because: the remote db is of SQL Server 2008? There is a soln mentioned in http://forums.asp.net/t/1324047.aspx Did anybody try this (installing 2008 express: http://www.microsoft.com/express/sql/download/)? I am just checking again, whether it will disturb my existing SQL Server installation, in any means...? Though it is mentioned that it will g...

Having trouble getting SP from sql Server 2005 to work in SQL Server 2000
I am getting an error saying incorrect syntax near fIt works in SQL Server 2005, but I cannot get it to work in SQL Server 2000  The error appears to be in the section that I marked in Bold. CREATE PROCEDURE [dbo].[pe_getReport]  -- Add the parameters for the stored procedure here    @BranchID INT,    @InvestorID INT,    @Status INT,    @QCAssigned INT,    @LoanOfficer nvarChar(40),    @FromCloseDate DateTime,    @ToCloseDate DateTime,    @Orde...

Web resources about - return a value from SQL server SP back to .net - asp.net.sql-datasource

Sample return mission - Wikipedia, the free encyclopedia
A sample return mission is a spacecraft mission with the goal of returning tangible samples from an extraterrestrial location to Earth for analysis. ...

Claims Tony Abbott plans to return as prime minister are 'fanciful'
Tony Abbott is not plotting a return to the prime ministership and suggestions to the contrary are "fanciful", according to his spokesman. ...

Lost Prediction #4 My Steve Jobs movie returns to Netflix
... out that I somehow missed 2016 Prediction #4, so let me throw something in right here. Steve Jobs: The Lost Interview will shortly return to ...

Darth Vader and Yoda will soon return in animated form
We’re not done fantasizing about what the upcoming Star Wars movies will bring us, now that Star Wars: The Force Awakens has come and gone. There ...

Pee-wee Herman madness returns in Netflix movie teaser
Childlike Pee-wee Herman greets the morning in a crazy way during a teaser trailer for the made-for-Netflix movie "Pee-wee's Big Holiday."

‘Victoria’s Secret Swim Special’ Returns To CBS In March
CBS is airing The Victoria’s Secret Swim Special for the second consecutive year. It’s set for 9 PM Wednesday, March 9. The special, shot on ...

'Neighbors 2' Trailer Is Hilarious With the Return of Zac Efron
'Neighbors 2' Trailer Is Hilarious With the Return of Zac Efron

Three Things You May Need In Order To E-File Your Tax Return
The IRS and certain states are putting safeguards in place to combat tax-related identity theft and refund fraud for the 2016 tax filing season, ...

A First Look At Micronauts' Return to Comics
... was the Bill Mantlo/Michael Golden ‘80s Marvel Comics series. Several publishers since, including Marvel themselves, have planned a return to ...

Invest in Ag Tech Education and Return Farmers to Entrepreneurship
Entrepreneurship, like so much of American history, began on our nation's farms. From the moment the first ancestors planted a seed, watered ...

Resources last updated: 1/20/2016 3:45:12 PM