HawaiiDragon
asked on
How to write a delete stored procedure
Hi all I have a question. I need to know how to write a delete stored procedure please.
my fields are @Name nvarchar (50), @Phone nvarchar(50), @Birthday datetime. and they exist in the table called tbl_Bday. any help would be great.
my fields are @Name nvarchar (50), @Phone nvarchar(50), @Birthday datetime. and they exist in the table called tbl_Bday. any help would be great.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes it needs to be a stored procedure. Im going to try chapmandews solution reall quick and see what I come up with.
any other ideas would be great too.
any other ideas would be great too.
Well, this is the DELETE portion of a stored procedure
create procedure mydeleteproc
(@BDayID)
as
DELETE FROM tbl_bday
WHERE tbl_BdayID = @BDayID
go
create procedure mydeleteproc
(@BDayID)
as
DELETE FROM tbl_bday
WHERE tbl_BdayID = @BDayID
go
ASKER
okay the procedure was great now im having problems executing it. I have a table and I need the row selected deleted when the user clicks the delete button. It is a datagridview. I think I see my problem but i dont know how to fix it I will put the code below.
private void btnDelete_Click(object sender, EventArgs e)
{
//Inside a try
try
{
SqlParameter[] test = new SqlParameter[3];
// I used the inserts from textboxes and now its in //data grid view row Any Ideas???
test[0] = new SqlParameter("@Name", tbName.Text);
test[1] = new SqlParameter("@Phone", tbPhone.Text);
test[2] = new SqlParameter("@Birthday", dateTimePicker1.Value);
SqlConnection scs = new SqlConnection();
string jaysisawesome = ConfigurationManager.ConnectionStrings["Phone_number_birthday.Properties.Settings.BirthdaysConnectionString"].ToString();
scs.ConnectionString = jaysisawesome;
SqlCommand cmd = new SqlCommand();
//cmd.Connection = scs.ConnectionString;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "NewDeleteCommand";
cmd.Parameters.AddRange(test);
cmd.Connection = scs;
cmd.Connection.Open();
int one = cmd.ExecuteNonQuery();
cmd.Connection.Close();
if (one > -1)
{
MessageBox.Show("Database Updated");
this.Close();
PhoneListForm.ActiveForm.Refresh();
}
else
{
}
}
//Close connection in finally
//Catch problems
//sql command object
//command type
//DataAccess
//Using AppConfig ConnectionString (pull from)
//Params
//ExecuteNonQuery
catch (Exception ex)
{
ex.ToString();
}
this.Close();
PhoneListForm.ActiveForm.Refresh();
}
Is there a Key in this database? If so, you should set up the DataKeyValue for your grid. Then, you can use the SelectedIndexValue property of the grid to pass the ID of the row you want to delete.
Then, instead of putting all this code on the button click event, you would wire it up to the SelectedIndexValueChanged event of the datagrid.
Then, instead of putting all this code on the button click event, you would wire it up to the SelectedIndexValueChanged event of the datagrid.
To be fair, you should probably submit a new question for your new question.
True. In fact, this type of data management is not something I suggest. The way I prefer is using a class with Get, Set, Update, Delete, and Insert methods. Then I like using the combination of ObjectDataSources and DataGrids for this type of work.
But, indeed, that is quite a different topic than how to write a Delete proc! :)
But, indeed, that is quite a different topic than how to write a Delete proc! :)
ASKER
true so I have closed this question and will open antoher in just a moment. Please check it out ... im lost... again :)
I would add however, that if you are expecting only one row to be deleted, you should consider using a datakey value rather than matching parameters. You could argue that a Name, plus a Phone number, plus a birthday are unique, but of course we all know to expect the unexpected in DB design.
So, a "safer" delete statement would be something like
DELETE FROM tbl_bday
WHERE tbl_BdayID = @BDayID