CODEWITHSUNDEEP
c#visual-studio
TechJak
TechJak

Reputation: 53

Deleting row from Database via Combobox

I have a combobox which is looking into my Database and showing me the names of the users, I also want to look at the the dID besides that, this task I am finding difficult to achieve. I have two tables, so I presume that I have to repeat this SQL statement. Please look into this code !

All in all what I want to achieve is to Just to delete the user from the database! the members personal info form table_1 and the info about members paid amount and year dID form the table_2 ! One delete command deletes one row for members info in table_1 & table_2.

Any other methods or ideas welcome ! 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace CemiyetAidatSistem
{
    public partial class DeleteUser : Form
    {
        String conStr ="My connection string";
        public UyeSil()
        {
            InitializeComponent();
        }
        SqlDataAdapter da;
        DataSet ds = new DataSet();
        private void UserDelete_Load(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(conStr);
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT FullName FROM Members";
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Members");
            cmbUyeSil.DataSource = ds.Tables["Members"];
            cmbUyeSil.DisplayMember = "DistinctID";
            cmbUyeSil.ValueMember = "FullName";

        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(conStr);
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "DELETE FROM Members WHERE ID = '" + cmbUyeSil.SelectedValue + "'";
            da = new SqlDataAdapter(cmd);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            MessageBox.Show(" Member with dID \"" + cmbUyeSil.SelectedValue + "\"is deleted");

        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

    }
}

Upvotes: 1

Views: 1628

Answers (2)

Melanie
Melanie

Reputation: 3111

OK, belxandre is completely correct; you shouldn't delete these records. You should have the ability to mark them Inactive or something. Having said that, you can create a SqlCommand that does the delete, add the necessary parameters to it, then call it on your DataAdapter:

SqlCommand delcomm = new SqlCommand();
delcomm.CommandType = CommandType.StoredProcedure; //I'd do it with an sp, up to you
delcomm.CommandText = "sp_delUser";
SqlParameter delParam = new SqlParameter("@dID", SqlDbType.Int);//I'm assuming this is the column you need and the right datatype
delcomm.Parameters.Add(delParam);
da.DeleteCommand = delcomm;
da.AcceptChangesDuringUpdate = false;
da.Update(UserTable);//you'll need to set up a DataTable with the right data structure
UserTable.AcceptChanges();

This is totally off the top of my head; you may need to fiddle with this a bit, but it's generally what you need to do, if I understand your question. Having cascading deletes on your tables as suggested is also a good idea.

Upvotes: 0

Simon Martin
Simon Martin

Reputation: 4231

If you have cascading delete on then when you delete the user this will cause the row(s) in table 2 to also be deleted

Upvotes: 1

Related Questions