display combination of records

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

SQL Server 2000 Forums

SQL Server Development (2000)

display combination of records

Author

Topic

arty
Starting Member

13 Posts

Posted - 2005-10-04 : 02:19:23

I have a databound datagrid in my asp.net application. I have two dropdown lists(i.e. two tables) 1.branch 2.year. In both these dropdown lists first item is 'ALL' followed by the rest of the items.

I want to generate records in datagrid based on options selected by the user in both these dropdowns,

i.e. branch selected is Sydney & year is 2005 or Adelaide & 2005 or Sydney & 2006 etc...

the default option for both the lists is always 'ALL' i.e on page load and when it is ALL i.e. when the page opens the first time or when user selects ALL from both lists, all the records in the database should be displayed i.e data from all branches and all years.

The code I have written is as follows:

SqlCmd.CommandText = " SELECT * "
SqlCmd.CommandText += " FROM chequedata"
if ddlBranch.SelectedIndex > 1 and ddlYear.SelectedIndex > 1
then
SqlCmd.CommandText += " where branchno=" & Branchno & "And
yearno=" & YearNo

this is not working very well for 'ALL' (i.e. index=1) option. How to display all records as well as selected combinations?

Thankyou for any and all help.

cheers!

Aartee
-We Live More Than Once!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-04 : 02:55:26

Try this

SqlCmd.CommandText = " SELECT * "
SqlCmd.CommandText += " FROM chequedata"
if ddlBranch.SelectedIndex = 1 and ddlYear.SelectedIndex = 1 then

else
SqlCmd.CommandText += " where branchno=" & Branchno & "And
yearno=" & YearNo

Madhivanan

Failing to plan is Planning to fail

arty
Starting Member

13 Posts

Posted - 2005-10-04 : 03:07:54

Dear Madhivanan,

thankyou for your reply but well... the datagrid is coming up blank

here is what i did...

SqlCmd.CommandText = " SELECT branchno,yearno,capexno,investmentno,amtbgdted,description "
SqlCmd.CommandText += " FROM Chequedata"
if ddlBranch.SelectedIndex = 1 and ddlYear.SelectedIndex = 1 then

else
SqlCmd.CommandText += " where branchno=" & Branchno & "And yearno=" & YearNo
end if

The other combinations I tried are

SqlCmd.CommandText = " SELECT * "
SqlCmd.CommandText += " FROM chequedata"
if ddlBranch.SelectedIndex > 0 and ddlYear.SelectedIndex = 0 then
SqlCmd.CommandText += " where branchno=" & Branchno
elseif ddlBranch.SelectedIndex = 0 and ddlYear.SelectedIndex > 0 then
SqlCmd.CommandText += " where yearno=" & YearNo
elseif ddlBranch.SelectedIndex > 0 and ddlYear.SelectedIndex > 0 then
SqlCmd.CommandText += " where branchno=" & Branchno & "And yearno=" & YearNo
end if

but it isn't working either...

The only thing working is

SqlCmd.CommandText = " SELECT *"
SqlCmd.CommandText += " FROM Chequedata"
SqlCmd.CommandText += " where branchno=" & Branchno & "And yearno=" & YearNo & --- for any combination of branch & year except ALL

because once again what I want to yield is :

1. All records for all branches and all years when page opns the first time
2. Records based on branch and year combinations selected by user
3. All records again if user selects 'ALL' for branch as well as year

Thankyou once again!

Aartee
-We Live More Than Once!!!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-04 : 03:10:46

create 2 sprocs for this
1. sproc with proper/specific parameters
2. sproc to retrieve all records

then in your app, call the appropriate sproc depending on your dropdown objects

--------------------
keeping it simple...

arty
Starting Member

13 Posts

Posted - 2005-10-04 : 03:20:51

Thankyou Jen,

An example of the proc will be a big help especially for the all branches and all years clause

Aartee
-We Live More Than Once!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-04 : 03:28:36

Arty, Try something like this

Create Procedure yourProc
(
@year int,
@branch varchar(10))
as
SELECT * FROM chequedata
where (yearNo=@year or @year is null) and (branchno = @Branchno or @Branchno is Null)

Also refer this for Dynamic Searches
http://www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-04 : 03:33:18

2 sprocs



create proc spChequeData_Select 
    @branchno varchar(10),
    @yearno varchar(5)
as

set nocount on

select [enumerate your fields here, don't use *]
from chequedata
where branchno=@branchno and yearno=@yearno

set nocount off



create proc spChequeData_SelectAll 
as

set nocount on

select [enumerate your fields here, don't use *]
from chequedata

set nocount off
GO

--------------------
keeping it simple...

arty
Starting Member

13 Posts

Posted - 2005-10-05 : 03:11:12

CREATE Procedure cheque_selectall
@yearno int,
@branchno int,
@investmentno varchar(50)

as
Begin
SELECT branchno,yearno,investmentno,description,amtbgdted FROM chequedata
where (yearNo=@yearno or @yearno is null) and (branchno = @Branchno or @Branchno is Null) and (@investmentno is not null)
End

GO

When I call the procedure in my asp.net progam or even when I run it in my query analyzer I get the following error:

"Server: Msg 201, Level 16, State 6, Procedure cheque_selectall, Line 0
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'cheque_selectall' expects parameter '@yearno', which was not supplied.
"

Any help?

Thanks a ton.

Aartee
-We Live More Than Once!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 03:17:31

You should supply the values to the parameter

Something like

cheque_selectall 2005,20,'E100'

Madhivanan

Failing to plan is Planning to fail

arty
Starting Member

13 Posts

Posted - 2005-10-05 : 03:32:43

Hmmm...

well, still the same empty datagrid! Even in the Query analyzer the @RETURN_VALUE = 0 and the table isn't yielding any results. I thought maybe I am missing something in my webform but nope, it isn't working in the query analyzer too...

any other suggestions? I thought of writing two seperate select statements and it works but the requirement is to run it with one click!

Thankyou so much for you help!

Aartee
-We Live More Than Once!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 04:10:53

Can you post some sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail

arty
Starting Member

13 Posts

Posted - 2005-10-05 : 20:43:40

Following is the exact stored procedure I have written in my 'CapexReport' database which I am attempting to run in the SQL Query analyzer...

CREATE Procedure capex_selectall
@yearno int,
@branchno int,
@investmentno varchar(50)
AS
Begin
SELECT branchno,yearno,investmentno,description,amtbgdted FROM capexdata
where (yearNo=@yearno or @yearno is null) and (branchno = @Branchno or @Branchno is Null) and (@investmentno is not null)
End
GO

I didn't post the long code in my webform, because this proc is not working in the QA itself , but let me know if u need some other bit of info....Thanks.

Aartee
-We Live More Than Once!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-06 : 00:56:16

Did you pass valid parameters to the sp?
Post some sample data and the result you want based on the parameter values

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources