Can't open sql connection in cs file - asp.net

Question

I am new to ASP.NET and following a tutorial where a user can register to a site and it checks a database to see if username already exist. When came time to create database, the tutorial said to create it in visual studio (mdf file), but i use MYSQL Workbench, so i created it there and tried connecting it on my own.

When adding the connection under Data Connections in Visual, I was able to see my databases and the test connection worked.

When adding the SQLDataSource, I was able to run a test query successfully and read from the database. I even used it with a GridView to display the data on a web page.

But when it comes to opening a connection in the cs file, i am getting an error.

[Win32Exception (0x80004005): The system cannot find the file specified]

[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5347119
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +546
   System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover) +5358907
   System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) +145
   System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) +892
   System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) +311
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData) +646
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +278
   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) +38
   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +732
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +85
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +1057
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +78
   System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +196
   System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +146
   System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +16
   System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +94
   System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +110
   System.Data.SqlClient.SqlConnection.Open() +96
   Registration.Page_Load(Object sender, EventArgs e) in e:\WebSite\Registration.aspx.cs:15
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +51
   System.Web.UI.Control.OnLoad(EventArgs e) +92
   System.Web.UI.Control.LoadRecursive() +54
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +772

Error appears in code here

15 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegisterCS"].ConnectionString);
16 conn.Open();//ERROR HERE

And here is my web.config, just in case

<?xml version="1.0"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <connectionStrings>
    <add name="RegisterCS" connectionString="server=localhost;User Id=root;password=******;database=asptest"
      providerName="MySql.Data.MySqlClient" />
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.0"/>
  </system.web>


</configuration>

I looked around and apparently it can be the connection string, but i think it should be ok since the data source actually read successfully from the database right? Am I missing something to connect the database properly? Maybe i have to something in Workbench? I also tried with the firewall down. Thanks.

Answer 1

MySQL Connection Strings are Uid and Pwd in place of User Id and Password respectively.

http://dev.mysql.com/doc/connector-net/en/connector-net-programming-connecting-connection-string.html

The following is a sample connection string:

Server=127.0.0.1;Uid=root;Pwd=12345;Database=test;

In this example, the MySqlConnection object is configured to connect to a MySQL server at 127.0.0.1, with a user name of root and a password of 12345. The default database for all statements will be the test database.

Also, comments discussion led to revealing that the Port is not the standard/default of 3306, but was 5354. Thus the connection string should be as follows:

Server=localhost;Uid=root;Pwd=******;Database=asptest;Port=5354

Also, you may not need to change User Id to Uid, and Password to Pwd, the documentation for the options allows for the following:

Password, pwd; The password for the MySQL account being used.

User Id, UserID, Username, Uid, User name, User: The MySQL login account being used.

http://dev.mysql.com/doc/connector-net/en/connector-net-connection-options.html

Lastly, you'll need the MySQL Connector. Then you'll need to switch your SqlConnections out for MySqlConnections.