SqlCommand insertion query

Question

I have to execute a query of insertion in my c# code

connexion = new SqlConnection(connexionString);
             connexion.Open();
            List<int> liste = client.GetIDFichiers(1210);
            int a = 2;
            foreach (int idfichier in liste)
            {
                a++;
                using (connexion)
                {
                  using (SqlCommand sqlCmd = new SqlCommand("INSERT INTO REL_Fab3DLotFichier_Fichier3D (IDREL_Fab3DLotFichier_Fichier3D,IDFichier3D,IDFab3DLotFichier,CreePar,CreeLe,ModifiePar,DateMaj,EstActif) VALUES (" + a + "," + idfichier + "," + 17965 + "," + null + "," + null + "," + null + "," + null + "," + 1 + ")", connexion))
                    {
                        try
                        {
                            sqlCmd.ExecuteNonQuery();
                        }
                        catch (Exception ex) { MessageBox.Show(ex.ToString()); }

                    }
                }

            }

The insertion isn't working and i don't know why. the selection and delete queries worked fine. Only when i try to insert values a syntaxic error appears.

what is the syntax error?
How can i fix it?

Wow where should I start! For a start you are concatenating ints with strings and with nulls. Secondly...consider parameterized queries. — Myles J
– Myles J, Commented May 3, 2013 at 9:53
Always use parameterized queries. It will prevent SQL injection attacks. — Sen Jacob
– Sen Jacob, Commented May 3, 2013 at 10:01

KV Prajapati · Accepted Answer · 2013-05-03 10:00:10Z

3

what is the syntax error? and How can i fix it?

Yes. Use parameterized SQL statement.

string sql = "INSERT INTO TableName (Column1,Column2) VALUES (@value1,@value2)";
using(SqlCommand sqlCmd = new SqlCommand(sql,connection))
{
 sqlCmd.Parameters.Add("@value1",SqlDbType.Varchar,20).Value = varName1;
 sqlCmd.Parameters.Add("@value2",SqlDbType.Varchar,20).Value = DBNull.Value;
 ...
 }

Try to include not null fields to prepare INSERT SQL.

sql=@"INSERT INTO REL_Fab3DLotFichier_Fichier3D 
                (IDREL_Fab3DLotFichier_Fichier3D,
                 IDFichier3D,
                 IDFab3DLotFichier,
                 DateMaj,EstActif) 
     VALUES 
                (@IDREL_Fab3DLotFichier_Fichier3D,
                 @IDFichier3D,
                 @IDFab3DLotFichier,
                 @DateMaj,@EstActif)";

edited May 3, 2013 at 10:00

answered May 3, 2013 at 9:50

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

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

Comments

James Wright · Accepted Answer · 2013-05-03 09:54:51Z

1

Some of the values you are passing may be string/varchar objects and you aren't wrapping them with quotes:

new SqlCommand("INSERT INTO REL_Fab3DLotFichier_Fichier3D (IDREL_Fab3DLotFichier_Fichier3D,IDFichier3D) VALUES ('" + a + "', '" + idfichier + "')", connexion);

Really, as AVD suggested, you should use parameterisation, which is less prone to injection attacks.

answered May 3, 2013 at 9:54

James Wright

3,0201 gold badge20 silver badges27 bronze badges

Comments

Smile Azeez · Accepted Answer · 2013-05-03 11:03:29Z

0

please enclose your string values in single quotes like below

 New SqlCommand("insert into Type(TypeName,TypeOrder,CategoryID) values ('" + txtType1.Text + "','" + txtOrder.Text + "','" + ViewState("CatID").ToString() + "')", con)

answered May 3, 2013 at 11:03

Smile Azeez

1471 gold badge4 silver badges13 bronze badges

Collectives™ on Stack Overflow

SqlCommand insertion query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related