Update SP and passing values from dropdownlists

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 SP and passing values from dropdownlists

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2012-04-23 : 10:35:18

I have an asp.net web page. I have three dropdownlists for selection from the sql table

Dep, Div and Sect

and three dropdownlists where new values can be selected to update the sql table

DepNew, DivNew and SectNew
This is done by passing the values to a stored procedure.

If a selection dropdown is left blank I want it to select all for that field and just filter on the ddl's where a selection has been made

If a new ddl is left blank I want the value left as it is, but the table updated with any new values in any ddl's where the value isn;t a blank

Help please.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 11:18:12

sorry can be elaborate on this using sample data?

If a selection dropdown is left blank I want it to select all for that field and just filter on the ddl's where a selection has been made

If a new ddl is left blank I want the value left as it is, but the table updated with any new values in any ddl's where the value isn;t a blank

What has ddl to do with combobox selections?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Pinto
Aged Yak Warrior

590 Posts

Posted - 2012-04-24 : 04:23:07

Example record with sample data
Id (Key) 25
Dept Central
Div Parks
Sect Flowerbeds
other fields relating to this record....
...
...

I want to change any one of Dept, Div or Sect or 2 of them or all three of them eg Central becomes known corporately as Centre Areas, so I want to select all records classified as Central (which may be 1000's) by choosing Central in the top drop downlist. In the bottom dropdownlist I choose Centre Areas, click a button and all the records are changed, but Parks and Flower beds fields remain the same.
another scenario -
I can select just Parks and then change all the records with this to Open Areas or I could select Parks and Flowers beds and change all records with these two classes to Open Areas and Gardening Areas

I want to pass the selection criteria from the top dropdownlists to an sp which selects the records (Using WHERE) and changes the data to what has been selected in the bottom dropdownlists - or leaves it as it was if the dropdownlist is left blank. if a dropdownlist is left blank in the top ddls then any data can be in that field in the table - or it can be blank

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-24 : 06:54:40

You need to do the filtering part in ASP.net page. You need to accept the inputs from the ddls, check the inputs in the ASP.net page itself and then pass the filtered output to SQL Server as input.
In such cases the lesser SQL code you'll use, the more will be the performance of your page.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2012-04-24 : 08:31:27

How do I filter in the asp page ?

Here's my sp and I pass the parameters from my asp page. If Sect in the table is blank and the ddl is left blank it works. If the Sect in the table is 'Flower beds' and the ddl is left blank to select all then it wil lnot work

ALTER PROCEDURE [dbo].[spUpdate]
-- Add the parameters for the stored procedure here

@strDept nvarchar (100)= null,
@strDivision nvarchar (100)= null,
@strSect nvarchar (100) = null,

@strDeptNew nvarchar (100)= null,
@strDivisionNew nvarchar (100)= null,
@strSectNew nvarchar (100) = null

AS

BEGIN

UPDATE MyTable SET

Dept=@strDeptNew,
Div=@strDivisionNew,
Sect=@strSectNew

WHERE Dept = @strDept and
Div = @strDivision and
Sect = @strSect and
Destroyed = 0

END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-24 : 08:45:01

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

No, you're never too old to Yak'n'Roll if you're too young to die.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2012-04-24 : 09:26:22

I've now got this and it still doesn't work. Is it because a dropdownlist left blank returns '' and not null ? An empty field in the table doesn't show null - just blank

ALTER PROCEDURE [dbo].[spRMU_BlockUpdateAllSelectedtblFiles2]
-- Add the parameters for the stored procedure here

@strDept nvarchar (100)= null,
@strDivision nvarchar (100)= null,
@strSect nvarchar (100) = null,

@strDeptNew nvarchar (100)= null,
@strDivisionNew nvarchar (100)= null,
@strSectNew nvarchar (100) = null

AS

BEGIN

UPDATE MyTable SET

Dept=@strDeptNew,
Division=@strDivisionNew,
Sect=@strSectNew

WHERE (Dept = @strDept or Dept is Null) and
(Div = @strDivision or Div is null) and
(Sect = @strSect or Sect is Null) and
Destroyed = 0

END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-24 : 09:43:23

accuracy is needed...

quote:
Originally posted by Pinto

I've now got this and it still doesn't work. Is it because a dropdownlist left blank returns '' and not null ? An empty field in the table doesn't show null - just blank

ALTER PROCEDURE [dbo].[spRMU_BlockUpdateAllSelectedtblFiles2]
-- Add the parameters for the stored procedure here

@strDept nvarchar (100)= null,
@strDivision nvarchar (100)= null,
@strSect nvarchar (100) = null,

@strDeptNew nvarchar (100)= null,
@strDivisionNew nvarchar (100)= null,
@strSectNew nvarchar (100) = null

AS

BEGIN

UPDATE MyTable SET

Dept=@strDeptNew,
Division=@strDivisionNew,
Sect=@strSectNew

WHERE (Dept = @strDept or @strDept is Null) and
(Div = @strDivision or @strDivision is null) and
(Sect = @strSect or @strSect is Null) and
Destroyed = 0

END

No, you're never too old to Yak'n'Roll if you're too young to die.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2012-04-24 : 09:54:32

Ooops - sorry :-(. It still doesn't work. It only returns records if there is an exact match between the dropdownlists and the records in the table. ie Record is as below.If I just pick Parks from Sect ddl - nothing is returned. If I pick Central, Parks and Flowerbeds it works

Id (Key) 25
Dept Central
Div Parks
Sect Flowerbeds

Pinto
Aged Yak Warrior

590 Posts

Posted - 2012-04-24 : 10:22:59

cHANGED IT TO THIS AND IT WORKS

WHERE (Dept = @strDept or @strDept ='') and
(Div = @strDivision or @strDivision ='') and
(Sect = @strSect or @strSect ='') and
Destroyed = 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 21:21:53

quote:
Originally posted by Pinto

cHANGED IT TO THIS AND IT WORKS

WHERE (Dept = @strDept or @strDept ='') and
(Div = @strDivision or @strDivision ='') and
(Sect = @strSect or @strSect ='') and
Destroyed = 0

keep in mind that above approach can cause batch query plans and hurt performance especially when dataset is large

see
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources