I asked this question before.I got the following query but it does not work
I have a field in my database called "interests"
the values that will be stored in this field will be numbers like this 1,2,3
and each number respresents an interest for ex. 1=sport 2=music
I have the following function but it does not work!
first it inserts the record in the db.I want to update a specific field
second when I even user the insert with loop it inserts multiple record with each field with one splitted value
My request:I store the value of interest field for each user in the db like this(1,2,3)
How can I display the record with the original name
for ex.records in db 1,2,3
display records like this sport,music,computer





dim ids,numOfIds,count
     count = 0

     Function splitOnComer(stringVar)
        if isNull(stringVar) then
           'do nothing
        else
           ids = Split(stringVar, ",", -1, 0)
           numOfIds = (UBound(ids) +1)
        end if
     End function

     splitOnComer(request.form("gId"))
     do until count = numOfIds
        sql = "INSERT INTO userGroups(gid)VALUES(" & trim(ids(count)) & ");"
        conn.execute(sql)
        count = (count + 1)
     loop

There are many options for this... so you can be creative.

First off... you probably know this, but you are breaking the one of the rules of the first normal form of relational database... you are storing multiple values in a single field of a record. If you wernen't doing that this would simply be a matter of a sql query.

Anyway - here are a few ideas,
You could have a function to which you pass each interest id, and which returns the string associated with it:

function getInterest(byval interestId)
    Select Case clng(interestId)
        Case 1
            getInterest = "sport"
        Case 2
            getInterest = "music"
        Case 3
            getInterest = "programming"
        Case 4
            getInterest = "drinking"
    end select
end function

Alternatively, you could create an array that holds all the values for each id:

    arrInterests = split("sport:music:programming:drinking", ":")


and then access them in a similar method:

function getInterest(byval interestId)
    getInterest = arrInterests(clng(interestId))
end function

These are just a few ideas...
You could use a dictionary object as well, or make an clsInterests class to provide the services... etc.



Woody Z http://www.learntoprogramnow.com

Interesting, you say:
;;;I asked this question before.I got the following query but it does not work

I was the person who posted it, where does this code fail? Have a look at this post:

http://p2p.wrox.com/topic.asp?TOPIC_ID=43070
This person said "Hi mat41 the code worked like a dream"

You are asking a different question all together:
;;;How can I display the record with the original name
That function was never going to give you this

FYI as woodyz said check out normalization regarding good database design. Good practice says you should be storing an integer that represents a string

Wind is your friend
Matt

matt this was not me who said "it worked like a dream"

Yes I realize this, as I said:
"This person said "Hi mat41 the code worked like a dream""

The operative words being 'this person'

All I was doing was replying to your comment:
;;;I got the following query but it does not work
It does work. You are obviously trying to do something other than what its supposed to do. Can I assist you making it do what you want it to do?



Wind is your friend
Matt