Insert syntax error in oledbconnection

Question

What is the problem in this code?

for (int i = 1; i <= kalanum; i++)
{
    foreach (Control ctr in panel1.Controls)
    {                       
        if (ctr is TextBox && ctr.Name == i.ToString())
        {
            int kalaid = int.Parse(((TextBox)ctr).Text);
            oleDbCommand1.Parameters.AddWithValue("@k", kalaid);
        }
        else if (ctr is TextBox && ctr.Name == "tbxfee_" + (i.ToString()))
        {
            int fee = int.Parse(((TextBox)ctr).Text);
            oleDbCommand1.Parameters.AddWithValue("@fe", fee);
        }
        else if (ctr is TextBox && ctr.Name == "tbxnumber_" + (i.ToString()))
        {
            int number = int.Parse(((TextBox)ctr).Text);
            oleDbCommand1.Parameters.AddWithValue("@n", number);
        }            
    }

    oleDbCommand1.CommandText = "INSERT INTO fackala(factornum,kalaid,fee,number) values(@f,@k,@fe,@n)";
    oleDbConnection1.Open();
    oleDbCommand1.ExecuteNonQuery();
    oleDbConnection1.Close();            
}

All columns in my access file are numbers. When I insert all dates in the form and send the form, it shows:

Syntax error in INSERT INTO statement.

@RezaZamani: Then you should have included it in the question... please make sure that future questions contain all the relevant code. — Jon Skeet, Dec 02 '14 at 11:57

Jon Skeet · Answer 1 · 2014-12-02T11:58:41.933

0

There are three problems here:

You're using four parameters, but only specifying 3 of them. (Comments suggest that you're specifying the other one outside the loop)
On any one iteration you're only populating at most one of the parameters - what do you expect the values of the others to be?
You're trying use named parameters with OleDbConnection. From the OleDbConnection.Parameters documentation:

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.

edited Dec 02 '14 at 11:58

answered Dec 02 '14 at 11:53

Jon Skeet

1,421,763
867
9,128
9,194

what should i do now? id dont understand, because i am new! – Reza Zamani Dec 02 '14 at 11:56
@RezaZamani: You should use positional parameters, as per the documentation. Follow that link for more details. – Jon Skeet Dec 02 '14 at 11:57
@RezaZamani: There are further issues I hadn't noticed before - I've updated my answer. I suspect you may actually only want *two* parameters... – Jon Skeet Dec 02 '14 at 11:59

Insert syntax error in oledbconnection

1 Answers1