1 Replies - 1163 Views - Last Post: 04 February 2010 - 11:19 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

Problem when edit and delete record in database...

Posted 04 February 2010 - 10:35 AM

hi every one...

i can't update and delete data in database. Database that i used is Microsoft Access
when i edit user profile, Form2 is show for edit profile's user but when i refresh gridview2 there's nothing happen.
profile's user in database is not edit.

how can i correct my code??

Form1:
Form1 :
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO.Ports;

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);
        }

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

        #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();
           
        }

        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 RS232
        private void Connect_Click(object sender, EventArgs e)
        {
            if (connect.Text == "เชื่อมต่อ")
            {
                srport.PortName = comport.Text;
                connect.Text = "หยุดการเชื่อมต่อ";
                timer1.Enabled = true;
                groupBox1.Enabled = true;
                groupBox2.Enabled = true;
                comport.Enabled = false;
                richTextBox1.Text = "";
                srport.Open();

            }
            else
            {
                connect.Text = "เชื่อมต่อ";
                timer1.Enabled = false;
                groupBox1.Enabled = false;
                groupBox2.Enabled = false;
                comport.Enabled = true;
                richTextBox1.Text = "";
                srport.Close();

            }

        }
        string readport, readportold;
        private void timer1_Tick(object sender, EventArgs e)
        {
            readport = srport.ReadExisting();            
            
            if (readport == "")
            {
                
            }
            else
            {
                readportold = readport;
                richTextBox1.Text = "datain : " + readportold;
            }
        }

        private void send_Click(object sender, EventArgs e)
        {
            srport.Write(textBox1.Text);
            textBox1.Text = "";
        }
        #endregion

        #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();

        }

        private void showName()
        {
            String SQLshowName = "SELECT * FROM USER_ACCOUNT";
            if (find == true)
            {
                //ds1.Tables["Accession"].Clear();
                ds.Tables.Clear();
            }

            da = new OleDbDataAdapter(SQLshowName, conn);
            da.Fill(ds, "Accession");
            if (ds.Tables["Accession"].Rows.Count != 0)
            {
                find = true;
                dataGridView2.ReadOnly = true;
                dataGridView2.DataSource = ds.Tables["Accession"];
            }
            else
            {
                find = false;
            }

        }

        /*
        private void showComboBox()
        {
            String combo = "SELECT * FROM USER_ACCOUNT";

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

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

        }
         * */
        #endregion

        #region Search
        private void button3_Click(object sender, EventArgs e)
        {           
           
        }
        #endregion

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




Form2 :
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;

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);
            }
        }




the code is doesn't get any error.
Could any one tell me where it's wrong or something missing??

Is This A Good Question/Topic? 0
  • +

Replies To: Problem when edit and delete record in database...

#2 vivekrane1986  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 102
  • Joined: 02-August 08

Re: Problem when edit and delete record in database...

Posted 04 February 2010 - 11:19 PM

Hi,

Have you debug the application? What are the return values you are getting?


Regards,
Vivek Rane
Emance infotech
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1