Updating and inserting information RSS

• • Reply
  

  connersz

  Member

  153 Points

  175 Posts

  Updating and inserting information

  Apr 26, 2012 01:27 PM|LINK

  I have a gridview on a webpage where you can update data or add a new line before saving.

  I have written a stored procedure which needs to do both things, it needs to update the rows already there with the new customerid and it needs to add the new one when it doesnt already exist, i have pasted the sp below, i know i am close but for some reason no matter what i try it wont save the new row, it also wont update when i change the order of the rankings of the customers, or change the customers around (i know the data makes it to the sp, but basically its doing nothing).

   

  UPDATE dbo.tblSalesTop20CustomersList
  	SET
  		CustomerID = data.c.value('@customerid', 'int')
  		,CustomerRank = data.c.value('@number', 'int')
  		,LastUpdatedBy = @RequestingUser
  		,DatetimeLastUpdated = GETDATE()
  	FROM
  		dbo.tblSalesTop20CustomersList AS tblSalesTop20CustomersList
  		--INNER JOIN @xmlCustomerData.nodes('/root/rank') AS data(c)
  			--ON data.c.value('@number', 'int') = tblSalesTop20CustomersList.CustomerRank
  	WHERE
  		tblSalesTop20CustomersList.Year = @Year
  		AND tblSalesTop20CustomersList.SectorID = @SectorID
  		AND tblSalesTop20CustomersList.SalesManagerID = @SalesManagerID
  
  INSERT INTO dbo.tblSalesTop20CustomersList
  	SELECT
  		@Year AS Year
  		,@SectorID AS SectorID
  		,@SalesManagerID AS SalesManagerID
  		,data.c.value('@customerid', 'int') AS CustomerID
  		,data.c.value('@number', 'int') AS CustomerRank
  		,@RequestingUser
  		,GETDATE()
  		,0
  	FROM
  		@xmlCustomerData.nodes('/root/rank') AS data(c)
  		LEFT OUTER JOIN dbo.tblSalesTop20CustomersList AS tblSalesTop20CustomersList
  			ON data.c.value('@number', 'int') = tblSalesTop20CustomersList.CustomerRank
  			AND tblSalesTop20CustomersList.Year = @Year
  			AND tblSalesTop20CustomersList.SectorID = @SectorID
  			AND tblSalesTop20CustomersList.SalesManagerID = @SalesManagerID
  	WHERE NOT EXISTS
  	(
  	SELECT CustList.CustomerID
  	FROM dbo.tblSalesTop20CustomersList As CustList
  	INNER JOIN @xmlCustomerData.nodes('/root/rank') AS data(c)
  	ON data.c.value('@customerid', 'int') = CustList.CustomerID
  	)

• • Reply
  

  limno

  All-Star

  117340 Points

  8005 Posts

  Moderator

  MVP

  Re: Updating and inserting information

  Apr 26, 2012 01:34 PM|LINK

  If you are using SQL Server 2008 or 2012, you can look into the MERGE operation.

  Limno
  
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  

  connersz

  Member

  153 Points

  175 Posts

  Re: Updating and inserting information

  Apr 26, 2012 01:35 PM|LINK

  I am using SQL server 2005 :-S

• • Reply
  

  limno

  All-Star

  117340 Points

  8005 Posts

  Moderator

  MVP

  Re: Updating and inserting information

  Apr 26, 2012 04:54 PM|LINK

  What is your xml parameter defined in your stored procedure? Can you post a sample data of your xml parameter ? Thanks.

  Limno
  
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  

  Chen Yu - MS...

  All-Star

  21600 Points

  2493 Posts

  Microsoft

  Re: Updating and inserting information

  May 02, 2012 09:17 AM|LINK

  Hi connersz,

  Have you tested your query that the record exists in their tables before you update dbo.tblSalesTop20CustomersList, INSERT INTO dbo.tblSalesTop20CustomersList ? If not, please test them.

  --UPDATE dbo.tblSalesTop20CustomersList
    --      SET
    --              CustomerID = data.c.value('@customerid', 'int')
    --              ,CustomerRank = data.c.value('@number', 'int')
    --              ,LastUpdatedBy = @RequestingUser
    --              ,DatetimeLastUpdated = GETDATE()
  
   select data.c.value('@customerid', 'int')
          ,data.c.value('@number', 'int')
          ,@RequestingUser
         , *
         FROM
                  dbo.tblSalesTop20CustomersList AS tblSalesTop20CustomersList
                  INNER JOIN @xmlCustomerData.nodes('/root/rank') AS data(c)
                  ON data.c.value('@number', 'int') = tblSalesTop20CustomersList.CustomerRank
          WHERE
                  tblSalesTop20CustomersList.Year = @Year
                  AND tblSalesTop20CustomersList.SectorID = @SectorID
                  AND tblSalesTop20CustomersList.SalesManagerID = @SalesManagerID
   
  --INSERT INTO dbo.tblSalesTop20CustomersList
          SELECT
                  @Year AS Year
                  ,@SectorID AS SectorID
                  ,@SalesManagerID AS SalesManagerID
                  ,data.c.value('@customerid', 'int') AS CustomerID
                  ,data.c.value('@number', 'int') AS CustomerRank
                  ,@RequestingUser
                  ,GETDATE()
                  ,0
          FROM
                  @xmlCustomerData.nodes('/root/rank') AS data(c)
                  LEFT OUTER JOIN dbo.tblSalesTop20CustomersList AS tblSalesTop20CustomersList
                          ON data.c.value('@number', 'int') = tblSalesTop20CustomersList.CustomerRank
                          AND tblSalesTop20CustomersList.Year = @Year
                          AND tblSalesTop20CustomersList.SectorID = @SectorID
                          AND tblSalesTop20CustomersList.SalesManagerID = @SalesManagerID
          WHERE NOT EXISTS
          (
          SELECT CustList.CustomerID
          FROM dbo.tblSalesTop20CustomersList As CustList
          INNER JOIN @xmlCustomerData.nodes('/root/rank') AS data(c)
          ON data.c.value('@customerid', 'int') = CustList.CustomerID
          )

  Thanks.

  Please mark the replies as answers if they help or unmark if not.
  Feedback to us
  
  Develop and promote your apps in Windows Store