locked
Help with insert into access database RRS feed

  • Question

  • User-564941787 posted

    Hi i am trying to at some data to a database.

    but i have a problem.
    i must put data into 2 different table.(klanten and reservering).
    that is not that diffecult. but in the klanten table is a autonumber column and this need olso to be insert into the reservering table.

    I need a way to get the auto number from table 1 and insert that into table 2.

    i use a ASP.net with C# and a access database.

    my code

        protected void Binvoeren_Click(object sender, EventArgs e)
        {
            // 1. Instantiate the connection
            OleDbConnection conn = new OleDbConnection(
                "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("App_Data/dbreservering.mdb") + ";");

            // 2. prepare command string
            string insertStringKlant = "insert into klanten(naam,geslacht,adres,postcode,woonplaats,telefoon,mobiel,email,land) values (?,?,?,?,?,?,?,?,?)";

            // 3. Instantiate a new command with a query and connection       
            using (OleDbCommand cmdKlant = new OleDbCommand(insertStringKlant, conn))
            {
                cmdKlant.CommandType = CommandType.Text;
                cmdKlant.Parameters.AddWithValue("naam", TBnaam.Text);
                cmdKlant.Parameters.AddWithValue("geslacht", TBgeslacht.Text);
                cmdKlant.Parameters.AddWithValue("adres", TBadres.Text);
                cmdKlant.Parameters.AddWithValue("postcode", TBpostcode.Text);
                cmdKlant.Parameters.AddWithValue("woonplaats", TBwoonplaats.Text);
                cmdKlant.Parameters.AddWithValue("telefoon", TBtelefoon.Text);
                cmdKlant.Parameters.AddWithValue("mobiel", TBmobiel.Text);
                cmdKlant.Parameters.AddWithValue("email", TBemail.Text);
                cmdKlant.Parameters.AddWithValue("land", TBland.Text);
               
                // 4. Open the connection
                conn.Open();
               
                // 5. Call ExecuteNonQuery to send command
                cmdKlant.ExecuteNonQuery();

                // 6. Open the connection
                conn.Close();
            }
            //get IDklant for klanten and put into a variable.
           
            //================================
            //================================
            //================================

            //Stop de gegevens in reservering

            // 1. prepare command string
            string InsertStringReser = "insert into reservering(Idklant,Datum aankomst,aantal personen) values (?,?,?)";

            // 2. Instantiate a new command with a query and connection       
            using (OleDbCommand cmdReser = new OleDbCommand(InsertStringReser, conn))
            {
                cmdReser.CommandType = CommandType.Text;
                cmdReser.Parameters.AddWithValue("Idklant", TBnaam.Text);
                cmdReser.Parameters.AddWithValue("Datum aankomst", TBaankomst.Text);
                cmdReser.Parameters.AddWithValue("aantal personen", TBaantal.Text);

                // 3. Open the connection
                conn.Open();

                // 4. Call ExecuteNonQuery to send command
                cmdReser.ExecuteNonQuery();

                // 5. Open the connection
                conn.Close();
            }
        }

    Tuesday, June 9, 2009 6:48 AM

Answers

  • User-1199946673 posted

    http://www.mikesdotnetting.com/Article.aspx?ArticleID=54

    Also, when you should avoid table and fieldnames in the database with spaces. If you have fieldnames with space (for example 'aantal personen') you, should use brackets in the SQL string.

    string InsertStringReser = "insert into reservering(Idklant,[Datum aankomst],[aantal personen]) values (?,?,?)";

    Veel succes....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 9, 2009 9:45 AM