How to delete a row from DB (*.mdb) in c#

Question

I have an access DB (.mdb) named: Programs, with one table named: Data. By using the DataGridView I present the data from the table Data. I want to delete a row from the DataGridView and from the DB during runtime. Does anyone know how to do that (using C#)?
Another question I have is, who can i run queries on my DB?
thanks!

Answer 1

Very simple. I suppose in your datagrid you have a check box by choosing which you will be able to choose the rows that you want to delete. And assuming you have a submit button, so after choosing the rows click on the submit button. In the button's click event call the Delete query say delete from tblname where id = @id [@id is the id that will be passed from your grid]

After that just populate the grid e.g. select * from tblname

e.g.

Aspx code

<asp:GridView runat="server" ID="gvTest" Width="100%" AutoGenerateColumns="false">
      <Columns>
      <asp:TemplateField HeaderText="Select"  ItemStyle-HorizontalAlign="Center"  ItemStyle-Width="5%">
        <ItemTemplate>
         <asp:CheckBox ID="chkDel" runat="server" />
        </ItemTemplate> 
       </asp:TemplateField>
       <asp:BoundField  HeaderText="RegID" DataField="RegID" ItemStyle-Width="10%">
        <ItemStyle HorizontalAlign="Left"/>
       </asp:BoundField>        
       <asp:TemplateField HeaderText="Name" ItemStyle-Width="22%" ItemStyle-HorizontalAlign="Left">
       <ItemTemplate>
       <asp:Label ID="lblName" runat="server" Width="100%" Text= '<%# DataBinder.Eval(Container.DataItem, "UserName")%>'></asp:Label>
       </ItemTemplate>
       </asp:TemplateField>          
      </Columns>
     </asp:GridView>

<br>

<asp:Button runat="server" ID="btnSubmit" Text="Submit" OnClick="btnSubmit_Click"></asp:Button>

Submit button cilck event's code

protected void btnSubmit_Click(object sender, EventArgs e)
    {
        for (int count = 0; count < gvTest.Rows.Count; count++ )
        {
            CheckBox chkSelect = (CheckBox)gvTest.Rows[count].FindControl("chkDel");
            if (chkSelect != null)
            {
                if (chkSelect.Checked == true)
                {
                    //Receiveing RegID from the GridView
                    int RegID = Int32.Parse((gvTest.Rows[count].Cells[1].Text).ToString());

                    object result = DeleteRecord(RegID); //DeleteRecord Function will delete the record                   
                }
            }
        }
        PopulateGrid(); //PopulateGrid Function will again populate the grid
    }

   public void DeleteRecord(int RegId)
    {
        string connectionPath = "Data Source=<your data source>;Initial Catalog=<db name>;Integrated Security=True;userid='test';pwd='test'";
        string command = "";
    SqlConnection connection = new SqlConnection(@connectionPath);
        command = "delete from tblname where id = " +  RegId 
    try
    {
        connection.Open();
        SqlCommand cmd = new SqlCommand(command, connection);
        cmd.ExecuteNonQuery();
    }
    catch (SqlException sqlExcep) {} 
    finally
     {
        connection.Close();
     }
    }

    public void PopulateGrid()
    {

        DataTable dt = new DataTable();
    dt = GetRecord();
    if(dt !=null && dt.rows.count>0)
    {
             gvTest.DataSource = dt;
             gvTest.DataBind();
    }

    }

    public DataTable GetRecord()
    {
       string connectionPath = "Data Source=<your data source>;Initial Catalog=<db name>;Integrated Security=True;userid='test';pwd='test'";
        string command = "";
        SqlDataAdapter adapter = new SqlDataAdapter();          
    SqlConnection connection = new SqlConnection(@connectionPath);
    command = "Select * from tblname" ;
    connection.Open();
    SqlCommand sqlCom = new SqlCommand(command, connection);
    adapter.SelectCommand = sqlCom;
    DataTable table = new DataTable();  
    adapter.Fill(table);
    return table;
    }

Hope this makes sense.