Solved

mssql delete if exists problem

Posted on 2010-09-24
15
287 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
export sql results to csv 6 36
SQL - Use results of SELECT DISTINCT in a JOIN 4 20
Loops and updating in SQL Query 9 29
Sql Query 6 67
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

809 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