locked
comboboxes.selectedvalue not getting passed to the connectionstring RRS feed

  • Question

  •  Hi!

    I have a combobox in which
    the user types the server name and then clicks on button 'CONNECT' to
    populate the next combobox which contains all the databases in that
    server. Then after the user selects a database, I have another button
    that he/she click and I want to retrieve file groups from a specific
    table. At this point when he/she clicks on that button I get an
    error:


    "An error has occurred while establishing a connection to the server.
    When connecting to SQL Server 2005, this failure may be caused by the
    fact that under the default settings SQL Server does not allow remote
    connections. (provider: Named Pipes Provider, error: 40 - Could not
    open a connection to SQL Server)"


    The error happens at the open line (button8_click):


    With cn
                .ConnectionString = "Data Source=" &
    comboServers.SelectedValue & ";Initial Catalog=" &
    comboDatabases.SelectedValue & ";Integrated Security=SSPI"
                .Open()
            End With


    Why am I getting this error? I also noticed that when i put the mouse
    over the .connectionstring, the datasource and the initial catalog
    are
    empty, which they shouldn't be, because I am passing the first
    combobox selectedvalue and the second combobox selectedvalue.


    How can I passed the values that the user typed in the first box and
    selected on the second one? That'll probably solve the problem. I
    tried selecteditem but did not work either and gave me the same error
    message.


    Thank you very much in advanced!


    Tammy


    Here is all my code from the beginning up to that button that is not
    working as it should.


    Imports System
    Imports System.IO
    Imports System.Collections
    Imports System.Data
    Imports System.Data.OleDb
    Imports System.Data.SqlClient
    Imports System.Configuration
    Imports System.Windows.Forms
    Imports Microsoft.SqlServer
    Imports Microsoft.SqlServer.Server
    Imports Microsoft.SqlServer.Management
    Imports Microsoft.SqlServer.Management.Smo
    Imports Microsoft.SqlServer.Management.Common
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports System.Text
    Imports System.Runtime.InteropServices


    Public Class Form1
        Private Declare Function ShellEx Lib "shell32.dll" Alias
    "ShellExecuteA" ( _
            ByVal hWnd As Integer, ByVal lpOperation As String, _
            ByVal lpFile As String, ByVal lpParameters As String, _
            ByVal lpDirectory As String, ByVal nShowCmd As Integer) As
    Integer


        'Public Class frmSQLConnection


        Private m_objServer As Server
        Public Property SMOServer() As Server
            Get
                Return m_objServer
            End Get
            Private Set(ByVal value As Server)
                m_objServer = value
            End Set
        End Property


        Private m_objDatabase As Database
        Public Property SMODatabase() As Database
            Get
                Return m_objDatabase
            End Get
            Private Set(ByVal value As Database)
                m_objDatabase = value
            End Set
        End Property


        Private Sub Mainform_Load(ByVal sender As System.Object, ByVal e
    As System.EventArgs) Handles MyBase.Load


            Dim objServers As DataTable
            Dim strServer As String


            '---- retrieve a list of SQL Server instances on the network
            objServers = SmoApplication.EnumAvailableSqlServers(False)


            For Each objRow As DataRow In objServers.Rows


                strServer = CStr(objRow("Server"))
                If Not TypeOf objRow("Instance") Is DBNull AndAlso
    CStr(objRow("Instance")).Length > 0 Then


                    strServer += "\" & CStr(objRow("Instance"))


                End If


                Me.comboServers.Items.Add(strServer)


            Next


            Me.comboDatabases.Enabled = False


        End Sub
        Private Sub button2_Click(ByVal sender As System.Object, ByVal e
    As System.EventArgs) Handles Button2.Click


            'Private Sub comboservers_SelectedIndexChanged(ByVal sender
    As
    System.Object, ByVal e As System.EventArgs) Handles
    comboServers.SelectedIndexChanged


            Dim objConn As ServerConnection


            'If Me.comboServers.Text.Trim.Length() = 0 Then


            objConn = New ServerConnection()


            If Me.comboServers.Text.Trim.Length() > 0 Then


                objConn.ServerInstance = Me.comboServers.Text.Trim()


            End If


            Me.SMOServer = New Server(objConn)


            'End If


            '---- Note: the connection will open when we call our first
    method on the Server object


            Me.comboDatabases.Items.Clear()


            For Each objDB As Database In Me.SMOServer.Databases


                Me.comboDatabases.Items.Add(objDB.Name)


            Next


            Me.comboDatabases.Enabled = True


            Me.comboDatabases.SelectedIndex = -1


        End Sub


        'Private Sub comboDatabases_Click(ByVal sender As System.Object,
    ByVal e As System.EventArgs)
        Private Sub button8_Click(ByVal sender As System.Object, ByVal e
    As System.EventArgs) Handles Button8.Click


            comboFilesets.Items.Clear()


            Dim cn As New SqlClient.SqlConnection()
            Dim cm As New SqlClient.SqlCommand()
            Dim dr As SqlClient.SqlDataReader


            With cn
                .ConnectionString = "Data Source=" &
    comboServers.SelectedValue & ";Initial Catalog=" &
    comboDatabases.SelectedValue & ";Integrated Security=SSPI"
                .Open()
            End With


            With cm
                .CommandText = "usp_DR_Spam_BB_Search_filesets"
                .CommandType = CommandType.StoredProcedure
                .Connection = cn
                .Parameters.AddWithValue("@Matter",
    comboDatabases.SelectedItem)
            End With


            dr = cm.ExecuteReader(CommandBehavior.CloseConnection)


            While dr.Read
                comboFilesets.Items.Add(dr.Item(0))
            End While


            dr.Close()
        End Sub


    If I use the following code, I cannot type the server name in the
    listbox and we are having problems with the sql server browser in our
    site so sometimes it shows the servers and sometimes it doesn't.
    That's why I wanted to actually type in the server name and then
    populate the databases like I am doing above (that part works fine).


    I hope this is clear enough....I know i am babbling at this
    point....sorry.


    Imports System
    Imports System.IO
    Imports System.Collections
    Imports System.Data
    Imports System.Data.OleDb
    Imports System.Data.SqlClient
    Imports System.Configuration
    Imports System.Windows.Forms
    Imports Microsoft.SqlServer
    Imports Microsoft.SqlServer.Server
    Imports Microsoft.SqlServer.Management
    Imports Microsoft.SqlServer.Management.Smo
    Imports Microsoft.SqlServer.Management.Common
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports System.Text
    Imports System.Runtime.InteropServices


    Public Class Form1
        Private Declare Function ShellEx Lib "shell32.dll" Alias
    "ShellExecuteA" ( _
            ByVal hWnd As Integer, ByVal lpOperation As String, _
            ByVal lpFile As String, ByVal lpParameters As String, _
            ByVal lpDirectory As String, ByVal nShowCmd As Integer) As
    Integer


        Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e
    As System.EventArgs) Handles MyBase.Load
            'just list local servers, set to false if you want to see all
    servers
            Dim dataTable = SmoApplication.EnumAvailableSqlServers(False)
            lstServers.ValueMember = "Name"
            lstServers.DataSource = dataTable
        End Sub


        Private Sub lstServers_SelectedIndexChanged(ByVal sender As
    System.Object, ByVal e As System.EventArgs) Handles
    lstServers.SelectedIndexChanged
            lstDatabases.Items.Clear()


            If lstServers.SelectedIndex <> -1 Then
                Dim serverName As String =
    lstServers.SelectedValue.ToString()
                Dim server As Server = New Server(serverName)
                Try
                    For Each database As Database In server.Databases
                        lstDatabases.Items.Add(database.Name)
                    Next


                Catch ex As Exception
                    Dim exception As String = ex.Message
                End Try
            End If
        End Sub


        Private Sub lstDatabases_Click(ByVal sender As System.Object,
    ByVal e As System.EventArgs) Handles lstDatabases.Click


            lstFileSets.Items.Clear()


            Dim cn As New SqlClient.SqlConnection()
            Dim cm As New SqlClient.SqlCommand()
            Dim dr As SqlClient.SqlDataReader


            With cn
                .ConnectionString = "Data Source=" &
    lstServers.SelectedValue & ";Initial Catalog=" &
    lstDatabases.SelectedValue & ";Integrated Security=SSPI"
                .Open()
            End With


            With cm
                .CommandText = "usp_DR_Spam_BB_Search_filesets"
                .CommandType = CommandType.StoredProcedure
                .Connection = cn
                .Parameters.AddWithValue("@Matter",
    lstDatabases.SelectedItem)
            End With


            dr = cm.ExecuteReader(CommandBehavior.CloseConnection)


            While dr.Read
                lstFileSets.Items.Add(dr.Item(0))
            End While


            dr.Close()
        End Sub
    Tuesday, June 10, 2008 7:09 PM

