How Do I Delete From A Table

Question

0.00/5 (No votes)

See more:

hey , i want to delete a selected "staff member" (displayed in listbox) / my code

C#

private void button2_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("Data Source=JAMES-PC\\SQLEXPRESS;Initial Catalog=staff;Integrated Security=True");
            con.Open();

            string sql = @"DELETE FROM staff1 where Id=@name;";

            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.AddWithValue("@name", 1);

            cmd.ExecuteNonQuery();
            con.Close();
        }

but when i run the program i click the button (remove) but no luck, doesnt delete it
any help would be appreciated
also the table displayed in the listbox is (name)

Posted 5-Aug-15 22:46pm

jamesmc1535

Updated 5-Aug-15 22:53pm

Suvendu Shekhar Giri

v2

Add a Solution

Comments

Suvendu Shekhar Giri 6-Aug-15 4:55am

but you have hardcoded value for Id to be 1, is it for a purpose?

jamesmc1535 6-Aug-15 5:00am

my friend helped me out with the code and he said i should add that part,so no i didnt insert that part i jus followed advice? / is it needed? how would i know if its needed?

Arasappan 6-Aug-15 5:11am

put a break point on the open { and run the proram

jamesmc1535 6-Aug-15 5:21am

did that,ran the program , got error ? must declare a scalar variable @name

Arasappan 6-Aug-15 5:27am

can u please show ur table and parameters

Arasappan 6-Aug-15 5:28am

example
staff_id bigint;
staff_name nvarchar(10);

jamesmc1535 6-Aug-15 6:14am

okay my table names are ass follows
name / surname / homead / location / position / cellnum / email listbox uses (name)
they all are varchar(50)

Arasappan 6-Aug-15 6:18am

u write in query id=@name please change that where id=@id what about the id on ur table..

jamesmc1535 6-Aug-15 6:22am

i hate sql :D uhm im goin to tell you how i made my table /
started query > create database staff1
use staff1

>create table name(
id primary key identity < is that hat you were refering to ?

Arasappan 6-Aug-15 6:26am

simply write where id=@id

jamesmc1535 6-Aug-15 6:29am

okay my code looks like this now >
SqlConnection con = new SqlConnection("Data Source=JAMES-PC\\SQLEXPRESS;Initial Catalog=staff;Integrated Security=True");
con.Open();

string sql = @"DELETE FROM staff1 where Id=@id;";

SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@id", listBox1.SelectedItem.ToString());

cmd.ExecuteNonQuery();
con.Close();<pre>
but i still get this error >
Conversion failed when converting the nvarchar value 'michsafj' to data type int.

Arasappan 6-Aug-15 6:33am

try this
string sql = @"DELETE FROM staff1 where name=@name;";

jamesmc1535 6-Aug-15 6:35am

Must declare the scalar variable "@name"
lol XD

jamesmc1535 6-Aug-15 6:36am

nevermind fixed it it worked i changed it 2 that but forgot to change
cmd.Parameters.AddWithValue("@id", listBox1.SelectedItem.ToString());
to
cmd.Parameters.AddWithValue("@name", listBox1.SelectedItem.ToString());

Arasappan 6-Aug-15 6:37am

what is your table name,I thing ur not including name column on that

jamesmc1535 6-Aug-15 6:40am

just another small follow up question>when i click delete it deletes but i have to close the form and reopen it again for the listbox to update is there a way to make it able to auto update so when i click remove its removed from the listbox instantly?

Arasappan 6-Aug-15 6:42am

now are u clear with deletion.

jamesmc1535 6-Aug-15 6:44am

yes its working now thanks

Arasappan 6-Aug-15 6:56am

Always moving to delete by means of id so only we use primary key identity,Because it may chance two name of james in the table..

jamesmc1535 6-Aug-15 7:01am

oh thanks man now it makes more sense :) because i wondered what that was so now u cleared that up :)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sreeyush sudhakaran · Accepted Answer · 2015-08-05T23:21:00

Solution 1

C#

private void button2_Click(object sender, EventArgs e)
{
            SqlConnection con = new SqlConnection("Data Source=JAMES-PC\\SQLEXPRESS;Initial Catalog=staff;Integrated Security=True");
            con.Open();
 
            string sql = @"DELETE FROM staff1 where Id=@name;";
 
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.AddWithValue("@name", listBox1.SelectedValue.ToString());
 
            cmd.ExecuteNonQuery();
            con.Close();
}

see if the parameter id is varchar it is enclosed like this:
DELETE FROM staff1 where Id= 'staff1';

Posted 5-Aug-15 23:21pm

sreeyush sudhakaran

Updated 5-Aug-15 23:38pm

v4

Comments

jamesmc1535 6-Aug-15 5:26am

Conversion failed when converting the nvarchar value 'james' to data type int. get a error

sreeyush sudhakaran 6-Aug-15 5:32am

that means you need the id not staff name in the place , for that select both id , staff name from database and then set :
listBox1.DisplayMember = "staffname";//Name of the field as in Database
listBox1.ValueMember = "id";//Name of the field as in Database
listBox1.DataSource = Ds; // A Datasource filled by Querying the database

Arasappan 6-Aug-15 5:31am

cmd.Parameters.AddWithValue("@name", int.Parse(listBox1.SelectedItem.ToString()));
or
cmd.Parameters.AddWithValue("@name", listBox1.SelectedValue.ToString());

Arasappan · Accepted Answer · 2015-08-05T23:23:00

Go to Web Config and paste this

XML

<connectionStrings>
    <add name="constring" connectionString="data source=JAMES-PC\\SQLEXPRESS; initial catalog=staff; persist security info= true; integrated security= true" providerName="System.Data.SqlClient"/>
  </connectionStrings>

and chane the code as follow

private void button2_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constring"].ConnectionString);
            con.Open();
 
            string sql = @"DELETE FROM staff1 where name=@name;";
 
            SqlCommand cmd = new SqlCommand("DELETE FROM staff1 where name=@name", con);
            cmd.Parameters.AddWithValue("@name", 1); or  //cmd.Parameters.AddWithValue("@name", listBox1.SelectedItem.ToString());
 
            cmd.ExecuteNonQuery();
            con.Close();
        }

Always go with id for deletion.Thank You:-)