Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: HOW TO: List SQL Servers in a ComboBox

  1. #1
    Join Date
    Mar 2003
    Posts
    1,700
    Rep Power
    0

    Wink HOW TO: List SQL Servers in a ComboBox

    If you have ever wanted to create an application in which you can configure a custom SQL connection string, one of the key features of this configuration module in your application, should be a combo box in which you can show a list of all visible SQL Servers on your network.

    To do this:

    1. Drag a combo box onto your application from the ToolBox.

    2. Right-Click on your Project Name in the Solution Explorer, and select Add Reference.

    3. In the Add Reference dialog box that appears, go to the COM tab.

    4. Scroll down and search for "Microsoft SQLDMO Object Library". Select it, and click OK.

    5. Open a Form.Load subroutine for the form on which the combo box resides, and add the following code:


    Code:
            'List all available SQL Servers in the combo box
            Dim SQList As SQLDMO.NameList
            Dim SQLApp As New SQLDMO.Application
            Dim x As Integer
            SQList = SQLApp.ListAvailableSQLServers
            For x = 1 To SQList.Count
                ComboBoxServers.Items.Add(SQList.Item(x))
            Next
    Compile and run. If you have at least one SQL Server on your network (at least SQL Server 2000) it will be listed in the combo box's drop down list.

    This will work just as well in C# as it does in VB. Just change the code standard as necessary.

    Enjoy.

  2. #2
    Join Date
    Sep 2004
    Posts
    1,905
    Rep Power
    21

    Default

    Thanks, this is good to know, and informitive
    Let's act on what we agree on now, and argue later on what we don't.
    Black men leave Barbeque alone if Barbeque don't trouble you

  3. #3
    Join Date
    Mar 2006
    Posts
    35
    Rep Power
    0

    Default

    xenocrates what version of visual basic do you have?
    i have .net 2003

    when i run the project i get this error

    An unhandled exception of type 'System.InvalidCastException' occurred in WindowsApplication2.exe

    Additional information: QueryInterface for interface SQLDMO.NameList failed.

    which occurs on this line

    SQList = SQLApp.ListAvailableSQLServers

    Any ideas?

  4. #4
    Join Date
    Mar 2003
    Posts
    1,700
    Rep Power
    0

    Cool

    I'm using Visual Studio 2005. A few things:

    1. Are you sure you added the right COM module to your project? Look in your Project/Solution explorer under the References subfolder and see if you have SQLDMO.dll added there.

    2. Did you copy / paste the code EXACTLY as you see it typed here? If so, move on:

    3. Ensure that the second line of code is written explicitly as: Dim SQLApp As New SQLDMO.Application and not Dim SQLApp As New Application as some versions of Visual Studio automatically strip the SQLDMO from the definition.

    4. Is at least SQL 2000 installed on the machine on which you're running the code? It might not work with SQL Version 6 or earlier. The documentation for the SQLDMO dll said something about it not working for earlier versions of SQL.

    5. Are you trying to run this code on a box that doesn't have SQL installed? If so, it won't work - not without having the COM class for SQLDMO registered on that machine. You need to create a Setup & Deployment project for your app on a machine that has SQL installed before trying to run it on a box without SQL. The setup.exe (or .msi) file generated will register the COM class using a wrapper.

    6. If your box doesn't have SQL Installed, ensure that you have at least SQL Client Tools are installed on the machine that you're trying to run the code from. The client tools will register the COM class for the SQLDMO dll.

  5. #5
    Join Date
    Sep 2004
    Posts
    1,905
    Rep Power
    21

    Default

    This is my C# version. I used VS2005 and SQLDMO ver 8 when anding the "Microsoft SQLDMO Object Library" COM reference.
    Code:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    
    namespace ListSQLServers
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                //List all available SQL Servers in the combo box
                SQLDMO.Application  SQLApp =    new SQLDMO.Application();
    
                SQLDMO.NameList SQList = SQLApp.ListAvailableSQLServers();
    
                for (int i = 1; i <= SQList.Count; i++)
                {
                    comboBox1.Items.Add(SQList.Item(i));
                }
            }
        }
    }
    Let's act on what we agree on now, and argue later on what we don't.
    Black men leave Barbeque alone if Barbeque don't trouble you

  6. #6
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default

    One of my first .net projects was creating an application that would have full control of sqlserver..backup,restore,create tables and users and lots of other stuff...using the SQLDMO object....i'll see if i can dig it up....
    and post some code....

  7. #7
    Join Date
    Mar 2003
    Posts
    1,700
    Rep Power
    0

    Talking

    Excellent job crosswire. MOST Impressive.

    To All & Sundry

    There is another way to implement this without using SQLDMO directly. I have come to understand that the code doesn't work on all machines. This is primarily because the COM wrapper for the SQLDMO dll class doesn't register properly on all machines - especially those running Windows 2000. So a lot of people have in fact written in about the same problem woodsprite is having.

    Thus, I am currently doing some research re: Using the Windows Management Instrumentation instruction set. Add the System.Management reference to your project to use it in the same way you did the SQLDMO. Only this time, you add it from the .NET tab, and not the COM tab.

    After adding the reference to your project, you need to identify that you're using classes from that name space explicitly, like so:

    Code:
    'Vb.NET
    Imports System.Management
    Imports System.Management.Instrumentation
    
    
    //C#
    using System.Management
    using System.Management.Instrumentation
    ...at the top of your class (before the class header definition). After doing that, you can tap into any number of the Win32 class objects and pull information from the system, the network, the registry, etc.

    For example, the following code lists the Domains to which the current machine belongs (only works on a Windows Domain network) in a Combobox:

    Code:
    Dim mos As New ManagementObjectSearcher("SELECT * FROM Win32_ComputerSystem")
    Dim mo As ManagementObject
    For Each mo In mos.Get()
        Try
            ComboBox1.Items.Add(mo("Domain"))
        Catch ex As Exception
            'Put custom exception handling code here
        End Try
    Next
    Notice the first line uses something like SQL. It's called WQL, and you can use this kind of syntax to pull all kinds of neat information from the WMI library. When my research is complete (or it uncovers substantial enough information) then I will return here with my findings.

    I'm sure crosswire will jump ahead and start playing with this thing. If so, here's a head start:

    WMI Tasks for Scripts And Applications

    I'll be back.
    Last edited by Xenocrates; Jul 10, 2006 at 01:48 PM.

  8. #8
    Join Date
    Mar 2006
    Posts
    35
    Rep Power
    0

    Default

    I'm using 2003, that would explain why i couldn't get the code to work.

  9. #9
    Join Date
    Mar 2003
    Posts
    1,700
    Rep Power
    0

    Default

    No that shouldn't be an inhibitor. It's more specific to the version of SQL you're using.

    Questions:
    • What version of SQL are you using?

    • Is it installed on your machine?


    Let us know.

    Crosswire

    Suggestion: How about putting up your code in the C# section? I'm sure those guys'd love to see it.

  10. #10
    Join Date
    Sep 2004
    Posts
    281
    Rep Power
    0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •