locked
Problems with connecting microsft access to c# RRS feed

  • Question

  • am trying to edit and save my data but it keep on dispalying a syntax error at saving.executenonreply(); any idea how to solve, this is what i have done so far. Thanks in advance...

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.OleDb;// this is used for database coding so that there is a relationship with the database

    namespace Assignment
    {
        public partial class Form3 : Form
        {
            private OleDbConnection mycustomer;//coding for database
            public Form3()
            {
                InitializeComponent();
                mycustomer = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Oscar\Documents\CustomerDetails.mdb");
                // calling method fro my data that i want to save
            }

            private void seatno1_button_Click(object sender, EventArgs e)
            {
                seatno1_button.BackColor = Color.Red;
            }

            private void seatno2_button_Click(object sender, EventArgs e)
            {
                seatno2_button.BackColor = Color.Red;
            }

            private void seatno3_button_Click(object sender, EventArgs e)
            {
                seatno3_button.BackColor = Color.Red;
            }

            private void seatno4_button_Click(object sender, EventArgs e)
            {
                seatno4_button.BackColor = Color.Red;

            }

            private void seatno5_button_Click(object sender, EventArgs e)
            {
                seatno5_button.BackColor = Color.Red;
            }

            private void seatno6_button_Click(object sender, EventArgs e)
            {
                seatno6_button.BackColor = Color.Red;
            }

            private void seatno7_button_Click(object sender, EventArgs e)
            {
                seatno7_button.BackColor = Color.Red;
            }

            private void seatno8_button_Click(object sender, EventArgs e)
            {
                seatno8_button.BackColor = Color.Red;
            }

            private void seatno9_button_Click(object sender, EventArgs e)
            {
                seatno9_button.BackColor = Color.Red;
            }

            private void seatno10_button_Click(object sender, EventArgs e)
            {
                seatno10_button.BackColor = Color.Red;
            }

            private void seatno11_button_Click(object sender, EventArgs e)
            {
                seatno11_button.BackColor = Color.Red;
            }

            private void seatno12_button_Click(object sender, EventArgs e)
            {
                seatno12_button.BackColor = Color.Red;
            }

            private void seatno13_button_Click(object sender, EventArgs e)
            {
                seatno13_button.BackColor = Color.Red;
            }

            private void seatno14_button_Click(object sender, EventArgs e)
            {
                seatno14_button.BackColor = Color.Red;
            }

            private void seatno15_button_Click(object sender, EventArgs e)
            {
                seatno15_button.BackColor = Color.Red;
            }

            private void seatno16_button_Click(object sender, EventArgs e)
            {
                seatno16_button.BackColor = Color.Red;
            }

            private void seatno17_button_Click(object sender, EventArgs e)
            {
                seatno17_button.BackColor = Color.Red;
            }

            private void seatno18_button_Click(object sender, EventArgs e)
            {
                seatno18_button.BackColor = Color.Red;
            }

            private void Form3_Load(object sender, EventArgs e)
            {
                // TODO: This line of code loads data into the 'customerDetailsDataSet.CustomerDetails' table. You can move, or remove it, as needed.
                this.customerDetailsTableAdapter.Fill(this.customerDetailsDataSet.CustomerDetails);

            }

            private void Save_button_Click(object sender, EventArgs e)
            {
                OleDbCommand saving = new OleDbCommand(); //  Creating a New Command
                saving.CommandType = CommandType.Text;
                saving.CommandText = "Values('"+ cstid_textBox.Text + "'+'"+ custname_textBox.Text +"'+'"+from_comboBox.SelectedText+
                    "'+ '"+ to_comboBox.Text+"'+'"+traveldate_textBox.Text+"'+'"+noofPeople_textBox.Text+"'+'"+ busno_textBox.Text+"')";
                
                saving.Connection = mycustomer;
                mycustomer.Open();
                saving.ExecuteNonQuery();//something is missing here, do not forget to review it
                mycustomer.Close();




            }

            private void Clear_button_Click(object sender, EventArgs e)
            {
                cstid_textBox.Text = "";
                custname_textBox.Text = "";
                from_comboBox.Text = "";
                to_comboBox.Text = "";
                traveldate_textBox.Text = "";
                noofPeople_textBox.Text = "";
                busno_textBox.Text = "";
            }

            private void pd_button_Click(object sender, EventArgs e)
            {
                Report reportdetails = new Report();
                reportdetails.ShowDialog();

            }

        
        }
    }
    Sunday, August 28, 2011 5:33 AM

Answers

  • Cust_IDNo -Number

    Cust_Name- Text

    From -Text

    To- Text

    DateofTravel- Datetime

    NoofPeople-Number

    You mean this?


    Are you sure you have only 6 columns. If yes, then try below code.
    private void Save_button_Click(object sender, EventArgs e)
    {
        OleDbCommand saving = new OleDbCommand(); // Creating a New Command
        saving.CommandType = CommandType.Text;
        saving.CommandText = "Insert into CustomerDetails Values (@CustId, @custName, @from, @to, @traveldate)";
    
        saving.Parameters.AddWithValue("@CustId", Convert.ToInt32(cstid_textBox.Text));
        saving.Parameters.AddWithValue("@custName", custname_textBox.Text);  
        saving.Parameters.AddWithValue("@from", from_comboBox.SelectedText); 
        saving.Parameters.AddWithValue("@to", to_comboBox.Text);
        saving.Parameters.AddWithValue("@traveldate", traveldate_textBox.Text);
    
        saving.Connection = mycustomer;
        mycustomer.Open();
        saving.ExecuteNonQuery();
        mycustomer.Close();
    }
    

    Just read the code carefully. See the parameters etc. The code is self explanatory. It should make you understand the logic.


    Please mark this post as answer if it solved your problem. Happy Programming!
    Sunday, August 28, 2011 6:25 PM

