469,568 Members | 1,455 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,568 developers. It's quick & easy.

Setting listbox items

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
Nov 19 '05 #1
8 1705


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" <ts**********@ftsolutions.com> wrote in message
news:OX**************@tk2msftngp13.phx.gbl...
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

Nov 19 '05 #2
"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:e8**************@TK2MSFTNGP09.phx.gbl...


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

--
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" <ts**********@ftsolutions.com> wrote in message
news:OX**************@tk2msftngp13.phx.gbl...
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


Nov 19 '05 #3
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" <ts**********@ftsolutions.com> wrote in message
news:Os**************@tk2msftngp13.phx.gbl...
"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:e8**************@TK2MSFTNGP09.phx.gbl...


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

--
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" <ts**********@ftsolutions.com> wrote in message
news:OX**************@tk2msftngp13.phx.gbl...
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



Nov 19 '05 #4

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:eO*************@TK2MSFTNGP10.phx.gbl...
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

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" <ts**********@ftsolutions.com> wrote in message
news:Os**************@tk2msftngp13.phx.gbl...
"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:e8**************@TK2MSFTNGP09.phx.gbl...
>
>
> 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
>
> --
> 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" <ts**********@ftsolutions.com> wrote in message
> news:OX**************@tk2msftngp13.phx.gbl...
>> 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
>>
>>
>
>



Nov 19 '05 #5
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!)
"tshad" <ts**********@ftsolutions.com> wrote in message
news:eu**************@TK2MSFTNGP12.phx.gbl...

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:eO*************@TK2MSFTNGP10.phx.gbl...
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


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" <ts**********@ftsolutions.com> wrote in message
news:Os**************@tk2msftngp13.phx.gbl...
"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:e8**************@TK2MSFTNGP09.phx.gbl...
>
>
> 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
>
> --
> 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" <ts**********@ftsolutions.com> wrote in message
> news:OX**************@tk2msftngp13.phx.gbl...
>> 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
>>
>>
>
>



Nov 19 '05 #6
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 ,
[email] [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" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:%2****************@TK2MSFTNGP10.phx.gbl...
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!)
"tshad" <ts**********@ftsolutions.com> wrote in message
news:eu**************@TK2MSFTNGP12.phx.gbl...

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:eO*************@TK2MSFTNGP10.phx.gbl...
> 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
>
>
> 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" <ts**********@ftsolutions.com> wrote in message
> news:Os**************@tk2msftngp13.phx.gbl...
>> "Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
>> net>
>> wrote in message news:e8**************@TK2MSFTNGP09.phx.gbl...
>> >
>> >
>> > 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
>> >
>> > --
>> > 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" <ts**********@ftsolutions.com> wrote in message
>> > news:OX**************@tk2msftngp13.phx.gbl...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>



Nov 19 '05 #7
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!)
"tshad" <ts**********@ftsolutions.com> wrote in message
news:ei**************@TK2MSFTNGP10.phx.gbl...
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 ,
[email] [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" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:%2****************@TK2MSFTNGP10.phx.gbl...
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!)
"tshad" <ts**********@ftsolutions.com> wrote in message
news:eu**************@TK2MSFTNGP12.phx.gbl...

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:eO*************@TK2MSFTNGP10.phx.gbl...
> 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
>
>
> 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" <ts**********@ftsolutions.com> wrote in message
> news:Os**************@tk2msftngp13.phx.gbl...
>> "Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
>> net>
>> wrote in message news:e8**************@TK2MSFTNGP09.phx.gbl...
>> >
>> >
>> > 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
>> >
>> > --
>> > 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" <ts**********@ftsolutions.com> wrote in message
>> > news:OX**************@tk2msftngp13.phx.gbl...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>



Nov 19 '05 #8
"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:uA**************@TK2MSFTNGP15.phx.gbl...
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!)
"tshad" <ts**********@ftsolutions.com> wrote in message
news:ei**************@TK2MSFTNGP10.phx.gbl...
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
,
[email] [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" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:%2****************@TK2MSFTNGP10.phx.gbl...
> 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!)
>
>
> "tshad" <ts**********@ftsolutions.com> wrote in message
> news:eu**************@TK2MSFTNGP12.phx.gbl...
>>
>> "Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
>> net>
>> wrote in message news:eO*************@TK2MSFTNGP10.phx.gbl...
>> > 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
>> >
>> >
>> > 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" <ts**********@ftsolutions.com> wrote in message
>> > news:Os**************@tk2msftngp13.phx.gbl...
>> >> "Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
>> >> net>
>> >> wrote in message news:e8**************@TK2MSFTNGP09.phx.gbl...
>> >> >
>> >> >
>> >> > 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
>> >> >
>> >> > --
>> >> > 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" <ts**********@ftsolutions.com> wrote in message
>> >> > news:OX**************@tk2msftngp13.phx.gbl...
>> >> >> 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
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>



Nov 19 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by 00_ChInkPoIntD12 | last post: by
2 posts views Thread by Hitesh | last post: by
3 posts views Thread by Ali Chambers | last post: by
7 posts views Thread by =?Utf-8?B?UmVraGE=?= | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.