How Can I Get Hte Return_Value??

Mar 23, 2006

Hi all~~~

I had used sqlDataSource + SQLserver procedure

The procedure like this:

.........................

BEGIN

   SELECT * FROM xxx

   RETURN 1

END

 

er~~~How can I get the return_value with sqlDataSource ??

View 4 Replies


ADVERTISEMENT

Return_Value = 11

Jul 1, 2004

I have a stored proc that retrives a segment of data, via a select statement and also returns @@RowCount to tell me the total number of records. It goes something like this:


SELECT ProductID FROM tbl_Products
@Counter = @@RowCount

SELECT ProductID FROM tbl_Products WHERE ProductID>1 AND ProductID<=25
RETURN @Counter

The Idea is that I can populate a "Showing items 1 to 25 of 75" label on my web form. Don't worry overly about syntax issues in the above snippet, I am only trying to get across what I am doing.

When I run my SP from query analyser it works properly. I get a list of my products, and the Return_Value is set correctly.

When I run my SP from a command as follows, the return value *ALWAYS* is set to 11.

Function MyFunction() as Integer
Dim dtr As SqlDataReader
Dim oConn As SqlConnection = New SqlConnection(sConn)
Dim cmd As New SqlCommand("sproc_ProductListByCategory", oConn)

With cmd
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@Return_Value", DbType.Int32)
.Parameters("@Return_Value").Direction = ParameterDirection.ReturnValue
End With

oConn.Open()
dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

'get the total number of products
Dim iReturn As Integer = cmd.Parameters("@Return_Value").Value

While dtr.Read
'code to process my product info
End While

oConn.Close()
dtr.Close()
cmd.Dispose()

Return iReturn

End Function
If I call the cmd object twice (eg once as ExecuteNonQuery and then as ExecuteDataReader) then it works just fine. But this seems stoopid as it involves processing the SP twice!!

So, has anyone else experienced this?
Anyone know what causes this problem?
Bearing in mind I already know an inefficient one, anyone know an *efficient* workaround? ;-)

TIA,
Mark

View 3 Replies View Related

Scope_Identity With @Return_Value

Apr 27, 2006

I am currently writing an application that inserts data into a MS SQL Server 2000 DB.  I am developing the application in Microsoft Visual Web Developer 2005 EE.  So far everything has come together nicely.  However, I have run into a situation that I can't seem to figure out.I wrote a stored procedure to insert data into the DB from a Web Form.  The insert works nicely; however, one of the requirements I have is that I would like the DB to return the last row inserted into to my table.  I am using the Scope_Identity() function at the end of my stored procedure to accomplish this task:...Last few lines of Stored Procedure...@ANNEXCITYCOUNCILACTION,@CRA)RETURN SCOPE_IDENTITY()GOI have conducted several tests on the SP, and received the following output from the DB when it is run:(1 row(s) affected)(0 row(s) returned)@GID = <NULL>@RETURN_VALUE = 116Finished running [dbo].[usp_InsertProject].I would like to take the result of the @RETURN_VALUE (116), and create a variable that I can use to embed into an e-mail.  The code for the e-mail generation is working however, the value that comes through for the @RETURN_VALUE is always 0.  I have tried several different things to get this to work, but so far no luck.Here is the application code I am using to create the e-mail: Sub SendEmail()        Dim mySqlDataSource1 = SqlDataSource1.ConnectionString.ToString        Dim myConnection As New Data.SqlClient.SqlConnection(mySqlDataSource1)        Dim myCommand As New Data.SqlClient.SqlCommand("usp_InsertProject", myConnection)        myCommand.CommandType = Data.CommandType.StoredProcedure        Dim parameterGID As New Data.SqlClient.SqlParameter("@RETURN_VALUE", Data.SqlDbType.Int)        parameterGID.Direction = Data.ParameterDirection.ReturnValue        myCommand.Parameters.Add(parameterGID)        Dim reader As Data.SqlClient.SqlDataReader = myCommand.ExecuteReader()        Dim GID As Integer = CInt(parameterGID.Value)        GID.ToString()             ...E-mail code is below this, but is working, so not included ...         End SubI would like to insert the GID variable into the e-mail, but for some reason it won't work. The following error occurs when the InsertCommand is invoked:ExecuteReader requires an open and available Connection. The connection's current state is closed.

View 4 Replies View Related

Fail To Get Return_Value In Stored Procedure NullValueReference

Dec 30, 2005

I have a stored procedure that insert into a table and return a value indicating if the insert really occured.If the a insert occurs then it returns the identity of that insert.
I have slimmed down my code here, so you can see.My problem is that a NullValueReference exceptions occurs when trying to reference the returnvalue.Is it not possible to get a return value along with a recordset ?
public int RegisterCustomer(Customer customer)
{
SqlCommand myCommand = new SqlCommand("ins_RegistrerKunde", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
SqlParameter Epost = new SqlParameter("@Epost", SqlDbType.VarChar, 25);
SqlParameter Passord = new SqlParameter("@Passord", SqlDbType.VarChar, 25);
SqlParameter ReturnValue = new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4);
ReturnValue.Direction = ParameterDirection.ReturnValue;
Epost.Value = customer.Email;
Passord.Value = customer.Password;
myCommand.Parameters.Add(ReturnValue);
myCommand.Parameters.Add(Epost);
myCommand.Parameters.Add(Passord);
myConnection.Open();
SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.SingleRow);
int isRegistered = (int)myCommand.Parameters["@RETURN_VALUE"].Value;
myConnection.Close();
if (isRegistered == 1)
{
result.Read();
return Convert.ToInt32(result["Kunde_ID"]);
result.Close();
}
return Convert.ToInt32(isRegistered);
}
 
//Stored Procedure ins_RegistrerKundecreate procedure %PROC% (@Epost varchar(25), @Passord varchar(25))asbeginIF EXISTS(  SELECT Epost FROM tbl_Kunde WHERE Epost = @Epost)    RETURN 0ELSE    INSERT INTO tbl_Kunde( Epost, Passord)    VALUES (@Epost, @Passord)    SELECT @@IDENTITY AS Kunde_ID    RETURN 1endgo

View 2 Replies View Related

Capturing Return_Value From SP And Displaying Message On The Page Based On The Value

Apr 14, 2008

HelloI'm using FromView to Insert record into a DB. My SP checks for the existence and returns -1 if record already exists. I know that my SP returns -1 but how do I capture that into FormView and display error message to the user? All I get is the "Agreement was created" regardless of what SP returns.Your help is appreciated.MarkHere is the code I'm using:Protected Sub OnInserted(sender As Object, e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)        Dim retValParam As New SqlParameter("@RETURN_VALUE", SqlDbType.Int)        retValParam.Direction = ParameterDirection.ReturnValue                 Dim valParam As Integer = Convert.ToInt32(retValParam.Value)        If valParam = -1 Then          lblMessage.Text = "The Account ID is not valid"        Else        if valParam = -2 then          lblMessage.Text = "Agreement ID already exists"          else          lblMessage.Text = "Agreement was created"        End If      end if  End Suband parts of ASPX page with FromView: InsertCommand="spNew_Agreement" OnInserted="OnInserted">   <FilterParameters>  <asp:ControlParameter Name="Acct_Name" ControlID="txtAcct_Name" />  </FilterParameters>  <InsertParameters>  <asp:Parameter Name="acct_id" Type="string" />  <asp:Parameter Name="agreement_id" Type="string" />  <asp:Parameter Name="return_value" Type="int32" Direction="ReturnValue" />  </InsertParameters>

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved