Solved

mssql delete if exists problem

Posted on 2010-09-24
15
276 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
  • 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now