Solved

Connecting to multiple SQL databases

Posted on 2006-06-20
10
205 Views
Last Modified: 2008-02-01
I had a program that needs to connect to multiple databases.  Problem is though that I can't seem to figure out the connection string.  This is my first attempt at programing a windows application as well so perhaps I am overlooking something.  Any help is greatly appreciated.

Dim ConnString As String = "Server=odbc_DYNSQL;Database=" + code + ";Trusted_Connection=True;"
Using connection As New Data.Odbc.OdbcConnection(ConnString)
                Dim command As New Data.Odbc.OdbcCommand(queryString, connection)
                connection.Open()
                Dim reader As Data.Odbc.OdbcDataReader = command.ExecuteReader()
                If reader.HasRows Then
                    Try
                        While reader.Read()
                            If reader(0) Is DBNull.Value Then
                                period_ID = ""
                            Else
                                period_ID = reader(0).ToString.Trim
                            End If
                            If reader(1) Is DBNull.Value Then
                                year = ""
                            Else
                                year = reader(1).ToString.Trim
                            End If
                            If reader(2) Is DBNull.Value Then
                                per_name = ""
                            Else
                                per_name = reader(2).ToString.Trim
                            End If
                            If reader(3) Is DBNull.Value Then
                                financial = ""
                            Else
                                financial = reader(3).ToString.Trim
                            End If
                            If reader(4) Is DBNull.Value Then
                                sales = ""
                            Else
                                sales = reader(4).ToString.Trim
                            End If
                            If reader(5) Is DBNull.Value Then
                                purchasing = ""
                            Else
                                purchasing = reader(5).ToString.Trim
                            End If
                        End While
                    Finally
                        reader.Close()
                    End Try
                    'Writes Detail Line -->
                    PrintLine(1, period_ID, TAB(10), "| ", year, TAB(17), "| ", per_name, TAB(32), "| ", financial, TAB(45), "| ", sales, TAB(55), "| ", purchasing, TAB(70), "| ", code)
                    PrintLine(1, "_ _ _ _ _ | _ _ _ | _ _ _ _ _ _ _ |_ _ _ _ _ _ _|_ _ _ _ _ |_ _ _ _ _ _ _ _|_ _ _ _ _ _ _ _ _ ")
                End If
            End Using
            counter = counter + 1


I am also getting the "Datasource name not found and no default driver specified" error as well.

Thanks again
0
Comment
Question by:Optamis
  • 5
  • 3
  • 2
10 Comments
 
LVL 11

Assisted Solution

by:MacNuttin
MacNuttin earned 200 total points
ID: 16942116
It's not recognizing  " + code + "; as a database and you haven't named a table

Try:

Imports System.Data.SqlClient
Dim oSQLConn As SqlConnection = New SqlConnection()
oSQLConn.ConnectionString="Data Source=odbc_DYNSQL;Initial Catalog=YourTable;User ID=sa;Password=YourPassword;"
oSQLConn.Open()
0
 

Author Comment

by:Optamis
ID: 16942181
I suppose I forgot part of the code:  

For counter = 1 To 7
            If counter = 1 Then code = "FCR"
            If counter = 2 Then code = "FCSN"
            If counter = 3 Then code = "HOLD"
            If counter = 4 Then code = "JACN"
            If counter = 5 Then code = "JAIX"
            If counter = 6 Then code = "OPER"
            If counter = 7 Then code = "PAT"
            PrintLine(1, "                   |                   |                                   |                ")
            PrintLine(1, "PERIODID", TAB(10), "| YEAR1", TAB(17), "| PERNAME", TAB(32), " | FINANCIAL", TAB(45), "| SALES", TAB(55), "| PURCHASING", TAB(70), "| COMPANY_CODE")
            PrintLine(1, "__________|_______|_______________|_____________|__________|_______________|__________________")
            PrintLine(1, "          |       |               |             |          |               |                  ")
            Dim queryString As String = "select PERIODID, YEAR1, PERNAME," + _
            "(CASE PSERIES_1 WHEN 1  THEN 'CLOSED' ELSE 'OPEN' END) as FINANCIAL," + _
            "(CASE PSERIES_2 WHEN 1  THEN 'CLOSED' ELSE 'OPEN' END) as SALES," + _
            "(CASE PSERIES_3 WHEN 1  THEN 'CLOSED' ELSE 'OPEN' END) as PURCHASING," + _
            "from SY40100 where ODESCTN = '' and PERNAME <> 'Beginning Balance' and YEAR1 = '2006'" + _
            "order by PERIODID;"
            'Imports System.Data.SqlClient
            Dim oSQLConn As SqlClient.SqlConnection = New SqlClient.SqlConnection()
            oSQLConn.ConnectionString = "Data Source=odbc_DYNSQL;Initial Catalog=" + code + ";User ID=" + Name_box.Text + ";Password=" + Pwd_Box.Text + ";"
            oSQLConn.Open()
            Dim ConnString As String = "Dsn=odbc_DYNSQL;Uid=" + Name_box.Text + ";Pwd=" + Pwd_Box.Text + ""
            Using connection As New Data.Odbc.OdbcConnection(ConnString)
                Dim command As New Data.Odbc.OdbcCommand(queryString, connection)
                connection.Open()
                Dim reader As Data.Odbc.OdbcDataReader = command.ExecuteReader()
                If reader.HasRows Then
                    Try
                        While reader.Read()
                            If reader(0) Is DBNull.Value Then
                                period_ID = ""
                            Else
                                period_ID = reader(0).ToString.Trim
                            End If
                            If reader(1) Is DBNull.Value Then
                                year = ""
                            Else
                                year = reader(1).ToString.Trim
                            End If
                            If reader(2) Is DBNull.Value Then
                                per_name = ""
                            Else
                                per_name = reader(2).ToString.Trim
                            End If
                            If reader(3) Is DBNull.Value Then
                                financial = ""
                            Else
                                financial = reader(3).ToString.Trim
                            End If
                            If reader(4) Is DBNull.Value Then
                                sales = ""
                            Else
                                sales = reader(4).ToString.Trim
                            End If
                            If reader(5) Is DBNull.Value Then
                                purchasing = ""
                            Else
                                purchasing = reader(5).ToString.Trim
                            End If
                        End While
                    Finally
                        reader.Close()
                    End Try
                    'Writes Detail Line -->
                    PrintLine(1, period_ID, TAB(10), "| ", year, TAB(17), "| ", per_name, TAB(32), "| ", financial, TAB(45), "| ", sales, TAB(55), "| ", purchasing, TAB(70), "| ", code)
                    PrintLine(1, "_ _ _ _ _ | _ _ _ | _ _ _ _ _ _ _ |_ _ _ _ _ _ _|_ _ _ _ _ |_ _ _ _ _ _ _ _|_ _ _ _ _ _ _ _ _ ")
                End If
            End Using
            counter = counter + 1
        Next


