i'm trying to save changes made to a gridView into an Sql database permanently upon button click. Currently what the program does is show all the changes done on the gridview but when i check the database nothing has happened.
Heres my code:
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
FillData();
}
void FillData()
{
//1
//open connection
using (SqlCeConnection c = new SqlCeConnection(Properties.Settings.Default.MyDatabase_1ConnectionString))
{
c.Open();
//2
//create new DataAdapter
using (SqlCeDataAdapter a = new SqlCeDataAdapter("SELECT * FROM Tasks", c))
{
//3
//use DataAdapter to fill DataTable
DataTable t = new DataTable();
a.Fill(t);
//4
//Render data onto the screen
dataGridView1.DataSource = t;
}
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
private void Addbutton_Click(object sender, EventArgs e)
{
DataSet tasksDataSet = new DataSet();
SqlCeDataAdapter da;
SqlCeCommandBuilder cmdBuilder;
try
{
using(SqlCeConnection sqlConn = new SqlCeConnection(Properties.Settings.Default.MyDatabase_1ConnectionString))
{
da = new SqlCeDataAdapter("select * from Tasks ", sqlConn);
//a = new SqlCeDataAdapter("SELECT * FROM Tasks", sqlConn);
cmdBuilder = new SqlCeCommandBuilder(da);
da.Fill(tasksDataSet, "Tasks");
string query = "INSERT INTO Tasks (TaskName, TaskDescription, TaskComments, TaskFrom, TaskStatus, TaskDayIn, TaskDayOut) VALUES (@TaskName, @TaskDescription, @TaskComments, @TaskFrom, @TaskStatus,@TaskDayIn, @TaskDayOut)";
SqlCeCommand cmd = new SqlCeCommand(query, sqlConn);
cmd.Connection.Open();
//cmd.Parameters.Add("@TaskID", SqlDbType.NVarChar, 100).Value = taskIDText.Text;
cmd.Parameters.Add("@TaskName", SqlDbType.NVarChar, 100).Value = taskNameText.Text;
cmd.Parameters.Add("@TaskDescription", SqlDbType.NVarChar, 100).Value = taskDescriptionText.Text;
cmd.Parameters.Add("@TaskComments", SqlDbType.NVarChar, 100).Value = taskCommentsText.Text;
cmd.Parameters.Add("@TaskFrom", SqlDbType.NVarChar, 100).Value = taskFromText.Text;
cmd.Parameters.Add("@TaskStatus", SqlDbType.NVarChar, 100).Value = taskStatusText.Text;
cmd.Parameters.Add("@TaskDayIn", SqlDbType.NVarChar, 100).Value = taskInDateText.Text;
cmd.Parameters.Add("@TaskDayOut", SqlDbType.NVarChar, 100).Value = taskDateOutText.Text;
da.Update(tasksDataSet, "Tasks");
int rows = cmd.ExecuteNonQuery();
} MessageBox.Show("Entry was succesful");
}
catch (SqlCeException ex)
{
MessageBox.Show("There is an Error" + ex);
}
finally
{
//MessageBox.Show("Connection Closed");
FillData();
//Setting the textboxes back to original state
//taskIDText.Text = "";
taskNameText.Text = "";
taskDescriptionText.Text = "";
taskCommentsText.Text = "";
taskFromText.Text = "";
taskStatusText.Text = "";
taskInDateText.Text = "";
taskDateOutText.Text = "";
}
}
private void button1_Click(object sender, EventArgs e)
{
Form2 form2 = new Form2();
form2.Show();
}
private void button2_Click(object sender, EventArgs e)
{
Form3 form3 = new Form3();
form3.Show();
}
}
Blockquote
Heres my second Form:
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}
private void Form2_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the '_MyDatabase_1DataSet1.Tasks' table. You can move, or remove it, as needed.
this.tasksTableAdapter.Fill(this._MyDatabase_1DataSet1.Tasks);
}
private void DeleteEntryButton_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Do you want to delete this row ?", "Delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
//dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
//sAdapter.Update(sTable);
string connectionString = "Data Source=C:\\Users\\90123155\\Documents\\Visual Studio 2010\\Projects\\WorkTrackingSystem\\WorkTrackingSystem\\MyDatabase#1.sdf";
//string sql = "SELECT * FROM Tasks";
SqlCeConnection connection = new SqlCeConnection(connectionString);
SqlCeCommand delcmd = new SqlCeCommand();
if (dataGridView1.Rows.Count > 1 && dataGridView1.SelectedRows[0].Index != dataGridView1.Rows.Count - 1)
{
delcmd.CommandText = "DELETE FROM Tasks WHERE taskID=" + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "";
connection.Open();
delcmd.Connection = connection;
delcmd.ExecuteNonQuery();
connection.Close();
dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
MessageBox.Show("Row Deleted");
}
}
}
}
Blockquote
public partial class Form3 : Form
{
SqlCeCommand sCommand;
SqlCeDataAdapter sAdapter;
SqlCeCommandBuilder sBuilder;
DataSet sDs;
DataTable sTable = new DataTable("Tasks");
BindingSource bindingSource1 = new BindingSource();
public Form3()
{
InitializeComponent();
}
private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
private void Form3_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the '_MyDatabase_1DataSet2.Tasks' table. You can move, or remove it, as needed.
this.tasksTableAdapter.Fill(this._MyDatabase_1DataSet2.Tasks);
}
private void SaveChangesButton_Click(object sender, EventArgs e)
{
string connectionString = "Data Source=C:\\Users\\90123155\\Documents\\Visual Studio 2010\\Projects\\WorkTrackingSystem\\WorkTrackingSystem\\MyDatabase#1.sdf";
string sql = "SELECT * FROM Tasks";
SqlCeConnection connection = new SqlCeConnection(connectionString);
connection.Open();
sCommand = new SqlCeCommand(sql, connection);
sAdapter = new SqlCeDataAdapter(sCommand);
sBuilder = new SqlCeCommandBuilder(sAdapter);
sDs = new DataSet();
sAdapter.Fill(sDs, "Tasks");
sTable = sDs.Tables["Tasks"];
connection.Close();
dataGridView1.DataSource = sDs.Tables["Tasks"];
dataGridView1.ReadOnly = true;
saveButton.Enabled = false;
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
}
private void DeleteButton_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Do you want to delete this row ?", "Delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
//dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
//sAdapter.Update(sTable);
string connectionString = "Data Source=C:\\Users\\90123155\\Documents\\Visual Studio 2010\\Projects\\WorkTrackingSystem\\WorkTrackingSystem\\MyDatabase#1.sdf";
//string sql = "SELECT * FROM Tasks";
SqlCeConnection connection = new SqlCeConnection(connectionString);
SqlCeCommand delcmd = new SqlCeCommand();
if (dataGridView1.Rows.Count > 1 && dataGridView1.SelectedRows[0].Index != dataGridView1.Rows.Count - 1)
{
delcmd.CommandText = "DELETE FROM Tasks WHERE taskID=" + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "";
connection.Open();
delcmd.Connection = connection;
delcmd.ExecuteNonQuery();
connection.Close();
dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
MessageBox.Show("Row Deleted");
}
}
}
private void saveButton_Click(object sender, EventArgs e)
{
using (SqlCeConnection c = new SqlCeConnection(Properties.Settings.Default.MyDatabase_1ConnectionString))
{
c.Open();
//2
//create new DataAdapter
using (SqlCeDataAdapter a = new SqlCeDataAdapter("SELECT * FROM Tasks", c))
{
//3
//use DataAdapter to fill DataTable
DataTable t = new DataTable();
a.Fill(t);
//4
//Render data onto the screen
a.Update(t);
dataGridView1.ReadOnly = true;
saveButton.Enabled = false;
saveButton.Enabled = true;
deleteButton.Enabled = true;
dataGridView1.DataSource = t;
}
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
}
Aucun commentaire:
Enregistrer un commentaire