0

am trying to insert data into Access 2007 using C#, i got some code from stackoverflow and it is really helping to insert data to a single table but i was trying to insert data into two tables which has one to many relationship and the code will run, it has no warring or error but when i click insert button there will be some message box which tells me failed due toSyntax error in insert into statement.can some one fix this thing??? other i have some buttons on form2 such us update,search and when i click one of this button the window will be open n the previous window will not get closed if i click buttons 10 time thr will be ten windows .... pleas am new to this language n i need some help with the codes.

here is my code...

namespace WindowsFormsApplication1
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

    private void button2_Click(object sender, EventArgs e)
    {
        Form3 f3 = new Form3();
        f3.ShowDialog();
    }

    private void button3_Click(object sender, EventArgs e)
    {
        Form4 f4 = new Form4();
        f4.ShowDialog();
    }


    private void button4_Click(object sender, EventArgs e)
    {
        Form1 f1 = new Form1();
        f1.Show();
    }


    private void button1_Click(object sender, EventArgs e)
    {
        System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
        conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
        @"Data source= C:\Users\user\Desktop\crt_db.accdb";
        try
        {
            conn.Open();
            String Name = txtName.Text.ToString();

            String AR = txtAr.Text.ToString();
            String Wereda = txtWereda.Text.ToString();
            String Kebele = txtKebele.Text.ToString();
            String House_No = txtHouse.Text.ToString();
            String P_O_BOX = txtPobox.Text.ToString();
            String Tel = txtTel.Text.ToString();
            String Fax = txtFax.Text.ToString();
            String Email = txtEmail.Text.ToString();
            String Item = txtItem.Text.ToString();
            String Dep = txtDep.Text.ToString();
            String Remark = txtRemark.Text.ToString();

            String Type = txtType.Text.ToString();
            String Brand = txtBrand.Text.ToString();
            String License_No = txtlicense.Text.ToString();
            String Date_issued = txtDate.Text.ToString();
            String my_querry = "INSERT INTO crtPro(Name,AR,Wereda,Kebele,House_No,P_O_Box,Tel,Fax,Email,Item,Dep,Status,Remark,)VALUES('" + Name + "','" + AR + "','" + Wereda + "','" + Kebele + "','" + House_No + "','" + P_O_BOX + "','" + Tel + "','" + Fax + "','" + Email + "','" + Item + "','" + Dep + "','" + Remark + "')" +
                      "AND INSERT INTO crtItemLicense (" +
                      "Type,Brand,License_No,Date_issued) VALUES('" + Type + "','" + Brand + "','" + License_No + "','" + Date_issued + "') ";

            OleDbCommand cmd = new OleDbCommand(my_querry, conn);
            cmd.ExecuteNonQuery();

            MessageBox.Show("Data saved successfuly...!");
        }
        catch (Exception ex)
        {
            MessageBox.Show("Failed due to" + ex.Message);
        }
        finally
        {
            conn.Close();

        }

    }
}

}

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
Bisrat
  • 1
  • 4
  • http://meta.stackexchange.com/questions/10647/how-do-i-write-a-good-title – Soner Gönül Mar 14 '14 at 08:06
  • apparently unlike SQL you can't combine inserts in one statement - there are ways around it (http://stackoverflow.com/questions/62504/is-there-any-way-to-create-multiple-insert-statements-in-a-ms-access-query/65027#65027) but if I were you I'd make them 2 separate commands – NDJ Mar 14 '14 at 12:36
  • sure NDJ, am trying to follow u,but as i told u thr is an error when i try to put using ';' or just put this code on one line String my_querry = "INSERT INTO crtPro(Name,AR,Wereda,Kebele,House_No,P_O_Box,Tel,Fax,Email,Item,Dep,Status,Rema‌​‌​rk)VALUES('" + Name + "','" + AR + "','" + Wereda + "','" + Kebele + "','" + House_No + "','" + P_O_BOX + "','" + Tel + "','" + Fax + "','" + Email + "','" + Item + "','" + Dep + "','" + Remark + "')"; "INSERT INTO crtItemLicense (Type,Brand,License_No,Date_issued) VALUES('" + Type + "','" + Brand + "','" + License_No + "','" + Date_issued + "')"; – Bisrat Mar 14 '14 at 12:53