I am trying to tie the login to two text boxes on the windows form Name_box.text and Pwd_box.text and trying to dynamically change the database on each pass through the For Next loop
0
 

Author Comment

by:Optamis
ID: 16942210
actually i changed the contents of the While reader.read too:

                        Do While reader.Read()
                            If reader(0) Is DBNull.Value Then
                                period_ID = ""
                            Else
                                period_ID = reader(0).ToString.Trim
                            End If
                            If reader(1) Is DBNull.Value Then
                                year = ""
                            Else
                                year = reader(1).ToString.Trim
                            End If
                            If reader(2) Is DBNull.Value Then
                                per_name = ""
                            Else
                                per_name = reader(2).ToString.Trim
                            End If
                            If reader(3) Is DBNull.Value Then
                                financial = ""
                            Else
                                financial = reader(3).ToString.Trim
                            End If
                            If reader(4) Is DBNull.Value Then
                                sales = ""
                            Else
                                sales = reader(4).ToString.Trim
                            End If
                            If reader(5) Is DBNull.Value Then
                                purchasing = ""
                            Else
                                purchasing = reader(5).ToString.Trim
                            End If
                            'Writes Detail Line -->
                            PrintLine(1, period_ID, TAB(10), "| ", year, TAB(17), "| ", per_name, TAB(32), "| ", financial, TAB(45), "| ", sales, TAB(55), "| ", purchasing, TAB(70), "| ", code)
                            PrintLine(1, "_ _ _ _ _ | _ _ _ | _ _ _ _ _ _ _ |_ _ _ _ _ _ _|_ _ _ _ _ |_ _ _ _ _ _ _ _|_ _ _ _ _ _ _ _ _ ")
                        Loop

0
 
LVL 11

Expert Comment

by:MacNuttin
ID: 16942366
You should create a view in one SQL database that includes the all the data you need from the 2 SQL Databases then connect to and read that view
0
 

Author Comment

by:Optamis
ID: 16942399
That was my original plan but the boss man won't approve that for some reason... you know how that goes...
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 11

Expert Comment

by:MacNuttin
ID: 16942746
No I've always been able to sell the full use of SQL based on how it speeds up any application
0
 
LVL 34

Accepted Solution

by:
Sancler earned 300 total points
ID: 16942799
Your code is pretty confusing.  To me, anyway ;-)

1)  It goes through an incrementing loop - For counter = 1 To 7.  That means that on each pass it will increment counter by 1 (without any other action on your part) until it finds counter > 7, then it will exit the loop.  But your code within that loop itself increments counter by 1 at the end so at the end of the first pass counter will be 2, not the 1 it started off as and so it will increment to 3 before the next pass through the loop.  This means that your line

            If counter = 2 Then code = "FCSN"
 
will never be hit.  Nor will 4, or 6.  Is that what you really want?

2)  On every pass through the loop you create and open (and never close) two new connections - oSQLConn and connection.  Is that what you really want?

3)  The ConnectionString that you use for the connection that you actually use for your datareader does not identify the database/catalog

            Dim ConnString As String = "Dsn=odbc_DYNSQL;Uid=" + Name_box.Text + ";Pwd=" + Pwd_Box.Text + ""

