Delete records from database through 3-tier programming

Hi, currently I am doing an online store web application. I am trying to do a delete record w/o the use of primary key.
In Ms sql server 2008 database, it contains all these fields: itemName, itemDesc, category, catID(FK).
itemName     itemDesc                                       category     catID(FK)
dress             Dress with flowers at the side      Floral          1
Skirt               Skirt with flower petals                 Floral          1

This is the database.cs:

 

public bool Delete_ItemsRecords(string 
catID)
        
{
            
SqlCommand cmd_ItemsList = new SqlCommand();

            
cmd_ItemsList.CommandText = 
"[VRM].[DELETE_ItemsRecords]";
            
cmd_ItemsList.CommandType = 
CommandType.StoredProcedure;
            
cmd_ItemsList.Parameters.Clear();

            
SqlParameter sqlParaCatID = new SqlParameter("@catID", SqlDbType.VarChar, 
10);
            
sqlParaCatID.Value = 
catID;
            
cmd_CriticalTermsList.Parameters.Add(sqlParaCatID);

            return 
executeNotQuery(cmd_ItemsList);
        
}

This is the stored procedure:

 ALTER PROCEDURE [OS].[DELETE_ItemsRecords] 
@catID 
varchar(10)
AS
BEGIN
 DELETE FROM OS.Items WHERE catID = 
@catID
END

This is the business logic:

 

protected void 
gvItems_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

vrmdb.Delete_ItemsRecords(gvItems.DataKeys[e.RowIndex].Values[0].ToString());

BindGrid(true);
}

When I try to delete the record I am unable to do it. I try it for those grid view that uses PK to delete works. May I know how to resolve it? Thanks!

4 Replies

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

Becky Bloomwood15y ago
Okay.Thanks.I add a new field and set PK to it:)
0
Krishna Garad15y ago
In this you can create one productid field which is primary key and on the basis of this product id you can selete unique record. If you tryed to delete on the basis of CatId field it will delete all the product under same category. That's better user ProductId field as primary key.
0
Becky Bloomwood15y ago
I tried to make use of FK but cant and it seems that FK is the only option that I can use. What if I make use of itemName, how do I declare it?

Thks:)You guys have been so helpful.Appreciate it alot.
0
Jean Paul15y ago
I could prefer two solutions:
1) Use the data to get correct id and use id to delete the record

or

2) Get the row number of the record and use that for deletion
http://www.dbforums.com/db2/1625506-sql0158n-during-delete-row_number.html
0