Resetting Connection String 1

[cschristian78]

I am having trouble resetting the connection string to my master server. I have a dialog box that defaults the server and database to local and sa respectively. When the user selects refresh, I populate all the of the attached databases and display them in a combo box. The problem is that when the user types in another server name and the hits refresh again, i get the same results. I added a messagebox to see what the problem was and it appears that the connection string is not pulling the cmbserver.text value. Here is the code below.

thanks
**********Refresh Command**********
Private Sub cmd_refresh_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmd_refresh.Click
MessageBox.Show(cmbServer.Text.ToString)
Call SelectEDM()
End Sub

************Command Action************
Public Sub SelectEDM()
Try
'Open Connection to Server
MasterCon.Open()
'Query the Master Database for all databases attached
Dim daFindEDM As New SqlDataAdapter("SELECT DISTINCT name FROM sysdatabases ORDER BY name", MasterCon)
'Create a dataset and fill with results from query
daFindEDM.Fill(dsEDMsonMaster, "EDMsonMaster")
'Create a view of the results and send it to the combobox
dvEDMsonMaster = dsEDMsonMaster.Tables("EDMsonMaster").DefaultView
'View the dataview
cmb_EDM.DataSource = dvEDMsonMaster
Catch
MessageBox.Show("Error in Connecting to Server. Must have Correct Server, User ID and Password.", "Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
cmbServer.Text = "(local)"
txtUserID.Text = "sa"
cmbServer.Focus()
MasterCon.Dispose()
Exit Sub
Finally
MasterCon.Close()
MasterCon.Dispose()
End Try
End Sub


********Connection String**********
' Open the connection to the Master Database
Public MasterCon As New SqlClient.SqlConnection("server= " & frmSeekDB.DefInstance.cmbServer.Text & ";Database = master;User ID= " & frmSeekDB.DefInstance.txtUserID.Text & _
";Password= " & frmSeekDB.DefInstance.txtPassword.Text & "")

 

[ThatRickGuy]

Okay, I see where the MasterCon is instantiated. And I see where it is opened. But I don't see where you are changing it's connection string.

this line:

Public MasterCon As New SqlClient.SqlConnection("server= " & frmSeekDB.DefInstance.cmbServer.Text & ";Database = master;User ID= " & frmSeekDB.DefInstance.txtUserID.Text & _
           ";Password= " & frmSeekDB.DefInstance.txtPassword.Text & "")

should be changed to:

MasterCon = New SqlClient.SqlConnection("server= " & frmSeekDB.DefInstance.cmbServer.Text & ";Database = master;User ID= " & frmSeekDB.DefInstance.txtUserID.Text & _
           ";Password= " & frmSeekDB.DefInstance.txtPassword.Text & "")

and placed inside the try block right befor the Open command. As it appears right now, it is only getting set at the first run, so it will never connect to a different database.

-Rick

----------------------

http://www.ringdev.com

 

[cschristian78]

The connection is initiated on a vb.file that contains all of my connections to specific databases (see below). I will move the master connection as this is the only place within the program I will need it. That being said, what is the best way of setting a connection globally so that I do not have to restate the connection string?

Thanks,

Scott

Option Strict Off
Option Explicit On
Module DBconn

' Open the connection to the Master Database
Public MasterConDefault As New SqlClient.SqlConnection("server=.;Database = master;User ID=sa;trusted_connection=true")

' Open the connection to the Master Database
Public MasterCon As New SqlClient.SqlConnection("server= " & frmSeekDB.DefInstance.cmbServer.Text.ToString & ";Database = master;User ID=" & frmSeekDB.DefInstance.txtUserID.Text.ToString & _
";Password=" & frmSeekDB.DefInstance.txtPassword.Text.ToString & "")

' Open the connection to the ProfData Database
Public ProfDataCon As New SqlClient.SqlConnection("server= " & frmSeekDB.DefInstance.cmbServer.Text & ";Database=Profdata;User ID=" & frmSeekDB.DefInstance.txtUserID.Text & _
";Password=" & frmSeekDB.DefInstance.txtPassword.Text & "")

' Open the connection to the EDM Database
Public EDMCon As New SqlClient.SqlConnection( _
"server = " & frmSeekDB.DefInstance.cmbServer.Text & ";Database=" & frmSeekDB.DefInstance.cmb_EDM.Text & _
";User ID = " & frmSeekDB.DefInstance.txtUserID.Text & ";Password=" & frmSeekDB.DefInstance.txtPassword.Text & "")

End Module

 

[ThatRickGuy]

Declare the MasterCon globally, but set it locally.

-Rick

----------------------

http://www.ringdev.com

 

[ThatRickGuy]

err, do you have 4 different connection objects and you want to fill data based off of which of those 4 connections the user selects? Or do you have one connection object w/ 4 different databases?

-Rick

----------------------

http://www.ringdev.com

 

[cschristian78]

I would like the user to specify a number of things:
1. SQL servername locally or across a network
2. Select a database that is attached to that server.
3. Export to Excel from the results database.

So the flow of the program is this: Use the master database on a specifyed server and find all attached databases. (which I can do not..funny thing is that because the combobox is set to a dataview, I can't remove the items in the combobox.) Select a specifyed database and run queries. Post the queries to a third database where we will export queried results to formated Excel spreadsheets.

I hope this helps.

Thanks again

 

[cschristian78]

I guess I could use one connection and then specify the database. but if the database is on a different instance of SQL Server, I wont be able to use one connection. By setting up a connection for each, I then don't have to worry about changing my queries. Am I off target here?

 

[ThatRickGuy]

I don't think I'll have a chance to look into it tonight, but I'll see if I can't drum something up in the morning.

-Rick

----------------------

http://www.ringdev.com

 

[cschristian78]

Sounds good. Have a good night and we'll talk to you tomorrow.

Scott