SQL return value

I have an application that calls a sp. I am not able to capture the return value (I am forcing the code not to find any records). I am not sure at what point and where to get the value returned. What is it that causes a SQL exception? Thank you.

Code:

 

EquentialCommand.Parameters.Add(New Data.SqlClient.SqlParameter("@RetValue", Data.SqlDbType.Char, 50))
            EquentialCommand.Parameters("@RetValue").Direction = Data.ParameterDirection.Output

            

EquentialCommand.CommandType = Data.CommandType.StoredProcedure

EquentialCommand.CommandText = (
"sp_Vecellio_JobInquiry1")

EquentialCommand.Connection = EquentialConnection

mySqlDataAdapter.SelectCommand = EquentialCommand

RetValue = EquentialCommand.Parameters(
"@RetValue").Value

If RetValue <> Nothing Then

myError = True

ASPNET_MsgBox(RetValue)

Else

mySqlDataAdapter.Fill(mySqlDataTable)

EquentialCommand.Parameters.Clear()

EquentialConnection.Close()

End If

Catch ex As Exception

ErrorString = ex.ToString

ASPNET_MsgBox(
"An error has occurred connection to server and loading table: " + vbCrLf + ErrorString)

End Try

 

Query:

 

 

CREATE PROCEDURE dbo.sp_Vecellio_JobInquiry1
(
	@JobNumber INT,
	@UserID VARCHAR(10),
	@Text VARCHAR(20) = NULL,
	@DateFrom DATETIME = NULL,
	@DateTo DATETIME = NULL,
             @RetValue CHAR(50) OUTPUT
)

AS

SET NOCOUNT ON

CREATE TABLE	#AuthorityTable
		(
			busunit_from BIGINT,
			busunit_to BIGINT
		)

INSERT	#AuthorityTable
	(
		busunit_from,
		busunit_to
	)

SELECT			msmcuf,
	          			msmcut
FROM				OPENQUERY(vgsys400, 'SELECT * FROM vgisec.f0001')
WHERE			msuser = @UserID

IF @@ROWCOUNT = 0
	BEGIN
		SET @RetValue = 'You are not authorized to any selections.'
		RETURN
	END
   
0
snufse
8/7/2008 2:33:25 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

5 Replies
1130 Views

Similar Articles

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

You have to call the ExecuteNonQuery() method on the SqlCommand instance before accessing the output value from the stored proc.

Mark replies as answers if they helped you solve the problem.
0
siva_sm
8/7/2008 4:26:18 PM

 fool question but are doing return @Retvalue in your sproc ? you are setting but never sending as return


Keep it Simple!!!
DRY
YAGNI
Last Responsible Moment

If you like my answer don't forget to mark it.
0
dacanetdev
8/7/2008 4:32:49 PM

 Yeah you are not sending any command to get the return value


Keep it Simple!!!
DRY
YAGNI
Last Responsible Moment

If you like my answer don't forget to mark it.
0
dacanetdev
8/7/2008 4:34:54 PM

 I thought this was setting and sending return? Can you please explain. Thank you.

 

SET @RetValue = 'You are not authorized to any selections.'
RETURN

0
snufse
8/7/2008 5:00:25 PM

Solved the problem this way:

  

            EquentialCommand.Parameters.Add(New Data.SqlClient.SqlParameter("@RetValue", Data.SqlDbType.Char, 50)).Value = Nothing
            EquentialCommand.Parameters("@RetValue").Direction = Data.ParameterDirection.Output

            EquentialCommand.CommandType = Data.CommandType.StoredProcedure
            EquentialCommand.CommandText = ("sp_Vecellio_JobInquiry1")
            EquentialCommand.Connection = EquentialConnection
            mySqlDataAdapter.SelectCommand = EquentialCommand
            mySqlDataAdapter.Fill(mySqlDataTable)
            Dim ReturnValue As String = EquentialCommand.Parameters("@RetValue").Value.ToString
            If ReturnValue <> Nothing Then
                myError = True
                ASPNET_MsgBox(ReturnValue)
            Else
                EquentialCommand.Parameters.Clear()
                EquentialConnection.Close()
            End If
        Catch ex As Exception
            ErrorString = ex.ToString
            ASPNET_MsgBox("An error has occurred connection to server and loading table: " + vbCrLf + ErrorString)
        End Try
 
