ODBC Setup on Program Install

How do I include a System DSN Sql Server ODBC setup with my 'Setup and Deploy' program install?
dreinmannIT Business Architect - Supply ChainAsked:
Who is Participating?
 
käµfm³d 👽Commented:
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
 
käµfm³d 👽Commented:
Have you been here yet?

http://support.microsoft.com/kb/184608
0
 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
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
 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
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
 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
BTW, that  error does not happen in Debug mode.  Just when I run the actual installed program.
0
 
käµfm³d 👽Commented:
I C...


Yours will not add a DSN to the list in ODBC settings of Windows, but it should connect to the DB.
0
 
käµfm³d 👽Commented:
Are you using a SqlClient.SqlConnection object or an Odbc.OdbcConnection object?
0
 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
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
 
käµfm³d 👽Commented:
No. I was just making sure you were using the right connector.
0
 
käµfm³d 👽Commented:
Can you obscure your personal data (passwords and such) and post any code for how you are declaring and connecting?
0
 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
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
 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
Ignore the line commented out in code pasted above:
'Dim cmdBuilder As New System.Data.Odbc.OdbcCommandBuilder(da)
0
 
käµfm³d 👽Commented:
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
 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.