New Topic/Question
Reply
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??
MultiQuote
| 