|
 |
asp_databases thread: array input
Message #1 by "Michael Goldman" <mg188@h...> on Tue, 26 Sep 2000 22:11:35 -0700
|
|
One field of my html form asks for a list of items. I think I need a comma
delimiter in the array, since some of the items could be more than one word
long. For example, "web design" could be a single item. I want to pair
each of those items with a piece of info from another field on my html form,
and add the pair as a record to my database.
I'm using ASP, html, vbscript, ADO, SQL, and Access. Could someone point me
in the right direction, please? Is the "split" function in vbscript part of
the puzzle?
thanks in advance
Message #2 by "Jason A. Greenfeld" <jgreenfeld@r...> on Wed, 27 Sep 2000 08:47:56 -0400
|
|
I think this may solve your problem...
The split function would be your best bet.
Lets say you have 2 sets of strings
<%
str1 = "Red,Blue,Navy Blue,Orange"
str2 = "Coat,Suede Shoes,Stripes,Juice"
Dim ARYColor
Dim aryObject
Dim NewStr
ARYColor = Split(str1,",")
ARYObject = Split(str2,",")
for x = 1 to number of records or if the arrays have the same number of
entries, use UBound(aryName)
NewStr = ARYColor(x) & " " & ARYObject(x)
...Code for inserting into a DB or making a new array...
next
%>
In this example, you would ge 4 new strings:
Red Coat
Blue Suede Shoes
Navy Stripes
Orange Juice
Is this what you are looking for?
Jason Greenfeld
Rubicon Technologies, Inc.
http://www.rubicontechnologies.com
-----Original Message-----
From: Michael Goldman [mailto:mg188@h...]
Sent: Wednesday, September 27, 2000 1:12 AM
To: ASP Databases
Subject: [asp_databases] array input
One field of my html form asks for a list of items. I think I need a comma
delimiter in the array, since some of the items could be more than one word
long. For example, "web design" could be a single item. I want to pair
each of those items with a piece of info from another field on my html form,
and add the pair as a record to my database.
I'm using ASP, html, vbscript, ADO, SQL, and Access. Could someone point me
in the right direction, please? Is the "split" function in vbscript part of
the puzzle?
thanks in advance
Message #3 by "Michael Goldman" <mg188@h...> on Wed, 27 Sep 2000 16:10:08 -0700
|
|
Thanks, Jason, but I didn't ask the question right. I'll try again. Nice
site at Rubicon, btw.
My database has 3 tables, "Color", "ClothingItem" and "ColorClothingItem"
In the form, the user enters a list in one field, such as "Red,Blue,Orange".
In a different field, the user enters a single item, such as "Tiger Woods
socks"
My response asp page should:
1. split up the string "Red,Blue,Orange" and put these into an array.
(using split function as below?)
2. trim off any spaces, in case the user entered " Red, Blue,Orange"
(trim function?)
3. add records for Red, Blue and Orange to the "Color" table, if they
don't already exist. (use a For Each...Next loop?)
4. add socks to the "ClothingItem" table, along with other info from the
form, such as style, stock #, etc. (no problem)
5. get from the database the id numbers (autonumbered) for all 4 new
records. (??)
6. pair up the id numbers and add the pairs as new records to the
"ColorClothingItem" table. (not sure how to handle this)
The 3 new records in the "ColorClothingItem" table would be:
Red Tiger Woods socks
BlueTiger Woods socks
Orange Tiger Woods socks
thanks again,
Mike
----- Original Message -----
From: "Jason A. Greenfeld" <jgreenfeld@r...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, September 27, 2000 5:47 AM
Subject: [asp_databases] RE: array input
> I think this may solve your problem...
> The split function would be your best bet.
>
> Lets say you have 2 sets of strings
>
> <%
> str1 = "Red,Blue,Navy Blue,Orange"
> str2 = "Coat,Suede Shoes,Stripes,Juice"
>
> Dim ARYColor
> Dim aryObject
> Dim NewStr
>
> ARYColor = Split(str1,",")
> ARYObject = Split(str2,",")
>
>
> for x = 1 to number of records or if the arrays have the same number of
> entries, use UBound(aryName)
> NewStr = ARYColor(x) & " " & ARYObject(x)
> ...Code for inserting into a DB or making a new array...
> next
> %>
>
> In this example, you would ge 4 new strings:
> Red Coat
> Blue Suede Shoes
> Navy Stripes
> Orange Juice
>
>
> Is this what you are looking for?
>
>
>
> Jason Greenfeld
> Rubicon Technologies, Inc.
> http://www.rubicontechnologies.com
>
>
>
> -----Original Message-----
> From: Michael Goldman [mailto:mg188@h...]
> Sent: Wednesday, September 27, 2000 1:12 AM
> To: ASP Databases
> Subject: [asp_databases] array input
>
>
> One field of my html form asks for a list of items. I think I need a
comma
> delimiter in the array, since some of the items could be more than one
word
> long. For example, "web design" could be a single item. I want to pair
> each of those items with a piece of info from another field on my html
form,
> and add the pair as a record to my database.
>
> I'm using ASP, html, vbscript, ADO, SQL, and Access. Could someone point
me
> in the right direction, please? Is the "split" function in vbscript part
of
> the puzzle?
>
> thanks in advance
>
>
Message #4 by "Ken Schaefer" <ken@a...> on Thu, 28 Sep 2000 18:07:23 +1000
|
|
Michael
I think you have most of it right...
I'd change the database layout though, and the order in which you are doing
things.
Since you only have a N:M (1 to many) relationship, enter the "Tiger Wood's
Socks" into the ClothingItem table first.
Get the ID number
If you're using Access, here's the necessary code:
http://www.adOpenStatic.com/experiments/fastestautonumber.asp
If you're using SQL Server, use @@Identity
Then do the other steps you mentioned:
a) Use Split() on "Red, Green, Blue"
b) Use Trim() on each item in the Array to get rid of spaces
c) Do a Loop from 0 to UBound(arrArrayName) executing an insert statement
into your second table. You'd be inserting the ID number you got from the
first table, plus the value of the array element. (I don't think you'd need
a separate table that associated Tiger Wood's Socks with Red, with Green and
with Blue, or whatever)
HTH
Cheers
Ken
----- Original Message -----
From: "Michael Goldman" <mg188@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, September 28, 2000 9:10 AM
Subject: [asp_databases] RE: array input
> Thanks, Jason, but I didn't ask the question right. I'll try again. Nice
> site at Rubicon, btw.
>
> My database has 3 tables, "Color", "ClothingItem" and "ColorClothingItem"
>
> In the form, the user enters a list in one field, such as
"Red,Blue,Orange".
> In a different field, the user enters a single item, such as "Tiger Woods
> socks"
>
> My response asp page should:
> 1. split up the string "Red,Blue,Orange" and put these into an array.
> (using split function as below?)
> 2. trim off any spaces, in case the user entered " Red, Blue,Orange"
> (trim function?)
> 3. add records for Red, Blue and Orange to the "Color" table, if they
> don't already exist. (use a For Each...Next loop?)
> 4. add socks to the "ClothingItem" table, along with other info from
the
> form, such as style, stock #, etc. (no problem)
> 5. get from the database the id numbers (autonumbered) for all 4 new
> records. (??)
> 6. pair up the id numbers and add the pairs as new records to the
> "ColorClothingItem" table. (not sure how to handle this)
>
> The 3 new records in the "ColorClothingItem" table would be:
> Red Tiger Woods socks
> BlueTiger Woods socks
> Orange Tiger Woods socks
>
> thanks again,
>
> Mike
Message #5 by "Michael Goldman" <mg188@h...> on Thu, 28 Sep 2000 10:27:33 -0700
|
|
I think it's many to many relationship though. For example, a user could
also enter Joe Picscopo Duck Shoes in Red and Green, or Cindy Crawford
Stilleto Shoes in Green, Yellow and Blue, or any other combination of
clothing and color. So I still need that ColorClothingItem table, don't I?
Else, how would another user run a search for all green shoes, for example?
My real application could potentially have thousands of either category, so
it wouldn't be practical to make a list in advance. I want users to enter
the actual items, and then be able to search for combinations.
Given the above, am I right about many to many? If so, any advice on step
6?
Thanks for all, including yet another useful link. This is a great
newsgroup.
Michael
----- Original Message -----
From: "Ken Schaefer" <ken@a...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, September 28, 2000 1:07 AM
Subject: [asp_databases] RE: array input
> Michael
>
> I think you have most of it right...
>
> I'd change the database layout though, and the order in which you are
doing
> things.
> Since you only have a N:M (1 to many) relationship, enter the "Tiger
Wood's
> Socks" into the ClothingItem table first.
>
> Get the ID number
> If you're using Access, here's the necessary code:
> http://www.adOpenStatic.com/experiments/fastestautonumber.asp
> If you're using SQL Server, use @@Identity
>
> Then do the other steps you mentioned:
> a) Use Split() on "Red, Green, Blue"
> b) Use Trim() on each item in the Array to get rid of spaces
> c) Do a Loop from 0 to UBound(arrArrayName) executing an insert statement
> into your second table. You'd be inserting the ID number you got from the
> first table, plus the value of the array element. (I don't think you'd
need
> a separate table that associated Tiger Wood's Socks with Red, with Green
and
> with Blue, or whatever)
>
> HTH
>
> Cheers
> Ken
>
>
> ----- Original Message -----
> From: "Michael Goldman" <mg188@h...>
> To: "ASP Databases" <asp_databases@p...>
> Sent: Thursday, September 28, 2000 9:10 AM
> Subject: [asp_databases] RE: array input
>
>
> > Thanks, Jason, but I didn't ask the question right. I'll try again.
Nice
> > site at Rubicon, btw.
> >
> > My database has 3 tables, "Color", "ClothingItem" and
"ColorClothingItem"
> >
> > In the form, the user enters a list in one field, such as
> "Red,Blue,Orange".
> > In a different field, the user enters a single item, such as "Tiger
Woods
> > socks"
> >
> > My response asp page should:
> > 1. split up the string "Red,Blue,Orange" and put these into an array.
> > (using split function as below?)
> > 2. trim off any spaces, in case the user entered " Red,
Blue,Orange"
> > (trim function?)
> > 3. add records for Red, Blue and Orange to the "Color" table, if they
> > don't already exist. (use a For Each...Next loop?)
> > 4. add socks to the "ClothingItem" table, along with other info from
> the
> > form, such as style, stock #, etc. (no problem)
> > 5. get from the database the id numbers (autonumbered) for all 4 new
> > records. (??)
> > 6. pair up the id numbers and add the pairs as new records to the
> > "ColorClothingItem" table. (not sure how to handle this)
> >
> > The 3 new records in the "ColorClothingItem" table would be:
> > Red Tiger Woods socks
> > BlueTiger Woods socks
> > Orange Tiger Woods socks
> >
> > thanks again,
> >
> > Mike
>
>
>
Message #6 by "Daniel Walker" <danielw@w...> on Thu, 28 Sep 2000 19:14:48 +0100
|
|
Well, my temptation would be to use a junction table...
Simple table: three fields.
Autonumber primary key field.
Foreign key from one table
Foreign from t'other.
Each record then identifies a specific tie-up of primary from table A with
primary from table B.
HTH
Daniel Walker
Wrox Press
> I think it's many to many relationship though. For example, a user could
> also enter Joe Picscopo Duck Shoes in Red and Green, or Cindy Crawford
> Stilleto Shoes in Green, Yellow and Blue, or any other combination of
> clothing and color. So I still need that ColorClothingItem table, don't I?
> Else, how would another user run a search for all green shoes, for example?
> My real application could potentially have thousands of either category, so
> it wouldn't be practical to make a list in advance. I want users to enter
> the actual items, and then be able to search for combinations.
>
> Given the above, am I right about many to many? If so, any advice on step
> 6?
>
> Thanks for all, including yet another useful link. This is a great
> newsgroup.
>
> Michael
>
> ----- Original Message -----
> From: "Ken Schaefer" <ken@a...>
> To: "ASP Databases" <asp_databases@p...>
> Sent: Thursday, September 28, 2000 1:07 AM
> Subject: [asp_databases] RE: array input
>
>
> > Michael
> >
> > I think you have most of it right...
> >
> > I'd change the database layout though, and the order in which you are
> doing
> > things.
> > Since you only have a N:M (1 to many) relationship, enter the "Tiger
> Wood's
> > Socks" into the ClothingItem table first.
> >
> > Get the ID number
> > If you're using Access, here's the necessary code:
> > http://www.adOpenStatic.com/experiments/fastestautonumber.asp
> > If you're using SQL Server, use @@Identity
> >
> > Then do the other steps you mentioned:
> > a) Use Split() on "Red, Green, Blue"
> > b) Use Trim() on each item in the Array to get rid of spaces
> > c) Do a Loop from 0 to UBound(arrArrayName) executing an insert statement
> > into your second table. You'd be inserting the ID number you got from the
> > first table, plus the value of the array element. (I don't think you'd
> need
> > a separate table that associated Tiger Wood's Socks with Red, with Green
> and
> > with Blue, or whatever)
> >
> > HTH
> >
> > Cheers
> > Ken
> >
> >
> > ----- Original Message -----
> > From: "Michael Goldman" <mg188@h...>
> > To: "ASP Databases" <asp_databases@p...>
> > Sent: Thursday, September 28, 2000 9:10 AM
> > Subject: [asp_databases] RE: array input
> >
> >
> > > Thanks, Jason, but I didn't ask the question right. I'll try again.
> Nice
> > > site at Rubicon, btw.
> > >
> > > My database has 3 tables, "Color", "ClothingItem" and
> "ColorClothingItem"
> > >
> > > In the form, the user enters a list in one field, such as
> > "Red,Blue,Orange".
> > > In a different field, the user enters a single item, such as "Tiger
> Woods
> > > socks"
> > >
> > > My response asp page should:
> > > 1. split up the string "Red,Blue,Orange" and put these into an array.
> > > (using split function as below?)
> > > 2. trim off any spaces, in case the user entered " Red,
> Blue,Orange"
> > > (trim function?)
> > > 3. add records for Red, Blue and Orange to the "Color" table, if they
> > > don't already exist. (use a For Each...Next loop?)
> > > 4. add socks to the "ClothingItem" table, along with other info from
> > the
> > > form, such as style, stock #, etc. (no problem)
> > > 5. get from the database the id numbers (autonumbered) for all 4 new
> > > records. (??)
> > > 6. pair up the id numbers and add the pairs as new records to the
> > > "ColorClothingItem" table. (not sure how to handle this)
> > >
> > > The 3 new records in the "ColorClothingItem" table would be:
> > > Red Tiger Woods socks
> > > BlueTiger Woods socks
> > > Orange Tiger Woods socks
> > >
> > > thanks again,
> > >
> > > Mike
> >
> >
> >
Message #7 by "Ajax" <ajax@i...> on Thu, 21 Sep 2000 23:53:18 +0530
|
|
Maybe a nested SPLIT would help u, Like this code in VB
Dim strValues, arrPairs, arrSingles
strValues = "NAME, Jack; SEX, Male; CITY, Sydney; AGE, 24"
' Here each pair is separated by a ";" and each
'element of a pair is separated by a ","
'Build up this string in the above format
'to retrieve values out of that string
arrPairs = Split(strValues, ";")
For i = 0 To UBound(arrPairs)
arrSingles = Split(arrPairs(i), ",")
Debug.Print arrSingles(0) & vbTab & arrSingles(1)
Next i
Yes u are right when u said SPACE as a delimiter is not a good idea like the
case u mentioned. Another precaution that u need to take is with relation to
numeric values. because all values in the arrays are stored as strings, so u
may need to convert them before use
AJAX
-------------------------------------------------------
If it ain't what it looks like It ain't worth it
WYSIWYG Mantra
-------------------------------------------------------
-----Original Message-----
From: Michael Goldman [mailto:mg188@h...]
Sent: Wednesday, September 27, 2000 10:42 AM
To: ASP Databases
Subject: [asp_databases] array input
One field of my html form asks for a list of items. I think I need a comma
delimiter in the array, since some of the items could be more than one word
long. For example, "web design" could be a single item. I want to pair
each of those items with a piece of info from another field on my html form,
and add the pair as a record to my database.
I'm using ASP, html, vbscript, ADO, SQL, and Access. Could someone point me
in the right direction, please? Is the "split" function in vbscript part of
the puzzle?
thanks in advance
Message #8 by "Ken Schaefer" <ken@a...> on Fri, 29 Sep 2000 15:22:12 +1000
|
|
No it's not a many-many relationship
Unless you already have a definitive list of colours, and what to link
colours to clothes, it ends up being one to many...
To search for all green items, just search the table where you've stored the
colours. Use the FK from the other table to get the associated Person's
name...
Table1:
ID Name
1 Bill Clinton's socks
2 Tony Blair's mufflers
3 John Howard's undies
Table2
ID Table1ID Colour
1 1 Green
2 1 Blue
3 1 Grey
4 2 Green
5 2 Blue
6 3 Green
To find all the Green items, just search for "Green" in the Colour column of
the second table, and use the Table1ID to get the name of the person...
If you have a table which is a definitive list of all colours (though you
said below that you didn't want to do that), then you have a many-many
relationship and need a junction table like Daniel suggested.
Cheers
Ken
----- Original Message -----
From: "Michael Goldman" <mg188@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Friday, September 29, 2000 3:27 AM
Subject: [asp_databases] RE: array input
> I think it's many to many relationship though. For example, a user could
> also enter Joe Picscopo Duck Shoes in Red and Green, or Cindy Crawford
> Stilleto Shoes in Green, Yellow and Blue, or any other combination of
> clothing and color. So I still need that ColorClothingItem table, don't
I?
> Else, how would another user run a search for all green shoes, for
example?
> My real application could potentially have thousands of either category,
so
> it wouldn't be practical to make a list in advance. I want users to enter
> the actual items, and then be able to search for combinations.
>
> Given the above, am I right about many to many? If so, any advice on step
> 6?
>
> Thanks for all, including yet another useful link. This is a great
> newsgroup.
>
> Michael
>
> ----- Original Message -----
> From: "Ken Schaefer" <ken@a...>
> To: "ASP Databases" <asp_databases@p...>
> Sent: Thursday, September 28, 2000 1:07 AM
> Subject: [asp_databases] RE: array input
>
>
> > Michael
> >
> > I think you have most of it right...
> >
> > I'd change the database layout though, and the order in which you are
> doing
> > things.
> > Since you only have a N:M (1 to many) relationship, enter the "Tiger
> Wood's
> > Socks" into the ClothingItem table first.
> >
> > Get the ID number
> > If you're using Access, here's the necessary code:
> > http://www.adOpenStatic.com/experiments/fastestautonumber.asp
> > If you're using SQL Server, use @@Identity
> >
> > Then do the other steps you mentioned:
> > a) Use Split() on "Red, Green, Blue"
> > b) Use Trim() on each item in the Array to get rid of spaces
> > c) Do a Loop from 0 to UBound(arrArrayName) executing an insert
statement
> > into your second table. You'd be inserting the ID number you got from
the
> > first table, plus the value of the array element. (I don't think you'd
> need
> > a separate table that associated Tiger Wood's Socks with Red, with Green
> and
> > with Blue, or whatever)
> >
> > HTH
> >
> > Cheers
> > Ken
> >
> >
> > ----- Original Message -----
> > From: "Michael Goldman" <mg188@h...>
> > To: "ASP Databases" <asp_databases@p...>
> > Sent: Thursday, September 28, 2000 9:10 AM
> > Subject: [asp_databases] RE: array input
> >
> >
> > > Thanks, Jason, but I didn't ask the question right. I'll try again.
> Nice
> > > site at Rubicon, btw.
> > >
> > > My database has 3 tables, "Color", "ClothingItem" and
> "ColorClothingItem"
> > >
> > > In the form, the user enters a list in one field, such as
> > "Red,Blue,Orange".
> > > In a different field, the user enters a single item, such as "Tiger
> Woods
> > > socks"
> > >
> > > My response asp page should:
> > > 1. split up the string "Red,Blue,Orange" and put these into an
array.
> > > (using split function as below?)
> > > 2. trim off any spaces, in case the user entered " Red,
> Blue,Orange"
> > > (trim function?)
> > > 3. add records for Red, Blue and Orange to the "Color" table, if
they
> > > don't already exist. (use a For Each...Next loop?)
> > > 4. add socks to the "ClothingItem" table, along with other info
from
> > the
> > > form, such as style, stock #, etc. (no problem)
> > > 5. get from the database the id numbers (autonumbered) for all 4
new
> > > records. (??)
> > > 6. pair up the id numbers and add the pairs as new records to the
> > > "ColorClothingItem" table. (not sure how to handle this)
> > >
> > > The 3 new records in the "ColorClothingItem" table would be:
> > > Red Tiger Woods socks
> > > BlueTiger Woods socks
> > > Orange Tiger Woods socks
> > >
> > > thanks again,
> > >
> > > Mike
> >
> >
> >
>
|
|
|
View Cart
