Setting listbox items

[tshad]

I have a string that I read from my database: 1|8|5620|541

These are all values in my ListBox. I want to select each of these items (4
of them - but could be many more). At the moment I am doing the following:

Dim a() As String
Dim j As Integer
a = JobCategoriesSelected.Split("|") ' Where
JobCategoriesSelected is set to 1|8|5620|541
For j = 0 To a.GetUpperBound(0)
trace.warn("string = " & a(j))
for each item as ListItem in JobCategories.items
if item.value = a(j) then
item.selected = true
end if
next
Next

The problem is that for each Item I am selecting, I am having to go through
the list to find the value that matches the one I want to set. In this
case, it would have to go through the list 4 times.

Is there some kind of a way to do it in one line where you say something
like set the listitem that is equal to my value to selected?

Thanks,

Tom

 

[Karl Seguin]

dim item as ListItem = jobCategories.ITems.FindbyValue(a(j))
if not item is nothing then
item.selected = true
end if


Btw, why bother using a relationa database (assuming) if you are gonna use
values which are split like that?

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)

 

[tshad]

dim item as ListItem = jobCategories.ITems.FindbyValue(a(j))
if not item is nothing then
item.selected = true
end if

I knew there was a better way.

Btw, why bother using a relationa database (assuming) if you are gonna use
values which are split like that?

What I am doing is storing the string in a field that shows which choices
the user made to a ListBox (could have many). I am collecting the values
and separating them by "|". The Job Categories are themselves in a table
("text and value"). But each user can have many search records (each of
which can have a different set of Job Categories). This seemed like the
best way to save it.

Thanks,

Tom

 

[Karl Seguin]

Seems like a join table is the best way


UserId, JobId

1,1
1,3
1,5
2,4
5,1
5,6

....

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)

 

[tshad]

Seems like a join table is the best way

You lost me on that one.

What I am doing is allowing the user to save different types of searches
based on Job Categorys, Dates, Zip codes etc. They can save this in a
table. One record for each search they want to save. They may want to save
10 different searches so that they don't have to keep putting in all the
different search criteria each time they come back.

One of the search criterea is Job Categories, which they pick from a list
box which as the name of the Job Category as well as the value (which is a
numeric value).

So if they pick 10 different Criteria, I need to store those numbers
somewhere. So I just put the numbers together in a string separated by "|".

When they come back to run another search, they can pick from their stored
searches and I then need to get all the ten (or however many categories
they picked) categories and use them in the search.

Thanks,

Tom

 

[Karl Seguin]

Tom:
Not sure how else to explain it...

If user1 can have his search 1 which has job 1, 4, 5 and 6
user 1 can have his search 2 which has jobs 1, 2 and 3

user2 can have her search 1 which has jobs 3

user 7 can have her search 3 which has jobs 1,3
user 7 can have her search 2 which has jobs 4,5


I'd do a table with 3 columns:

UserId, SearchId, JobId
//first case
1, 1, 1
1, 1, 4
1, 1, 5
1, 1, 6
//second case
1,2,1
1,2,2
1,2,3
//third case
2,1,3
//forth case
7,3,1
7,3,3
//fifth case
7,2,4
7,2,5

then when you say, "I want all the jobs for user 7 for the her 3rd saved
search", you do:

select JobId from UserSearchJob
where UserId = 7 and SearchId = 3

and you end up with
1
3

Karl

 

[tshad]

I thought that was what you meant. The problem is why have all those
records suppose each search option has 15 Job Categories. That's a lot of
records just to handle one field and a lot more work for Sql to do.

Maybe, you are thinking that there is only one field in the record.

Actually, the whole records is:

CREATE TABLE [dbo].[Searches] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SearchID] [int] IDENTITY (1, 1) NOT NULL ,
[SearchName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KeyWords] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EmailNewJobs] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailNewJobsAddress] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[EmailNewJobsFrequency] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CompanyName] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullTime] [bit] NULL ,
[PartTime] [bit] NULL ,
[Temporary] [bit] NULL ,
[Contract] [bit] NULL ,
[Seasonal] [bit] NULL ,
[WagesMin] [money] NULL ,
[WagesMax] [money] NULL ,
[WagesType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ByState] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ByCity] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Miles] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZipCode] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ByKeyword] [bit] NULL ,
[ByDate] [bit] NULL ,
[ByWages] [bit] NULL ,
[ViewBrief] [bit] NULL ,
[ViewDetailed] [bit] NULL ,
[JobCategories] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DatePosted] [datetime] NULL ,
[DateModified] [datetime] NULL
) ON [PRIMARY]

This would mean that if you have 10 Job categories you have 10 identical
records, except for the one field. An awful lot of wasted space (I would
think). Maybe I'm wrong.

I assume you are normalizing the table.

Tom

 

[Karl Seguin]

Tshad...
I didn't look at your table. I just wanted to offer an alternative. The old
saying I've used is "Normalize 'til it hurts, denormalize 'til it works".
Your space conerns are certainly valid...and if that's what drove your
design decision, it's very valid. However, unless you can draw from similar
past experience, you typically shouldn't assume. It's far better to start
off with cleaner code and improve it as your testing/profiling indicates,
than start off with an "optimized" version which is less readable and who's
value you never really know.

I have no doubt that your solution, while seemingly very efficient, is far
uglier and difficult to maintain. Not knowing your system, I'll leave it to
you to figure out what tradeoffs are necessary, but I certainly hope that
you'll go through due deligence and not make assumptions. Every day of my
life lately I'm stuck in a world where someone else made decisions based on
assumptions which (a) proved to be uterly flawed and (b) resulted in
millions of dollars worth of code being thrown out.

I certainly don't wish to preach or come off sounding all high and mighty.
But the cost of hard of the extra hard drive room probably equals about 4
hours of your salary.....it's pretty obvious which should be the driving
factor between maintainabilty and disk usage...(again, these rules certainly
have valid exceptions....)

Cheers!
Catch you on the flip side (I don't know what that means)

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)

I thought that was what you meant. The problem is why have all those
records suppose each search option has 15 Job Categories. That's a lot of
records just to handle one field and a lot more work for Sql to do.

Maybe, you are thinking that there is only one field in the record.

Actually, the whole records is:

CREATE TABLE [dbo].[Searches] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SearchID] [int] IDENTITY (1, 1) NOT NULL ,
[SearchName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KeyWords] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EmailNewJobs] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailNewJobsAddress] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[EmailNewJobsFrequency] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CompanyName] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullTime] [bit] NULL ,
[PartTime] [bit] NULL ,
[Temporary] [bit] NULL ,
[Contract] [bit] NULL ,
[Seasonal] [bit] NULL ,
[WagesMin] [money] NULL ,
[WagesMax] [money] NULL ,
[WagesType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ByState] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ByCity] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Miles] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZipCode] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ByKeyword] [bit] NULL ,
[ByDate] [bit] NULL ,
[ByWages] [bit] NULL ,
[ViewBrief] [bit] NULL ,
[ViewDetailed] [bit] NULL ,
[JobCategories] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DatePosted] [datetime] NULL ,
[DateModified] [datetime] NULL
) ON [PRIMARY]

This would mean that if you have 10 Job categories you have 10 identical
records, except for the one field. An awful lot of wasted space (I would
think). Maybe I'm wrong.

I assume you are normalizing the table.

Tom

[QUOTE="Karl Seguin"]
Tom:
Not sure how else to explain it...

If user1 can have his search 1 which has job 1, 4, 5 and 6
user 1 can have his search 2 which has jobs 1, 2 and 3

user2 can have her search 1 which has jobs 3

user 7 can have her search 3 which has jobs 1,3
user 7 can have her search 2 which has jobs 4,5


I'd do a table with 3 columns:

UserId, SearchId, JobId
//first case
1, 1, 1
1, 1, 4
1, 1, 5
1, 1, 6
//second case
1,2,1
1,2,2
1,2,3
//third case
2,1,3
//forth case
7,3,1
7,3,3
//fifth case
7,2,4
7,2,5

then when you say, "I want all the jobs for user 7 for the her 3rd saved
search", you do:

select JobId from UserSearchJob
where UserId = 7 and SearchId = 3

and you end up with
1
3

Karl



--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)


(each
of (more
to
[/QUOTE]
[/QUOTE]

 

[tshad]

Tshad...
I didn't look at your table. I just wanted to offer an alternative. The
old
saying I've used is "Normalize 'til it hurts, denormalize 'til it works".
Your space conerns are certainly valid...and if that's what drove your
design decision, it's very valid. However, unless you can draw from
similar
past experience, you typically shouldn't assume. It's far better to
start
off with cleaner code and improve it as your testing/profiling indicates,
than start off with an "optimized" version which is less readable and
who's
value you never really know.

Not just space concerns, but that is one of them. I just don't think that
you normalize for normalizing sake (and I know many would disagree - and
there positions are also valid). If I were going to sort this field, I
would agree with you, but it it takes just a couple lines of code to split
out the different numbers versus the amount of space and work for SQL to do
that is really unnecessary (at least in my mind).

Another example of normalization that I would have a problem with would be
phone numbers.

If I have an employee record with 4 phone numbers (home,work,fax and cell),
I would put the phone numbers on the employee record.

Someone else might set up a Phone table with 3 fields

Create table Phone(
PhoneID int,
EmployeeID int,
PhoneType int,
PhoneNumber)

Then you would need a table for Phone types:

Create Table PhoneTypes(
PhoneType int,
PhoneDescription)

A little much, if you ask me.

I have no doubt that your solution, while seemingly very efficient, is far
uglier and difficult to maintain. Not knowing your system, I'll leave it
to
you to figure out what tradeoffs are necessary, but I certainly hope that
you'll go through due deligence and not make assumptions. Every day of
my
life lately I'm stuck in a world where someone else made decisions based
on
assumptions which (a) proved to be uterly flawed and (b) resulted in
millions of dollars worth of code being thrown out.

As you say, efficient. And easy to maintain.

I agree on decision that need to be made. And these need to be made based
on valid assumptions. And you're right, many times you have to make changes
as assumptions change. That can happen to valid assumptions, as well.

There are no guarantees and you can't assume that things won't change.

We just finished a discussion on code/behind/beside/inside models (I don't
want to get into the discussion again as that was pretty well hashed out).
There were various reasons for each and all valid based on the situation.
But I feel you are making a mistake to stick strictly to a model just for
the models sake, if it doesn't fit the situation. Asp.net 2.0 may change
the way code/behind is used for a lot of people who use (and swear by it)
now.

I certainly don't wish to preach or come off sounding all high and mighty.
But the cost of hard of the extra hard drive room probably equals about 4
hours of your salary.....it's pretty obvious which should be the driving
factor between maintainabilty and disk usage...(again, these rules
certainly
have valid exceptions....)

I have no problem with your opinion. I learn a lot listening to everyones
opinion. It helps me shape mine. Sometimes it changes mine, other times
(even if the opinion is opposite of mine) it will reinforce mine.

I was told once that if you have to know the rules and then break them,
that's one thing.
To not know the rules and break them is just ignorance.

Tom.

Cheers!
Catch you on the flip side (I don't know what that means)

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)

I thought that was what you meant. The problem is why have all those
records suppose each search option has 15 Job Categories. That's a lot
of
records just to handle one field and a lot more work for Sql to do.

Maybe, you are thinking that there is only one field in the record.

Actually, the whole records is:

CREATE TABLE [dbo].[Searches] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
(e-mail address removed)