2 Replies - 1562 Views - Last Post: 02 February 2010 - 10:04 PM Rate Topic: -----

#1 ichigo_cin  Icon User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 5
  • Joined: 28-January 10

i can't update and delete data....

Posted 02 February 2010 - 01:08 PM

hi every one...

i can't update and delete data in database. Database that i used is Microsoft Access

i archive my code here..

how can i correct my code??

thanks for your advice.

Attached File(s)

  • Attached File  code.txt (10.88K)
    Number of downloads: 80

Is This A Good Question/Topic? -1
  • +

Replies To: i can't update and delete data....

#2 SwiftStriker00  Icon User is offline

  • No idea why my code works
  • member icon

Reputation: 433
  • View blog
  • Posts: 1,596
  • Joined: 25-December 08

Re: i can't update and delete data....

Posted 02 February 2010 - 01:35 PM

ichigo, from now on please just post your code like this:

Code Form1
namespace ASC
{
    public partial class Form1 : Form
    {
        public OleDbConnection database;
         SerialPort srport = new SerialPort();
         DataGridViewButtonColumn editButton;
         DataGridViewButtonColumn deleteButton;
         string UIDString;
        public Form1()
        {
            InitializeComponent();
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ACSdb.accdb";
            try
            {
                database = new OleDbConnection(connectionString);
                database.Open();
                string queryString = "SELECT USER_ACCOUNT.[UID], USER_ACCOUNT.[password], USER_ACCOUNT.[title], USER_ACCOUNT.[fName], USER_ACCOUNT.[lName] FROM USER_ACCOUNT;";
                loadDataGrid(queryString);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }
        }

        OleDbConnection conn = new OleDbConnection();

        OleDbDataAdapter da;
        DataSet ds = new DataSet();
        String connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\CINDY\\Desktop\\ASC\\ASC\\ASC\\ACSdb.accdb";
        Boolean find = false;

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'aCSdbDataSet3.USER_ACCOUNT' table. You can move, or remove it, as needed.
            this.uSER_ACCOUNTTableAdapter2.Fill(this.aCSdbDataSet3.USER_ACCOUNT);
            // TODO: This line of code loads data into the 'aCSdbUSER_ACCOUNTDataSet.USER_ACCOUNT' table. You can move, or remove it, as needed.
            this.uSER_ACCOUNTTableAdapter1.Fill(this.aCSdbUSER_ACCOUNTDataSet.USER_ACCOUNT);

            OleDbCommand comAdd = new OleDbCommand();

            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }

            conn.ConnectionString = connStr;
            conn.Open();

            // TODO: This line of code loads data into the 'aCSdbDataSet1.DOORS' table. You can move, or remove it, as needed.
            this.dOORSTableAdapter.Fill(this.aCSdbDataSet1.DOORS);
            // TODO: This line of code loads data into the 'aCSdbUSERNAMEDataSet.USER_ACCOUNT' table. You can move, or remove it, as needed.
            this.uSER_ACCOUNTTableAdapter.Fill(this.aCSdbUSERNAMEDataSet.USER_ACCOUNT);
            showAccess();

          
            groupBox1.Enabled = false;
            groupBox2.Enabled = false;
            richTextBox1.Text = "";

            // insert edit button into datagridview
            editButton = new DataGridViewButtonColumn();
            editButton.HeaderText = "Edit";
            editButton.Text = "Edit";
            editButton.UseColumnTextForButtonValue = true;
            editButton.Width = 80;
            dataGridView2.Columns.Add(editButton);

            // insert delete button to datagridview
            deleteButton = new DataGridViewButtonColumn();
            deleteButton.HeaderText = "Delete";
            deleteButton.Text = "Delete";
            deleteButton.UseColumnTextForButtonValue = true;
            deleteButton.Width = 80;
            dataGridView2.Columns.Add(deleteButton);
        }

         public void loadDataGrid(string sqlQueryString)
        {
            OleDbCommand SQLQuery = new OleDbCommand();
            DataTable data = null;
            dataGridView1.DataSource = null;
            SQLQuery.Connection = null;
            OleDbDataAdapter dataAdapter = null;
            dataGridView1.Columns.Clear(); // <-- clear columns
            //---------------------------------
            SQLQuery.CommandText = sqlQueryString;
            SQLQuery.Connection = database;
            data = new DataTable();
            dataAdapter = new OleDbDataAdapter(SQLQuery);
            dataAdapter.Fill(data);
            dataGridView1.DataSource = data;
            dataGridView1.AllowUserToAddRows = false; // remove the null line
            dataGridView1.ReadOnly = true;
        
        }
      

        #region Close database connection
        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            database.Close();
        }
        #endregion

        #region Add to USER_ACCESS
        private void button1_Click(object sender, EventArgs e)
        {
            OleDbCommand comAdd = new OleDbCommand();
             
             if(conn.State == ConnectionState.Open)
             {
                 conn.Close();
             }

            conn.ConnectionString = connStr;
            conn.Open();

            String insertStr;
            insertStr = "INSERT INTO USER_ACCESS(UID,DID,dateAccess,timeAccess)";
            insertStr += "VALUES('"+comboBox1.SelectedValue+"','"+comboBox2.SelectedValue+"','"+dateTimePicker1.Value.Date+"','"+dateTimePicker1.Value.TimeOfDay+"')";

            comAdd.CommandType = CommandType.Text;
            comAdd.CommandText = insertStr;
            comAdd.Connection = conn;
            comAdd.ExecuteNonQuery();

            showAccess();
            //showImage();
        }

        private void showAccess()
        {
            String SQLshow = "SELECT * FROM USER_ACCESS";

            if (find == true)
            { 
                ds.Tables["Accession"].Clear(); 
            }

            da = new OleDbDataAdapter(SQLshow, conn);
            da.Fill(ds, "Accession");

            if (ds.Tables["Accession"].Rows.Count != 0)
            {
                find = true;
                dataGridView1.ReadOnly = true;
                dataGridView1.DataSource = ds.Tables["Accession"];
            }
            else
            {
                find = false;
            }

        }

        #endregion

        private void dataGridView2_CellContentClick_1(object sender, DataGridViewCellEventArgs e)
        {
            
           string queryString = "SELECT USER_ACCOUNT.[UID], USER_ACCOUNT.[password], USER_ACCOUNT.[title], USER_ACCOUNT.[fName], USER_ACCOUNT.[lName] FROM USER_ACCOUNT;";
            int currentRow = int.Parse(e.RowIndex.ToString());
            try
            {
                UIDString = dataGridView2[0, currentRow].Value.ToString();
            }
            catch (Exception ex) { }
            // edit button
            if (dataGridView2.Columns[e.ColumnIndex] == editButton && currentRow >= 0)
            {
               
                string UID = dataGridView2[0, currentRow].Value.ToString();
                string password = dataGridView2[1, currentRow].Value.ToString();
                string title = dataGridView2[2, currentRow].Value.ToString();
                string fname = dataGridView2[3, currentRow].Value.ToString();
                string lname = dataGridView2[4, currentRow].Value.ToString();
                //runs form 2 for editing    
                Form2 f2 = new Form2();
                f2.UID = UID;
                f2.Password = password;
                f2.Title = title;
                f2.fName = fname;
                f2.lName = lname;
                f2.Show();
                dataGridView2.Update();
                //showName();
               

            }
            // delete button
            else if (dataGridView2.Columns[e.ColumnIndex] == deleteButton && currentRow >= 0)
            {
                // delete sql query
                string queryDeleteString = "DELETE USER_ACCOUNT.* FROM USER_ACCOUNT WHERE USER_ACCOUNT.[UID] = '" + UIDString + "';";
                OleDbCommand sqlDelete = new OleDbCommand();
                sqlDelete.CommandText = queryDeleteString;
                sqlDelete.Connection = database;
                sqlDelete.ExecuteNonQuery();
                loadDataGrid(queryString);
                //showName();

            }
             
           
        }
        #region Add UserName
        private void button2_Click(object sender, EventArgs e)
        {
        
            OleDbCommand Add = new OleDbCommand();


            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }

            conn.ConnectionString = connStr;
            conn.Open();
           
            OleDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = "INSERT INTO USER_ACCOUNT([UID], [Password], [title], [fName], [lName]) VALUES('" + maskedTextBox1.Text + "', '" + maskedTextBox2.Text + "',  '" + maskedTextBox3.Text + "', '" + maskedTextBox4.Text + "', '" + maskedTextBox5.Text + "')";

            OleDbDataReader reader = cmd.ExecuteReader();
            loadDataGrid1(queryString);

            showName();
            
            
           maskedTextBox1.Clear();
            maskedTextBox2.Clear();
            maskedTextBox3.Clear();
            maskedTextBox4.Clear();
            maskedTextBox5.Clear();

        }

        
        #endregion

        #region
        private void button4_Click(object sender, EventArgs e)
        {
             string queryString = "SELECT USER_ACCOUNT.[UID], USER_ACCOUNT.[password], USER_ACCOUNT.[title], USER_ACCOUNT.[fName], USER_ACCOUNT.[lName] FROM USER_ACCOUNT;";
            loadDataGrid(queryString);        }
       
      
        #endregion
        }
        
}



form 2
namespace ASC
{

    public partial class Form2 : Form
    {
        public string UID, Password, Title, fName, lName;
        public Form2()
        {
            InitializeComponent();
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            maskedTextBox1.Text = UID;
            maskedTextBox2.Text = Password;
            maskedTextBox3.Text = Title;
            maskedTextBox4.Text = fName;
            maskedTextBox5.Text = lName;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Form1 f1 = new Form1();
            string SQLUpdateString = "UPDATE USER_ACCOUNT SET USER_ACCOUNT.[password]='" + maskedTextBox2.Text + "', USER_ACCOUNT.[title]='" + maskedTextBox3.Text + "', USER_ACCOUNT.[fName]='" + maskedTextBox4.Text + "', USER_ACCOUNT.[lName]='" + maskedTextBox5.Text + "' WHERE USER_ACCOUNT.[UID]='" + UID + "';";
            OleDbCommand SQLCommand = new OleDbCommand();
            SQLCommand.CommandText = SQLUpdateString;
            SQLCommand.Connection = f1.database;
            int response = SQLCommand.ExecuteNonQuery();
            MessageBox.Show("Update successful!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
            Close();
        }

        private void button1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                button1_Click(null, null);
            }
        }

        
    }
}



Also what errors are you getting? Do you know if you data is correct before you send the request to the server?
Was This Post Helpful? 0
  • +
  • -

#3 ichigo_cin  Icon User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 5
  • Joined: 28-January 10

Re: i can't update and delete data....

Posted 02 February 2010 - 10:04 PM

The code don't have any error.

but when i run this programme it can't delete and update user profile in table.

i don't know where it's wrong.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1