How to get records from a ListBox

[sramesh]

Hello,

I have a list of databases in a ListBox (did the Insert into...Temporary). I need to run sql scripts on each of these database. I have created a vbscript to do the same. I am trying to get each record from the ListBox and getting the database from the Server and if the Database name is the same as the one from the listbox, then runnning the sql script.

I am unable to get the records from the listbox.

Can someone please help. I have been struggling with this for the past 3 days with no luck.

here is my vbscript

----------------------------------------
Set db = Session.Database

Const msiViewModifyInsertTemporary = 7
Const IDOK = 1

Dim SQLCmd
Dim srv
Dim objDB
Dim ServName
Dim UserID, Password
Dim dbNames
Dim viewlist, reclist

ServName = Cstr(Session.Property("SERVER_PROP")) & "\" & Cstr(Session.Property("INSTANCE_PROP"))
UserID = Cstr(Session.Property("USERID_PROP"))
Password = Session.Property("PASSWORD_PROP")

Set srv = CreateObject("SQLDMO.SQLServer")
srv.LoginTimeout = 15
srv.Connect ServName , UserID , Password
Set objDB = CreateObject("SQLDMO.Database")

Set viewlist = db.OpenView("SELECT * FROM `ListBox` WHERE `Property`='DBLIST_PROP'")
viewlist.Execute
Set reclist = viewlist.Fetch

While Not (reclist Is Nothing)
For Each objDB In srv.Databases
If objDB.SystemObject = False Then
If objDB.Name = reclist.Value Then
If err.number = vbEmpty then
SQLCmd = Session.Property("APPDIR") & "SQL Scripts\Test.sql"
objDB.ExecuteImmediate objFSO.OpenTextFile(SQLCmd).ReadAll
dbNames = dbNames & objDB.Name
Else
MsgBox "VBScript Error: " & err.Number
End If
End If
End If
Next
' viewlist.Modify 6, reclist ' 6 = delete
Set reclist = viewlist.Fetch
Wend

MsgBox "SQLDMO query done for " & dbNames

viewlist.Close
Set srv = Nothing
Set objDB = Nothing
Set objFSO = Nothing

[Ionut]

Hi,

In VBScript you can obtain the "Value" and "Text" for a ListBox/ComboBox record by using the "StringData" Property of the Record object you obtain from "viewlist.Fetch".

Code: Select all

Const QuerySelectControlItems = "SELECT * FROM `ListBox` WHERE `Property`='DBLIST_PROP'"

Set viewlist = db.OpenView(QuerySelectControlItems)
viewlist.Execute
Set reclist = viewlist.Fetch

While Not (reclist Is Nothing)
  MsgBox "Value=" & reclist.StringData(3) & " | " & "Text=" & reclist.StringData(4)
  Set reclist = viewlist.Fetch 
Wend

In the next version of Advanced Installer there will be predefined Custom Actions to populate, delete from and extract data from ComboBox and ListBox controls.

Hope this helps.

Regards,
Ionut

[sramesh]

Thank you very much.



I will try this.