1

I'm having a strange issue with entering data into a SQL Server 2008 database, the error tells me that

System.Data.SqlClient.SqlException was unhandled HResult=-2146232060 Message=The variable name '@comments' has already been declared. Variable names must be unique within a query batch or stored procedure. Must declare the scalar variable "@employeeId".

Problem is... I did declare both of them, am I missing something?

public bool Addfix(DateTime receiveDate, int clientId, int modelID, string problem, string comment, int employeeId, float priceOffer, float price)
{
        _cmd.CommandText ="INSERT INTO fixes(receiveDate, clientId, modelID, problem, comments, employeeId, priceOffer, price, returned) VALUES (@receiveDate, @clientId, @modelID, @problem, @comments, @employeeId, @priceOffer,@price,@returned);";
        _cmd.Parameters.Add("@receiveDate", SqlDbType.Date).Value = receiveDate;
        _cmd.Parameters.AddWithValue("@clientId", clientId);
        _cmd.Parameters.AddWithValue("@modelID", modelID);
        _cmd.Parameters.AddWithValue("@problem", problem);
        _cmd.Parameters.AddWithValue("@comments", comment);
        _cmd.Parameters.AddWithValue("@employeeId", employeeId);
        _cmd.Parameters.AddWithValue("@priceOffer", priceOffer);
        _cmd.Parameters.AddWithValue("@price", price);
        _cmd.Parameters.AddWithValue("@returned ", 0);

        _con.Open(); 
        _cmd.ExecuteNonQuery(); 
        _con.Close();

        return true; 
    }
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Iakovl
  • 1,013
  • 2
  • 13
  • 20
  • 1
    You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... – marc_s Jan 17 '15 at 12:22
  • 3
    Does it give that exception the first time, or maybe the second time you call this method? Apart from the `AddWithValue` issues, you need to clear the parameters if you want to reuse your command: `_cmd.Parameters.Clear()`. See also [Should I call Parameters.Clear when reusing a SqlCommand with a transation?](http://stackoverflow.com/questions/14374541/should-i-call-parameters-clear-when-reusing-a-sqlcommand-with-a-transation). – CodeCaster Jan 17 '15 at 12:22
  • @CodeCaster the first time i try it... testing the function to see if it works and get the error – Iakovl Jan 17 '15 at 12:24
  • 1
    @CodeCaster the Clear() solved the issue, will add it to all my other functions to avoid further problems – Iakovl Jan 17 '15 at 12:30
  • Don't use clobal sql command, sql connection ... Also for this article I don't see anywhere prove that this is true. – mybirthname Jan 17 '15 at 12:31

1 Answers1

1

Looking at your function definition, it seems your sqlcommand/sqlconnectin objects are either class level or global. In your case it seems you used the _cmd object which had @comments parameter and did not get cleared before execution of this method. Always discourage to use global sqlcommand and sqlconnection objects, but for some unavoidable reason you need to use global sqlcommand, its always good practice to clear the parameters of command objects, e.g. sqlcommandObject.Parameters.Clear().

user4455840
  • 11
  • 1