Insert command error in OleDB

Question

A little noob says HY. I have a small problem with a homework project in Microsoft Visual Studio 2010. Also, i work in C#. I must do a site for selling products and i have an Access database. So, the problem is this: i wrote code but it seems something is wrong and i don't know what! When i try to Add a command by site i receive an error:

Data type mismatch in criteria expression.

Code is:

string date = DateTime.Now.ToShortDateString();
string string_baza_de_date = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\BogCs\Documents\Visual Studio 2010\WebSites\WebSite1\App_Data\magazin.mdb";
OleDbConnection ConexiuneSQL = new OleDbConnection(string_baza_de_date);

ConexiuneSQL.Open();

int numar_total_de_produse = CheckBoxList1.Items.Count; //  se numara produsele

for (int i = 0; i < numar_total_de_produse; i++) // de la primul articol din CheckBoxList1 pana la ultimul
{
    if (CheckBoxList1.Items[i].Selected == true) // daca am selectat un produs
    {
        // interogarea comenzii:
        string interogare_adauga_comanda = "INSERT INTO comanda_finala (ID_comanda, ID_client, ID_produs, produs, tip_produs,  data_comanda, pret) VALUES ("
        + TextBox1.Text + ",'" + TextBox2.Text + "',"
        + CheckBoxList1.Items[i].Value + ",'" + CheckBoxList1.Items[i].Text + "', 'Televizoare LED','"
        + data_curenta + "','" + GridView3.Rows[i].Cells[3].Text.ToString() + "');";

        OleDbCommand comanda_inserare_comanda = new OleDbCommand(interogare_adauga_comanda, ConexiuneSQL);

        comanda_inserare_comanda.ExecuteNonQuery(); 
    }
}

ConexiuneSQL.Close();

GridView3.Visible = false;
Button1.Visible = false;
Button2.Visible = false;
CheckBoxList1.Visible = false;
Label1.Visible = false;
TextBox1.Visible = false;
Label2.Visible = true;

When i press "Add command" gives me that error and i don't know how to solve!

Stop putting all the data into the SQL itself. Use parameterized SQL and I suspect the problem will disappear - along with SQL injection vulnerabilities. — Jon Skeet, May 03 '13 at 19:57
It's a bit hard for me cause it's 2nd time when i worked in this program:)) But i will try following google tutorials! — user2348197, May 03 '13 at 20:04

score 0 · Answer 1 · answered May 03 '13 at 19:59

you have to put " ' " before and after textbox1.text so it would look like this:

 string interogare_adauga_comanda = "INSERT INTO comanda_finala (ID_comanda, ID_client, ID_produs, produs, tip_produs,  data_comanda, pret) VALUES ('" + TextBox1.Text + "','" + TextBox2.Text + "', " + CheckBoxList1.Items[i].Value + ",'" + CheckBoxList1.Items[i].Text + "', 'Televizoare LED','"+ data_curenta + "','" + GridView3.Rows[i].Cells[3].Text.ToString()+ "');";

score 0 · Answer 2 · edited May 23 '17 at 11:57

Probably the error lies in some of your strings used to build the command.
As usual this is the first obvious reason to use parametrized query.
Let the framework code format your strings according to the rules of the current database.

The most important reason however, is the Sql Injecton problem

So let me change your code in this way to get rid of that ugly string concatenation

 string interogare_adauga_comanda = "INSERT INTO comanda_finala (ID_comanda, ID_client, "+ 
                                    "ID_produs, produs, tip_produs,  data_comanda, pret) " + 
                                    "VALUES (?,?,?,?,?,?,?)";

 OleDbCommand comanda_inserare_comanda = new OleDbCommand(interogare_adauga_comanda, ConexiuneSQL);
 comanda_inserare_comanda.Parameters,AddWithValue("@p1",TextBox1.Text );
 comanda_inserare_comanda.Parameters,AddWithValue("@p2",TextBox2.Text );
 comanda_inserare_comanda.Parameters,AddWithValue("@p3",CheckBoxList1.Items[i].Value );
 comanda_inserare_comanda.Parameters,AddWithValue("@p4",CheckBoxList1.Items[i].Text );
 comanda_inserare_comanda.Parameters,AddWithValue("@p5","Televizoare LED");
 comanda_inserare_comanda.Parameters,AddWithValue("@p6",data_curenta);
 comanda_inserare_comanda.Parameters,AddWithValue("@p7",GridView3.Rows[i].Cells[3].Text.ToString());
 comanda_inserare_comanda.ExecuteNonQuery();

Also, keep in mind that you should pass the value to the parameters with the correct datatype expected by the database field. For example, if your first field ID_comanda is numeric then the line of the relative parameter should be changed to

 comanda_inserare_comanda.Parameters,AddWithValue("@p1",Convert.ToInt32(TextBox1.Text));

and this raises another problem. Did you check if the text in the TextBox1 is really a number?

This code will be very difficult to me to explain my teacher, cause is a little exam where we should explain why and how:)) So, a noob like me, who opened 2 times this program will be a little too smart for that code:) About TextBox1, yup is a number:) — user2348197, May 03 '13 at 20:33
Don't be afraid for this. Every action you take to better your knowledge should always welcomed. And by the way this is not rocket science, every single online tutorial recommend to use parameters and not string concatenations. Try to search by yourself for the string 'Parametrized query' — Steve, May 03 '13 at 20:47

Insert command error in OleDB

2 Answers2