and has a blank string at the end - "".  I'm pretty sure that's not what you really want.

Can I suggest you sort those bits and pieces out and then let us know what the problems/error messages are and what lines they relate to?

Roger
0
 

Author Comment

by:Optamis
ID: 16942996
Here is my subprocedure as of right now:

Public Sub Print_Sub()
        Dim code As String = ""
        Dim test_site As String = ""
        Dim period_ID As String = ""
        Dim year As String = ""
        Dim per_name As String = ""
        Dim financial As String = ""
        Dim sales As String = ""
        Dim purchasing As String = ""
        Dim counter As Integer = 1
        'Writes File Headers -->
        For counter = 1 To 7
            If counter = 1 Then code = "FCR"
            If counter = 2 Then code = "FCSN"
            If counter = 3 Then code = "HOLD"
            If counter = 4 Then code = "JACN"
            If counter = 5 Then code = "JAIX"
            If counter = 6 Then code = "OPER"
            If counter = 7 Then code = "PAT"
            PrintLine(1, "                   |                   |                                   |                ")
            PrintLine(1, "PERIODID", TAB(10), "| YEAR1", TAB(17), "| PERNAME", TAB(32), " | FINANCIAL", TAB(45), "| SALES", TAB(55), "| PURCHASING", TAB(70), "| COMPANY_CODE")
            PrintLine(1, "__________|_______|_______________|_____________|__________|_______________|__________________")
            PrintLine(1, "          |       |               |             |          |               |                  ")
            Dim queryString As String = "select PERIODID, YEAR1, PERNAME," + _
            "(CASE PSERIES_1 WHEN 1  THEN 'CLOSED' ELSE 'OPEN' END) as FINANCIAL," + _
            "(CASE PSERIES_2 WHEN 1  THEN 'CLOSED' ELSE 'OPEN' END) as SALES," + _
            "(CASE PSERIES_3 WHEN 1  THEN 'CLOSED' ELSE 'OPEN' END) as PURCHASING," + _
            "from SY40100 where ODESCTN = '' and PERNAME <> 'Beginning Balance' and YEAR1 = '2006'" + _
            "order by PERIODID;"
            Dim ConnectionString As String = "Data Source=odbc_DYNSQL;Initial Catalog=" + code + ";User ID=" + Name_box.Text + ";Password=" + Pwd_Box.Text + ";"
            Using oSQLConn As SqlClient.SqlConnection = New SqlClient.SqlConnection(ConnectionString)
                Dim command As New System.Data.SqlClient.SqlCommand(queryString, oSQLConn)
                oSQLConn.Open()
                Dim reader As System.Data.SqlClient.SqlDataReader = command.ExecuteReader()
                If reader.HasRows Then
                    Try
                        Do While reader.Read()
                            If reader(0) Is DBNull.Value Then
                                period_ID = ""
                            Else
                                period_ID = reader(0).ToString.Trim
                            End If
                            If reader(1) Is DBNull.Value Then
                                year = ""
                            Else
                                year = reader(1).ToString.Trim
                            End If
                            If reader(2) Is DBNull.Value Then
                                per_name = ""
                            Else
                                per_name = reader(2).ToString.Trim
                            End If
                            If reader(3) Is DBNull.Value Then
                                financial = ""
                            Else
                                financial = reader(3).ToString.Trim
                            End If
                            If reader(4) Is DBNull.Value Then
                                sales = ""
                            Else
                                sales = reader(4).ToString.Trim
                            End If
                            If reader(5) Is DBNull.Value Then
                                purchasing = ""
                            Else
                                purchasing = reader(5).ToString.Trim
                            End If
                            'Writes Detail Line -->
                            PrintLine(1, period_ID, TAB(10), "| ", year, TAB(17), "| ", per_name, TAB(32), "| ", financial, TAB(45), "| ", sales, TAB(55), "| ", purchasing, TAB(70), "| ", code)
                            PrintLine(1, "_ _ _ _ _ | _ _ _ | _ _ _ _ _ _ _ |_ _ _ _ _ _ _|_ _ _ _ _ |_ _ _ _ _ _ _ _|_ _ _ _ _ _ _ _ _ ")
                        Loop
                    Finally
                        reader.Close()
                        oSQLConn.Close()
                    End Try
                End If
            End Using
        Next
    End Sub

I just tried a test run in our testing area and came back with the 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 default settings SQL Server does not allow remote connections (provider named pipes provider, error 40 - Could not open a connection to SQL Server)."

-any ideas?
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16943579
Sorry, the code (as code) now looks OK to me (although I haven't tested), and actual connection issues with SQL Server 2005 are not really my scene.  But a quick Google led me to

http://blogs.msdn.com/sql_protocols/archive/2005/11/14/492616.aspx

and following the link in that to

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Might that help?

Roger
0
 

Author Comment

by:Optamis
ID: 16945384
I finally got it all figured out, turns out my problem was in the DSN in the connection string, should of been Dynamics instead of odbc_dynsql.  This is what I get trying to develop a windows ap instead of my usual web stuff.  I'll split the points between you guys for the help, greatly appreciated!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now