Link to home
Start Free TrialLog in
Avatar of Fidelisme
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 !!
Avatar of Fidelisme
Fidelisme

ASKER

I forgot to mention that i'm using :
Dim SQlconn As SqlClient.SqlConnection
 SQlconn = New SqlClient.SqlConnection("Data Source=SQLServer;Initial Catalog = DB1;User ID=sa;password=fidelisme")

can't i simply change the name of the Catalog from DB1 to DB2 ??
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("Data Source=SQLServer;Initial Catalog=master")
Dim myCommand As New SqlClient.SqlCommand("SELECT 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
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_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

       sDbNames  = ComboBox1.SelectedItem.ToString()
       Call mServerCatalog()
    End Sub

    Sub mServerCatalog()

Try
   Dim SQlconn As SqlClient.SqlConnection
    SQlconn = New SqlClient.SqlConnection("Data 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
I'm capable of seeing the different databases i have in my SQL server:

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
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("SQLServer")
        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(dbase.Name)
        Next
        databasesCombo.SelectedIndex = 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.CommandText = MySelect
        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.CommandText = 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
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.CommandText = MySelect
        DsCustClear()
        MainCustDa.Fill(Me.DsCust.Customer)

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_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles   databasesCombo.SelectedIndexChanged

       sDbNames  = databasesCombo.SelectedItem.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("Data Source=SQLServer;Initial Catalog = '" & sDbName    &"';User ID=sa;password=fidelisme")
           SQlconn.open()

          Dim MySelect as String
          MySelect = "select * from Customer"
          MainCustDa1 = New SqlClient.SqlDataAdapter(MySelect , 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
ASKER CERTIFIED SOLUTION
Avatar of vbturbo
vbturbo
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.

Sorry Fidelisme

I first now realise that is tableadapter and not data adapters

please carry over with me

vbturbo
OK

Have a nice weekend
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