Dictionary Object

[Johnny Klunk]

Wondering if someone can give me a hand with something that I'm sure is
really easy - but damned if I know what I'm doing wrong. I'm trying to read
the contents of a database into an ASP dictionary object. However I'm
getting the error

Microsoft VBScript runtime error '800a01c9'
This key is already associated with an element of this collection
/test.asp, line 21

There's definately no repeated data in either column, it's currently only
test data, one column is 1,2,3,4 etc the other is a,b,c,d. I can confirm
it's reading the data properly if I use response.write instead of d.add
The code I'm using is this:

Set rsCount = Server.CreateObject ("ADODB.Recordset")
sqlView = "select data,number from tblTemp"
Set d=Server.CreateObject("Scripting.Dictionary")

rsCount.Open sqlView, "dsn=dsn_hits_2"
do while not rsCount.eof
d.add rsCount("number"),rsCount("data")
rsCount.MoveNext
loop
rsCount.close

Set rsCount = Nothing




Many thanks in advance.

[Ray at]

When you add an item to the dictionary object, the key must be unique. The
key is the first argument in the add method, which in this case, is
rsCount("number"). What that error means is that it's hitting a record that
has rsCount("number") with the same value as another record that has already
been inserted into the dictionary object. i.e.


number data
1 Joe
2 Kelly
3 Frank
2 Jorg

When it gets to the Jorg record and tries to use 2 as the key, that'll throw
an error since the Kelly item in the dictionary object already has a key of
2.

Possible Solutions:

Use an array instead of a dictionary object.

Use the primary key in your recordset if the key doesn't matter.

Use a counter for the key and increase it by one in your loop if the key
doesn't matter.

Probably some other things.

Ray at work


"Johnny Klunk" <johnnyklunk@:rem0ve-this:johnnyklunk.com> wrote in message
news:befbpm$47q$1@sparta.btinternet.com...

> Wondering if someone can give me a hand with something that I'm sure is
> really easy - but damned if I know what I'm doing wrong. I'm trying to

read

> the contents of a database into an ASP dictionary object. However I'm
> getting the error
>
> Microsoft VBScript runtime error '800a01c9'
> This key is already associated with an element of this collection
> /test.asp, line 21
>
> There's definately no repeated data in either column, it's currently only
> test data, one column is 1,2,3,4 etc the other is a,b,c,d. I can confirm
> it's reading the data properly if I use response.write instead of d.add
> The code I'm using is this:
>
> Set rsCount = Server.CreateObject ("ADODB.Recordset")
> sqlView = "select data,number from tblTemp"
> Set d=Server.CreateObject("Scripting.Dictionary")
>
> rsCount.Open sqlView, "dsn=dsn_hits_2"
> do while not rsCount.eof
> d.add rsCount("number"),rsCount("data")
> rsCount.MoveNext
> loop
> rsCount.close
>
> Set rsCount = Nothing
>
>
>
>
> Many thanks in advance.
>
>

[Johnny Klunk]

"Ray at <%=sLocation%>" <ask@me.forit> wrote in message
news:O3txsYZRDHA.2248@TK2MSFTNGP12.phx.gbl...

> When you add an item to the dictionary object, the key must be unique.

The

> key is the first argument in the add method, which in this case, is
> rsCount("number"). What that error means is that it's hitting a record

that

> has rsCount("number") with the same value as another record that has

already

> been inserted into the dictionary object. i.e.

Hi,
Thanks for the response. The thing is, the data is definately not repeated.
I entered the data manually to test and make there's no error. It's just
1,2,3,4 etc..
That's a good idea with the primary key. I've just tested again, setting
each of my columns as the primary key to ensure the database has no repeated
entries. I'm still getting the same thing. It's driving me nuts!

[Ray at]

Try this then and look:

rsCount.Open sqlView, "dsn=dsn_hits_2"
do while not rsCount.eof
response.write rsCount("number") & "," & rsCount("data") &
"<br />"
rsCount.MoveNext
loop
rsCount.close

Ray at work


"Johnny Klunk" <johnnyklunk@:rem0ve-this:johnnyklunk.com> wrote in message
news:befd6c$rv5$1@hercules.btinternet.com...

>
> "Ray at <%=sLocation%>" <ask@me.forit> wrote in message
> news:O3txsYZRDHA.2248@TK2MSFTNGP12.phx.gbl...

> > When you add an item to the dictionary object, the key must be unique.

> The

> > key is the first argument in the add method, which in this case, is
> > rsCount("number"). What that error means is that it's hitting a record

> that

> > has rsCount("number") with the same value as another record that has

> already

> > been inserted into the dictionary object. i.e.

>
>
> Hi,
> Thanks for the response. The thing is, the data is definately not

repeated.

> I entered the data manually to test and make there's no error. It's just
> 1,2,3,4 etc..
> That's a good idea with the primary key. I've just tested again, setting
> each of my columns as the primary key to ensure the database has no

repeated

> entries. I'm still getting the same thing. It's driving me nuts!
>
>

[Johnny Klunk]

"Ray at <%=sLocation%>" <ask@me.forit> wrote in message
news:e7IwBlZRDHA.1924@TK2MSFTNGP12.phx.gbl...

> Try this then and look:
>
> rsCount.Open sqlView, "dsn=dsn_hits_2"
> do while not rsCount.eof
> response.write rsCount("number") & "," & rsCount("data") &
> "<br />"
> rsCount.MoveNext
> loop
> rsCount.close
>
> Ray at work
>

Ray,
Thanks again for the quick response :) I've actually tried that idea - but
to be sure, to be sure I pasted your code in to make sure the results
matched. I've copy-pasted the output from test.asp below. You can see why
this is driving me nuts ! As far as I see, every row has different data in
each column.

a,1
b,2
c,3
d,4
e,5
f,6
g,7

[Chris Hohmann]

"Johnny Klunk" <johnnyklunk@:rem0ve-this:johnnyklunk.com> wrote in
message news:befbpm$47q$1@sparta.btinternet.com...

>d.add rsCount("number"),rsCount("data")

Try:
d.add rsCount.Fields.Item("number").Value,
rsCount.Fields.Item("data").Value

Also you can take advantage of the fact that the dictionary object will
create/overwrite a key based on whether it exists or not:
d(rsCount.Fields.Item("number").Value) =
rsCount.Fields.Item("data").Value

However this method wreaks of "programming by side effect" so if the
first method work, use that.

HTH
-Chris

[Chris Hohmann]

"Johnny Klunk" <johnnyklunk@:rem0ve-this:johnnyklunk.com> wrote in
message news:befga2$cga$1@sparta.btinternet.com...

>

> > d.add rsCount.Fields.Item("number").Value,

> rsCount.Fields.Item("data").Value

> >

>
>
> Yep, thats worked. Superb, thanks so much. No idea why my bit of

code

> didn't work, but I'm glad for a solution.
>
> Cheers
>
>

You code was attempting to set the dictionary key to a field Object, not
it's value. Whenever possible, try to explicitly reference values. When
you reference the value of a field object implicitly (i.e.
rsCount("number") you're asking the parser to do the following:

1. Determine the default property/method for the rsCount object, the
Fields collection
2. Determine the default property/method for the Fields collection, the
Item method
3. Determine the default property/method for the Item object, the Value
property
4. Determine if the Value property is an object
5. If it is an object do the whole object model default method/property
traversal thing again.
6. If not, return the Value

All of these steps are also dependent on the context in which they are
called. So sometimes the default thing happens, sometime it doesn't.
There are also a lot more going on before, between and after each of
these steps, but you get the idea. Always better to explicitly identify
the "value" you want.

HTH-
Chris

[Dave Karmens]

I have the following:
sub readcsv
dim csv_dsn, csv_filename, csv_oconn, csv_rs, csv_sql, itm
csv_dsn = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=D:\ul_tmp\;Extensions=asc,csv,tab,txt;" & _
"Persist Security Info=False"
csv_filename = session("filename")
set csv_oconn = server.createobject("ADODB.Connection")
set csv_rs = server.createobject("ADODB.Recordset")
csv_sql = "select * from " & csv_filename & ""
csv_oconn.open csv_dsn
set csv_rs = csv_oconn.execute(csv_sql)
response.write "<select name=""userflds""><br>"
response.write "<option value=""none"">none</option><br>"
for each itm in csv_rs.fields
response.write "<option value=""" & itm.name & """>" & itm.name &
"</option><br>"
next
response.write "</select><br>"
csv_rs.close
csv_oconn.close
set csv_oconn = nothing
set csv_rs = nothing
end sub

That builds select list with available user fields. However, I need this
to be loaded several times -- currently I just call the sub each time
but that takes way too long. I'm looking to load this once, then
populate a dictionary object and then use that to write out the select
list each time it's required. Any examples on how I coudl do that? Is
there a better way to do this?

[Ray at]

Instead of using a sub, make it a function that returns an array of items,
or make it a function that returns an html string from "<select>" to
"</select>." Then, you can do:


sVals = readcsv()
Response.Write sVals



'''later on down the page
Response.Write sVals


Are you familiar with Sub vs. Function?

Ray at work


"Dave Karmens" <spam_just1coder@yahoo.ca> wrote in message
news:epIuyfG$DHA.3712@tk2msftngp13.phx.gbl...

> I have the following:
> sub readcsv
> dim csv_dsn, csv_filename, csv_oconn, csv_rs, csv_sql, itm
> csv_dsn = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
> "Dbq=D:\ul_tmp\;Extensions=asc,csv,tab,txt;" & _
> "Persist Security Info=False"
> csv_filename = session("filename")
> set csv_oconn = server.createobject("ADODB.Connection")
> set csv_rs = server.createobject("ADODB.Recordset")
> csv_sql = "select * from " & csv_filename & ""
> csv_oconn.open csv_dsn
> set csv_rs = csv_oconn.execute(csv_sql)
> response.write "<select name=""userflds""><br>"
> response.write "<option value=""none"">none</option><br>"
> for each itm in csv_rs.fields
> response.write "<option value=""" & itm.name & """>" & itm.name &
> "</option><br>"
> next
> response.write "</select><br>"
> csv_rs.close
> csv_oconn.close
> set csv_oconn = nothing
> set csv_rs = nothing
> end sub
>
> That builds select list with available user fields. However, I need this
> to be loaded several times -- currently I just call the sub each time
> but that takes way too long. I'm looking to load this once, then
> populate a dictionary object and then use that to write out the select
> list each time it's required. Any examples on how I coudl do that? Is
> there a better way to do this?

[Dave Karmens]

Ah right. I was just looking up how to populate an array with a recordset.

Ray at <%=sLocation%> [MVP] wrote:

> Instead of using a sub, make it a function that returns an array of items,
> or make it a function that returns an html string from "<select>" to
> "</select>." Then, you can do:
>
>
> sVals = readcsv()
> Response.Write sVals
>
>
>
> '''later on down the page
> Response.Write sVals
>
>
> Are you familiar with Sub vs. Function?
>
> Ray at work
>
>
> "Dave Karmens" <spam_just1coder@yahoo.ca> wrote in message
> news:epIuyfG$DHA.3712@tk2msftngp13.phx.gbl...
>

>>I have the following:
>>sub readcsv
>>dim csv_dsn, csv_filename, csv_oconn, csv_rs, csv_sql, itm
>>csv_dsn = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
>>"Dbq=D:\ul_tmp\;Extensions=asc,csv,tab,txt;" & _
>>"Persist Security Info=False"
>>csv_filename = session("filename")
>>set csv_oconn = server.createobject("ADODB.Connection")
>>set csv_rs = server.createobject("ADODB.Recordset")
>>csv_sql = "select * from " & csv_filename & ""
>>csv_oconn.open csv_dsn
>>set csv_rs = csv_oconn.execute(csv_sql)
>>response.write "<select name=""userflds""><br>"
>>response.write "<option value=""none"">none</option><br>"
>>for each itm in csv_rs.fields
>>response.write "<option value=""" & itm.name & """>" & itm.name &
>>"</option><br>"
>>next
>>response.write "</select><br>"
>>csv_rs.close
>>csv_oconn.close
>>set csv_oconn = nothing
>>set csv_rs = nothing
>>end sub
>>
>>That builds select list with available user fields. However, I need this
>>to be loaded several times -- currently I just call the sub each time
>>but that takes way too long. I'm looking to load this once, then
>>populate a dictionary object and then use that to write out the select
>>list each time it's required. Any examples on how I coudl do that? Is
>>there a better way to do this?

>
>
>

[Ray at]

Ah, conventienly, the recordset object has a .GetRows method which will do
just that.

[url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthgetrows.asp[/url]

[url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoapireference.asp[/url]

Ray at work

"Dave Karmens" <spam_just1coder@yahoo.ca> wrote in message
news:e11U8VH$DHA.3536@tk2msftngp13.phx.gbl...

> Ah right. I was just looking up how to populate an array with a recordset.
>
> Ray at <%=sLocation%> [MVP] wrote:
>

> > Instead of using a sub, make it a function that returns an array of

items,

> > or make it a function that returns an html string from "<select>" to
> > "</select>." Then, you can do:
> >
> >
> > sVals = readcsv()
> > Response.Write sVals
> >
> >
> >
> > '''later on down the page
> > Response.Write sVals
> >
> >
> > Are you familiar with Sub vs. Function?
> >
> > Ray at work
> >
> >
> > "Dave Karmens" <spam_just1coder@yahoo.ca> wrote in message
> > news:epIuyfG$DHA.3712@tk2msftngp13.phx.gbl...
> >

> >>I have the following:
> >>sub readcsv
> >>dim csv_dsn, csv_filename, csv_oconn, csv_rs, csv_sql, itm
> >>csv_dsn = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
> >>"Dbq=D:\ul_tmp\;Extensions=asc,csv,tab,txt;" & _
> >>"Persist Security Info=False"
> >>csv_filename = session("filename")
> >>set csv_oconn = server.createobject("ADODB.Connection")
> >>set csv_rs = server.createobject("ADODB.Recordset")
> >>csv_sql = "select * from " & csv_filename & ""
> >>csv_oconn.open csv_dsn
> >>set csv_rs = csv_oconn.execute(csv_sql)
> >>response.write "<select name=""userflds""><br>"
> >>response.write "<option value=""none"">none</option><br>"
> >>for each itm in csv_rs.fields
> >>response.write "<option value=""" & itm.name & """>" & itm.name &
> >>"</option><br>"
> >>next
> >>response.write "</select><br>"
> >>csv_rs.close
> >>csv_oconn.close
> >>set csv_oconn = nothing
> >>set csv_rs = nothing
> >>end sub
> >>
> >>That builds select list with available user fields. However, I need this
> >>to be loaded several times -- currently I just call the sub each time
> >>but that takes way too long. I'm looking to load this once, then
> >>populate a dictionary object and then use that to write out the select
> >>list each time it's required. Any examples on how I coudl do that? Is
> >>there a better way to do this?

> >
> >
> >

[Dave Karmens]

Thanks Ray. I'll check those out.

Ray at <%=sLocation%> [MVP] wrote:

> Ah, conventienly, the recordset object has a .GetRows method which will do
> just that.
>
> [url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthgetrows.asp[/url]
>
> [url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoapireference.asp[/url]
>
> Ray at work
>
> "Dave Karmens" <spam_just1coder@yahoo.ca> wrote in message
> news:e11U8VH$DHA.3536@tk2msftngp13.phx.gbl...
>

>>Ah right. I was just looking up how to populate an array with a recordset.
>>
>>Ray at <%=sLocation%> [MVP] wrote:
>>
>>

>>>Instead of using a sub, make it a function that returns an array of

>
> items,
>

>>>or make it a function that returns an html string from "<select>" to
>>>"</select>." Then, you can do:
>>>
>>>
>>>sVals = readcsv()
>>>Response.Write sVals
>>>
>>>
>>>
>>>'''later on down the page
>>>Response.Write sVals
>>>
>>>
>>>Are you familiar with Sub vs. Function?
>>>
>>>Ray at work
>>>
>>>
>>>"Dave Karmens" <spam_just1coder@yahoo.ca> wrote in message
>>>news:epIuyfG$DHA.3712@tk2msftngp13.phx.gbl...
>>>
>>>
>>>>I have the following:
>>>>sub readcsv
>>>>dim csv_dsn, csv_filename, csv_oconn, csv_rs, csv_sql, itm
>>>>csv_dsn = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
>>>>"Dbq=D:\ul_tmp\;Extensions=asc,csv,tab,txt;" & _
>>>>"Persist Security Info=False"
>>>>csv_filename = session("filename")
>>>>set csv_oconn = server.createobject("ADODB.Connection")
>>>>set csv_rs = server.createobject("ADODB.Recordset")
>>>>csv_sql = "select * from " & csv_filename & ""
>>>>csv_oconn.open csv_dsn
>>>>set csv_rs = csv_oconn.execute(csv_sql)
>>>>response.write "<select name=""userflds""><br>"
>>>>response.write "<option value=""none"">none</option><br>"
>>>>for each itm in csv_rs.fields
>>>>response.write "<option value=""" & itm.name & """>" & itm.name &
>>>>"</option><br>"
>>>>next
>>>>response.write "</select><br>"
>>>>csv_rs.close
>>>>csv_oconn.close
>>>>set csv_oconn = nothing
>>>>set csv_rs = nothing
>>>>end sub
>>>>
>>>>That builds select list with available user fields. However, I need this
>>>>to be loaded several times -- currently I just call the sub each time
>>>>but that takes way too long. I'm looking to load this once, then
>>>>populate a dictionary object and then use that to write out the select
>>>>list each time it's required. Any examples on how I coudl do that? Is
>>>>there a better way to do this?
>>>
>>>
>>>

>
>

[Ray at]

P.S. Typical code for using an array that is returned by the .GetRows
method would be like:

sSQL = "SELECT [ID],[TheName],[TheSomethingElse] FROM [TheTable] WHERE
[TheColumn]='some value'"
oADO.Open YourConnectionString
Set rs = oADO.Execute(sSQL)
If Not rs.EOF Then aVals = rs.GetRows()
rs.Close : Set rs = Nothing
oADO.Close : Set oADO = Nothing

If Not IsEmpty(aVals) Then
For i = 0 To UBound(aVals, 2)
Response.Write "The ID is " & aVals(0,i) & "<br>"
Response.Write "The name is " & aVals(1,i) & "<br>"
Response.Write "The something else is " & aVals(2,i) & "<br>"
Response.Write "<hr>"
Next
Else
Response.Write "There was nothing"
End If

Unproofread!

Ray at work



"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:%23mqBmYH$DHA.3852@TK2MSFTNGP10.phx.gbl...

> Ah, conventienly, the recordset object has a .GetRows method which will do
> just that.
>
> [url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthgetrows.asp[/url]
>
>

[url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoapireference.asp[/url]

>
> Ray at work
>
> "Dave Karmens" <spam_just1coder@yahoo.ca> wrote in message
> news:e11U8VH$DHA.3536@tk2msftngp13.phx.gbl...

> > Ah right. I was just looking up how to populate an array with a

recordset.

> >
> > Ray at <%=sLocation%> [MVP] wrote:
> >

> > > Instead of using a sub, make it a function that returns an array of

> items,

> > > or make it a function that returns an html string from "<select>" to
> > > "</select>." Then, you can do:
> > >
> > >
> > > sVals = readcsv()
> > > Response.Write sVals
> > >
> > >
> > >
> > > '''later on down the page
> > > Response.Write sVals
> > >
> > >
> > > Are you familiar with Sub vs. Function?
> > >
> > > Ray at work
> > >
> > >
> > > "Dave Karmens" <spam_just1coder@yahoo.ca> wrote in message
> > > news:epIuyfG$DHA.3712@tk2msftngp13.phx.gbl...
> > >
> > >>I have the following:
> > >>sub readcsv
> > >>dim csv_dsn, csv_filename, csv_oconn, csv_rs, csv_sql, itm
> > >>csv_dsn = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
> > >>"Dbq=D:\ul_tmp\;Extensions=asc,csv,tab,txt;" & _
> > >>"Persist Security Info=False"
> > >>csv_filename = session("filename")
> > >>set csv_oconn = server.createobject("ADODB.Connection")
> > >>set csv_rs = server.createobject("ADODB.Recordset")
> > >>csv_sql = "select * from " & csv_filename & ""
> > >>csv_oconn.open csv_dsn
> > >>set csv_rs = csv_oconn.execute(csv_sql)
> > >>response.write "<select name=""userflds""><br>"
> > >>response.write "<option value=""none"">none</option><br>"
> > >>for each itm in csv_rs.fields
> > >>response.write "<option value=""" & itm.name & """>" & itm.name &
> > >>"</option><br>"
> > >>next
> > >>response.write "</select><br>"
> > >>csv_rs.close
> > >>csv_oconn.close
> > >>set csv_oconn = nothing
> > >>set csv_rs = nothing
> > >>end sub
> > >>
> > >>That builds select list with available user fields. However, I need

this

> > >>to be loaded several times -- currently I just call the sub each time
> > >>but that takes way too long. I'm looking to load this once, then
> > >>populate a dictionary object and then use that to write out the select
> > >>list each time it's required. Any examples on how I coudl do that? Is
> > >>there a better way to do this?
> > >
> > >
> > >

>
>

[Dave Karmens]

Great!

What if the sSQL is " SELECT * ", and the # columns is unknown?

Ray at <%=sLocation%> [MVP] wrote:

> P.S. Typical code for using an array that is returned by the .GetRows
> method would be like:
>
> sSQL = "SELECT [ID],[TheName],[TheSomethingElse] FROM [TheTable] WHERE
> [TheColumn]='some value'"
> oADO.Open YourConnectionString
> Set rs = oADO.Execute(sSQL)
> If Not rs.EOF Then aVals = rs.GetRows()
> rs.Close : Set rs = Nothing
> oADO.Close : Set oADO = Nothing
>
> If Not IsEmpty(aVals) Then
> For i = 0 To UBound(aVals, 2)
> Response.Write "The ID is " & aVals(0,i) & "<br>"
> Response.Write "The name is " & aVals(1,i) & "<br>"
> Response.Write "The something else is " & aVals(2,i) & "<br>"
> Response.Write "<hr>"
> Next
> Else
> Response.Write "There was nothing"
> End If
>
> Unproofread!
>
> Ray at work
>
>
>
> "Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
> message news:%23mqBmYH$DHA.3852@TK2MSFTNGP10.phx.gbl...
>

>>Ah, conventienly, the recordset object has a .GetRows method which will do
>>just that.
>>
>>[url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthgetrows.asp[/url]
>>
>>

>
> [url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoapireference.asp[/url]
>

>>Ray at work
>>
>>"Dave Karmens" <spam_just1coder@yahoo.ca> wrote in message
>>news:e11U8VH$DHA.3536@tk2msftngp13.phx.gbl...
>>

>>>Ah right. I was just looking up how to populate an array with a

>
> recordset.
>

>>>Ray at <%=sLocation%> [MVP] wrote:
>>>
>>>
>>>>Instead of using a sub, make it a function that returns an array of

>>
>>items,
>>

>>>>or make it a function that returns an html string from "<select>" to
>>>>"</select>." Then, you can do:
>>>>
>>>>
>>>>sVals = readcsv()
>>>>Response.Write sVals
>>>>
>>>>
>>>>
>>>>'''later on down the page
>>>>Response.Write sVals
>>>>
>>>>
>>>>Are you familiar with Sub vs. Function?
>>>>
>>>>Ray at work
>>>>
>>>>
>>>>"Dave Karmens" <spam_just1coder@yahoo.ca> wrote in message
>>>>news:epIuyfG$DHA.3712@tk2msftngp13.phx.gbl.. .
>>>>
>>>>
>>>>>I have the following:
>>>>>sub readcsv
>>>>>dim csv_dsn, csv_filename, csv_oconn, csv_rs, csv_sql, itm
>>>>>csv_dsn = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
>>>>>"Dbq=D:\ul_tmp\;Extensions=asc,csv,tab,txt; " & _
>>>>>"Persist Security Info=False"
>>>>>csv_filename = session("filename")
>>>>>set csv_oconn = server.createobject("ADODB.Connection")
>>>>>set csv_rs = server.createobject("ADODB.Recordset")
>>>>>csv_sql = "select * from " & csv_filename & ""
>>>>>csv_oconn.open csv_dsn
>>>>>set csv_rs = csv_oconn.execute(csv_sql)
>>>>>response.write "<select name=""userflds""><br>"
>>>>>response.write "<option value=""none"">none</option><br>"
>>>>>for each itm in csv_rs.fields
>>>>>response.write "<option value=""" & itm.name & """>" & itm.name &
>>>>>"</option><br>"
>>>>>next
>>>>>response.write "</select><br>"
>>>>>csv_rs.close
>>>>>csv_oconn.close
>>>>>set csv_oconn = nothing
>>>>>set csv_rs = nothing
>>>>>end sub
>>>>>
>>>>>That builds select list with available user fields. However, I need

>
> this
>

>>>>>to be loaded several times -- currently I just call the sub each time
>>>>>but that takes way too long. I'm looking to load this once, then
>>>>>populate a dictionary object and then use that to write out the select
>>>>>list each time it's required. Any examples on how I coudl do that? Is
>>>>>there a better way to do this?
>>>>
>>>>
>>>>

>>

>
>

[Ray at]

"Dave Karmens" <spam_just1coder@yahoo.ca> wrote in message
news:uNNJ%23jH$DHA.2308@tk2msftngp13.phx.gbl...

> Great!
>
> What if the sSQL is " SELECT * ", and the # columns is unknown?

Well, in an ideal world, you wouldn't ever use SELECT *.
([url]http://www.aspfaq.com/show.asp?id=2096[/url]) But, you can know the number of
columns by the UBound of the first dimension of the array.

iNumberOfColumns = UBound(theArray, 1) + 1

VBScript arrays are zero based, so the first column is 0, second 1, third 2,
and so on. Actually knowing the number of columns doesn't really matter, as
you could use code like:

<%
If Not IsEmpty(aVals) Then
For i = 0 To UBound(aVals, 2)
Response.Write "<tr>"
For iColumns = 0 To UBound(aVals, 1)
Response.Write "<td>" & aVals(iColumns, i) & "</td>"
Next
Response.Write "</tr>"
Next
Else
Response.Write "There was nothing"
End If
%>

Note that you could .GetString your recordset and just return a string of
<tr><td>values</td></tr>s, without having to loop like this.

Ray at work

[Dave Karmens]

I am only using SELECT * because the the file has an unknown # of
columns...

What I'm trying to do is write a little app that lets me upload a list
into a database and also have the ability to map columns from the list
to the columns in the database.

For this part I only want the first row so I can show the available
column names from the text file. So would not "select top 1" work?

Then, another function that runs a " select * " to perform the import
after the mapping is complete.


Ray at <%=sLocation%> [MVP] wrote:

> Well, in an ideal world, you wouldn't ever use SELECT *.
> ([url]http://www.aspfaq.com/show.asp?id=2096[/url]) But, you can know the number of
> columns by the UBound of the first dimension of the array.
>
> iNumberOfColumns = UBound(theArray, 1) + 1
>
> VBScript arrays are zero based, so the first column is 0, second 1, third 2,
> and so on. Actually knowing the number of columns doesn't really matter, as
> you could use code like:
>
> <%
> If Not IsEmpty(aVals) Then
> For i = 0 To UBound(aVals, 2)
> Response.Write "<tr>"
> For iColumns = 0 To UBound(aVals, 1)
> Response.Write "<td>" & aVals(iColumns, i) & "</td>"
> Next
> Response.Write "</tr>"
> Next
> Else
> Response.Write "There was nothing"
> End If
> %>
>
> Note that you could .GetString your recordset and just return a string of
> <tr><td>values</td></tr>s, without having to loop like this.
>
> Ray at work
>
>
>
>
>
>