Saved QueryString into MS SQL DB Now need to convert it into a string/array?

Question

I have saved a really big QueryString into a MS SQL column the string looks something like this:

&s1=Toledo,OH&s2=Chicago,IL&s3=Madison,WI.....and so on...

I would like to be able to do/have something like this in ASP-Classic:

Dim s1,s2,s3,s4....and son on...

s1="Toledo,OH"
s2="Chicago,IL"
s3="Madison,WI"
.....and son on.....

I would like to be able to call them like I would a QueryString for example a QueryString call would be Request.QueryString("s1") or I can use Do and loop all of the Request.QueryString("s" & i) until the query ="" then I would Exit the Do.

But how would I make all of this happen if I saved it the query.string into a MS DB Column?

Please help,

Thank you...

I keep getting this error: Variable is undefined: 's1', what am I doing wrong here ?

    Function qq(s)
      qq = """" & s & """"
    End Function ' qq


    Dim sInp    : sInp        = objRSConnSAVE("QSTRING")
      Dim dicData : Set dicData = Server.CreateObject("Scripting.Dictionary")
      Dim oRE     : Set oRE     = New RegExp
      oRE.Global  = True
      oRE.Pattern = "&([^=]+)=([^&]*)"
      Dim oMTS    : Set oMTS    = oRE.Execute(sInp)
      Dim oMT
      For Each oMT In oMTS
          dicData(oMT.SubMatches(0)) = oMT.SubMatches(1)
      Next

      Dim sKey, sValue
        For Each sKey In dicData.Keys
          sValue = dicData(sKey)
         '''// Response.write qq(sKey) & "=>" & qq(sValue)

      Next

    Response.write "TEST" & s1
   '// I even tried Response.write "TEST" & s(1) same error, how do I call it ?

Ekkehard.Horner · Accepted Answer · 2012-07-26 16:24:24Z

2

Use a RegExp (instead of Split) and a dictionary (instead of a bunch of scalar variables):

  Dim sInp    : sInp        = "&s1=Toledo,OH&s2=Chicago,IL&s3=Madison,WI"
  Dim dicData : Set dicData = CreateObject("Scripting.Dictionary")
  Dim oRE     : Set oRE     = New RegExp
  oRE.Global  = True
  oRE.Pattern = "&([^=]+)=([^&]*)"
  Dim oMTS    : Set oMTS    = oRE.Execute(sInp)
  Dim oMT
  For Each oMT In oMTS
      dicData(oMT.SubMatches(0)) = oMT.SubMatches(1)
  Next
  Dim sKey, sValue
  For Each sKey In dicData.Keys
      sValue = dicData(sKey)
      WScript.Echo qq(sKey), "=>", qq(sValue)
  Next

output:

"s1" => "Toledo,OH"
"s2" => "Chicago,IL"
"s3" => "Madison,WI"

UPDATE:

qq() is a function to double quote a string:

Function qq(s)
  qq = """" & s & """"
End Function ' qq

UPDATE II:

Use dicData("s2") to get Chicago,IL

edited Jul 26, 2012 at 16:24

answered Jul 26, 2012 at 14:37

Ekkehard.Horner

38.8k2 gold badges50 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AnthonyWJones Over a year ago

+1 nice approach with RegEx submatches. To be complete the values will need URI data decoding.

compcobalt Over a year ago

I keep getting Variable is undefined: 'qq' and if I add dim qq then I get type mismatched error, what am I doing wrong here ?

compcobalt Over a year ago

I had to change the code a lil I don't know if that is the problem. I will add the code at the end of the question so you can review it. Thank you so much for the help.

compcobalt Over a year ago

I added the qq function but I'm still getting the Error: Variable is undefined: 's1'. I will also update the code again on top so you can review it.

LittleBobbyTables - Au Revoir · Accepted Answer · 2012-07-26 15:05:36Z

1

Why declare s1, s2, etc.?

Create a variable to be used as an array, and use the Split function on the string by the ampersand (&), and then when you need to reference an individual row, split it again on the equal sign (=).

For example:

arMyArray = Split(YourQueryString, "&")
for i = 0 to uBound(arMyArray)
    key = Split(arMyArray(i), "=")(0)
    cityAndState = Split(arMyArray(i), "=")(1)
next

edited Jul 26, 2012 at 15:05

answered Jul 26, 2012 at 14:26

LittleBobbyTables - Au Revoir

33k25 gold badges113 silver badges115 bronze badges

4 Comments

AnthonyWJones Over a year ago

arMyArray will be 0 based not 1 based.

LittleBobbyTables - Au Revoir Over a year ago

@AnthonyWJones - You're correct, my initial version of YourQueryString was copied from the page and had a leading ampersand in it, throwing off my test.

compcobalt Over a year ago

Hi Thanks for the help but when I do this: Response.write arMyArray(1) I get s0=MONEE,IL but how do I get only MONEE,IL to show and not the S0=

compcobalt Over a year ago

I also tried Response.write cityAndState(1) but get an error but when I try Response.write cityAndState() then I get the last City,State but now do I make it so I can do array like cityAndState(0),cityAndState(1) and so on ??

Collectives™ on Stack Overflow

Saved QueryString into MS SQL DB Now need to convert it into a string/array?

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related