UPDATE statement not updating table

I am having a problem getting this UPDATE statement to execute. No error is returned, it just does not开发者_Python百科 update the table.

    @recordExists varchar(10),
@fileName varchar(50),
@itemCode varchar (50),
--@uploadDate datetime,
@submittedBy varchar(30),
@revision varchar(50),
@itemCode5 varchar(50),
@itemCkDigit varchar(10),
@suffix varchar(10)

AS  

DECLARE @sql varchar(1000)
DECLARE @uploadDate datetime
SET @uploadDate = GetDate()
    -- Establish update or insert in to the graphics info table.
IF @recordExists = 'Y' 

        SET @sql = 'UPDATE tblGraphicInfo SET [uploadDate] = ''' +  CONVERT(nvarchar(20), @uploadDate) + ''', [submittedBy] = ''' + @submittedBy + ''' WHERE [itemCode] = "' + @itemCode + '"; '
        EXEC(@sql)  
ELSE

Any help would be appreciated.

FYI, I changed passing the date in because I thought that was the problem. The uploadDate field is defined as a datetime field in the tblGraphicInfo table.

---

I don't see why you would even need to dynamically string together your UPDATE statement - just use:

DECLARE @sql varchar(1000)

DECLARE @uploadDate datetime
SET @uploadDate = GetDate()

-- Establish update or insert in to the graphics info table.
IF @recordExists = 'Y' 

   UPDATE dbo.tblGraphicInfo 
   SET [uploadDate] = CONVERT(NVARCHAR(20), @uploadDate),
       [submittedBy] = @submittedBy 
   WHERE [itemCode] = @itemCode

ELSE

---

Your issue is your where statement

WHERE [itemCode] = "' + @itemCode + '"; '

You will want to wrap strings in single quote (') not double quote ("). When escaping them in your string, you will need to double the single quotes.

WHERE [itemCode] = ''' + @itemCode + '''; '

You might also look at sp_executsql. It has a much cleaner syntax for handling parameters.

---

Have you checked to see if any of the variables you are passing in are null? This can cause the whole of your @sql variable to be null too. Try printing your @sql variable with Print() to check it is what it should be.

You could also run profiler to see what is being executed.

---

If any of your fields are null @sql will be null (Concatinating null yields null).

Is there any reason you are using EXEC rather than doing..

   @recordExists varchar(10), 
@fileName varchar(50), 
@itemCode varchar (50), 
--@uploadDate datetime, 
@submittedBy varchar(30), 
@revision varchar(50), 
@itemCode5 varchar(50), 
@itemCkDigit varchar(10), 
@suffix varchar(10) 

AS   

DECLARE @sql varchar(1000) 
DECLARE @uploadDate datetime 
SET @uploadDate = GetDate() 
    -- Establish update or insert in to the graphics info table. 
IF @recordExists = 'Y'  

        UPDATE tblGraphicInfo SET [uploadDate] = @uploadDate, [submittedBy] = @submittedBy WHERE [itemCode] =  @itemCode 
ELSE 

Additionally if you perform more than one line inside an if else you will need to wrap it in a BEGIN END

You could actually do the following instead, the @@ROWCOUNT=0 will test if the previous statement modified no records and so needs to be an insert

UPDATE tblGraphicInfo SET [uploadDate] = @uploadDate, [submittedBy] = @submittedBy WHERE [itemCode] =  @itemCode 

 if @@ROWCOUNT=0 then

 INSERT into tblGraphicInfo (uploadDate,submittedBy,itemCode) values (@uploadDate,@submittedBy,@itemCode)