OledbCommandBuilder Syntax error in insert into St

Question

I know this question has been asked a hundred of times and I have looked at them all and don't know what's going wrong. This is the table in access:

tbl_Slip

SlipNo*,
EmpID,
ScaleID,
Month-Year,
RaiseBasic,
OtherDed,
Tax,
Arrears,
Notes,

all the fields are integer type except for Month-Year and Notes which are text.

Now the C# Part: This is the code I use. I tried to find out the error so made a button with harcoded values.

(The oledbConnection works fine. I use it to display data on the form)

This function I use to link to tbl_Slip.

    private void linkToSlip()
    {
        con.Open();

        string sqlRecords = "Select * FROM tbl_Slip ORDER BY SlipNo";
        SlipDA = new OleDbDataAdapter(sqlRecords, con);
        SlipDA.Fill(SlipDS, "Slip");

        // Primary key so DataTable.Rows.Find[] can be used
        SlipDS.Tables["Slip"].PrimaryKey = new DataColumn[] { SlipDS.Tables["Slip"].Columns["SlipNo"] };
        maxSlipRows = SlipDS.Tables["Slip"].Rows.Count;

        dataGrid_Slip.DataSource = SlipDS;
        dataGrid_Slip.DataMember = "Slip";

        con.Close();
    }

This is the form load part:

    private void frm_Slip_Load(object sender, EventArgs e)
    {
        SlipDS = new DataSet();
        createcon();
        linkToSlip();
    }

(createcon is the function I use to make the connection withthe database)

This is the button code:

    private void button1_Click(object sender, EventArgs e)
    {
        OleDbCommandBuilder saveCB = new OleDbCommandBuilder(SlipDA);

        DataRow dR = SlipDS.Tables["Slip"].NewRow();

        dR[0] = Convert.ToInt32("2");
        dR[1] = Convert.ToInt32("1");
        dR[2] = Convert.ToInt32("1");
        dR[3] = "January-2013";
        dR[4] = Convert.ToInt32("15");
        dR[5] = Convert.ToInt32("20");
        dR[6] = Convert.ToInt32("2");
        dR[7] = Convert.ToInt32("223");
        dR[8] = "notes";

        SlipDS.Tables["Slip"].Rows.Add(dR);
        saveCB.DataAdapter.Update(SlipDS.Tables["Slip"]);
        maxSlipRows++;
    }

When I press the button the error given is

Syntax error in INSERT INTO statement.

I cannot figure out what is the problem. I checked for duplication, I checked if the data was saved in SlipDS.Tables["Slip"] (IT WAS). I can't think of any thing else.

What is wrong ?

Answer 1

Quick answer: Don't use the OleDbCommandBuilder. Create an OleDbCommand manually as INSERT INTO ... (...) VALUES (...) and use a parameterized query. Then, add the parameters appropriately.

You'd have to show us the INSERT statement so we can tell what's wrong with it.

Answer 2

I would bet that the problem for the invalid command is that your field name has a hyphen in it and that is probably NOT being created properly and is being interpreted as

insert into tbl_Slip ( EmpID, ScaleID, Month-Year, ...

Bad idea to have columns with spaces and/or hyphens. Yes, databases allow it, but you don't have to shoot yourself in the foot just because you can.