How to fix the error (incorrect syntax near ','. )

Question

How to fix the error (incorrect syntax near ','. )

Soufiane Sahraoui 20

incorrect syntax near ','.

I am working on a small program, but this problem stopped me. When I do the update data, this message appears to me, although the update works in more than one other form within the program.

Please help me.

string d = Date.Value.ToString("yyyy/MM/dd");
                    string Dinstall = txtdateInstal.Value.ToString("yyyy/MM/dd");
                    db.ExcuteData("update TBL_Employees Set FName ='"+txtFName.Text+"' ,LName ='" + txtLName.Text + "' ,DateB='" + d + "', PlaceB='" + txtBDaye.Text + "', ID_C= " + cmbCenter.SelectedValue + ", ID_S= " + cmbService.SelectedValue + ",ID_Sec=" + cmbSecture.SelectedValue + ", ID_Grade = " + cmbGrade.SelectedValue + ", DateInstall= '" + Dinstall + "', Credit=" + txtRacid.Text + ", Adress= '" + txtAddress.Text + "', Phone=" + txtPhone.Text + " ,Status = " + status.Text + "  where ID_Emp ="+txtMat.Text+" " ," Modified successfully ");

Karen Payne MVP 35,606 Reputation points Volunteer Moderator

2023-05-12T23:21:13.06+00:00

What you need to do is not use string concatenation for your SQL, instead use parameters.

Basic example using SqlClient but works with any provider.
Erland Sommarskog 134.1K Reputation points MVP Volunteer Moderator

2023-05-13T22:04:02.9366667+00:00

What Karen said. The model you use is difficult, opens for SQL injection and has a number of more issues. Parameterised statements is the way to go.

Jack J Jun 25,306

@Soufiane Sahraoui, Welcome to Microsoft Q&A, I recommend that you use string.format to avoid that you missed single-quote in the string.

   string query = "update TBL_Employees Set FName ='{0}' ,LName ='{1}' ,DateB='{2}', PlaceB='{3}', ID_C= '{4}', ID_S= '{5}',ID_Sec='{6}', ID_Grade = '{7}', DateInstall= '{8}', Credit='{9}', Adress= '{10}', Phone='{11}' ,Status = '{12}'  where ID_Emp ='{13}' ";
           
            string result = string.Format(query, txtFName.Text, txtLName.Text,.......) ;//-> you need to input 14 parameters here

Erland Sommarskog 134.1K Reputation points MVP Volunteer Moderator

2023-05-15T21:22:38.7833333+00:00

I recommend that you use string.format to avoid that you missed single-quote in the string.

Please no. It does not solve the problem and it is still open for SQL injection. That is not a parameterised statement.
Yorks Lopez 0 Reputation points

2023-05-15T23:52:12.33+00:00

Sourfiance, first of all remove slash simbol in the dateformar string, like this : Date.Value.ToString("yyyyMMdd")

i hope that it help you.

greetings.

1 answer

Your answer

Karen Payne MVP 35,606 Reputation points Volunteer Moderator

2023-05-12T23:21:13.06+00:00

What you need to do is not use string concatenation for your SQL, instead use parameters.

Basic example using SqlClient but works with any provider.
Erland Sommarskog 134.1K Reputation points MVP Volunteer Moderator

2023-05-13T22:04:02.9366667+00:00

What Karen said. The model you use is difficult, opens for SQL injection and has a number of more issues. Parameterised statements is the way to go.
Jack J Jun 25,306 Reputation points

2023-05-15T06:27:04.9366667+00:00

@Soufiane Sahraoui, Welcome to Microsoft Q&A, I recommend that you use string.format to avoid that you missed single-quote in the string.

string query = "update TBL_Employees Set FName ='{0}' ,LName ='{1}' ,DateB='{2}', PlaceB='{3}', ID_C= '{4}', ID_S= '{5}',ID_Sec='{6}', ID_Grade = '{7}', DateInstall= '{8}', Credit='{9}', Adress= '{10}', Phone='{11}' ,Status = '{12}' where ID_Emp ='{13}' "; string result = string.Format(query, txtFName.Text, txtLName.Text,.......) ;//-> you need to input 14 parameters here
Erland Sommarskog 134.1K Reputation points MVP Volunteer Moderator

2023-05-15T21:22:38.7833333+00:00

I recommend that you use string.format to avoid that you missed single-quote in the string.

Please no. It does not solve the problem and it is still open for SQL injection. That is not a parameterised statement.
Yorks Lopez 0 Reputation points

2023-05-15T23:52:12.33+00:00

Sourfiance, first of all remove slash simbol in the dateformar string, like this : Date.Value.ToString("yyyyMMdd")

i hope that it help you.

greetings.

Answer 1

It's probably your input on the form.

You can try this sample below. Insert any new parameters you like, as I included only a few to make it short. It will also make your code more protected against SQL injection. (Note: I only assumed your SQL data types and length, you may change it as well as needed.)

        private string updateEmployees(int ID_Emp)
        {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection("Your Connection String Goes Here");
            SqlCommand myCommand = new SqlCommand();

            try
            {

                // The Command
                myCommand.Connection = myConnection;
                myCommand.CommandType = CommandType.Text;
                myCommand.CommandText = "UPDATE TBL_Employees Set FName=@FName, LName=@LName where ID_Emp=@ID_Emp";
                SqlParameter myPara;

                // Add Parameters 
                myPara = new SqlParameter("@ID_Emp", SqlDbType.Int, 8);
                myPara.Value = ID_Emp;
                myCommand.Parameters.Add(myPara);

                myPara = new SqlParameter("@FName", SqlDbType.Char, 50);
                myPara.Value = txtFName.Text;
                myCommand.Parameters.Add(myPara);

                myPara = new SqlParameter("@LName", SqlDbType.Char, 50);
                myPara.Value = txtLName.Text;
                myCommand.Parameters.Add(myPara);

                myConnection.Open();

                myCommand.ExecuteNonQuery();

                return "Modified successfully";
            }
            catch (Exception ex)
            {
                //LOg Exception
                return "Failed to Modify";
            }
            finally
            {
                if (myConnection.State != ConnectionState.Closed)
                {
                    myConnection.Close();
                    myCommand.Dispose();
                    myConnection.Dispose();
                }
            }
        }

Share via

How to fix the error (incorrect syntax near ','. )

1 answer

Your answer