2 Answers2

1

In your first insert statement there is a "," after Remark, I believe this could be causing the invalid syntax error.

String my_querry = "INSERT INTO crtPro(Name,AR,Wereda,Kebele,House_No,P_O_Box,Tel,Fax,Email,Item,Dep,Status,Remark**,**
Jason Clair
  • 76
  • 1
  • 4
  • well you are right i put useless "," after Remark thank you, can u also help me with the breaks that were posted before 1 hour ago thank you again NDJ :) – Bisrat Mar 14 '14 at 10:39
0

//this will add a data into multiple table. 

    private void button2_Click(object sender, EventArgs e)
    {
        Form3 f3 = new Form3();
        f3.ShowDialog();
    }

    private void button3_Click(object sender, EventArgs e)
    {
        Form4 f4 = new Form4();
        f4.ShowDialog();
    }


    private void button4_Click(object sender, EventArgs e)
    {
        Form1 f1 = new Form1();
        f1.Show();
    }
    public void ClearControls()
        {

        txtName.Text=""; // use your id to clear the textbox
        txtItem.Text="";
        txtType.Text = "";
        txtBrand.Text = "";
        txtlicense.Text = "";
        txtDep.Text = "";
        txtDate.Text = "";
        txtRemark.Text = "";
        txtAr.Text = "";
        txtWereda.Text = "";
        txtKebele.Text = "";
        txtHouse.Text = "";
        txtPobox.Text = "";
        txtTel.Text = "";
        txtFax.Text = "";
        txtEmail.Text = "";

        }



    private void button1_Click(object sender, EventArgs e)
    {
        System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
        conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
        @"Data source= C:\Users\user\Desktop\crt_db.accdb";
        try
        {
            conn.Open();
            // for the firs table
            String Name = txtName.Text.ToString();

            String AR = txtAr.Text.ToString();
            String Wereda = txtWereda.Text.ToString();
            String Kebele = txtKebele.Text.ToString();
            String House_No = txtHouse.Text.ToString();
            String P_O_BOX = txtPobox.Text.ToString();
            String Tel = txtTel.Text.ToString();
            String Fax = txtFax.Text.ToString();
            String Email = txtEmail.Text.ToString();
            String Item = txtItem.Text.ToString();
            String Dep = txtDep.Text.ToString();
            String Remark = txtRemark.Text.ToString();
           //for the second table
            String Type = txtType.Text.ToString();
            String Brand = txtBrand.Text.ToString();
            String License_No = txtlicense.Text.ToString();
            String Date_issued = txtDate.Text.ToString();
            String my_querry = "INSERT INTO crtPro(Name,AR,Wereda,Kebele,House_No,P_O_BOX,Tel,Fax,Email,Item,Dep,Remark)VALUES('" + Name + "','" + AR + "','" + Wereda + "','" + Kebele + "','" + House_No + "','" + P_O_BOX + "','" + Tel + "','" + Fax + "','" + Email + "','" + Item + "','" + Dep + "','" + Remark + "')";
            OleDbCommand cmd = new OleDbCommand(my_querry, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            conn.Open();

            String my_querry1 = "SELECT LAST(PID) FROM crtPro";
            OleDbCommand cmd1 = new OleDbCommand(my_querry1, conn);
            string var = cmd1.ExecuteScalar().ToString();
            txtStatus.Text = var;
            String PID = txtStatus.Text.ToString();
            String my_querry2 = "INSERT INTO crtItemLicense(PID,Type,Brand,License_No,Date_issued)VALUES('" +PID + "','" + Type + "','" + Brand + "','" + License_No + "','" + Date_issued + "')";
            OleDbCommand cmd2 = new OleDbCommand(my_querry2, conn);
            cmd2.ExecuteNonQuery();
            MessageBox.Show("Message added succesfully");

        }
        catch (Exception ex)
        {
            MessageBox.Show("Failed due to" + ex.Message);
        }
        finally
        {
            conn.Close();

        }

    }
}

}

Bisrat
  • 1
  • 4