Bob Pokorny

I am attempting to connect to a variety of data providers (Access, SQL, Excel, etc...). Using VB2005, you can use the connect to database connection dialog which is great, when you are in design mode. Hoever, how can I invoke and communicate with a connection dialog box to create and test the data source connection

This doesn't seem like rocket science, but I have been having problems doing this. Your help is appreciated!

Bob



Re: Visual Basic General HOW DO I - Invoke data connection dialog?

Aw Ali

Yes you can do that. Since the database connection depends on the connection string you provide and the connection string includes the provider, you can make the connection dynamic based on user's selection. You must have some sort of dialog that will enable the user to select among the providers. Then based on the selection, you can modify the connection string and there it is.






Re: Visual Basic General HOW DO I - Invoke data connection dialog?

Bob Pokorny

I understand that - however, I do not know how to invoke the data connection wizard at run-time. Is there some way to open the dialog box(es) similair to how the font or color dialogs work It doesn't make sense to create my own dialog boxes that already exist via the data connection wizard available at design time.

So how can I start the connection dialog (wizard) and retrieve my validated connection string





Re: Visual Basic General HOW DO I - Invoke data connection dialog?

Aw Ali

Bob,

The data connection wizard you are talking about is a design time tool. It is not available at run-time. From what you have metioned earlier, you want your connection to dynamically direct to different providers based on user's selection. Since you want this to happen at run-time, you should hard-code the database connectivity. It is much simpler than invoking the connection wizard, though. Suppose you want to connect to three different providers, SQL, Access and Excel for example. Suppose you have a comboBox from which you can select which database to connect to.

In the case of pre attached SQL Database, use the connection string

Dim ConnStr As String ="Server=DTDPC59\SQLEXPRESS; initial catalog=Trial1; Integrated Security = SSPI"

Dim myCon As New SqlClient.SqlConnection(ConnStr)

myCon.Open()

-In the case of Acces , use the connection string

Dim AccessFile As String = "AccessFilePath"

Dim ConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & AccessFilePath

Dim myCon As New OleDb.OleDbConnection(ConnStr)

myCon.Open()

In case of Excel, use the connection string

Dim ExcelFile As String = "ExcelFilePath"

Dim ConnStr As String =

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ExcelFilePath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

Dim myCon As New OleDb.OleDbConnection(ConnStr)

myCon.Open()

It is you who will determine how to organize the logic. But the core issue here is instead of invoking the connection wizard, you just use simple selection technique like that of comboBox and connect to the selected database.






Re: Visual Basic General HOW DO I - Invoke data connection dialog?

Bob Pokorny

Well, that is what I was afraid of. I was hoping I could just use the wizard as to developing my own control. I still think it's kind of silly that I need to reinvent something that already exists in a design time environment.

I appreciate your help on this.

Thanks!

Bob





Re: Visual Basic General HOW DO I - Invoke data connection dialog?

Bob Pokorny

I was doing more thinking as I started creating my dialog windows...is there somewhere I can get information on how to do the following:

In the case of a standard ODBC Connection, how can I iterate through or get a list of available system and/or maching DSN's

In the case of a SQL Server Connection, how do I get a list of available servers, finally, how do I get a list of databases associated to the selected server

If I can find some VB code or have someone lead me in the direction to complete the above tasks...that would be great. Otherwise, pointing me in the right direction would help as well.





Re: Visual Basic General HOW DO I - Invoke data connection dialog?

cybertaz69

SQL Help examples.....

List the servers

Code Snippet

Imports System.Data.Sql

Imports System.Data

Imports System.Collections

Public Class SqlDataHandler

Public Function GetSqlServers() As ArrayList

Dim sqlSource As SqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance

Dim dt As DataTable = sqlSource.GetDataSources()

Dim serverList As New ArrayList()

For Each Dim dr As DataRow in dt.Rows

serverList.Add(dr("ServerName").ToString()

Next

Return aList

End Function

End Class

List DB Tables (sample from forum source....)

Code Snippet

Imports System.Data.SqlClient

Public Module Module1

Sub Main()

Dim strConn As String

Dim conn As SqlConnection

Dim cmd As SqlCommand

Dim dr, drTables As SqlDataReader

strConn = "Server =(local);"

strConn &= "Database = ; Integrated Security = SSPI;"

conn = New SqlConnection(strConn)

cmd = New SqlCommand("sp_Databases", conn)

cmd.CommandType = CommandType.StoredProcedure

conn.Open()

dr = cmd.ExecuteReader

If dr.HasRows Then

Do While dr.Read

Console.WriteLine(String.Format("Name {0} Size {1}", _

dr.Item("Database_Name"), dr.Item("Database_Size")))

Dim connTable As SqlConnection

strConn = "Server =(local);"

strConn &= "Database = " & dr.Item("Database_Name").ToString

strConn &= "; Integrated Security = SSPI;"

connTable = New SqlConnection(strConn)

Dim cmdTables As New SqlCommand("sp_Tables", connTable)

connTable.Open()

drTables = cmdTables.ExecuteReader

Do While drTables.Read

Console.Write(" ")

Console.WriteLine(drTables.Item("TABLE_NAME").ToString)

Loop

drTables.Close()

connTable.Close()

Loop

End If

dr.Close()

conn.Close()

End Sub

End Module

List Databases...

Code Snippet

Dim strConn As String

Dim conn As SqlConnection

Dim cmd As SqlCommand

Dim dr As SqlDataReader

strConn = "Server =(local);"

strConn &= "Database = ; Integrated Security = SSPI;"

conn = New SqlConnection(strConn)

cmd = New SqlCommand("sp_Databases", conn)

cmd.CommandType = CommandType.StoredProcedure

conn.Open()

dr = cmd.ExecuteReader

If dr.HasRows Then

Do While dr.Read

Trace.WriteLine(String.Format("Name {0} Size {1}", _

dr.Item("Database_Name"), dr.Item("Database_Size")))

Loop

End If

dr.Close()

conn.Close()






Re: Visual Basic General HOW DO I - Invoke data connection dialog?

Bob Pokorny

Awesome! Since I have to write the tools, this was exactly what I was looking for.

Thanks!





Re: Visual Basic General HOW DO I - Invoke data connection dialog?

Aw Ali

Please mark the post that addressed your issue as answered. It may help forum members looking for the same solution.