0

Here is a simple subtraction with 2 integers. The SQL Statement works but the value doesn't update in the database. Here is the code below.

var rest = antrag.Urlauber.RestUrlaub - antrag.Tage;
var sqlcom = "UPDATE Person SET RestUrlaub = " + rest +" WHERE ID = " + id +"";
db.Database.ExecuteSqlCommand(sqlcom);

Anyone got an idea what the problem might be?

Eden
  • 51
  • 5
  • 2
    This is dangerous code by the way, please go and read about SQL injection. – DavidG May 06 '21 at 13:27
  • Does this answer your question? [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) It's not just about *security*, but also *correctness* – Charlieface May 06 '21 at 14:18

2 Answers2

1

The query looks okay.

The issue can be with your "id"

Execute the query manually on your database and see if it updates. If the id is wrong then the query returns success without updating anything.

Suggestion - Usage of queries like this are prone to very big security loopholes like SQL injection. Consider using ORMs like EntityFramework or pass query parameters as ADO parameters rather that stitching directly in query

Optionally If you didn't prefer to use big ORMs like EntityFrameworks, I've created a simple dapper based library that you can consider to run your queries in 1 or 2 lines safely.

How to use : https://github.com/sangeethnandakumar/Express-Data-Library

Installation

Install-Package Twileloop.ExpressData -Version 1.0.0

Running a parameterized query safely to prevent SQL Injection

var sql2 = $ "SELECT * FROM tblUser WHERE Id=@id AND Fname=@fname";
result = SqlHelper.QuerySafe<TblUser>(sql2, 
new 
{
    id = 1,
    fname = "Sangeeth"
},
_connectionString);
Sangeeth Nandakumar
  • 1,185
  • 1
  • 9
  • 18
  • @Sangeethnadakumar I have tested the query manually and it worked UPDATE Person SET RestUrlaub = 3 WHERE ID = 4 – Eden May 06 '21 at 13:50
  • Can you try running SQL profiler to identify if the query from your C# code is reaching your SQL server – Sangeeth Nandakumar May 06 '21 at 13:54
  • I looked it up! It reached the profiler and showed this UPDATE Person SET RestUrlaub = '3' WHERE ID = 33. No wonder it doesn't update the ID 33 does not exist. Does this help? – Eden May 06 '21 at 14:10
  • Now the issue is in your program logic. Check how "id" variable populated. The question not explained or demonstrated how id variable is populated – Sangeeth Nandakumar May 06 '21 at 17:44
  • Yeah. I swear this is probably a logic issue in getting the id. This answer explains it right. – Almero Rick May 06 '21 at 17:48
0

db.Database.ExecuteSqlCommand returns an integer, you should try to print it to see what it returns, you might have an error executing this query : https://docs.microsoft.com/en-us/dotnet/api/system.data.entity.database.executesqlcommand?view=entity-framework-6.2.0

LittleSoap
  • 74
  • 5