Solved

comboboxes.selectedvalue not getting passed to the connectionstring

Posted on 2008-06-10
3
859 Views
Last Modified: 2013-11-26
Hi!

Once again I have hit a brick wall here. 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


0
Comment
Question by:jtammyg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 7

Expert Comment

by:AUmidh
ID: 21756500
To get selected value of combo box use the followin

comboBox1.SelectedItem.ToString()
0
 
LVL 4

Expert Comment

by:CSLEEDS
ID: 21757978
hi, try to put ur connection string into a temp var before u assign it as the connection string to ur sql connection.

dim strTemp as string ="Data Source=" &
comboServers.SelectedValue & ";Initial Catalog=" &
comboDatabases.SelectedValue & ";Integrated Security=SSPI"

        With cn
            .ConnectionString = strTemp
            .Open()
        End With

and 1 more thing, add the last ";" for ur connection string at the end
& ";Integrated Security=SSPI;"
0
 

Accepted Solution

by:
jtammyg earned 0 total points
ID: 21762357
I have found the solution myself

Thank you all for your comments:

comboFilesets.Items.Clear()

 
        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 = "master"
            cnb.IntegratedSecurity = True

            cn.ConnectionString = cnb.ConnectionString
            cn.Open()

            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 If

        Me.comboFilesets.SelectedIndex = 0
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question