unable to insert data in MS access database using c# windows application

Question

I have tried to insert data in MS access database but data is not added in database and error not given.

  private void btnsubmit_Click(object sender, EventArgs e)
    {

        int row = dataGridView1.RowCount;
        for (int i = 0; i < row - 1; i++)
        {
            String str = "INSERT INTO JDS_Data(job_no,order_no,Revision,DesignSpec,Engine_Type,Record_date,LE_IN_Designer,CPH_Designer,Exp_Del_Week,Action_code,Rev_Description,Ref_pattern,Name_of_mock_up,EPC_Drawing,Turbocharger_no_Type,Engine_Specific_Requirement,Draft_sketch_with_details,Air_cooler_type,Description_of_Job,SF_No,Standard,Prority_Sequence,Remark,Part_family,Modified_Date,User)  values('" + txtjobno.Text + "','" + txtorderno.Text + "','" + txtrevison.Text + "','" + txtds.Text + "','" + txtenginetype.Text + "','" + dateTimePicker1.Text + "','" + txtleindesigner.Text + "','" + txtcphdesigner.Text + "','" + txtexpweek.Text + "','" + txtactioncode.Text + "','" + txtrevdescription.Text + "','" + txtrefpatern.Text + "','" + txtmockup.Text + "','" + txtepcdwg.Text + "','" + txtturbono.Text + "','" + txtenginereq.Text + "','" + txtdraft.Text + "','" + txtaircolertype.Text + "','" + txtdespjob.Text + "','" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[1].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[2].Value.ToString() + "','" + txtremark.Text + "','" + dataGridView1.Rows[i].Cells[3].Value.ToString() + "','" + DateTime.Today + "','" + mdlconnection.user_name + "')";

            int dd = mdlconnection.excuteQuery(str);
            MessageBox.Show(str);
            //if (dd > 0)
            {
                MessageBox.Show("Data Saved Successfully..!!!");

            }

        }

    }   

Answer 1

Hey your query have Syntax error use below line

String str = "INSERT INTO JOB_Quality_Rating(Team,JOB_Type,Designer_Name,AUR_NO,Task_No,Sub_Function_no,Severity_Level,Checkpoints,Points_Deducted,Total_Points,Submitted_By,Submitted_Date) values('" + comboBox1.SelectedItem + "','"+comboBox7.SelectedItem+"','" + comboBox3.SelectedItem + "','" + comboBox6.SelectedItem + "','" + cmbtaskno.SelectedItem + "','" + comboBox2.SelectedItem + "','" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[1].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[2].Value.ToString() + "','" + txtTotal.Text + "','" + mdlconnection.user_name + "','" + dateTimePicker1.Text + "')";

Instead of

String str = "INSERT INTO JOB_Quality_Rating(Team,JOB_Type,Designer_Name,AUR_NO,Task_No,Sub_Function_no,Severity_Level,Checkpoints,Points_Deducted,Total_Points,Submitted_By,Submitted_Date) values('" + comboBox1.SelectedItem + "',,'"+comboBox7.SelectedItem+"','" + comboBox3.SelectedItem + "','" + comboBox6.SelectedItem + "','" + cmbtaskno.SelectedItem + "','" + comboBox2.SelectedItem + "','" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[1].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[2].Value.ToString() + "','" + txtTotal.Text + "','" + mdlconnection.user_name + "','" + dateTimePicker1.Text + "')";

Hope it helps.

Answer 2

Probably your syntax is incorrect. Constructing query with string concatenation is always a bad idea. Use parametrized query string with built-in parameter values handling via OdbcCommand.Parameters.Add(...).

Answer 3

cyberj0g is right, your syntax is incorrect. Particularly, you've got a value missing in your values list at around here: ...comboBox1.SelectedItem + "',,'"+comboBox7.SelectedItem.... You can't pass an empty value this way. Either pass NULL here or remove the respective field form the field list clause: ...Team,Designer_Name... (and the extra comma too, or course).

Answer 4

You can try the following - - Try executing the query string (what is formed in str variable) manually in access to make sure there are no errors in he syntax and it executes there successfully - Wrap the execution statement in try..catch block to get error detail - Make sure the connection is pointing to the correct database instance