?
Solved

ODBC Setup on Program Install

Posted on 2009-04-16
15
Medium Priority
?
300 Views
Last Modified: 2013-11-26
How do I include a System DSN Sql Server ODBC setup with my 'Setup and Deploy' program install?
0
Comment
Question by:dreinmann
  • 8
  • 7
15 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24161136
Have you been here yet?

http://support.microsoft.com/kb/184608
0
 

Author Comment

by:dreinmann
ID: 24161274
Actually, before I pursue that....do I need it?
If SQL Native Client is installed and I use the following connection string, will it work with setting up a 'System DSN' ODBC?

"Driver={SQL Native Client};Server=SeverName;Database=MyData;Trusted_Connection=yes;"
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 2000 total points
ID: 24161317
My question to you would then be do you need a DSN at all? You can pass connection strings to your connection object. For sql server check the link below for the method that suits your situation and just modify the particulars for your private data (I'm not sure if you're using 2005 or 2008, so there are links for each).

http://www.connectionstrings.com/sql-server-2005
http://www.connectionstrings.com/sql-server-2008

Once you put in the particulars, your connection object declaration should look something like:

Dim con As New System.Data.SqlClient.SqlConnection("Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;")
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:dreinmann
ID: 24161414
Check out this post, and you'll see why I can't/won't/etc. do that method.  Been there, tried that, suffered massively!

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24298145.html
0
 

Author Comment

by:dreinmann
ID: 24161630
Ok, this connection string does work without actually setting up the System DSN on the remote computer, but it does need to have SQL Native Client installed.

Can you answer me this?  This connection string seems to work with the connecting to the Views I have setup, but I got this error when I tried to connect to a Table?

ConnString: "Driver={SQL Native Client};Server=SeverName;Database=MyData;Trusted_Connection=yes;"
System.ArgumentException: Keyword not supported: 'driver'.

Is ODBC.ODBCConnection not allowed when connecting to the tables?
0
 

Author Comment

by:dreinmann
ID: 24161705
BTW, that  error does not happen in Debug mode.  Just when I run the actual installed program.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24161721
I C...


Yours will not add a DSN to the list in ODBC settings of Windows, but it should connect to the DB.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24161746
Are you using a SqlClient.SqlConnection object or an Odbc.OdbcConnection object?
0
 

Author Comment

by:dreinmann
ID: 24161805
Using Odbc.OdbcConnection.  From that other post, you can see I had massive problems when trying to use SqlClient.SqlConnection....that is whenever the View had something to do with a 'Linked Server'.  
Maybe I should try the SqlClient.SqlConnection when I'm connecting straight to the Tables?
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24161826
No. I was just making sure you were using the right connector.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24161933
Can you obscure your personal data (passwords and such) and post any code for how you are declaring and connecting?
0
 

Author Comment

by:dreinmann
ID: 24162000
Sure...see code below.
I tested using the odbc.odbcconnection for the views and the sqlclient.sqlconnection for the tables and it worked.
See anything wrong with that?


----CONNECT TO TABLE----
Public Shared LogManSqlConnString As String = "Data Source=ServerName;Initial Catalog=DBName;Integrated Security=SSPI;"
 
'Dim cmdBuilder As New System.Data.Odbc.OdbcCommandBuilder(da)
            Dim cn As New System.Data.SqlClient.SqlConnection(Globals.LogManSqlConnString)
            cn.Open()
 
            Dim da As New System.Data.SqlClient.SqlDataAdapter("SELECT * FROM UnionEmpData", cn)
            Dim ds As New DataSet()
            da.FillSchema(ds, SchemaType.Source, "UnionEmpData")
            da.Fill(ds, "UnionEmpData")
            Dim cmdBuilder As New System.Data.SqlClient.SqlCommandBuilder(da)
 
----CONNECT TO VIEW----
Public Shared LogManConnString As String = "Driver={SQL Native Client};Server=ServerName;Database=DBName;Trusted_Connection=yes;"
 
Dim cn As New System.Data.Odbc.OdbcConnection(Globals.LogManConnString)
            cn.Open()
 
            Dim da As New System.Data.Odbc.OdbcDataAdapter("SELECT * FROM PayTypesNew", cn)
            Dim ds As New DataSet()
            da.FillSchema(ds, SchemaType.Source, "PayTypesNew")
            da.Fill(ds, "PayTypesNew")
            Dim cmdBuilder As New System.Data.Odbc.OdbcCommandBuilder(da)

Open in new window

0
 

Author Comment

by:dreinmann
ID: 24162121
Ignore the line commented out in code pasted above:
'Dim cmdBuilder As New System.Data.Odbc.OdbcCommandBuilder(da)
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24162205
In the last code you posted, which line gave you the error

ConnString: "Driver={SQL Native Client};Server=SeverName;Database=MyData;Trusted_Connection=yes;"
System.ArgumentException: Keyword not supported: 'driver'.

0
 

Author Comment

by:dreinmann
ID: 24162518
On reference 'CONNECT TO TABLE', I originally had it setup just like the 'CONNECT TO VIEW'.

It was very weird, I didn't get the error when I was Debugging, but I did when I installed it on the computer and ran it.
----CONNECT TO TABLE----
Public Shared LogManSqlConnString As String = "Data Source=ServerName;Initial Catalog=DBName;Integrated Security=SSPI;"
 
            Dim cn As New System.Data.SqlClient.SqlConnection(Globals.LogManSqlConnString)
            cn.Open()
 
            Dim da As New System.Data.SqlClient.SqlDataAdapter("SELECT * FROM UnionEmpData", cn)
            Dim ds As New DataSet()
            da.FillSchema(ds, SchemaType.Source, "UnionEmpData")
            da.Fill(ds, "UnionEmpData")
            Dim cmdBuilder As New System.Data.SqlClient.SqlCommandBuilder(da)
 
----CONNECT TO VIEW----
Public Shared LogManConnString As String = "Driver={SQL Native Client};Server=ServerName;Database=DBName;Trusted_Connection=yes;"
 
Dim cn As New System.Data.Odbc.OdbcConnection(Globals.LogManConnString)
            cn.Open()
 
            Dim da As New System.Data.Odbc.OdbcDataAdapter("SELECT * FROM PayTypesNew", cn)
            Dim ds As New DataSet()
            da.FillSchema(ds, SchemaType.Source, "PayTypesNew")
            da.Fill(ds, "PayTypesNew")
            Dim cmdBuilder As New System.Data.Odbc.OdbcCommandBuilder(da)
 

Open in new window

0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

864 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