index > Windows Forms Data Controls and Databinding > Cannot delete row on T-Sql table after checking CheckBox in datagridview. C# Windows App.

Cannot delete row on T-Sql table after checking CheckBox in datagridview. C# Windows App.

Hi.

I am using VS2008 to create a C# windows app.
I have put a DataGridViewCheckBox column on the dataGridView1 that i placed onto Form1.
I am populating the dataGridView1 using a dataAdapter that calls a sproc on the database. This part is working.

I am wanting the user to be able to select the CheckBoxes as needed, then click a button and have these checked rows deleted from the database table. This part is not working. No errors are being thrown but the rows are not being deleted and i have stepped through the code.

The delete command is a sproc.

here is the create table code.

create table Frames
(
FrameImagePath nvarchar(256) not null,

Constraint PK_FrameImagePath
Primary Key(FrameImagePath)
)

here is the sproc.

create proc deleteFrameImage(@framePath nvarchar(256))
as
begin

if exists(select FrameImagePath from Frames
where FrameImagePath = @framePath
)
begin
delete Frames
where FrameImagePath = @framePath
end

end
go

and here is the C# app code

public partial class Form1 : Form
{
SqlConnection conn;
String myConnectionString;
SqlDataAdapter da;
SqlCommand cmdGetFrames;
SqlCommand cmdDeleteFrames;
SqlCommandBuilder cb;
DataSet dsFrameImages;

public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
// create the connection string to connect to the database.
// it takes in the server name and the database name
myConnectionString = "Data Source=BOB\\SQLEXPRESS;Initial Catalog=Panorama;Integrated Security=True;";
//myConnectionString = "Data Source=D206-48020\\SQLEXPRESS;Initial Catalog=Panorama;Integrated Security=True;";
conn = new SqlConnection(myConnectionString);

}

// populate the DataSet and dataGridView1 with the results
// from the SqlCommand, cmdGetFrames.
private void button1_Click(object sender, EventArgs e)
{
dsFrameImages = new DataSet();

SqlCommand cmdGetFrames = new SqlCommand("getFrameImages", conn);
cmdGetFrames.CommandType = CommandType.StoredProcedure;

da = new SqlDataAdapter(cmdGetFrames);
//da = new SqlDataAdapter("select * from LibraryImages", conn);
cb = new SqlCommandBuilder(da);
da.Fill(dsFrameImages, "Frames");
dataGridView1.DataSource = dsFrameImages.Tables["Frames"];

// you can make it grid readonly.
dataGridView1.ReadOnly = false;
}

// delete row that has been checked in the CheckBox on the dataGridView1.
// Update the DatagridView and update the table in the database
private void button2_Click(object sender, EventArgs e)
{

int n = dataGridView1.SelectedRows.Count;
DataGridViewRowCollection col = new DataGridViewRowCollection(dataGridView1);
// Loop though the table

dsFrameImages.Tables[0].Rows[1].Delete();

// keep a note of the rows that have been checked into a List
// and display the row numbers in a ListBox
List<int> listOfCheckedRows = new List<int>();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
DataGridViewCheckBoxCell cell = row.Cells[0] as DataGridViewCheckBoxCell;
if (cell != null && row.IsNewRow != true && (bool)cell.FormattedValue)
{
int index = row.Index;
listOfCheckedRows.Add(index);
listBox1.Items.Add(index.ToString());

// set the row flag to delete
dsFrameImages.Tables[0].Rows[index].Delete();
}
}

// instantiate a new SqlCommand to call the deleteFrameImage SPROC.
cmdDeleteFrames = new SqlCommand("deleteFrameImage", conn);
cmdDeleteFrames.CommandType = CommandType.StoredProcedure;

// open the SqlConnection to the database
conn.Open();

// state the input parameter that the stored procedure deleteFrameImage takes.
cmdDeleteFrames.Parameters.Add(new SqlParameter("@framePath", "FrameImagePath"));
da.DeleteCommand = cmdDeleteFrames;

//now u can save changes to back end with this da.Update
da.Update(dsFrameImages, "Frames");

conn.Close();

}
}
}

Is there something I have missd out or got wrong?

Regards
Trev.

