CODEWITHSUNDEEP
c#asp.net
vihaan thakur
vihaan thakur

Reputation: 1

How to fix System.Data.SqlClient.SqlException: 'Incorrect syntax near ''.'

im creating a website using asp.net in front end along with c#. I have two tables and im trying to update the columns of one table using the below query. however im getting the below error. Can anyone please help me resolving it.

string sql = "UPDATE CurrentStudent SET CurrentStudent.DateOfJoining ='" + dateOfJoining.Text + "',CurrentStudent.DateOfLeaving = '" + dateOfLeaving.Text + "',CurrentStudent.Course = '"+ "'FROM CurrentStudent SI INNER JOIN UserDetails UI ON SI.Email = UI.Email'"+"'";


System.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=Incorrect syntax near ''.
  Source=.Net SqlClient Data Provider
  StackTrace:
<Cannot evaluate the exception stack trace>

Upvotes: 0

Views: 6778

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131384

The actual problem is the use of string concatenation. Even if the extra quote or missing space are fixed, it's always possible to enter an invalid string, a date that doesn't match the server's locale, 22.04.2019 for example, or an actual malicious string that results in SQL injection.

Using parameterized queries with strongly typed parameters is actually easier then string concatenation:

var sql = @"UPDATE CurrentStudent 
SET CurrentStudent.DateOfJoining =@joinDate
    CurrentStudent.DateOfLeaving = @leaveDate,
    CurrentStudent.Course = ''
FROM CurrentStudent SI INNER JOIN UserDetails UI ON SI.Email = UI.Email";

using(var conn=new SqlConnection(...))
using(var cmd=new SqlCommand(sql,conn);
{
    var joinDate=cmd.Parameters.Add("@joinDate",SqlDbType.Date);
    var leaveDate=cmd.Parameters.Add("@leaveDate",SqlDbType.Date);

    //Set a DateTime, not a string
    joinDate.Value=joinDaterPicker.Value;
    leaveDate.Value=leaveDatePicker.Value;

    conn.Open();
    cmd.ExecuteNonScalar();
}

You can use a microORM like Dapper to simplify the code even more:

var sql = @"UPDATE CurrentStudent 
SET CurrentStudent.DateOfJoining =@joinDate
    CurrentStudent.DateOfLeaving = @leaveDate,
    CurrentStudent.Course = ''
FROM CurrentStudent SI INNER JOIN UserDetails UI ON SI.Email = UI.Email";

using(var conn=new SqlConnection(...))
{
    conn.Execute(sql,new { joinDate  = joinDaterPicker.Value, 
                           leaveDate = leaveDatePicker.Value});
}

Upvotes: 2

Tayyab
Tayyab

Reputation: 1217

You are missing out a space between the From and possible string end:

"UPDATE CurrentStudent SET CurrentStudent.DateOfJoining ='" + dateOfJoining.Text + "',CurrentStudent.DateOfLeaving = '" + dateOfLeaving.Text + "',CurrentStudent.Course = '"+ Course.Text +"' FROM CurrentStudent SI INNER JOIN UserDetails UI ON SI.Email = UI.Email'"+"'"

Note the "' FROM added space.

Try the above string and also i will recommend you use parametrized queries. Some useful links:

link1

link2

Also a bit more on why to use parametrized queries:

SQL Injection

why use parameterized queries

Upvotes: 1

Related Questions