Solved

mssql delete if exists problem

Posted on 2010-09-24
15
304 Views
Last Modified: 2012-05-10
hi
i want to delete all the record from the table if they exists problem is my SP isnt doing its only inserting one record instead of all the other records please advice
/****** Object:  StoredProcedure [dbo].[UpdateSecEventsandSchedle]    Script Date: 09/24/2010 13:58:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[UpdateSecEventsandSchedle]

	
	@ScheduleID int,
	@ScheduledSlotId int,
	@VideoItemID int,
	@ItemOrder int,
	@VideoStartTime datetime,
	@VideoEndTime datetime,
	@VideoLength datetime ,
	@Userid int,
	@showid int,
	@SecEvent int,
	@SecEventText text 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;

	IF EXISTS (SELECT ScheduleID FROM SlotVideoItemsevents WHERE ScheduleID=@ScheduleID)
      BEGIN
      delete  FROM SlotVideoItemsevents WHERE ScheduleID=@ScheduleID
		INSERT INTO SlotVideoItemsevents
           ([ScheduleID]
           ,[ScheduledSlotId]
           ,[VideoItemID]
           ,[ItemOrder]
           ,[VideoStartTime]
           ,[VideoEndTime]
           ,[VideoLength]
           ,[Userid]
           ,[Showid]
           ,[SecEvent] 
	,[SecEventText] )
VALUES     

(@ScheduleID ,
	@ScheduledSlotId ,
	@VideoItemID ,
	@ItemOrder ,
	@VideoStartTime ,
	@VideoEndTime ,
	@VideoLength  ,
	@Userid,
	@showid,
	@SecEvent,
	@SecEventText )
		END
END

Open in new window

0
Comment
Question by:mattibutt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 9

Expert Comment

by:auke_t
ID: 33753473
Why bother checking if it exists if you are going to delete it anyway?

Just do this:
/****** Object:  StoredProcedure [dbo].[UpdateSecEventsandSchedle]    Script Date: 09/24/2010 13:58:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[UpdateSecEventsandSchedle]

	
	@ScheduleID int,
	@ScheduledSlotId int,
	@VideoItemID int,
	@ItemOrder int,
	@VideoStartTime datetime,
	@VideoEndTime datetime,
	@VideoLength datetime ,
	@Userid int,
	@showid int,
	@SecEvent int,
	@SecEventText text 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;

	delete  FROM SlotVideoItemsevents WHERE ScheduleID=@ScheduleID
	
	INSERT INTO SlotVideoItemsevents
           ([ScheduleID]
           ,[ScheduledSlotId]
           ,[VideoItemID]
           ,[ItemOrder]
           ,[VideoStartTime]
           ,[VideoEndTime]
           ,[VideoLength]
           ,[Userid]
           ,[Showid]
           ,[SecEvent] 
	,[SecEventText] )
VALUES     

(@ScheduleID ,
	@ScheduledSlotId ,
	@VideoItemID ,
	@ItemOrder ,
	@VideoStartTime ,
	@VideoEndTime ,
	@VideoLength  ,
	@Userid,
	@showid,
	@SecEvent,
	@SecEventText )
END

Open in new window

0
 
LVL 11

Author Comment

by:mattibutt
ID: 33753748
hi
i have tried it like this its also inserting one row please advice i havent saw this kind of behaviour it should insert all of them but its not
0
 
LVL 9

Expert Comment

by:auke_t
ID: 33753818
Well, your stored procedure is only inserting a single record.

Do you by any chance call it multiple times with different parameters, but with the same @ScheduleID?
Then every call will remove the previous calls.

You should first delete all records for a certain @scheduleID and then insert all new records.
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 11

Author Comment

by:mattibutt
ID: 33753843
thats the idea how do i do that
my stored procedure is inserting multiple rows in front repeater is repeated until all the items in there are inserted into the database
0
 
LVL 9

Accepted Solution

by:
auke_t earned 500 total points
ID: 33753879
You should remove the delete statement from the procedure and put it before the repeater starts.

ALTER PROCEDURE [dbo].[UpdateSecEventsandSchedule]

	
	@ScheduleID int,
	@ScheduledSlotId int,
	@VideoItemID int,
	@ItemOrder int,
	@VideoStartTime datetime,
	@VideoEndTime datetime,
	@VideoLength datetime ,
	@Userid int,
	@showid int,
	@SecEvent int,
	@SecEventText text 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	
	INSERT INTO SlotVideoItemsevents
           ([ScheduleID]
           ,[ScheduledSlotId]
           ,[VideoItemID]
           ,[ItemOrder]
           ,[VideoStartTime]
           ,[VideoEndTime]
           ,[VideoLength]
           ,[Userid]
           ,[Showid]
           ,[SecEvent] 
	,[SecEventText] )
VALUES     

(@ScheduleID ,
	@ScheduledSlotId ,
	@VideoItemID ,
	@ItemOrder ,
	@VideoStartTime ,
	@VideoEndTime ,
	@VideoLength  ,
	@Userid,
	@showid,
	@SecEvent,
	@SecEventText )
END

Open in new window

0
 
LVL 11

Author Comment

by:mattibutt
ID: 33754200
you want me to execute another stored procedure which deletes anything exists within same command?
0
 
LVL 9

Expert Comment

by:auke_t
ID: 33754239
Cam you post the code of your repeater?
0
 
LVL 11

Author Comment

by:mattibutt
ID: 33754313
i am trying to add it like this it doesnt seem to be working

      try
        {
            int pScheduleID = Convert.ToInt32(ScheduleID.Value);
            //DateTime pAdRequiredTime = Helper.FormatVideoLength(reqAdvertizing.Text);
            string[] paramTypes = { "@pScheduleID" };
            object[] paramValues = { pScheduleID };
            DBManager.ExecuteSQLSP("UpdateSecEventsandSchedledelete", paramTypes, paramValues);
            // saving required advertizing time

            // saving DataList slots shows
        }
        catch (Exception exshow)
        {
            Helper.StoreErrLog("UserSchedules", "SavingDataListItems", exshow.ToString());
        }
0
 
LVL 9

Expert Comment

by:auke_t
ID: 33754421
You seem to only set  @pScheduleId! Of you call the stored procedure from the database management tool, dies it work then?
0
 
LVL 11

Author Comment

by:mattibutt
ID: 33754450
it works perfectly fine from management tool
0
 
LVL 9

Expert Comment

by:auke_t
ID: 33754482
Ok, so the problem is the C# code.
0
 
LVL 11

Author Comment

by:mattibutt
ID: 33754512
you are using mobile i can send you full block code when you get time havea look at it thanks
void SavingDataListItems()
    {

        try
        {
            int pScheduleID = Convert.ToInt32(ScheduleID.Value);
            //DateTime pAdRequiredTime = Helper.FormatVideoLength(reqAdvertizing.Text);
            string[] paramTypes = { "@pScheduleID" };
            object[] paramValues = { pScheduleID };
            DBManager.ExecuteSQLSP("UpdateSecEventsandSchedledelete", paramTypes, paramValues);
            // saving required advertizing time

            // saving DataList slots shows
        }
        catch (Exception exshow)
        {
            Helper.StoreErrLog("UserSchedules", "SavingDataListItems", exshow.ToString());
        }
        DataList rep = scheduleDay;
        for (int i = 0; i < rep.Items.Count; i++)
        {

            try
            {
               
                try
                {
                    
                }
                catch (Exception exshow)
                {
                    Helper.StoreErrLog("UserSchedules", "SavingDataListItems", exshow.ToString());
                }

                CheckBoxList SecEvents = ((CheckBoxList)rep.Items[i].FindControl("SecEvents"));
            

                foreach (ListItem li in SecEvents.Items)
                {
                    if (li.Selected)
                    {
                        string strSelectedItemText = li.Text;
                        string strSelectedItemValue = li.Value;
                        //Insert Function 
                        //This will repeat for the checked items of the checkboxlists
                        //If N Checkboxes Get Selected the insert will be called for N Times
              
                int SEVideoItemID = 0;
                SEVideoItemID = Convert.ToInt32(((DropDownList)rep.Items[i].FindControl("Drop").Controls[0]).SelectedValue);
                string ItemType = ((HiddenField)rep.Items[i].FindControl("ItemType")).Value;
                int itemtypes = Convert.ToInt32(ItemType);
                string starttime = ((HiddenField)rep.Items[i].FindControl("starttimes")).Value;
                string ShowIDs = ((HiddenField)rep.Items[i].FindControl("usershowid")).Value;
                int SEScheduleID = Convert.ToInt32(ScheduleID.Value);
                string ScheduledSlotId = ((HiddenField)rep.Items[i].FindControl("ScheduledSlotId")).Value;
                int SEScheduledSlotId = Convert.ToInt32(ScheduledSlotId);
                int SEShowID = Convert.ToInt32(ShowIDs);
                DateTime itemstarttime = Convert.ToDateTime(starttime);
                string ItemOrder = ((HiddenField)rep.Items[i].FindControl("ItemOrder")).Value;
                int SEItemOrder = Convert.ToInt32(ItemOrder);
                string VideoStartTime = ((HiddenField)rep.Items[i].FindControl("starttimes")).Value;
                DateTime SEVideoStartTime = Convert.ToDateTime(VideoStartTime);
                string VideoEndTime = ((HiddenField)rep.Items[i].FindControl("starttimes")).Value;
                DateTime SEVideoEndTime = Convert.ToDateTime(VideoEndTime);
                string VideoLength = ((HiddenField)rep.Items[i].FindControl("starttimes")).Value;
                DateTime SEVideoLength = Convert.ToDateTime(VideoLength);
                int SEUserid = Convert.ToInt32(Session["UserID"].ToString());
              string SecEvent = ((HiddenField)rep.Items[i].FindControl("ItemOrder")).Value;
                int SESecEvent = Convert.ToInt32(SecEvent);
                string SESecEventText = ((HiddenField)rep.Items[i].FindControl("starttimes")).Value;
                string[] paramTypes = { "@ScheduleID", "@ScheduledSlotId", "@VideoItemID", "@ItemOrder "
                                      ,"@VideoStartTime", "@VideoEndTime", "@VideoLength","@Userid","@showid" ,"@SecEvent", 
                                      "@SecEventText" };
                object[] paramValues = { SEScheduleID, SEScheduledSlotId, SEVideoItemID, SEItemOrder, SEVideoStartTime,
                                       SEVideoEndTime, SEVideoLength, SEUserid,SEShowID, strSelectedItemValue, SESecEventText};
                    DBManager.ExecuteSQLSP("UpdateSecEventsandSchedle", paramTypes, paramValues);
                   ((Label)rep.Items[i].FindControl("ERRShow")).Text = "";
                    }
                }
            }
            catch (Exception ex)
            {
                ERRMain.Text = ex.Message.ToString();
                Helper.StoreErrLog("UserSchedules", "Executing UpdateSecEventsandSchedle", ex.ToString());
            }
        }
    }

Open in new window

0
 
LVL 9

Expert Comment

by:auke_t
ID: 33754736
Ok, have you removed the deletestatement from the UpdateSecEventsandSchedule? If so, it should work.

Otherwise start a trace with the SQL profiler and watch wat SQL statements the code generates.
0
 
LVL 11

Author Comment

by:mattibutt
ID: 33754765
it works perfectly fine the issue was associated with the parameter of the schedule i was passing a wrong paramter
0
 
LVL 11

Author Closing Comment

by:mattibutt
ID: 33754775
thanks buddy
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can we attach PDF to table 2 46
Removing SCCM 2016 4 53
store vs query adhoc - no show rows 4 37
mssql 7 32
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question