Delete one or multiple selected rows usind Database

Israel

Hi,

I put whole my code to be clear. Then I would like to delete multiple selected rows.

But when I run my program and select one or more rows its shows a error message saying "object doesnt be converted DBNull em others types".

Its sturk on this line: where Convert.ToBoolean(row.Cells["checkBoxColumn"].Value) == true

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 WindowsFormsApplication1

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

this.BindGrid();

}

private void BindGrid()

{

dataGridView1.DataSource = null;

using (SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Test\\WindowsFormsApplication1\\Database1.mdf;Integrated Security=True"))

{

using (SqlCommand cmd = new SqlCommand("SELECT * FROM Test", con))

{

cmd.CommandType = CommandType.Text;

using (SqlDataAdapter sda = new SqlDataAdapter(cmd))

{

using (DataTable dt = new DataTable())

{

sda.Fill(dt);

dataGridView1.DataSource = dt;

}

// SqlConnection conn = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Test\\WindowsFormsApplication1\\Database1.mdf;Integrated Security=True");

//SqlCommand comm;

//string connstr = @"Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Test\\WindowsFormsApplication1\\Database1.mdf;Integrated Security=True";

private void button1_Click(object sender, EventArgs e)

{

List selectedRows = (from row in dataGridView1.Rows.Cast()

where Convert.ToBoolean(row.Cells["checkBoxColumn"].Value) == true //Sturks here

select row).ToList();

if (MessageBox.Show(string.Format("Do you want to delete {0} rows?", selectedRows.Count), "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)

{

foreach (DataGridViewRow row in selectedRows)

{

using (SqlConnection con = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = C:\\Test\\WindowsFormsApplication1\\Database1.mdf; Integrated Security = True"))

{

using (SqlCommand cmd = new SqlCommand("DELETE FROM test WHERE ID = @ID", con))

{

cmd.CommandType = CommandType.Text;

cmd.Parameters.AddWithValue("@ID", row.Cells["ID"].Value);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

}

this.BindGrid();

}

//using (SqlConnection conn = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Test\\WindowsFormsApplication1\\Database1.mdf;Integrated Security=True")) //ADDED

}

private void Form1_Load(object sender, EventArgs e)

{

string connectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Test\\WindowsFormsApplication1\\Database1.mdf;Integrated Security=True";

string sql = "SELECT * FROM test";

SqlConnection connection = new SqlConnection(connectionString);

SqlDataAdapter dataadapter = new SqlDataAdapter(sql, connection);

DataSet ds = new DataSet();

connection.Open();

dataadapter.Fill(ds, "test");

connection.Close();

dataGridView1.DataSource = ds;

dataGridView1.DataMember = "test";

dataGridView1.CurrentCell = dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[0];

this.dataGridView1.RowsDefaultCellStyle.BackColor = Color.AliceBlue;

this.dataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige;

}

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Israel8y ago
@Junith Thakkar

The same error message come back again. What's happen???

The type arguments for method 'EnumerableRowCollectionExtensions.Cast(EnumerableRowCollection)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

List selectedRows = dataGridView1.Rows.Cast().where(row => row.Cells["checkBoxColumn"] != DBNull.Value && row.Cells["checkBoxColumn"] == true).select(row => row).ToList();
if (MessageBox.Show(string.Format("Do you want to delete {0} rows?", selectedRows.Count), "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
foreach (DataGridViewRow row in selectedRows)
{
using (SqlConnection conn = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = C:\\Test\\WindowsFormsApplication1\\Database1.mdf; Integrated Security = True"))
{
using (SqlCommand comm = new SqlCommand("DELETE FROM test WHERE ID = @ID", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ID", row.Cells["ID"].Value);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
Jenith Thakkar8y ago
Please remove this because we check this condition in List so no need to check

if (row.Cells["checkBoxColumn"] == DBNull.Value)
{

Also

Please Replace List selectedRows to List selectedRows
Israel8y ago
@ Jenith Thakkar

These are the whole code. Now the problem is where colored in red. Erro message:

1) The name 'row' does not exist in the current context
2) The type arguments for method 'EnumerableRowCollectionExtensions.Cast(EnumerableRowCollection)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

private void button1_Click(object sender, EventArgs e)
{
if (row.Cells["checkBoxColumn"] == DBNull.Value)
{

List selectedRows = dataGridView1.Rows.Cast().where(row => row.Cells["checkBoxColumn"] != DBNull.Value && row.Cells["checkBoxColumn"] == true).select(row => row).ToList();
if (MessageBox.Show(string.Format("Do you want to delete {0} rows?", selectedRows.Count), "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
foreach (DataGridViewRow row in selectedRows)
{
using (SqlConnection con = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = C:\\Test\\WindowsFormsApplication1\\Database1.mdf; Integrated Security = True"))
{
using (SqlCommand cmd = new SqlCommand("DELETE FROM test WHERE ID = @ID", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ID", row.Cells["ID"].Value);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
}
}
Jenith Thakkar8y ago
Please Change List from

List selectedRows =dataGridView1.Rows.Cast().where(row => row.Cells["checkBoxColumn"] != DBNull.Value && row.Cells["checkBoxColumn"] == true).select(row => row).ToList();

to

List selectedRows = dataGridView1.Rows.Cast().where(row => row.Cells["checkBoxColumn"] != DBNull.Value && row.Cells["checkBoxColumn"] == true).select(row => row).ToList();
Israel8y ago
@Jenith Thakkar

It's sound near good.

But it's sturk on the first line just on the row. Its says "The name 'row' does not exist in the current context" :

if (row.Cells["checkBoxColumn"]== DBNull.Value // Just on this line

Thanx.
Israel8y ago
@Jenith Thakkar

See what I do as you codes sent. But its sturks on List, Cast and selectRows

These are the errors messages its shows:

1) foreach statement cannot operate on variables of type 'System.Collections.Generic.List' because 'System.Collections.Generic.List' does not contain a public definition for 'GetEnumerator'

2) The type arguments for method 'EnumerableRowCollectionExtensions.Cast(EnumerableRowCollection)' cannot be inferred from the usage. Try specifying the type arguments explicitly

3) Using the generic type 'List' requires 1 type arguments

private void button1_Click(object sender, EventArgs e)
{
List selectedRows = dataGridView1.Rows.Cast().where(row => row.Cells["checkBoxColumn"] != DBNull.Value && row.Cells["checkBoxColumn"] == true).select(row => row).ToList();
if (MessageBox.Show(string.Format("Do you want to delete {0} rows?", selectedRows.Count), "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
foreach (DataGridViewRow row in selectedRows)
{
using (SqlConnection con = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = C:\\Test\\WindowsFormsApplication1\\Database1.mdf; Integrated Security = True"))
{
using (SqlCommand cmd = new SqlCommand("DELETE FROM test WHERE ID = @ID", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ID", row.Cells["ID"].Value);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
Jenith Thakkar8y ago
List selectedRows=dataGridView1.Rows.Cast().where(row=>row.Cells["checkBoxColumn"] != DBNull.Value && row.Cells["checkBoxColumn"] == true).select(row=>row).ToList();

At Place of

List selectedRows = (from row in dataGridView1.Rows.Cast()
where Convert.ToBoolean(row.Cells["checkBoxColumn"].Value) == true //Sturks here
select row).ToList();

So Code will be

List selectedRows=dataGridView1.Rows.Cast().where(row=>row.Cells["checkBoxColumn"] != DBNull.Value && row.Cells["checkBoxColumn"] == true).select(row=>row).ToList();
if (MessageBox.Show(string.Format("Do you want to delete {0} rows?", selectedRows.Count), "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
foreach (DataGridViewRow row in selectedRows)
{
using (SqlConnection con = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = C:\\Test\\WindowsFormsApplication1\\Database1.mdf; Integrated Security = True"))
{
using (SqlCommand cmd = new SqlCommand("DELETE FROM test WHERE ID = @ID", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ID", row.Cells["ID"].Value);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
Israel8y ago
@Jenith Takkar

I do exactly that. In runtime I received this error message:
The name 'row' does not exist in the current context
Jenith Thakkar8y ago
check DBNull With Condition using this if condition like below

if (row.Cells["checkBoxColumn"]== DBNull.Value)
{
List selectedRows = (from row in dataGridView1.Rows.Cast()
where Convert.ToBoolean(row.Cells["checkBoxColumn"].Value) == true //Sturks here
select row).ToList();
if (MessageBox.Show(string.Format("Do you want to delete {0} rows?", selectedRows.Count), "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
foreach (DataGridViewRow row in selectedRows)
{
using (SqlConnection con = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = C:\\Test\\WindowsFormsApplication1\\Database1.mdf; Integrated Security = True"))
{
using (SqlCommand cmd = new SqlCommand("DELETE FROM test WHERE ID = @ID", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ID", row.Cells["ID"].Value);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}