System.Data.SqlClient.SqlException: 'Incorrect syntax near '.'.'

Question

I try to something about .Net Core Wep API with SQL. I got the incorrect syntax near '.''.'

ı use postman for api and try whether it came or not with json.

in appsettings.json code;

{
  "ConnectionStrings": {
    "EmployeeAppCon": "Data Source=.;Initial Catalog=EmployeeDB; Integrated Security=true"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

And ı tried get, post method but ı got an error in here with Uptade method ;

[HttpPut]
        public JsonResult Put(Department dep)
        {
            string query = @"
                       Uptade dbo.Department set 
                       DepartmentName='"+dep.DepartmentName+@"'
                       where DepartmentId="+dep.DepartmentId+@"
                       ";
            DataTable table = new DataTable();
            string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");
            SqlDataReader myReader;
            using (SqlConnection myCon = new SqlConnection(sqlDataSource))
            {
                myCon.Open();
                using (SqlCommand myCommand = new SqlCommand(query, myCon))
                {
                    myReader = myCommand.ExecuteReader();
                    table.Load(myReader); ;
                    myReader.Close();
                    myCon.Close();
                }

            }
            return new JsonResult("Uptade Successfull");

        }

Where is my fault please help me thanks a lot?

Use parameters in your query to resolve your issue as well as any injection issues. Also the word is Update an not Uptade — apomene
– apomene, Commented Nov 24, 2020 at 8:38
The department name could contain single quote and mass up your SQL. This also prone to SQL injection. — HardcoreGamer
– HardcoreGamer, Commented Nov 24, 2020 at 8:40
very minor, but: new JsonResult("Uptade Successfull") [sic] is odd... because: that's not JSON — Marc Gravell
– Marc Gravell, Commented Nov 24, 2020 at 8:46
(just to add: in addition to the bit where concatenation causes errors, and opens you up to SQL injection - it also causes problems with i18n/l10n (in particular dates and numbers), and is suboptimal re query plan re-use; so: fundamentally "always use parameters" :) — Marc Gravell
– Marc Gravell, Commented Nov 24, 2020 at 8:56

Marc Gravell · Accepted Answer · 2020-11-24 08:49:05Z

2

This is most likely a problem with concatenation in the SQL; long story short: never ever concatenate input into SQL; the correct operation is more like:

update dbo.Department
set DepartmentName=@name
where DepartmentId=@id

where @name and @id are parameters.

Then you would use myCommand.Parameters.Add(...) to include those two parameters and their names/values, and use ExecuteNonQuery (not ExecuteReader).

However! It would be much simpler to get Dapper (free etc) to do all the hard work for us, then we can just do:

using var myCon = new SqlConnection(sqlDataSource); // don't even need to open it
myCon.Execute(@"
update dbo.Department
set DepartmentName=@name
where DepartmentId=@id",
    new { name = dep.DepartmentName, id = dep.DepartmentId });

where the new {...} here defines our named parameters with values.

edited Nov 24, 2020 at 8:49

answered Nov 24, 2020 at 8:41

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

System.Data.SqlClient.SqlException: 'Incorrect syntax near '.'.'

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related