Insert into database table with OLE DB .NET Provider won't work

Question

I am getting an error msg of "syntax error" for the INSERT INTO command when it gets to cmd.ExecuteNonQuery.

It is important that I use string.Format, and that the structure stays as close to the current structure as possible.

        {
            OleDbConnection con = DAL.GetConnection();
            con.Open();
            if (con.State == ConnectionState.Open)
            {
                string s = string.Format("INSERT INTO DataTable1 (Username, Password, FName, LName, Bdate, Sex, City, Mail) VALUES ('{0}', '{1}', '{2}', '{3}', #{4}#, {5}, {6}, '{7}')", uname, pass, fname, lname, bd, sex, city, mail);
                OleDbCommand cmd = DAL.GetCommand(con, s);
                int check = cmd.ExecuteNonQuery();
                if (check == 0)
                {
                    con.Close();
                    Response.Redirect("Reg.aspx?err=-An error has occured. Please try again-");
                }

Thank you.

writing sql like that leaves you open to [SQL injection attacks](http://bobby-tables.com)... — Marc B, Apr 21 '13 at 18:52
I know it's important to use string.format but i have a suggestion for you. DONT use string.Format. Check out prepared statements or parameters. — Lefteris E, Apr 21 '13 at 19:02

score 4 · Answer 1 · answered Apr 21 '13 at 19:00

Probably you have some quotes in the passed text, try using parameters ...

string s = "INSERT INTO DataTable1 (Username) VALUES (@user)";
OleDbCommand cmd = DAL.GetCommand(con, s);
//Add the parameter ...
OleDbParameter nam = new OleDbParameter("@user",uname);
cmd.Parameters.Add(nam);

score 1 · Answer 2 · answered Apr 21 '13 at 19:05

Check out OleDbCommand.Parameters if you haven't already. It is safer (along the lines of @Mark B's comment), and it will probably clear up the syntax error you have.

If you insist on a pure String.Format approach, simply output cmd.CommandText for debugging to see where the syntax error lies.

Insert into database table with OLE DB .NET Provider won't work

2 Answers2