Answers

  • Here is an example of your Button8 code, changed to use a SqlConnectionStringBuilder:

            Dim cn As New SqlClient.SqlConnection()  
            Dim cnb As New SqlClient.SqlConnectionStringBuilder  
            Dim cm As New SqlClient.SqlCommand()  
            Dim dr As SqlClient.SqlDataReader  
     
     
            cnb.DataSource = comboServers.Text  
            cnb.InitialCatalog = comboDatabases.Text  
            cnb.IntegratedSecurity = True 
     
            cn.ConnectionString = cnb.ConnectionString  
            cn.Open() 

    You might also put the whole thing in an If block that checks for the comboboxes being empty:

            If Me.comboServers.Text.Length > 0 AndAlso Me.comboDatabases.Text.Length > 0 Then 
                Dim cn As New SqlClient.SqlConnection()  
                Dim cnb As New SqlClient.SqlConnectionStringBuilder  
                Dim cm As New SqlClient.SqlCommand()  
                Dim dr As SqlClient.SqlDataReader  
     
     
                cnb.DataSource = comboServers.Text  
                cnb.InitialCatalog = comboDatabases.Text  
                cnb.IntegratedSecurity = True 
     
                cn.ConnectionString = cnb.ConnectionString  
                cn.Open()  
     
                ''''''REST OF CODE'''''''  
            End If 

    See if the connection string has a value when you do it like this...
    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"
    • Marked as answer by jtammyg Wednesday, June 11, 2008 12:54 AM
    Tuesday, June 10, 2008 8:31 PM
    Moderator

All replies

  • Hmm... I can't see anything obviously wrong with Button8's code...

    Try creating a SqlConnectionStringBuilder and use it build the connection string.  Access the Text property of the comboboxes to get the value typed in or selected from the list.

    See if that helps...
    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"
    Tuesday, June 10, 2008 8:02 PM
    Moderator
  • Hi Reed!

    Thank you for taking time to answer me.

    I am not sure how to do that since I am new at coding in VB.

    Can you show me an example please?

    Thanks!

    Tammy
    Tuesday, June 10, 2008 8:10 PM
  • Reed,

    If I passed the server name and the database name as in the example below it that button works fine. But that is not what I need.

    I need a way to create variables for server and database after the user selects them and then pass those variables in the connectionstring below. I have no clue how to achieve this though.




    ComboFileset.Items.Clear()

    Dim cn As New SqlClient.SqlConnection()

    Dim cm As New SqlClient.SqlCommand()

    Dim dr As SqlClient.SqlDataReader



    With cn

    .ConnectionString = "Data Source=server001;Initial Catalog=master;Integrated Security=SSPI"

    .Open()

    End With



    With cm

    .CommandText = "usp_Search"

    .CommandType = CommandType.StoredProcedure

    .Connection = cn

    .Parameters.AddWithValue("@Matter", "Client_001")

    End With

    dr = cm.ExecuteReader(CommandBehavior.CloseConnection)



    While dr.Read

    ComboFileset.Items.Add(dr.Item(0))

    End While



    dr.Close()


    Tuesday, June 10, 2008 8:24 PM
  • Here is an example of your Button8 code, changed to use a SqlConnectionStringBuilder:

            Dim cn As New SqlClient.SqlConnection()  
            Dim cnb As New SqlClient.SqlConnectionStringBuilder  
            Dim cm As New SqlClient.SqlCommand()  
            Dim dr As SqlClient.SqlDataReader  
     
     
            cnb.DataSource = comboServers.Text  
            cnb.InitialCatalog = comboDatabases.Text  
            cnb.IntegratedSecurity = True 
     
            cn.ConnectionString = cnb.ConnectionString  
            cn.Open() 

    You might also put the whole thing in an If block that checks for the comboboxes being empty:

            If Me.comboServers.Text.Length > 0 AndAlso Me.comboDatabases.Text.Length > 0 Then 
                Dim cn As New SqlClient.SqlConnection()  
                Dim cnb As New SqlClient.SqlConnectionStringBuilder  
                Dim cm As New SqlClient.SqlCommand()  
                Dim dr As SqlClient.SqlDataReader  
     
     
                cnb.DataSource = comboServers.Text  
                cnb.InitialCatalog = comboDatabases.Text  
                cnb.IntegratedSecurity = True 
     
                cn.ConnectionString = cnb.ConnectionString  
                cn.Open()  
     
                ''''''REST OF CODE'''''''  
            End If 

    See if the connection string has a value when you do it like this...
    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"
    • Marked as answer by jtammyg Wednesday, June 11, 2008 12:54 AM
    Tuesday, June 10, 2008 8:31 PM
    Moderator
  • Hi Reed!

    This works like a charm!!!! :-) :-)

    Thank you so much for all your help and time.

    Why did it made a difference to make it this way?

    Also do you have a good book on Visual Basic 2008 for beginners?


    Thank you soooo much again!!!!!

    Best regards!

    Tammy
    Wednesday, June 11, 2008 12:53 AM
  • Well, the selected value won't return anything unless the ValueMember is set to a valid member of the first item in the list.  In this case your list is just strings so you can simply read the Text property of the ComboBox.  However, using SelectedItem should have worked too, so I'm not sure what happend when you tried it...  In any event, making use of the ConnectionStringBuilder is a good idea since it is there and easy to use.

    As for books, sorry no.  I like the F1 key and to explore intellisense - that's my preferred method of learning this stuff.  But the VB Developer Center has a lot of good info and many people rave about the video lessons that are available.  I would suggest starting at the dev center and with the MSDN library.

    Good luck!


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"
    Wednesday, June 11, 2008 5:17 PM
    Moderator