Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

VBScript - Use column titles in array vs column number

Experts,

I have a VBScript that converts a RecordSet to an Array, and now I'm attempting to make it more "readable" for me when accessing items out of the array.

Right now, I'm setting variables based on where the items are returning in the array:

                    Variable1 = arrRS(0,i)
                    Variable2 = arrRS(1,i)
                    Variable3 = arrRS(2,i)

ETC.

Right now, my fear is that at some point the RS order could change, and I'd have to revisit this code and figure out what columns represented what data all over again.

I'd like to return the code to how it handled itself when everything was still a RecordSet, where I could call data by it's column title in the record set.  I know Array's don't support this natively, but I'd like to do something as identified in the last post in this page:

http://www.webmasterkb.com/Uwe/Forum.aspx/vbscript/3452/Does-GetRows-Destroy-RecordSet-Field-Names

Unfortunately, I have absolutely now idea on the code that would be needed to make that happen.  IE, grab the titles from the RecordSet, then throw them into an Array.  Match the data that I stream from the original RecordSet, and pair the column number up with column title, then use that information to reference the data that's needed.  If this is even possible.

Would somebody be able to point me in the right direction?
0
usslindstrom
Asked:
usslindstrom
  • 5
  • 4
3 Solutions
 
Bill PrewCommented:
So, would something like this work?

                    Variable1 = arrRS(Fieldname1,i)
                    Variable2 = arrRS(Fieldname11,i)
                    Variable3 = arrRS(Fieldname12,i)

where the values of Fieldname1 etc are set in the code based on data from the recordset?

~bp
0
 
usslindstromAuthor Commented:
Thanks for chiming in here BillPrew...

That would definately be more friendly up front, but I think I'd still be stuck if the RS column order ever changed, in coming back to revisit this code.
0
 
Bill PrewCommented:
Not the way I'm thinking of it.  Let's say today the columns are named C1, C2, and C3, and they appear in that order.  Then this would work.

C1 = 0
C2 = 1
C3 = 2

V1 = arrRS(C1, i)
V2 = arrRS(C2, i)
V2 = arrRS(C3, i)

but then in the future, perhaps the column order becomes C1, C3, C2.  The the following change would accommodate this.

C1 = 0
C2 = 2
C3 = 1

V1 = arrRS(C1, i)
V2 = arrRS(C2, i)
V2 = arrRS(C3, i)

So what I'm proposing is getting the values to assign to C1, C2 and C3 variables from the Fields() collection, dynamically at runtime.

That make any more sense, or still off the mark.

~bp
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
usslindstromAuthor Commented:
I think I see what you're trying to show me here...


So, with my code, I'm needing to make the following changes from the original  *Please keep in mind that they're already out of order:

                    'First pass through.
                    strPrinterName = arrRS(0,i)
                    strProtocol = arrRS(4,i)
                    strPortNumber = arrRS(5,i)
                    strSNMPEnabled = arrRS(6,i)
                    strExtendedAttributes = arrRS(7,i)
                    strQueue = arrRS(8,i)
                    strDoubleSpool = arrRS(9,i)

                    'Second pass through.
                    strPrinterName = arrRS(0,i)
                    strDriverFileName = arrRS(1,i)
                    strDriverTextString = arrRS(2,i)
                    strModelNumber = arrRS(3,i)
                    strIsDefault = arrRS(10,i)
                    strPrinterSerialNumber = arrRS(11,i)

                        strPrinterSerialNumber = Replace(strPrinterSerialNumber,"""","")

                    strPrinterLocation = arrRS(12,1)
                    strManufacturer = arrRS(13,1)
                    strIsColor = arrRS(14,1)




To the following:

                    'First pass through.
                    strPrinterName = arrRS(PrinterName,i)
                    strDriverFileName = arrRS(DriverFileName,i)
                    strDriverTextString = arrRS(DriverTextString,i)
                    strModelNumber = arrRS(ModelNumber,i)
                    strProtocol = arrRS(Protocol,i)
                    strPortNumber = arrRS(PortNumber,i)
                    strSNMPEnabled = arrRS(SNMPEnabled,i)
                    strExtendedAttributes = arrRS(ExtendedAttributes,i)
                    strQueue = arrRS(Queue,i)
                    strDoubleSpool = arrRS(DoubleSpool,i)
                    strIsDefault = arrRS(IsDefault,i)
                    strPrinterSerialNumber = arrRS(PrinterSerialNumber,i)
                    strPrinterLocation = arrRS(PrinterLocation,1)
                    strManufacturer = arrRS(Manufacturer,1)
                    strIsColor = arrRS(IsColor,1)


                    'Second pass through.
                    strPrinterName = arrRS(PrinterName,i)
                    strDriverFileName = arrRS(DriverFileName,i)
                    strDriverTextString = arrRS(DriverTextString,i)
                    strModelNumber = arrRS(ModelNumber,i)
                    strProtocol = arrRS(Protocol,i)
                    strPortNumber = arrRS(PortNumber,i)
                    strSNMPEnabled = arrRS(SNMPEnabled,i)
                    strExtendedAttributes = arrRS(ExtendedAttributes,i)
                    strQueue = arrRS(Queue,i)
                    strDoubleSpool = arrRS(DoubleSpool,i)
                    strIsDefault = arrRS(IsDefault,i)
                    strPrinterSerialNumber = arrRS(PrinterSerialNumber,i)
                        strPrinterSerialNumber = Replace(strPrinterSerialNumber,"""","")
                    strPrinterLocation = arrRS(PrinterLocation,1)
                    strManufacturer = arrRS(Manufacturer,1)
                    strIsColor = arrRS(IsColor,1)

0
 
Bill PrewCommented:
Yes, and then you need to add logic to process the Fields collection and set the values or those index variables. I can help  with that if needed.

~bp
0
 
usslindstromAuthor Commented:
" I can help  with that if needed."

Yes please - very much so.  :)  If you can point me in the right direction here, I'd be forever in your debt (I already am, from earlier EE questions).
0
 
Bill PrewCommented:
Okay, with the record set open, do something like below.  Change the RS to the variable name of your RS, and adjust the quoted strings in the CASE statement to be the actual database field names.

For i = 0 To RS.Fields.Count - 1
   Select Case RS.Fields(i).Name
      Case "DoubleSpool"             DoubleSpool = i
      Case "DriverFileName"          DriverFileName = i
      Case "DriverTextString"        DriverTextString = i
      Case "ExtendedAttributes"      ExtendedAttributes = i
      Case "IsColor"                 IsColor = i
      Case "IsDefault"               IsDefault = i
      Case "Manufacturer"            Manufacturer = i
      Case "ModelNumber"             ModelNumber = i
      Case "PortNumber"              PortNumber = i
      Case "PrinterLocation"         PrinterLocation = i
      Case "PrinterName"             PrinterName = i
      Case "PrinterSerialNumber"     PrinterSerialNumber = i
      Case "Protocol"                Protocol = i
      Case "Queue"                   Queue = i
      Case "SNMPEnabled"             SNMPEnabled = i
   End Select
Next

Open in new window

0
 
usslindstromAuthor Commented:
Absolutely perfect!

Thank you so much for your assistance here!  It is very VERY much appreciated!
0
 
Bill PrewCommented:
Great, glad that idea worked out for you, thanks for the feedback.

~bp
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now