Problem with code behind update to MySQL

[dbrewerton]

Ok everyone, when I do this update clause using code behind it gives me a stupid error that says:

Line: 6
Char: 62099
Error: Sys.WebForms.Pa geRequestManage rServerErrorExc eption: Format of the initialization string does not conform to specification starting at index 0.
Code: 0
URL: http://localhost:1038/webapp/admin/assignuser.aspx

Now, my code for the SQL code behind is:

                SqlConnection con = null;
                SqlCommand cmd = null;
                try
                {
                    con = new SqlConnection();
                    con.ConnectionString = "SqlDataSource4";
                    cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("?aspnet_id", Session["aspnet_id"]);
                    cmd.Parameters.AddWithValue("?contact_id", Session["contact_id"]);
                    cmd.Parameters.AddWithValue("?device_id", Session["device_id"]);
                    con.Open();
                    cmd.CommandText = "update egw_addressbook set aspnet_id = ?aspnet_id where contact_id = ?contact_id;";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "update ewise_device set aspnet_id = ?aspnet_id where device_id = ?device_id;";
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    if (con.State.Equals(ConnectionState.Open)) con.Close();
                }

I am baffled, any idea what's wrong???

[Curtis Rutland]

I think the problem is your connection string. You are passing the literal string "SqlDataSource4 " to the connection object.

A MySql conn string should look something like

"Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"

[dbrewerton]

When I use the code above, it gives me a logon failure even though I have dbowner. The logon I'm using works everywhere else so I'm really not sure what's wrong.

[Curtis Rutland]

Can you post your updated code for me? Thanks.

[dbrewerton]

protected void Button1_Click(object sender, EventArgs e)
    {
//        DropDownList1.Enabled = true;
//        DropDownList2.Enabled = true;
//        DropDownList3.Enabled = true;

                SqlConnection con = null;
                SqlCommand cmd = null;
                try
                {
                    con = new SqlConnection();
                    con.ConnectionString = "server=192.168.1.160;database=mytestdb;uid=username;pwd=nottelling;";
                    cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("aspnet_id", Session["aspnet_id"]);
                    cmd.Parameters.AddWithValue("contact_id", Session["contact_id"]);
                    cmd.Parameters.AddWithValue("device_id", Session["device_id"]);
                    con.Open();
                    cmd.CommandText = "update egw_addressbook set aspnet_id = ?aspnet_id where contact_id = ?contact_id;";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "update ewise_device set aspnet_id = ?aspnet_id where device_id = ?device_id;";
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    if (con.State.Equals(ConnectionState.Open)) con.Close();
                }
    }

[Curtis Rutland]

Is your DB on a nonstandard port?

Here's a good connection string resource:

http://www.connectionstrings.com/mysql

Use the one that fits your needs.

Let me know how it goes.

[dbrewerton]

No, the standard MySQL port is 3306. I want to keep this as vanilla as possible.

[Curtis Rutland]

Hmm, odd. Post the exact exception text please.

EDIT: Oh, I think I know the problem.

The SqlClient and SqlCommand objects are native objects for MS SQL Server.

Use OleDb instead.

Here's the OleDb conn string:

"Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword;"

The OleDb objects are very similar to the Sql objects, just named differently:
Take a look at this article. It has examples of using the OleDb objects.

[dbrewerton]

Ok, here is the exact text:

Line: 6
Char: 62099
Error: Sys.WebForms.Pa geRequestManage rServerErrorExc eption: Login failed for user 'username'.
Code: 0
URL: http://localhost:1038/webapp/admin/assignuser.aspx

[Curtis Rutland]

I edited my post, probably while you were posting. Check it out, try it out, and let me know how it works.

[dbrewerton]

Well, I tried it but the system complains saying Keyword not supported: 'provider'. I'll look at that article too.

[Curtis Rutland]

Yeah, you have to use completely different objects. SqlConnection becomes OleDbConnection , SqlCommand becomes OleDbCommand, and the namespace is now System.Data.Ole Db rather than System.Data.Sql Client.

Other than that it is pretty much the same.

[dbrewerton]

Can you take a peek at this and tell me how to recode it? I have the aspnet_id in a query string named MyUserId and DropDownList1 value is the contact ID. I'd like to know how to put these variables into the code behind.

            con = new OleDbConnection();
            con.ConnectionString = "server=192.168.1.160;database=mytestdb;uid=userna me;pwd=nottelling;";
            cmd = new OleDbCommand();
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("aspnet_id", Session["aspnet_id"]); <-- QueryStringValue
            cmd.Parameters.AddWithValue("contact_id", Session["contact_id"]); <-- Value of dropdown list selection
            con.Open();
            cmd.CommandText = "update egw_addressbook set aspnet_id = ?aspnet_id where contact_id = ?contact_id;";
            cmd.ExecuteNonQuery();

[dbrewerton]

Ready to rip hair out...

Ok, after recoding once again, I'm getting new error now. Page displays:
Server Error in '/evanhee_webapp' Application.
--------------------------------------------------------------------------------

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.Odb c.OdbcException : ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Code behind where problem coming from is:

    protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
    {

        OdbcConnection con = null;
        OdbcCommand cmd = null;

        try
        {
            con = new OdbcConnection();

//See the Following LINE:
           con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;server=192.168.1.160;database=mydb;uid=username;pwd=nottelling;";
            cmd = new OdbcCommand();
            cmd.Connection = con;
            cmd.Parameters.Add("?aspnet_id", OdbcType.Int).Value = (Session["MyUserId"] != null) ? Convert.ToInt32(Session["MyUserId"]) : 1;
            cmd.Parameters.Add("?contact_id", OdbcType.Int).Value = (Session["contact_id"] != null) ? Convert.ToInt32(Session["contact_id"]) : 0;
            cmd.Parameters.Add("?device_id", OdbcType.Int).Value = (Session["device_id"] != null) ? Convert.ToInt32(Session["device_id"]) : 0;
            con.Open();
            cmd.CommandText = "update egw_addressbook set aspnet_id = ?aspnet_id where contact_id = ?contact_id;";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "update ewise_device set aspnet_id = ?aspnet_id where device_id = ?device_id;";
            cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            con.Close();
        }
    }

[Curtis Rutland]

JET is MS Access's provider. Try changing your connection string to match the correct one for MySql:

Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword;