Solved

mssql delete if exists problem

Posted on 2010-09-24
15
310 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

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.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

632 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