SQL syntax error near 'fieldname'

Question

I am fairly new to C# i have a database which i need fill with windows forms, the button that inserts data into the table has the following code:

private void btnAddEmployee_Click(object sender, EventArgs e)
        {
            SqlConnection cn = new SqlConnection("server=.\\server1; database = PMS; Integrated security=true;");
            SqlDataAdapter da = new SqlDataAdapter("INSERT INTO tblEmployees cid, empID, empFirstName, empMidName, "
                +"empLastName, empAge, empTitle, empAddress, empRank, empSalary, empEmail, empPhone, "
                +"empMobile, Notes, userName, usrPassword, usrAccessLevel, empActive, empMarked, empType "
                +"VALUES ('" + this.txtID + "', '" + this.txtEmpID + "', '" + this.txtFirstName + "','" + this.txtMidName + "'," +
                " '" + this.txtLastName + "', '" + this.txtEmpAge + "', '" + this.txtJobTitle + "', '" + this.txtAddress + "', " +
                " '" + this.cmbRank + "', '" + this.txtSalary + "', '" + this.txtEmail + "', '" + this.txtPhone + "', " + 
                " '" + this.txtMobile + "', '" + this.txtNote + "', '" + this.txtUserName + "', '" + this.txtPassword + "', " + 
                " '" + this.cmbAcsLevel + "', '" + this.txtActive + "', '" + this.txtMarked + "', '" + this.txtType + "')", cn);

        if (cn.State != ConnectionState.Open)
        {
            cn.Open();
        }
        object o = da.SelectCommand.ExecuteNonQuery();
        cn.Close();
    }

however after clicking the button i get the following error:

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

Missing brackets around field names. It should be `INSERT INTO tblEmployees (cid,..... empType )` — trailmax, Jun 21 '18 at 13:09
Also, read about SQL injection and how to prevent it using parameterised queries. Your code is horribly insecure. — Equalsk, Jun 21 '18 at 13:10
And even with forms apps, you should practice safety and use *parameters* to keep SQL *code* and *data* separate, rather than mangling them together by string manipulation — Damien_The_Unbeliever, Jun 21 '18 at 13:11
Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Jun 21 '18 at 13:11
Also consider not storing numeric data as strings (e.g. salary). Also consider storing date of birth, rather than Age (since Age keeps changing). — mjwills, Jun 21 '18 at 13:13

TAHA SULTAN TEMURI · Accepted Answer · 2018-06-21T13:27:13.653

First of all make it simple and use Parameterized query to prevent sql injection

     using(SqlConnection connection = new SqlConnection("server=.\\server1; database = PMS; Integrated security=true;"))
      {
     String query = "INSERT INTO tblEmployees (cid, empID, empFirstName, empMidName,empLastName, empAge, empTitle, empAddress, empRank, empSalary, empEmail, empPhone,empMobile, Notes, userName, usrPassword, usrAccessLevel, empActive, empMarked, empType) VALUES (@cid, @empID, @empFirstName, @empMidName,@empLastName, @empAge, @empTitle, @empAddress, @empRank, @empSalary, @empEmail, @empPhone,@empMobile, @Notes, @userName, @usrPassword, @usrAccessLevel, @empActive, @empMarked, @empType)";

using(SqlCommand command = new SqlCommand(query, connection))
{
    command.Parameters.AddWithValue("@cid", cid);
    command.Parameters.AddWithValue("@empID", empID);
    command.Parameters.AddWithValue(" @empFirstName", empFirstName);
    command.Parameters.AddWithValue("@empMidName", empMidName);

    command.Parameters.AddWithValue("@empLastName", empLastName);
    command.Parameters.AddWithValue("@empAge", empAge);
    command.Parameters.AddWithValue(" @empTitle", empTitle);
    command.Parameters.AddWithValue("@empAddress", empAddress);
    command.Parameters.AddWithValue("@empRank", empRank);
    command.Parameters.AddWithValue("@empSalary", empSalary);
    command.Parameters.AddWithValue(" @empEmail", empEmail);
    command.Parameters.AddWithValue("@empPhone", empPhone);
    command.Parameters.AddWithValue("@empMobile", empMobile);
    command.Parameters.AddWithValue("@Notes", Notes);
    command.Parameters.AddWithValue("@userName", userName);
    command.Parameters.AddWithValue("@usrPassword", usrPassword);
     command.Parameters.AddWithValue("@usrAccessLevel", usrAccessLevel);
    command.Parameters.AddWithValue("@empActive", empActive);
    command.Parameters.AddWithValue("@empMarked", empMarked);
    command.Parameters.AddWithValue("@empType", empType);
     connection.Open();
      int result = command.ExecuteNonQuery();

    // Check Error
       if(result < 0)
        Console.WriteLine("Error inserting data into Database!");
      }
  }

Thank you, however it gives me an error like: System.ArgumentException: 'No mapping exists from object type System.Windows.Forms.TextBox to a known managed provider native type.' — Khalid, Jun 21 '18 at 14:08
System.ArgumentException: 'No mapping exists from object type System.Windows.Forms.TextBox to a known managed provider native type.' — Khalid, Jun 21 '18 at 14:11
you just need to verify that all the values on the right hand side are valid see https://stackoverflow.com/questions/9543635/no-mapping-exists-from-object-type-system-windows-forms-datetimepicker-to-a-know — TAHA SULTAN TEMURI, Jun 21 '18 at 14:12

score 0 · Answer 2 · answered Jun 21 '18 at 13:23

@trailmax is right -- for SQL you would need parentheses around the list of column names, to separate them from the "INSERT INTO " and "VALUES" parts which come before and after, like so:

INSERT INTO tblEmployees (cid, empID, empFirstName, empMidName,empLastName, empAge, empTitle, empAddress, empRank, empSalary, empEmail, empPhone,empMobile, Notes, userName, usrPassword, usrAccessLevel, empActive, empMarked, empType) VALUES (
[...]

score -4 · Answer 3 · answered Jun 21 '18 at 13:22

I find it is easy to debug code like this by creating a string for the query using string.format. The you can take the string results and put into SQL Server Management Studio (SSMS) which has much better diagnostics to help find issue.

            string query = string.Format("INSERT INTO tblEmployees cid, empID, empFirstName, empMidName, " +
                           "empLastName, empAge, empTitle, empAddress, empRank, empSalary, empEmail, empPhone, " +
                           "empMobile, Notes, userName, usrPassword, usrAccessLevel, empActive, empMarked, empType " +
                           "VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}'," +
                                   "'{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}')",
                                   this.txtID, this.txtEmpID, this.txtFirstName, this.txtMidName, this.txtLastName,
                                   this.txtEmpAge, this.txtJobTitle, this.txtAddress, this.cmbRank, this.txtSalary,
                                   this.txtEmail, this.txtPhone, this.txtMobile, this.txtNote, this.txtUserName,
                                   this.txtPassword, this.cmbAcsLevel, this.txtActive, this.txtMarked, this.txtType);

Perhaps a good tip, but IMO this is by no means an answer to the question. — Peter B, Jun 21 '18 at 13:24
My code probably solves the issue since you can easily see all the commas and single quotes are in the correct locations. — jdweng, Jun 21 '18 at 13:33
This is still susceptible to Sql Injection, however. Better to use parameters and get the best of both worlds! — Chris Dunaway, Jun 21 '18 at 15:17

SQL syntax error near 'fieldname'

3 Answers3