Server Behavior: Show if dataset NOT empty 1

[sgraham]

Hello everyone, thanks for taking a look and possibly helping me solve this headache. I'm using DMX 2004, SQL 2000, and VB.NET

I've setup an Intranet page using a simple form and a textbox to search for a specific security change request. This page has multiple datasets, one for each departments security list, each dataset has the 'Show Record if dataset is NOT empty' server behavior applied.

The problem is that no matter what record I search for, every single dataset shows up.

For example, lets say I'm searching for record '97', which happens to be a test entry from engineering. I get the appropriate security group list for engineering, but I also get all the other department lists as well, although they are blank whereas the engineering security list is populated.

Why is this??

Each dataset references the same texbox to get their value - would this cause a problem? Also, they all reference the same database table, just specific columns - is this a problem??

ds_scRequests
SELECT scrid, senderName, depName, empNameF, empNameL, ...
FROM myDB
WHERE scrid = ? [COLOR=red](? = Textbox Form Variable)[/color]

/* --- Department Security List Datasets --- */

ds_dep1
SELECT secName1, secName2, secName3, ...
FROM myDB [COLOR=red](same database table as above)[/color]
WHERE scrid = ? [COLOR=red](same form variable as above)[/color]

ds_dep2
SELECT secName4, secName5, secName6, ...
FROM myDB [COLOR=red](same database table as above)[/color]
WHERE scrid = ? [COLOR=red](same form variable as above)[/color]

(one dataset for each department)

What I shoud see once the record has been found are the values from ds_scRequests(populated) and ds_dep1(populated) but nothing else.

What the page is currently returning is ds_scRequests(populated)
ds_dep1(populated)
ds_dep2(empty, but showing)
ds_dep3(empty, but showing)
ds_dep4(empty, but showing)
ds_dep5(empty, but showing)
...
ds_dep10(empty, but showing)

Any ideas as to how I can get only ds_scRequests and ds_dep1 to show up?

Thanks for the help, it's much appreciated!

Steve

 

[CTekMedia]

Use IF statements. IF ds_dep1 NEQ "" THEN (insert code here to display data for ds_dep1)

Not sure what the syntax is for VB.net but IF statements are all similar. I use <TR><TD> to house the data in this case as table rows can be easily added or deleted without leaving spaces on your page.

Cheers,


Peace,

Shane

http://www.acmantels.com

 

[Cheech]

Each dataset references the same texbox to get their value

Look at the logic of your select statements. Even though you are selecting different columns for each department you will get a null result returned for each one where the criterea is matched. Add something to your WHERE statement that dismmisses any empty values.
Something like: (untested)

WHERE scrid = ? AND secName4 != "" AND secName5 != "" AND secName6 != ""

Cheech

[Peace][Pipe]

 

[sgraham]

Thank you SO MUCH for the help!

I've still got one security group that wants to show up when it's not supposed to but all the others are behaving like they should.

bluetone: Thanks for the tip. I'm currently teaching myself php in an attempt to replace the vb.net code, and I'm sure I'll be making good use of the if statements.

Cheech: You deserve a smoke break and a star! I ended up having to use the following code.

WHERE scrid = ? AND (secName4 IS NOT NULL or secName5 IS NOT NULL or AND secName6 IS NOT NULL or ...)

 

[Cheech]

Could you not have another field in the database that defines the dept and store all of the other dept details in a seperate linked table

[Peace][Pipe]

 

[sgraham]

Yes, actually I could and should have a seperate linked table for that information.

I've come to realize my biggest problem is me, or more specifically the way I designed the database, so I've been contemplating redoing the database in order to creat a seperate linked table for each department then using an SQL JOIN when retrieving the records.

I should do that now before the database gets large and cumbersome.

Thanks again for the help and the ideas. If you have any more, I'm happy to hear them. :0)

 

[Cheech]

Definately use a seperate table for the dept info and then just add the dept id to each request. Then you can use a join to retrieve all of the information you require sorted on the dept first. I would have to know a lot more about the application to comment fully on the database, but as you have already said, it looks like the database needs a good thinking about before it takes off.

Cheech

[Peace][Pipe]

 

[sgraham]

I should probably start a new topic for this, but what do you think of the following database layout?

create table supportRequest_ID (
	req_id		int not null primary key IDENTITY(1,1),
	req_type	char(3) not null check(req_type in('HDR', 'SCR', '400'),
	dep_nameShort	char(3) not null,
	submittedBy	varchar(15) not null,
	date_entered	datetime not null,
);

create table supportRequest_info (
	req_id		int not null references supportRequest_ID(req_id),
	req_type	char(3) not null references supportRequest_ID(req_type),
	dep_nameShort	char(3) not null references supportRequest_ID(dep_nameShort),
	submittedBy	varchar(15) references supportRequest_ID(submittedBy),
	priority	varchar(10),
	location	varchar(10),
	date_expected	varchar(25),
	date_completed	varchar(25),
	appliesTo	varchar(10),
	requestedAction	varchar(10),
	empNameF	varchar(25),
	empNameL	varchar(25),
	comments	text,
);

create table supportRequest_updates (
	req_id		int not null references supportRequest_ID(req_id),
	req_type	char(3) not null references supportRequest_ID(req_type),
	dep_nameShort	char(3) not null references supportRequest_ID(dep_nameShort),
	recievedBy	varchar(25),
	assignedTo	varchar(25),
	status		varchar(10),
	actionTaken	text,
	closedBy	varchar(25),
	updatedBy	varchar(15),
	emailClosureSent	char(1),
	dcFormRequired		char(1),
	dcFormNumber		varchar(15),
);

create table secList_cs (
	req_id		int not null references supportRequest_ID(req_id),
	dep_nameShort	char(3) not null references supportRequest_ID(dep_nameShort),
	secName1	char(5),
	secName2	char(5),
	secName3	char(5),
	...
	secName10	char(5),
);

create table secList_eng (
	req_id		int not null references supportRequest_ID(req_id),
	dep_nameShort	char(3) not null references supportRequest_ID(dep_nameShort),
	secName1	char(5),
	secName2	char(5),
	secName3	char(5),
	...
	secName10	char(5),
);

What are your thoughts?

...

 

[Cheech]

You can JOIN all of them on req_id negating the need to include req_type & dep_shortname etc in other tables. Add another table with dep details and join that with a dep_id so that you only need ref a small id field and not the dep name every time, just some thoughts ;-)

Cheech

[Peace][Pipe]

 

[sgraham]

I think the req_type & dep_nameShort were an attempt to feed my newbie paranoia that everything would be ok...

Thanks for all the help and the tips! I think I'll remove the req_type & dep_nameShort from all but the first table, and use the JOIN to tie everything together as you suggested.

Thanks Again!
Steve

 

[Cheech]

No probs

[Peace][Pipe]