selected values or typed not being passed to connectionstring

[jtammyg713]

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


I 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

 

[RiverGuy]

If you want to preserve your server name and database name for your connection string, then store them in variables in your SelectedIndexChanged event of your comboboxes.

 

[jtammyg713]

Hi Riverguy!

thank you for your reply!

I'm sorry but I am new to VB....how do I do that?

Thanks!

Regards,

Tammy

 

[chiph]

What network protocol are you specifying for connection to the database server?

The message seems to be indicating that the server has been configured to only allow connection over named-pipes.

Your connection string parameters and the configuration shown in the Sql Server Configuration Manager (under Network Configuration) have to match.

Chip H.


____________________________________________________________________
www.chipholland.com

 

[jtammyg713]

if I use this code below it works but using listboxes...I need to be able to type the name of the server instead of getting it from a listbox.


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