Fidelisme
asked on
Database Selection
Hi Guys.
What i'm trying to do is creating a application using VB.net and SQL server 2005 with the following:
I have a Database called DB1 with 2 tables:
- Customer
- Preferences
When i create a new form and in order to place textboxes and datagrid on my form, i create a new DataSet and name it DsDB1.
Then to display the info on my form, i drag DsDB1 from the datasource window into my form and it automatically creates all the necessary fields and datagrid, with the appropriate bindingsource and tableadapter.
Now my problem comes here.
I have another database in my SQL server called DB2 with exactly the same tables and number of fields as DB1.
The only differences is with the content of the databases (Different records).
Is there a way to create an option: Select Database
meaning i get to select which database i want to use, select it and use it normally ??
Thanks a lot !!
What i'm trying to do is creating a application using VB.net and SQL server 2005 with the following:
I have a Database called DB1 with 2 tables:
- Customer
- Preferences
When i create a new form and in order to place textboxes and datagrid on my form, i create a new DataSet and name it DsDB1.
Then to display the info on my form, i drag DsDB1 from the datasource window into my form and it automatically creates all the necessary fields and datagrid, with the appropriate bindingsource and tableadapter.
Now my problem comes here.
I have another database in my SQL server called DB2 with exactly the same tables and number of fields as DB1.
The only differences is with the content of the databases (Different records).
Is there a way to create an option: Select Database
meaning i get to select which database i want to use, select it and use it normally ??
Thanks a lot !!
You can change the name manually, or you can retrieve a list of database names from the server. For example, to populate a ComboBox with the names of databases from your server, you could use:
Dim myConn As New SqlClient.SqlConnection("D ata Source=SQLServer;Initial Catalog=master")
Dim myCommand As New SqlClient.SqlCommand("SELE CT name FROM sysdatabases",myConn)
myConn.Open()
Dim dr As SqlClient.SqlDataReader = myCommand.ExecuteReader()
While dr.Read
myCombo.Items.Add(dr(0))
End While
Then, re-construct your connectionstring using whatever the user selects...
Hope this helps
Dim myConn As New SqlClient.SqlConnection("D
Dim myCommand As New SqlClient.SqlCommand("SELE
myConn.Open()
Dim dr As SqlClient.SqlDataReader = myCommand.ExecuteReader()
While dr.Read
myCombo.Items.Add(dr(0))
End While
Then, re-construct your connectionstring using whatever the user selects...
Hope this helps
Hi
well i hope this is what you are asking (Though wath out for abigius names in your dataset(s))
Make a combox with your known dabase names from your server
Dim sDbNames as String
Private Sub ComboBox1_SelectedIndexCha nged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexCha nged
sDbNames = ComboBox1.SelectedItem.ToS tring()
Call mServerCatalog()
End Sub
Sub mServerCatalog()
Try
Dim SQlconn As SqlClient.SqlConnection
SQlconn = New SqlClient.SqlConnection("D ata Source=SQLServer;Initial Catalog = '" & sDbNames &"';User ID=sa;password=fidelisme")
'Fetch your data or what ever
MessageBox.Show(" You selected this catalog " & sDbNames)
Catch
End try
End Sub
vbturo
well i hope this is what you are asking (Though wath out for abigius names in your dataset(s))
Make a combox with your known dabase names from your server
Dim sDbNames as String
Private Sub ComboBox1_SelectedIndexCha
sDbNames = ComboBox1.SelectedItem.ToS
Call mServerCatalog()
End Sub
Sub mServerCatalog()
Try
Dim SQlconn As SqlClient.SqlConnection
SQlconn = New SqlClient.SqlConnection("D
'Fetch your data or what ever
MessageBox.Show(" You selected this catalog " & sDbNames)
Catch
End try
End Sub
vbturo
ASKER
I'm capable of seeing the different databases i have in my SQL server:
Imports Microsoft.SqlServer.Manage ment.Smo
Imports Microsoft.SqlServer.Manage ment.Commo n
Public Class Form2
Dim connInfo As SqlConnectionInfo
Dim svrConn As ServerConnection
Dim svr As Server
Dim db As Database
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
connInfo = New SqlConnectionInfo("SQLServ er")
connInfo.ApplicationName = "SMO Application"
svrConn = New ServerConnection(connInfo)
svr = New Server(svrConn)
DisplayServerInfo()
End Sub
Private Sub DisplayServerInfo()
databasesCombo.Items.Clear ()
For Each dbase As Database In svr.Databases
databasesCombo.Items.Add(d base.Name)
Next
databasesCombo.SelectedInd ex = 0
End Sub
Private Sub connectButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles connectButton.Click
?????????????????????????? ???
End Sub
End Class
I want to go to Form1 where the datagrid should display the info from the database i selected
Note: in Form1, there's a datagrid and the textboxes.
I placed them by dragging them to form1
after doing that, i automatically got:
- DB1BindingSource
-DB1TableAdapter
-DB1BindingNavigator
I also created a DB1DataAdapter with the command: Select * from DB1
on Form1_Load i have:
Dim MySelect As String
MySelect = "select * from DB1 "
DB1Da.SelectCommand.Comman dText = MySelect
DsDB1.Clear()
DB1Da.Fill(Me.DsDB1)
Imports Microsoft.SqlServer.Manage
Imports Microsoft.SqlServer.Manage
Public Class Form2
Dim connInfo As SqlConnectionInfo
Dim svrConn As ServerConnection
Dim svr As Server
Dim db As Database
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
connInfo = New SqlConnectionInfo("SQLServ
connInfo.ApplicationName = "SMO Application"
svrConn = New ServerConnection(connInfo)
svr = New Server(svrConn)
DisplayServerInfo()
End Sub
Private Sub DisplayServerInfo()
databasesCombo.Items.Clear
For Each dbase As Database In svr.Databases
databasesCombo.Items.Add(d
Next
databasesCombo.SelectedInd
End Sub
Private Sub connectButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles connectButton.Click
??????????????????????????
End Sub
End Class
I want to go to Form1 where the datagrid should display the info from the database i selected
Note: in Form1, there's a datagrid and the textboxes.
I placed them by dragging them to form1
after doing that, i automatically got:
- DB1BindingSource
-DB1TableAdapter
-DB1BindingNavigator
I also created a DB1DataAdapter with the command: Select * from DB1
on Form1_Load i have:
Dim MySelect As String
MySelect = "select * from DB1 "
DB1Da.SelectCommand.Comman
DsDB1.Clear()
DB1Da.Fill(Me.DsDB1)
Is DB1 a table?
<<I have a Database called DB1 with 2 tables:
<<- Customer
<<- Preferences
========================== ========== ========== ======
<< I also created a DB1DataAdapter with the command: Select * from DB1
<< on Form1_Load i have:
<< Dim MySelect As String
<< MySelect = "select * from DB1 "
<< DB1Da.SelectCommand.Comman dText = MySelect
<< DsDB1.Clear()
<< DB1Da.Fill(Me.DsDB1)
I belive your MySelect should contain a "select * from mytable"
but you have some issues here
You have already setup ( with the designer "wizzard" )... 1) a connection object (named ?) , 2) a data adapter DB1Da , 3) a dataset DsDB1
are they not populating the dataset ? , i assume you have your datagrid bound to DsDB1 ?
vbturbo
<<I have a Database called DB1 with 2 tables:
<<- Customer
<<- Preferences
==========================
<< I also created a DB1DataAdapter with the command: Select * from DB1
<< on Form1_Load i have:
<< Dim MySelect As String
<< MySelect = "select * from DB1 "
<< DB1Da.SelectCommand.Comman
<< DsDB1.Clear()
<< DB1Da.Fill(Me.DsDB1)
I belive your MySelect should contain a "select * from mytable"
but you have some issues here
You have already setup ( with the designer "wizzard" )... 1) a connection object (named ?) , 2) a data adapter DB1Da , 3) a dataset DsDB1
are they not populating the dataset ? , i assume you have your datagrid bound to DsDB1 ?
vbturbo
ASKER
oups ... sorry for the mistake ! Let me correct that.
Let us start again.
DB1 is the name of the database
MySelect = "select * from Customer"
MainCustDa.SelectCommand.C ommandText = MySelect
DsCustClear()
MainCustDa.Fill(Me.DsCust. Customer)
after doing that, i automatically get:
-MainCustBindingSource
-MainCustTableAdapter
-MainCustBindingNavigator
Let us start again.
DB1 is the name of the database
MySelect = "select * from Customer"
MainCustDa.SelectCommand.C
DsCustClear()
MainCustDa.Fill(Me.DsCust.
after doing that, i automatically get:
-MainCustBindingSource
-MainCustTableAdapter
-MainCustBindingNavigator
Well
it looks like you wish to switch between the 2 databases
Then you could
Dim MainCustDa1 As SqlClient.SqlDataAdapter
Dim sDbName as String
Dim MySelect As String
'select a catalog
Private Sub databasesCombo_SelectedInd exChanged( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles databasesCombo.SelectedInd exChanged
sDbNames = databasesCombo.SelectedIte m.ToString ()
End Sub
'connect to that catalog a populate your dataset
Private Sub connectButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles connectButton.Click
Dim SQlconn As SqlClient.SqlConnection
SQlconn = New SqlClient.SqlConnection("D ata Source=SQLServer;Initial Catalog = '" & sDbName &"';User ID=sa;password=fidelisme")
SQlconn.open()
Dim MySelect as String
MySelect = "select * from Customer"
MainCustDa1 = New SqlClient.SqlDataAdapter(M ySelect , SQlconn)
DsDB1.Clear()
MainCustDa1.Fill(Me.DsCust .Customer)
End Sub
But you'll 2 seperate adapters to achive that, but you can use the same dataset as long as you clear it before filling a new table
into it ! with a table that has the same name in each catalog(database)
vbturbo
it looks like you wish to switch between the 2 databases
Then you could
Dim MainCustDa1 As SqlClient.SqlDataAdapter
Dim sDbName as String
Dim MySelect As String
'select a catalog
Private Sub databasesCombo_SelectedInd
sDbNames = databasesCombo.SelectedIte
End Sub
'connect to that catalog a populate your dataset
Private Sub connectButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles connectButton.Click
Dim SQlconn As SqlClient.SqlConnection
SQlconn = New SqlClient.SqlConnection("D
SQlconn.open()
Dim MySelect as String
MySelect = "select * from Customer"
MainCustDa1 = New SqlClient.SqlDataAdapter(M
DsDB1.Clear()
MainCustDa1.Fill(Me.DsCust
End Sub
But you'll 2 seperate adapters to achive that, but you can use the same dataset as long as you clear it before filling a new table
into it ! with a table that has the same name in each catalog(database)
vbturbo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'll try working on that during the weekend.
If it doesn't work, u'll b hearing from me on monday ;)
Have a nice week end.
If it doesn't work, u'll b hearing from me on monday ;)
Have a nice week end.
Sorry Fidelisme
I first now realise that is tableadapter and not data adapters
please carry over with me
vbturbo
I first now realise that is tableadapter and not data adapters
please carry over with me
vbturbo
OK
Have a nice weekend
Have a nice weekend
ASKER
Hi vbturbo
Hope u had a nice week end too.
I tried what u gave me and it seems to work perfectly.
I have to try it on a big scale now.
Thanks a lot
Hope u had a nice week end too.
I tried what u gave me and it seems to work perfectly.
I have to try it on a big scale now.
Thanks a lot
ASKER
Dim SQlconn As SqlClient.SqlConnection
SQlconn = New SqlClient.SqlConnection("D
can't i simply change the name of the Catalog from DB1 to DB2 ??