November 18, 2013 at 3:40 am
Hi All,
I have a grid with checkbox, where users can select multiple rows and edit at the same time and save it to the DB. Now I have used a Footer Template with textbox in the gridview. So if I want to put similar data's for some particular rows at the same time in the grid, I select the multiple rows and try to put values in the footer template textbox and when I click on save, it saves successfully.
UPDATE QUERY:
"UPDATE [Test] SET [Name]='" + Name + "',[Designation]= '" + Designation + "', [City]= '" + City + "' WHERE EmpID='" + EmpID + "'";
Now here is the challenge, but even when I enter null values in the footer template textbox it has to save with the old values of the rows and not null values. I tried it and couldn't make it happen. So anything like putting the case for each column and mentioning like if null accept the old value and not null accept new value. Any suggestions with the query?
November 18, 2013 at 3:47 am
Aside, do you know how vulnerable that is to SQL injection attacks?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2013 at 3:54 am
To my knowledge, improper coding of the web applications that allows hacker to develop and inject their SQL commands and which allows access to our web app held in the DB.
Please correct me if I am wrong.
November 18, 2013 at 4:04 am
It's not limited to web apps, and by the looks of your update, your code is vulnerable. Do not concatenate user input into a sql statement that will be executed. Parameterise your sql statements, always
One form of an update like that:
UPDATE [Test]
SET [Name]= CASE WHEN @Name IS NULL THEN [Name] ELSE @Name END,
[Designation]= CASE WHEN @Designation IS NULL THEN Designation ELSE @Designation END,
[City]= CASE WHEN @City IS NULL THEN City ELSE @City END
WHERE EmpID= @EmployeeID;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2013 at 4:29 am
A bit shorter code (but doing the same as Gail's solution):
UPDATE [Test]
SET [Name]= COALESCE(@Name, [Name]),
[Designation]= COALESCE(@Designation, Designation),
[City]= COALESCE(@City, City)
WHERE EmpID= @EmployeeID;
Explanation: COALESCE evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
November 18, 2013 at 5:42 am
I tried it and its getting saved with null values.:(
November 18, 2013 at 5:45 am
Post the exact update you're running, because the two above will not update the values to null unless the parameter is null and the column value is already null
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2013 at 5:56 am
You could add an empty string to the parameters of the COALESCE function. That will update the value to an empty string if both the variable and the field value are NULL. But it depends on your business requirements if this is an acceptable solution.
UPDATE [Test]
SET [Name]= COALESCE(@Name, [Name], ''),
[Designation]= COALESCE(@Designation, Designation, ''),
[City]= COALESCE(@City, City, '')
WHERE EmpID= @EmployeeID;
November 19, 2013 at 5:04 am
Thanks all for helping. Got it after making some validations.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply