Solved

mssql delete if exists problem

Posted on 2010-09-24
15
260 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

758 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

27 Experts available now in Live!

Get 1:1 Help Now