Update query with & without null values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update query with & without null values

Author

Topic

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-11-18 : 05:40:08

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-18 : 06:00:12

make statement like below and try


"UPDATE [Invent_Test] SET [Name]=COALESCE('" + Name + "',[Name]),[Designation]= COALESCE('" + Designation + "',[Designation]), [City]= COALESCE('" + City + "',[City]) WHERE EmpID='" + EmpID + "'";

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-11-18 : 06:09:32

quote:
Originally posted by visakh16

make statement like below and try

"UPDATE [Invent_Test] SET [Name]=COALESCE('" + Name + "',[Name]),[Designation]= COALESCE('" + Designation + "',[Designation]), [City]= COALESCE('" + City + "',[City]) WHERE EmpID='" + EmpID + "'";

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi Visakh,
Its getting saved with null values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-18 : 06:15:23

It wont unless you pass it as string NULL
in which case make it like this


"UPDATE [Invent_Test] SET [Name]=COALESCE(NULLIF('" + Name + "','NULL'),[Name]),[Designation]= COALESCE(NULLIF('" + Designation + "','NULL'),[Designation]), [City]= COALESCE(NULLIF('" + City + "','NULL'),[City]) WHERE EmpID='" + EmpID + "'";

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-11-18 : 08:43:18

quote:
Originally posted by visakh16

It wont unless you pass it as string NULL
in which case make it like this

"UPDATE [Invent_Test] SET [Name]=COALESCE(NULLIF('" + Name + "','NULL'),[Name]),[Designation]= COALESCE(NULLIF('" + Designation + "','NULL'),[Designation]), [City]= COALESCE(NULLIF('" + City + "','NULL'),[City]) WHERE EmpID='" + EmpID + "'";

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thanks a lot visakh. It worked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-18 : 08:45:45

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources