What does this mean, and is it something I should be concerned about?My guess is that there is an error in your ADO.NET code. How areyou returning the @.@.ERROR to your calling program, and how is thecalling program picking it up? Can you show us the relevantpieces of your code?
|||
OK. Here are the C# function for creating the parameters from the form data and calling the procedure. This one consistantly returns '1' when, apparently, it's working fine.
void createInvoice()
{
myConnection = (SqlConnection)Session["connection"];
SqlCommand cmdUpdateInvoice;
cmdUpdateInvoice = new SqlCommand("usp_updateinvoice", myConnection);
cmdUpdateInvoice.CommandType = CommandType.StoredProcedure;
SqlParameter invoicedate = cmdUpdateInvoice.Parameters.Add("@.InvoiceDate", SqlDbType.SmallDateTime);
invoicedate.Value = DateTime.Parse(txtInvoiceDate.Text.Trim());
SqlParameter odate = cmdUpdateInvoice.Parameters.Add("@.OldDate", SqlDbType.SmallDateTime);
if (olddate != "")
odate.Value = DateTime.Parse(olddate);
else
odate.Value = DateTime.Parse("01/01/1900");
olddate = txtInvoiceDate.Text; //reset the olddate value
SqlParameter cycle = cmdUpdateInvoice.Parameters.Add("@.Cycle", SqlDbType.Int);
cycle.Value = rblCycles.SelectedIndex + 1;
SqlParameter accountnum = cmdUpdateInvoice.Parameters.Add("@.AccountNum", SqlDbType.VarChar, 50);
accountnum.Value = txtAccountNumber.Text.Trim();
SqlParameter carrierid = cmdUpdateInvoice.Parameters.Add("@.CarrierID", SqlDbType.Int);
carrierid.Value = lstCarriers.SelectedValue;
SqlParameter pastdue = cmdUpdateInvoice.Parameters.Add("@.PastDue", SqlDbType.Money);
pastdue.Value = Convert.ToDouble(txtPastDue.Text);
SqlParameter late = cmdUpdateInvoice.Parameters.Add("@.LateFee", SqlDbType.Money);
late.Value = Convert.ToDouble(txtLateFee.Text);
SqlParameter credit = cmdUpdateInvoice.Parameters.Add("@.Credit", SqlDbType.Money);
credit.Value = Convert.ToDouble(txtCredit.Text);
SqlParameter totaldue = cmdUpdateInvoice.Parameters.Add("@.TotalDue", SqlDbType.Money);
totaldue.Value = Convert.ToDouble(txtTotalDue.Text);
SqlParameter comments = cmdUpdateInvoice.Parameters.Add("@.Comments", SqlDbType.VarChar, 255);
comments.Value = txtComments.Text.Trim();
SqlParameter notes = cmdUpdateInvoice.Parameters.Add("@.Notes", SqlDbType.VarChar, 255);
notes.Value = txtNotes.Text.Trim();
SqlParameter ispaid = cmdUpdateInvoice.Parameters.Add("@.isPaid", SqlDbType.Bit);
ispaid.Value = 0;
SqlParameter visible = cmdUpdateInvoice.Parameters.Add("@.Visible", SqlDbType.Bit);
visible.Value = cbxVisible.Checked;
SqlParameter charges = cmdUpdateInvoice.Parameters.Add("@.CurrentCharges", SqlDbType.Money);
charges.Value = Convert.ToDouble(txtCurrentCharges.Text);
SqlParameter payment = cmdUpdateInvoice.Parameters.Add("@.Payment", SqlDbType.Money);
payment.Value = Convert.ToDouble(txtPayment.Text);
SqlParameter tax = cmdUpdateInvoice.Parameters.Add("@.Tax", SqlDbType.Money);
tax.Value = Convert.ToDouble(txtTax.Text);
int returnvalue = runStoredProcedure(cmdUpdateInvoice);
btnDisplaySubs_Click(btnDisplaySubs, EventArgs.Empty);
}
private int runStoredProcedure(SqlCommand command)
{
int returnvalue = -1;
try{
myConnection.Open();
}
catch{
Response.Write("Something went wrong with the Database Connection");
}
try{
returnvalue = command.ExecuteNonQuery();
}
catch(Exception e){
Response.Write(e.ToString());
}
finally{
myConnection.Close();
}
Response.Write(returnvalue.ToString());
return returnvalue;
}
Here is the procedure:
CREATE procedure usp_updateinvoice
(
@.InvoiceDate smalldatetime,
@.AccountNum varchar( 50 ),
@.CarrierId int,
@.PastDue money,
@.LateFee money,
@.Credit money,
@.CurrentCharges money,
@.TotalDue money,
@.isPaid bit,
@.Comments varchar( 255 ),
@.Notes varchar( 255 ),
@.Visible bit,
@.Cycle int,
@.Payment money,
@.Tax money,
@.OldDate smalldatetime
)
As
If Exists
(
select InvoiceDate, AccountNum
from invoice
where InvoiceDate = @.InvoiceDate and AccountNum = @.AccountNum and CarrierId = @.CarrierId and Cycle = @.Cycle
)
Begin
--runs if the record exists
Update invoice
Set PastDue = @.PastDue, LateFee = @.LateFee,
Credit = @.Credit, CurrentCharges = @.CurrentCharges, TotalDue = @.TotalDue, isPaid = @.isPaid,
Comments = @.Comments, Notes = @.Notes, Visible = @.Visible, Cycle = @.Cycle, Payment = @.Payment, InvoiceTax = @.Tax
Where InvoiceDate = @.InvoiceDate and AccountNum = @.AccountNum and CarrierId = @.CarrierId and Cycle = @.Cycle
return @.@.error
End
Else
Begin
begin transaction
Insert into invoice(InvoiceDate, AccountNum, CarrierId, PastDue, LateFee, Credit, CurrentCharges, TotalDue, isPaid, Comments,
Notes, Visible, Cycle, Payment, InvoiceTax)
Values(@.InvoiceDate, @.AccountNum, @.CarrierId, @.PastDue, @.LateFee, @.Credit, @.CurrentCharges, @.TotalDue, @.isPaid, @.Comments,
@.Notes, @.Visible, @.Cycle, @.Payment, @.Tax)
exec dbo.CopySubaccountsToNewInvoice @.InvoiceDate, @.OldDate, @.CarrierId, @.AccountNum, @.Cycle
commit transaction
return @.@.error
End
GO
What do I do with this?
|||You are not extracting the RETURN value correctly. ExecuteNonQuery returns the number of rows affected by the SQLstatement, not the RETURN value of the stored procedure.
You need to add a Parameter with ParameterDirection = ReturnValue tocapture the RETURN from the stored procedure. See this article byJames Crowley for an example:Using ADO.NET with SQL Server - Stored Procedures and other queries with parameter
Alternately, if it were mine to write, I'd add an OUTPUT parameter andSELECT @.@.ERROR into that parameter instead of doing a RETURN @.@.ERROR.
|||>ExecuteNonQuery returns the number of rows affected by the SQL statement, not the RETURN value of the stored procedure.<
Ah! That's exactly what I needed to know. Thanks
|||EDIT: I figured out that the 18 return value is caused by another stored procedure that is called when a new record is created . . . it updates related tables. Odd thing is, it's returning 2 for each row updated. And when I call the procedures for updating those tables individually, it returns 2 as well. It could just be some other minor thing I've not caught.
Ok, I was getting a 1 as a return value. Now I'm getting return values like 2 and 18 from this procedure. There are not even 18 rows in the entire table. What is going on?
|||Personally I think you should ignore the value being returned byExecuteNonQuery. It has no relevance to your application. Running the stored procedure from Query Analyzer and looking at theMessages tab will likely give you insight as to why you are seeingvalues like 2 and 18 being returned.
No comments:
Post a Comment