mySQL through c# writing data to database

Question

I've got the following code where i'm basically taking data from mysql and plonking in SQL server.

The SQL server part works fine, but I can't work out how to keep the mysql connection open to do the update. When it does the mysql execute reader in the foreach loop it bums out with the error:

An unhandled exception of type 'System.InvalidOperationException' occurred in MySql.Data.dll

Additional information: Connection must be valid and open.

Code:

 DB db = new DB();
            String sConfig_hostname = 
            String sConfig_dbname = 
            String sConfig_dbusername = 
            String sConfig_dbpassword = 
            string MyConString = "SERVER=" + sConfig_hostname + ";" +
                "DATABASE=" + sConfig_dbname + ";" +
                "UID=" + sConfig_dbusername + ";" +
                "PASSWORD=" + sConfig_dbpassword + ";Allow Zero Datetime=true;";
            MySqlConnection connection = new MySqlConnection(MyConString);
             string sQuery="Select * from inbox where Transferred = 0";

             MySqlDataAdapter myDA = new MySqlDataAdapter(sQuery, connection);
             MySqlCommandBuilder cmb=new MySqlCommandBuilder(myDA);

             DataTable MyDT = new DataTable();
             myDA.Fill(MyDT);

             foreach (DataRow row in MyDT.Rows)
             {
                String SQL = String.Format("Insert into Inbox (Message, Received, Sender) VALUES ('{0}', '{1}', '{2}')", GeneralFunctions.SQLescape(row["TextDecoded"].ToString()), row["ReceivingDateTime"].ToString(), row["SenderNumber"].ToString());
                String mySQL = "Update inbox set Transferred = 1 where ID = " + row["ID"].ToString();
                db.Update(SQL);

                MySqlCommand SQLup = new MySqlCommand(mySQL);
                MySqlDataReader reader = SQLup.ExecuteReader();

             }

asdf_enel_hak · Accepted Answer · 2014-10-02 13:08:59Z

2

MySqlConnection connection = new MySqlConnection(MyConString);
connection.open(); // insert this line

for more information

edited Oct 2, 2014 at 13:08

answered Oct 2, 2014 at 13:02

asdf_enel_hak

7,6585 gold badges47 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Perrenoud · Accepted Answer · 2014-10-02 13:05:16Z

1

The reason it's failing is because you don't want to execute a reader here, you just want to execute the statement and because you're not initializing the command with the connection:

MySqlConnection connection = new MySqlConnection(MyConString);
connection.Open();

...

MySqlCommand SQLup = new MySqlCommand(mySQL, connection);
SQLup.ExecuteNonQuery();

edited Oct 2, 2014 at 13:05

answered Oct 2, 2014 at 12:59

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

3 Comments

MissCoder87 Over a year ago

Ah - That makes sense. I've changed that, but unfortunately the same error: Additional information: Connection must be valid and open. connection isnt null at this point and it's the write information

Mike Perrenoud Over a year ago

@Tom you could move that line, and probably should move that line, to right below the construction of the MySqlConnection.

MissCoder87 Over a year ago

Ah perfect. THank you for your help. Will mark this as answer when the time limit allows me to!

Collectives™ on Stack Overflow

mySQL through c# writing data to database

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related