Question regarding Combo Box

[vmaruv]

hi,

In my Visual Basic Code, I am connecting to a SQL Server using SQL DMO. Later, I am listing all the databases available in the server. Once the user selects one of the database from the list, the name of the database needs to be assigned to a string. Down the line, I am using SQL Namespace object to connect display the properties of the databse by clicking the Properties button.

The problem I am facing is, Inspite of selecting a database from the list, I am not able to assign it to a string. When I click on the properties button, the properties of the first database in the list is displayed while I want the properties of the database selected to be displayed.
Please suggest.

Code :

If success = True Then
Set oSQLServer = SQL2000.SQLServers.ItemByID(SQLSrvrID)
Dim x As Integer
x = 1
For Each DB In oSQLServer.Databases
If Not oSQLServer.Databases x).SystemObject Then
Me.lstDBs.AddItem (oSQLServer.Databases(x).Name)
End If
x = x + 1
Next
strDatabase = lstDBs.Text

If lstDBs.ListIndex = -1 Then
MsgBox " Please select a Database from the List"
End If
If lstDBs.DataChanged = True Then
strDatabase = lstDBs.Text
End If
Set CurDB = oSQLServer.Databases(strDatabase)
nsArray(0) = oSQLNS.GetRootItem
nsArray(1) = oSQLNS.GetFirstChildItem(nsArray(0), SQLNSOBJECTTYPE_DATABASES)
nsArray(2) = oSQLNS.GetFirstChildItem(nsArray(1), SQLNSOBJECTTYPE_DATABASE, strDatabase)
Set oSQLNSObj = oSQLNS.GetSQLNamespaceObject(nsArray(2))
End If

 

[HarleyQuinn]

Hi,

Try putting either a breakpoint at strDatabase = lstDBs.Text
or adding debug.print strDatabase after that line to see what is contained within strDatabase.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[vmaruv]

I tried printing the value. I noticed that I is not even entering the IF condition - If lstDBs.DataChanged
even though I am selecting a database.

 

[HarleyQuinn]

That's a bit of a puzzler.
Is all of the above code in the same sub??

Harleyquinn

---------------------------------
For tsunami relief donations

 

[vmaruv]

yes. All the code is in the same sub.

What I suspect is, after listing all the databases, it checks if the database index is -1 and prompts if it is not selected. Immideately the control is passed to the next line before I select the database. not completely sure what is happening though !

Another thing is I am using a combo box with style as Dropdown Combo. Not sure if it is different for different styles. Please help.

 

[johnwm]

I believe that the DataChanged property only works with a BOUND control. If yours is not bound, you could set a module level variable using the Click event of the list and test that

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[HarleyQuinn]

So you select a DB (in lstDBs), it then repopulates lstDBs???

Harleyquinn

---------------------------------
For tsunami relief donations

 

[vmaruv]

Harleyquinn, I don't think it will repopulate lstDBs.

Here is my understanding of the code while I was coding. I'm new to VB programming. Please correct me if my logic is incorrect.

Once the connection to the server is successful (IF Success = True) get the list of databases into the combo box. Initially the value of the variable strDatabase is "" as no database is selected. Hence the message "Please select a Database from the List" is displayed. Then, once a database from the list is selected, the value of strDatabase should be set to the selected database so that the code
nsArray(2) = oSQLNS.GetFirstChildItem(nsArray(1), SQLNSOBJECTTYPE_DATABASE, strDatabase)
will be set to the database selected.

but I'm not sure how to set the value of the database selected to strDatabase.

 

[HarleyQuinn]

Got it

You were pretty much right. After it prompts you to select the database the rest of the code in the sub executes (therefore does not allow you time to select the DB before it has finished). Try putting the rest of the code after prompting the users selection in the Click event of the ComboBox and that should solve your problem.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

John, I think the DataChanged property does work (might not be the best choice of words ), albeit in a strange way not described in the help I have just looked at. If the ComboBox is not bound and the user doesn't select a value from it the DataChanged stays at the Default (False). Once a record is selected it changes to 'True' for (as far as I can tell) the life of the loaded form. Looking at the help I am assuming that this is not the way that it is intended to work (as your post describes) but more probably a quirk.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[vmaruv]

I have tried to put the part of the lstDBs.DataChanged code in the lstDBs.click() sub as below. but how do I pass the value of the strDatabase set back to the main sub ?
I still want to set the namespace objects etc in the main sub.


Private Sub lstDBs_Click()
If lstDBs.DataChanged Then
strDatabase = lstDBs.Text

End If
End Sub

 

[HarleyQuinn]

Sorry, I didn't mean just the If Statement. I meant all of the code after the msgbox. What I mean is that when the user is prompted to click that is the last part of the sub.
Then in the Click event all of the rest goes in.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[vmaruv]

Thank you Harleyquinn and every one else who helped me. My code is working as expected. I really can't tell you how great this site is !!

Regards to everyone.