use SQL Query in asp to populate scripting dictionary

Question

this is an answer I received from another question. I was just wondering how I use an SQL Query in asp to add users to the scripting dictionary using a database instead of writing them manually.

Set bannedUsers = CreateObject("Scripting.Dictionary")
    bannedUsers.Add "johnsmith", True
    bannedUsers.Add "cmanson", True
    ...

    For Each opt In document.getElementById("frmNew").options
      If opt.selected And bannedUser.Exists(opt.text) Then
        MsgBox "This user is banned."
      End If
    Next

Ansgar Wiechers · Accepted Answer · 2013-06-15 09:51:07Z

2

You need to establish a connection to your database (in case it isn't already established), e.g.:

connectionString = "..."
Set conn = CreateObject("ADODB.Connection")
conn.open connectionString

This place has a collection of connection strings for various database backends.

With the connection established you run a query against the database. There are several ways to do this, e.g. like this:

query = "SELECT fieldname FROM table WHERE condition"
Set cmd = CreateObject("ADODB.Command")
cmd.CommandText = query
Set rs = cmd.Execute

or like this:

query = "SELECT fieldname FROM table WHERE condition"
Set rs = CreateObject("ADODB.Recordset")
rs.CursorPosition = 3
rs.open query, conn, 3, 1

Adjust fieldname, table and condition according to your actual data and requirements.

Fill the dictionary with the values from the database like this:

Set bannedUsers = CreateObject("Scripting.Dictionary")
Do Until rs.EOF
  bannedUsers.Add rs("fieldname").Value, True
  rs.MoveNext
Loop

If the table doesn't have a unique index on fieldname you may want to check the dictionary for the existence of the key before adding it:

If Not bannedUsers.Exists(rs("fieldname").Value) Then
  bannedUsers.Add rs("fieldname").Value, True
End If

Since you're querying a database anyway you don't even have to use a dictionary. You could disconnect the recordset and check it directly for user names:

query = "SELECT fieldname FROM table WHERE condition"
Set bannedUsers = CreateObject("ADODB.Recordset")
bannedUsers.CursorPosition = 3
bannedUsers.open query, conn, 3, 1

bannedUsers.ActiveConnection = Nothing  'disconnect recordset

For Each opt In document.getElementById("frmNew").options
  If opt.selected Then
    bannedUsers.Filter = "[fieldname] = '" & opt.text & "'"
    If bannedUser.recordCount > 0 Then
      MsgBox "This user is banned."
    End If
  End If
Next

answered Jun 15, 2013 at 9:51

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

6 Comments

neeko Over a year ago

Thank you for your reply! I will test this out, in regards to disconnecting the recordset, would this still account for new records that are added to the table, for example if I add new users?

Ansgar Wiechers Over a year ago

For new records in the database you'd have to re-run the query either way.

neeko Over a year ago

Oh I see, so the query is being run each time anyways, thank you for all your help!

Ansgar Wiechers Over a year ago

No, the query is (hopefully) not run each time. If it were, it'd be better to check the value in question directly against the database. The whole purpose of loading an in-memory data structure with values from a database is to improve performance by avoiding repeated queries to the database. However, if the data is subject to change, you will have to repopulate the data structure either periodically or triggered by your application.

neeko Over a year ago

how would I repopulate the data structure? Sorry I am very new to this

|

Max · Accepted Answer · 2013-06-14 22:36:15Z

0

This should do :

Set bannedUsersSet = conn.execute "SELECT DISTINCT LOGIN FROM BANNED_USERS /* Here goes your query */"
Set bannedUsers = CreateObject("Scripting.Dictionary")

While not bannedUsersSet.EOF
  bannedUsers(bannedUsersSet("LOGIN")) = True
  bannedUsersSet.MoveNext
WEnd
bannedUsersSet.close
Set bannedUsersSet = Nothing

answered Jun 14, 2013 at 22:36

Max

6854 silver badges14 bronze badges

Collectives™ on Stack Overflow

use SQL Query in asp to populate scripting dictionary

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related