0
snufse
8/8/2008 4:02:33 PM
Reply:

Similar Artilces:

How do I check to see if a value returned from a sql datasource equals a listitem value
I'm trying to see if this listitem equals a value returned in my sql data source, but I get keep getting a error saying  'Item' is not a member of 'System.Web.UI.WebControls.SqlDataSource'.    How do I check if the employeeID returned is equal to the li.value? For Each li As ListItem In BillingEmployeeID.Items If li.Value = getEmployeewithMostOpenClient.Item("employeeID") ThenxxxxxxxEnd If NextNever make important decisions on a Monday! Try Items.FindByValue() or Items.FindByText() method. Something like this... string szValueFromDataso...

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? thanksmother be Hi, The return value from the ExecuteNonQuery returns the ...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

ADO.NET returns different colum value when compared to View results in SQL 2005 Management Studio
I have a complex view in my sql 2005 database. The view returns a column that could be null (as the result of a left outer join). The coulmn that is returned is an integer. Everything works fine if I run the view from SQL 2005 Management Studio. My column value is always null if I use ADO.NET's SqlAdapter to return a DataTable. Has anybody seen this behaviour before? Any help appreciated. Regards, Paul. Hi Paul, From you description, I understand that when you query data from the database view, the destination column in the DataSet is always null. This could be caused by a w...

Return Value from SQL
I am trying to get the following to work, here is the stored procedureSTORED PROCEDURE (@varA varchar(25),@varb varchar(25))AS IF EXISTS (SELECT column1 FROM table1 WHERE nameA=@NameA)RETURN 01IF EXISTS (SELECT column2 FROM table1 WHERE nameB=@nameB)RETURN 02ELSE     BEGIN              INSERT INTO table1 (nameA, NameB)              VALUES (@nameA, @nameB)          RETURN 03     EN...

Returning a value From SQL
On   cmd.ExecuteNonQuery(); I am getting the following error "Procedure or function usp_Question_Count has too many arguments specified."Any Ideas as to how to fix this. Oh and I will include the SP down at the bottom        // Getting the Correct Answer from the Database.        int QuiziD = Convert.ToInt32(Session["QuizID"]);        int QuestionID = Convert.ToInt32(Session["QuestionID"]);        SqlConnection oConn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\quiz.mdf;In...

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 query returns data, .NET returns #42S22Unknown column binding to dataset
I have a webservice with a common function which calls MYSQL and returns a dataset. Today I encounterd a strange error when performing a Select with a count() in it which I cant figure out. From a command line, the SQL query works fine and returns 9 rows... but VB.NET bombs with an error when filling the data adapter with the returned data. Please see my sample code below, is there an additional flag I need to call to make this work with VB? SQL Command Line: ( Works Great )mysql> Select taction, count(*) from kiosklogs where packageid='NewHi-Def-Test' group by taction;...

Passing values to sql datasource
HiI am trying to pass the asp.net membership username to the sqldatasource of a gridview as below; <asp:SqlDataSource ID="EventsSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnStr %>" SelectCommand="SELECT * FROM [MyTable] WHERE [Username] = '<%Me.User.Identity.Name%>'"></asp:SqlDataSource> It is not working. How can I do it correctly?ThanksRegards Hi There, You can add SQLParameter and set the parameter in code behind: ASPX:  <asp:SqlDataSource ID="EventsSql...

SQL SP Return Value
Hello,I develop custom module for my DNN site. When inserting a new row to a SQL table using a Stored Procedure I do not get the SCOPE_IDENTITY value which is returned by the SP.The code within SqlDataProvide.cs looks like: public override int Insert(string Title, int CityId, bool IsActive, string Url){  int i = -1;  i = SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner + ObjectQualifier + "eosp_CreateItem",  Title, CityId, IsActive, Url);  return i;}The SP : ALTER procedure eosp_CreateItem(@title NVarChar(100),@cityId Int,@isActive Bit,@url VarChar(100))asinser...

