0

I have an app that is supposed to let the user choose between a MySQL or MS SQL connection.

I set the connection string like so:

OdbcConnection SqlConn = new OdbcConnection();

connString = String.Format("Driver={{{0}}}; Server={1}; Port={2}; DataBase={3}; Uid={4}; Pwd={5};", protocol, hostname, port, database, user, password);
SqlConn.ConnectionString = connString;

However, when attempting to open the connection, I get the following error:

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Now, I tested a MySQLConnection instead and took our the Driver part of the connection string and everything works fine. I'm sure it's something to do with the Driver, but I'm not sure what. Most examples I see use Driver={MySQL}, but that didn't work for me.

Note: Not sure if it matters, but the user is going to be selecting a radio button to determine if MySQL or MS SQL will be used.

pfinferno
  • 1,779
  • 3
  • 34
  • 62
  • Can you create a DSN to SQL Server that tests successfully? If so, it's just a matter of building a working connection string. – JLB Mar 21 '16 at 13:33
  • Never used a DSN before. Not a pro at SQL stuff, but I can connect to the SQL Server also using the same basic set-up. I just want to avoid using a bunch of if statements every time I connect to the DB to switch connection strings. – pfinferno Mar 21 '16 at 13:45
  • 1
    I opened ODBC Data Source Administrator and have `MySQL ODBC 5.3 ANSI` and `Unicode` Drivers as well as `SQL Native Client`, `SQL Server`, and `SQL Server Native Client 10.0/11.0`. – pfinferno Mar 21 '16 at 13:48
  • 1
    To be perfectly honest with you, you should be storing your connection strings in your app config, then you can just present the user with the possible connections from the cfg. That way it doesn't matter if you have a slightly different connection string format per server / type. See here: https://msdn.microsoft.com/en-us/library/ms254494.aspx – JLB Mar 21 '16 at 13:56
  • Also, if your users are advanced enough to choose between server types, they should be advanced enough to create a DSN I would think. If the user creates a DSN, you can just connect to that (by showing them a list of DSNs) and you won't have to worry about adding Oracle, SQLite, Firebird, Postgres, and DB2 later :) – JLB Mar 21 '16 at 13:59
  • From what I understand, the two databases are basically exactly the same besides connection strings. I.E. same tables, same data, etc. I think they are just told which one to use. So I'm not sure if they would be advanced enough to create a DSN :/ I'll try the app config, but wouldn't I still have to make several `if` statements for creating new commands based on what the user selects? – pfinferno Mar 21 '16 at 14:16
  • No additional "ifs". You just loop through the connection strings in the app config and present them to the user, they pick one, you read that one from the file, then connect using the string. Here's a console example from MS: http://dpaste.com/2PVB4ZM – JLB Mar 21 '16 at 14:27
  • I'm sorry, maybe I just don't understand. I thought SqlCommand and MySqlCommand had to be use for their relative databases. I can't use SqlCommand for MySQL stuff, and MySqlDataReader can't be used for MS SQL, correct? So wouldn't I have to make if statements (or something like them) to switch between them? – pfinferno Mar 21 '16 at 14:44
  • 1
    Never code anything database-specific if you plan to support more than one database type. I've never used MySqlDataReader (and you don't have to either!). http://stackoverflow.com/questions/3080601/keeping-an-application-database-agnostic-ado-net-vs-encapsulating-db-logic – JLB Mar 21 '16 at 14:48
  • Thank you! That link looks promising, I will try that out. – pfinferno Mar 21 '16 at 14:52

1 Answers1

2

The correct connection string depends on the driver being used. This site has an entire library of connection strings for different databases, connection methods and driver versions.

Brett Donald
  • 6,745
  • 4
  • 23
  • 51