I've created a code that allows the user to sign in (still work in progress) and then add patient records to a database that is displayed in a datagridview. I'm needing the delete button to delete the selected row in the datagridiew and save that to the database. Right now my code runs but it's not actucally deleting anything.
Heres my code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Reports
{
public partial class ReportsForm : Form
{
public ReportsForm()
{
InitializeComponent();
}
private void ReportsForm_Load(object sender, EventArgs e)
{
string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\RecordsDatabase.mdf;Integrated Security=True;Connect Timeout=30";
SqlConnection con = new SqlConnection(connectionString);
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Database_tbl", con);
DataSet ds = new DataSet();
da.Fill(ds, "Database_tbl");
RecordsdataGridView.DataSource = ds.Tables["Database_tbl"].DefaultView;
// TODO: This line of code loads data into the 'recordsDatabaseDataSet.Database_tbl' table. You can move, or remove it, as needed.
this.database_tblTableAdapter.Fill(this.recordsDatabaseDataSet.Database_tbl);
}
private void DisplayData()
{
string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\RecordsDatabase.mdf;Integrated Security=True;Connect Timeout=30";
SqlConnection con = new SqlConnection(connectionString);
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Database_tbl", con);
DataSet ds = new DataSet();
da.Fill(ds, "Database_tbl");
RecordsdataGridView.DataSource = ds.Tables["Database_tbl"].DefaultView;
}
private void btnSubmit_Click(object sender, EventArgs e)
{
string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\RecordsDatabase.mdf;Integrated Security=True;Connect Timeout=30";
SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO Database_tbl (EntryDate, ReportText) VALUES ('" + DateTime.Now + "','" + txtReports.Text + "')", con);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Report Successfully Added");
DisplayData();
txtReports.Text = "";
}
private void RecordsdataGridView_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (RecordsdataGridView.Columns[e.ColumnIndex].Name == "Edit")
{
int rowIndex = RecordsdataGridView.CurrentCell.RowIndex;
txtReports.Text = RecordsdataGridView.Rows[rowIndex].Cells[2].Value.ToString();
lblRowID.Text = RecordsdataGridView.Rows[rowIndex].Cells[0].Value.ToString();
btnUpdate.Visible = true;
btnSubmit.Visible = false;
}
if (RecordsdataGridView.Columns[e.ColumnIndex].Name == "Cancel")
{
txtReports.Text = "";
}
else if (RecordsdataGridView.Columns[e.ColumnIndex].Name == "Delete")
{
string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\RecordsDatabase.mdf;Integrated Security=True;Connect Timeout=30";
DialogResult result = MessageBox.Show("Are you sure you want to delete this patient report?", "Conformation", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("DELETE FROM Database_tbl WHERE EntryID = @EntryID ", con);
con.Open();
cmd.Parameters.AddWithValue("@EntryID", lblRowID.Text);
cmd.ExecuteNonQuery();
con.Close();
DisplayData();
}
else if (result == DialogResult.No)
{
return;
}
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\RecordsDatabase.mdf;Integrated Security=True;Connect Timeout=30";
SqlConnection con = new SqlConnection(connectionString);
con.Open();
using (SqlCommand cmd = new SqlCommand("Update Database_tbl set ReportText = @ReportText Where EntryID = @EntryID", con))
{
cmd.Parameters.AddWithValue("@ReportText", txtReports.Text);
cmd.Parameters.AddWithValue("@EntryID", lblRowID.Text);
cmd.ExecuteNonQuery();
}
con.Close();
MessageBox.Show("Report Successfully Updated");
DisplayData();
txtReports.Text = "";
}
private void btnExit_Click(object sender, EventArgs e)
{
Application.Exit();
}
}
}
What I have tried:
I have tried this which it does work and while the applicatin is running it will delete the selected row. However, it doesn't save it to the database so when the application is ran again the selected row that was deleted is back.
Code:
DialogResult result = MessageBox.Show("Are you sure you want to delete this patient report?", "Conformation", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
int rowIndex = RecordsdataGridView.CurrentCell.RowIndex;
RecordsdataGridView.Rows.RemoveAt(rowIndex);
else if (result == DialogResult.No)
{
return;
}