TrevorF Saturday, September 12, 2009 7:00 AM

Bump.

Can someone have a look at my code please?

Marked As Answer byTrevorF Tuesday, September 15, 2009 1:31 AM

TrevorF Sunday, September 13, 2009 9:29 PM

Finally figured out what was wrong. The parameters that the cmdDeleteFrames take were not stated properly. I missed out the SqlDbType.NVarChar,256, bit.
See below.

// state the input parameter that the stored procedure deleteFrameImage takes.
//****************************************************************************************************
// cmdDeleteFrames.Parameters.Add(new SqlParameter("@framePath", "FrameImagePath")); // this is wrong
//****************************************************************************************************

// state the SPROC input name, it's type and size and the column from the dataGridView it is coming from
cmdDeleteFrames.Parameters.Add(new SqlParameter("@framePath", SqlDbType.NVarChar,256,"FrameImagePath"));

Full code below:

public partial class Form1 : Form
{
private SqlConnection conn;
private String myConnectionString;
private SqlDataAdapter da;
private SqlCommand cmdGetFrames;
private SqlCommand cmdDeleteFrames;
private SqlCommandBuilder cb;
private DataSet dsFrameImages;
private BindingSource bindingSource;

public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
// create the connection string to connect to the database.
// it takes in the server name and the database name
//myConnectionString = "Data Source=BOB\\SQLEXPRESS;Initial Catalog=Panorama;Integrated Security=True;";
myConnectionString = "Data Source=D206-48020\\SQLEXPRESS;Initial Catalog=Panorama;Integrated Security=True;";
conn = new SqlConnection(myConnectionString);
bindingSource = new BindingSource();
}

// populate the DataSet and dataGridView1 with the results
// from the SqlCommand, cmdGetFrames.
private void button1_Click(object sender, EventArgs e)
{
dsFrameImages = new DataSet();

// state the SPROC to be used
SqlCommand cmdGetFrames = new SqlCommand("getUnusedFrameImages", conn);
cmdGetFrames.CommandType = CommandType.StoredProcedure;

da = new SqlDataAdapter(cmdGetFrames);
//da = new SqlDataAdapter("select * from LibraryImages", conn);
cb = new SqlCommandBuilder(da);
da.Fill(dsFrameImages);
//dataGridView1.DataSource = dsFrameImages.Tables["Frames"];

bindingSource.DataSource = dsFrameImages.Tables[0]; // ds.Tables[0];
dataGridView1.DataSource = bindingSource;

// you can make it grid readonly.
dataGridView1.ReadOnly = false;
}

// delete the row that has been checked in the CheckBox on the dataGridView1.
// Update the DatagridView and update the table in the database
private void button2_Click(object sender, EventArgs e)
{

// keep a note of the rows that have been checked into a List
// and display the row numbers in a ListBox
List<int> listOfCheckedRows = new List<int>();

foreach (DataGridViewRow row in dataGridView1.Rows)
{
DataGridViewCheckBoxCell cell = row.Cells[0] as DataGridViewCheckBoxCell;
if (cell != null && row.IsNewRow != true && (bool)cell.FormattedValue)
{
int index = row.Index;
listOfCheckedRows.Add(index);
listBox1.Items.Add(index.ToString());

// set the row flag to delete for the row
dsFrameImages.Tables[0].Rows[index].Delete();
}
}

// instantiate a new SqlCommand to call the deleteFrameImage SPROC.
cmdDeleteFrames = new SqlCommand("deleteFrameImage", conn);
cmdDeleteFrames.CommandType = CommandType.StoredProcedure;

// state the input parameter that the stored procedure deleteFrameImage takes.
//****************************************************************************************************
// cmdDeleteFrames.Parameters.Add(new SqlParameter("@framePath", "FrameImagePath")); // this is wrong
//****************************************************************************************************

// state the SPROC input name, it's type and size and the column from the dataGridView it is coming from
cmdDeleteFrames.Parameters.Add(new SqlParameter("@framePath", SqlDbType.NVarChar,256,"FrameImagePath"));
da.DeleteCommand = cmdDeleteFrames;

//now u can pass on the changes to TSQL database table with this da.Update
da.Update(dsFrameImages);

}
}