CODEWITHSUNDEEP
c#sql-server
vpv
vpv

Reputation: 938

SQL Update command not working from c# code behind

I know this question has been asked several times and I read those with no luck :( So I am asking it again with my code.

I have created a stored procedure to update a database table.

Stored procedure:

CREATE PROCEDURE usp_PettyCash_EditExpenseInfo 
(
    @ExpenseID bigint,
    @ExpenseName varchar(100),
    @SAPCode varchar(50),
    @MaxLimit decimal,
    @ExpenseType varchar(50)
)
AS
BEGIN 
    UPDATE t_ExpenseInfo 
    SET ExpenseName = @ExpenseName, 
        SAPCode = @SAPCode,       
        MaxLimit = @MaxLimit, 
        ExpenseType = @ExpenseType 
    WHERE 
        ExpenseID = @ExpenseID
END 
GO

But when I call this from code behind with below code, it gives the exception similar to

Error in converting from nvarchar to bigint

Code behind:

oOleDbCommand.Parameters.AddWithValue("@ExpenseName", oInputExpense.ExpenseName.ToString());
oOleDbCommand.Parameters.AddWithValue("@SAPCode", oInputExpense.SAPCode.ToString());
oOleDbCommand.Parameters.AddWithValue("@MaxLimit", Convert.ToDecimal(oInputExpense.MaxLimit));
oOleDbCommand.Parameters.AddWithValue("@ExpenseType", oInputExpense.ExpenseType.ToString());
oOleDbCommand.Parameters.AddWithValue("@ExpenseID", Convert.ToDouble(oInputExpense.ExpenseID.ToString()));

I also tried this:

oOleDbCommand.CommandText = "UPDATE t_ExpenseInfo SET ExpenseName='" + oInputExpense.ExpenseName.ToString() + "', SAPCode='" + oInputExpense.SAPCode.ToString() + "', MaxLimit=" + oInputExpense.MaxLimit + ", ExpenseType='" + oInputExpense.ExpenseType.ToString() + "' WHERE ExpenseID=" + oInputExpense.ExpenseID + "";

this DIRECT command runs OK from SQL Query Analyzer from from code behind this also give an "Access Violation" error.

I am really confused about what to do? Please help !

Upvotes: 1

Views: 1430

Answers (4)

vpv
vpv

Reputation: 938

Currently I am using the 1st part. But I have checked both are working :)

OleDbCommand.Parameters.Add("@ExpenseID", OleDbType.BigInt).Value = Convert.ToInt64(oInputExpense.ExpenseID);
OleDbCommand.Parameters.Add("@ExpenseName", OleDbType.VarChar).Value = Convert.ToString(oInputExpense.ExpenseName);
OleDbCommand.Parameters.Add("@SAPCode", OleDbType.VarChar).Value = Convert.ToString(oInputExpense.SAPCode);
OleDbCommand.Parameters.Add("@MaxLimit", OleDbType.Decimal, 2).Value = Convert.ToDecimal(oInputExpense.MaxLimit);
OleDbCommand.Parameters.Add("@ExpenseType", OleDbType.VarChar).Value = Convert.ToString(oInputExpense.ExpenseType);

//OleDbCommand.Parameters.AddWithValue("@ExpenseID", Convert.ToInt64(oInputExpense.ExpenseID.ToString()));
//OleDbCommand.Parameters.AddWithValue("@ExpenseName", oInputExpense.ExpenseName.ToString());
//OleDbCommand.Parameters.AddWithValue("@SAPCode", oInputExpense.SAPCode.ToString());
//OleDbCommand.Parameters.AddWithValue("@MaxLimit", Convert.ToDecimal(oInputExpense.MaxLimit));
//OleDbCommand.Parameters.AddWithValue("@ExpenseType", oInputExpense.ExpenseType.ToString());

Upvotes: 0

xurc
xurc

Reputation: 99

I collect the query in SQL Profiler and find out that OleDB doesn't care about the name of parameter, but it cares about order of given parameters. In your case ExpenseName is given as first parameter and it tried to convert it to ExpenseId (first parameter in your stored procedure).

Here is query from SQL Profiler:

exec usp_PettyCash_EditExpenseInfo N'ExpenseName',N'SAP',21.209999084472656,N'a',1

Try to change order of given parameters. I hope it help.

Upvotes: 1

mslliviu
mslliviu

Reputation: 1138

Try Convert.ToInt64(...) instead of Convert.ToDouble(oInputExpense.ExpenseID.ToString())

Upvotes: 1

marc_s
marc_s

Reputation: 754438

is there any particular reason why you're using OleDbCommand instead of the "native" SQL Server SqlCommand ??

I would try this:

using(SqlConnection conn = new SqlConnection("(add your connection string here)"))
using(SqlCommand cmd = new SqlCommand("dbo.usp_PettyCash_EditExpenseInfo", conn))
{
   cmd.CommandType = CommandType.StoredProcedure;

   // add parameters with defined type!
   cmd.Parameters.Add("@ExpenseID", SqlDbType.BigInt).Value = .....;
   cmd.Parameters.Add("@ExpenseName", SqlDbType.VarChar, 100).Value = ".....";
   cmd.Parameters.Add("@SAPCode", SqlDbType.VarChar, 50).Value = ".....";
   cmd.Parameters.Add("@MaxLimit", SqlDbType.Decimal, 15, 2).Value = 100.00;
   cmd.Parameters.Add("@ExpenseType", SqlDbType.VarChar, 50).Value = "......";

   // open connection, call stored procedure, close connection
   conn.Open();
   cmd.ExecuteNonQuery();
   conn.Close();
}

Basically, I prefer using the native SqlConnection/SqlCommand over the old, legacy OleDb stuff, and I also prefer to explicitly specify what type my parameters are - don't rely on ADO.NET or some other part figuring it out automagically - when it has to guess, it can get it wrong, and error like the one you see might occur. If you define it yourself, you're in control!

Upvotes: 3

Related Questions