SQL A and SQL Server
Hi I heard SQL Server used to be owned by Watcom is this true? Is SQL Anywhere, therefore, similar to SQL Server? -- Regards, John Not quite true. SQL Anywhere was created by Watcom. SQLServer was created by Sybase, and Microsoft licensed the technology. So MSSQL and ASE have common roots, not SQLAnywhere. -- Paul Horan[TeamSybase] "John Kingan" <john.kingan@abibuildingdata.com> wrote in message news:40d82aa1$1@forums-1-dub... > Hi I heard SQL Server used to be owned by Watcom is this true? Is SQL > Anywhere, therefore, similar to SQL Ser...

Compiled sql CommandText going from DataSource to sql server
HiI need to know what command text goes to sql server in a compiled (parameterless) formatEx :I have a sql query : "Select  * from table where name=@name"name parameter has value = "bill gates"When I read the e.command.commandtext value in sqldatasource.inserting event, it gives the original command (with parameter @name)In datasource.inserting event, how can I get the compiled version of the command  : "Select * from table where name='bill gates'"please help me Why do you need to get the value?  What do you plan to do with it?Brian"Trust in the Lord and do what is good...

sql or my sql query
hi,in my database  i have the two tables...1)programs                                2)reservedin the programs...two fields 1)program id           2)maxseats................these details wil be entered by adminin the reserved table  two fileds ..1)PRogramid        2)statusmy requirement is .. i need the available seats..meansif any body is intersted in  any program...they wil select the program...then status becomes approved(1)..then i need the o/p as ...

SQL > My SQL
I have a problem, my asp.net page wants to connect to the sql server, but it has to be the mysql. How can I solve this problem? This is my code: <%@ page explicit="true" language="VB" debug="true" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.Odbc" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script runat="server"> sub page_load(sender as object, e as eventargs) If Not IsPostBack Then dim connect as sqlconnectio...

Web resources about - SQL return value - asp.net.sql-datasource

Desert Strike: Return to the Gulf - Wikipedia, the free encyclopedia
The game was later released on several other formats, including a much upgraded version for the Amiga home computer. The game was inspired by ...

Eric Abetz calls for Tony Abbott to return in Malcolm Turnbull cabinet reshuffle
There are calls for former prime minister Tony Abbott to return to the frontbench in a reshuffle prompted by the dumping of two ministers.

TurboTax Returns to Super Bowl to Ease Users' Self-Doubts
TurboTax is returning to the Super Bowl as part of a broad campaign "It doesn't take a genius to do your taxes" to erase consumers' self doubt ...

History Channel's American Restoration returns January 1 with Bodie Stroud
Filed under: Design/Style , TV/Movies , Videos History Channel brings back American Restoration with a new cast and a new premise. Bodie Stroud ...

Cops: "Affluenza" teen won't return to U.S. Wed.
Lawyers for Ethan and Tonya Couch filed a legal writ to halt their deportation from Mexico

Bill Cosby Donated To The Clinton Foundation. Will The Charity Return The Money?
Bill Cosby Donated To The Clinton Foundation. Will The Charity Return The Money?

NCC approves Asia Pacific Telecom return of 3G band unit
Taiwan's National Communications Commission (NCC) on December 30 approved the return of a 900MHz frequency band by Asia Pacific Telecom because ...

EXCLUSIVE: Jane Lynch Returns to TV in Fine Form With CBS' New Comedy 'Angel From Hell'
EXCLUSIVE: Jane Lynch Returns to TV in Fine Form With CBS' New Comedy 'Angel From Hell'

Could Daniel Bryan Make His Return At The Royal Rumble?
Daniel Bryan could finally make his return to WWE at the 2016 Royal Rumble event.

Florida Man Finds Wallet With $600 in Dumpster, Returns It
A central Florida man recently found a lost wallet containing $600 in cash in a dumpster, but instead of keeping it, he set out to find the wallet's ...

Resources last updated: 1/1/2016 3:44:26 AM