0

I am using Visual Studio 2013 in C#. I am trying to use SqlCommand to insert data similar to stored procedures. The code below works if I substitute the values with real data instead of variables but when I try to use variables I get this error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in EntityFramework.SqlServer.dll
Additional information: Must declare the scalar variable "@cashmemo".

This is my code:

dbContext.Database.ExecuteSqlCommand("INSERT INTO CashAccounts(CashDate,CashMemo,CashDebit,CashCredit)VALUES(Getdate(),@cashmemo,@cashcredit,@cashdebit)");

SqlCommand sqlCom = new SqlCommand();
sqlCom.Parameters.Add("@cashmemo", SqlDbType.NVarChar);
sqlCom.Parameters.Add("@cashcredit", SqlDbType.Decimal);
sqlCom.Parameters.Add("@cashdebit", SqlDbType.Decimal);

sqlCom.ExecuteNonQuery();
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Robert Omete
  • 64
  • 1
  • 10
  • 3
    Chck out: [How to use DbContext.Database.SqlQuery(sql, params) with stored procedure? EF Code First CTP5][1] [1]: http://stackoverflow.com/questions/4873607/how-to-use-dbcontext-database-sqlquerytelementsql-params-with-stored-proced – Michal Ciechan May 17 '15 at 00:05
  • Chck out: [How to use DbContext.Database.SqlQuery(sql, params) with stored procedure? EF Code First CTP5][1] [1]: http://stackoverflow.com/questions/4873607/how-to-use-dbcontext-database-sqlquerytelementsql-params-with-stored-proced – Michal Ciechan May 17 '15 at 00:05
  • 1
    You've **not set** the `CommandText` property of your `SqlCommand` - set it to the SQL query string. Also: if you're using EF - why on earth do you want to resort to "raw" ADO.NET `SqlCommand` processing? – marc_s May 17 '15 at 05:54
  • EF is unsuitable for creating multiple inserts to databases in the same transaction but it is suitable for other ordinary classes – Robert Omete May 18 '15 at 10:37

1 Answers1

0

It turns out I did not need to use variables but column values from the windows forms like below:

dbContext.Database.SqlQuery<CashAccount>("INSERT INTO CashAccounts(CashFK,CashDate,CashMemo,CashDebit,CashCredit)VALUES(Getdate(),cashmemos,cashcredits,cashdebits)");
Robert Omete
  • 64
  • 1
  • 10
  • Something doesn't smell right here; This is a very error prone way to do inserts, and it's vulnerable to SQL injection attacks; also, `dbContext` has easier ways to do inserts. – Claies May 17 '15 at 07:45
  • I am testing the waters for the right way to do multiple inserts using raw sql unless you know of a better way to do it without using stored procedures? – Robert Omete May 17 '15 at 08:54