Solved

Connecting to multiple SQL databases

Posted on 2006-06-20
10
210 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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
 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

724 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