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 ??
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
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 RelatedI 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
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