All replies

  • As error says problem is in your saving.CommandText statement.Debug this line and verify you are getting correct values in it.

    Also you not use select or update clause in your commandtext.


    Sunday, August 28, 2011 5:44 AM
  • thnx....I tried it but still cannot execute, completely lost and it is really giving me a headache.
    Sunday, August 28, 2011 7:24 AM
  • Your CommandText need to have update or insert - giving it a set of values does not tell it what to do.

    Perhaps something like:

       savings.CommandText = "INSERT INTO sometable ";

       savings.Command.Text += "VALUES (.....)";

    I think this is what Zain_Ali was getting at with his last line.


    Regards David R
    ---------------------------------------------------------------
    Every program eventually becomes rococo, and then rubble. - Alan Perlis
    The only valid measurement of code quality: WTFs/minute.
    Sunday, August 28, 2011 10:54 AM
  • Update your Save_button_Click function as below,

    private void Save_button_Click(object sender, EventArgs e)
    {
       OleDbCommand saving = new OleDbCommand(); // Creating a New Command
       saving.CommandType = CommandType.Text;
       saving.CommandText = "Insert Into CustomerDetails Values('"+ cstid_textBox.Text + "'+'"+ custname_textBox.Text +"' + '" + from_comboBox.SelectedText + "'+ '"+ to_comboBox.Text + "' + '" + traveldate_textBox.Text + "' + '" + noofPeople_textBox.Text + "' + '" + busno_textBox.Text + "')";
    
       saving.Connection = mycustomer;
       mycustomer.Open();
       saving.ExecuteNonQuery();
       mycustomer.Close();
    }
    

    Please mark this post as answer if it solved your problem. Happy Programming!
    Sunday, August 28, 2011 1:02 PM
  • still there is the error at saving.ExecuteNonQuery();

    I got this message OleDBException was unhandled

    Number of query values and destination fields are not the same.

    I Really appreciate your help guys...

    Sunday, August 28, 2011 5:35 PM
  • So, we arrive at the problem. Couple of questions.

    1. You are inserting values into CustomerDetails  table right?

    2. Can you show us all columns (along with thier data types) in the table?


    Please mark this post as answer if it solved your problem. Happy Programming!
    Sunday, August 28, 2011 5:39 PM
  • Cust_IDNo -Number

    Cust_Name- Text

    From -Text

    To- Text

    DateofTravel- Datetime

    NoofPeople-Number

    You mean this?

    Sunday, August 28, 2011 6:05 PM
  • I count 7 values in your SQL but only 6 fields in your table.

    Could that be the problem?

    PS Don't you need commas to separate the values? The line is so badly formated that I misses they were missing. :)


    Regards David R
    ---------------------------------------------------------------
    Every program eventually becomes rococo, and then rubble. - Alan Perlis
    The only valid measurement of code quality: WTFs/minute.
    • Edited by Riced Sunday, August 28, 2011 6:25 PM Added PS
    Sunday, August 28, 2011 6:13 PM
  • Cust_IDNo -Number

    Cust_Name- Text

    From -Text

    To- Text

    DateofTravel- Datetime

    NoofPeople-Number

    You mean this?


    Are you sure you have only 6 columns. If yes, then try below code.
    private void Save_button_Click(object sender, EventArgs e)
    {
        OleDbCommand saving = new OleDbCommand(); // Creating a New Command
        saving.CommandType = CommandType.Text;
        saving.CommandText = "Insert into CustomerDetails Values (@CustId, @custName, @from, @to, @traveldate)";
    
        saving.Parameters.AddWithValue("@CustId", Convert.ToInt32(cstid_textBox.Text));
        saving.Parameters.AddWithValue("@custName", custname_textBox.Text);  
        saving.Parameters.AddWithValue("@from", from_comboBox.SelectedText); 
        saving.Parameters.AddWithValue("@to", to_comboBox.Text);
        saving.Parameters.AddWithValue("@traveldate", traveldate_textBox.Text);
    
        saving.Connection = mycustomer;
        mycustomer.Open();
        saving.ExecuteNonQuery();
        mycustomer.Close();
    }
    

    Just read the code carefully. See the parameters etc. The code is self explanatory. It should make you understand the logic.


    Please mark this post as answer if it solved your problem. Happy Programming!
    Sunday, August 28, 2011 6:25 PM
  • Will the named parameters work with OleDB? I thought it used positional ones, so instead of:

    saving.CommandText = "Insert into CustomerDetails Values (@CustId, @custName, @from, @to, @traveldate)";

    you would use:

    saving.CommandText = "Insert into CustomerDetails Values ?, ?, ?, ?, ?)";

     


    Regards David R
    ---------------------------------------------------------------
    Every program eventually becomes rococo, and then rubble. - Alan Perlis
    The only valid measurement of code quality: WTFs/minute.
    Sunday, August 28, 2011 7:12 PM
  • Will the named parameters work with OleDB? I thought it used positional ones, so instead of:

    saving.CommandText = "Insert into CustomerDetails Values (@CustId, @custName, @from, @to, @traveldate)";

    you would use:

    saving.CommandText = "Insert into CustomerDetails Values ?, ?, ?, ?, ?)";

     


    Regards David R
    ---------------------------------------------------------------
    Every program eventually becomes rococo, and then rubble. - Alan Perlis
    The only valid measurement of code quality: WTFs/minute.

    ofcourse it works. :)
    Please mark this post as answer if it solved your problem. Happy Programming!
    Monday, August 29, 2011 4:39 AM
  • Hi Kichwa,
    Welcome to the MSDN forum!

    How is it going with the problem?

    If you have any question, please feel free to tell us.

    Have a nice day!


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 30, 2011 6:06 AM
    Moderator