Connection must be valid and open

[scott_rider]

I am getting an "Connection must be valid and open" error. After some research I have found two possible reasons for this: malformed sql or bad connection string. Since the error is being thrown on a second .executescalar statement I will assume the connection string is working. I have two questions. First - if I can cut and paste the sql statement into mySql's query browser and it returns the information, is the sql malformed? (e.g. select user_id from cms.accounts where [EMAIL="user_id='[e-mail]'"]user_id='[e-mail]'[/EMAIL]) Next - are there other reasons for the error message.

[pty]

can you paste the surrounding code/function?

[scott_rider]

using MySql.Data.MySqlClient;
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Web;
using System.Web.Mail;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace login_menu{
///
/// Summary description for WebForm1.
///
public class login_form: System.Web.UI.Page{
protected System.Web.UI.WebControls.TextBox user_id;
protected System.Web.UI.HtmlControls.HtmlInputText password;
protected System.Web.UI.WebControls.Label error_display;

private void Page_Load(object sender, System.EventArgs e){
if(Page.IsPostBack){
string user_id_string=user_id.Text;
string password_string=password.Value;
string sql=mySql.sql_login(user_id_string,password_string);
error_display.Text="";
MySqlConnection mysql_db_access=mySql.connect;
MySqlCommand mysql_db_query=new MySqlCommand(sql);
mysql_db_query.Connection=mysql_db_access;
mysql_db_access.Open();
int test_account_id=Convert.ToInt16(mysql_db_query.ExecuteScalar());
sql=mySql.sql_test_user_id(user_id_string);
mysql_db_query=new MySqlCommand(sql);
string test_user_id=Convert.ToString(mysql_db_query.ExecuteScalar());

if(test_account_id==0){
#region test_user_id
if(test_user_id==System.String.Empty){
// no user account error message
// display link to new account web page
error_display.Text="Invalid user account new">http://www.yahoo.com\">new account";
}else{
// wrong password error message
// display link to send password to email account
error_display.Text="Invalid password new">http://www.mama.com\">new account";
}
#endregion
}else{
// successful login
// set cms access to granted
mysql_db_query.Connection.Close();
Response.Redirect(" http://www.google.com ");
}
}
}
}
}
public class mySql{
public static MySqlConnection connect{
get{
return new MySqlConnection(mySql.connect_string);
}
}
public static string connect_string{
get{
return "server=localhost; database=cms; uid=root; port=3306; password=;";
}
}
public static string sql_login(string user_id, string password){
StringBuilder sql = new StringBuilder();
sql.Append("select account_id from cms.accounts ")
.Append("where user_id='" + user_id + "' ")
.Append("and user_password='" + password + "'");
return sql.ToString();
}
public static string sql_test_user_id(string user_id){
StringBuilder sql=new StringBuilder();
sql.Append("select user_id from cms.accounts ")
.Append("where user_id='" + user_id + "'");
return sql.ToString();
}
}
}

[Tschuikow]

make:

MySqlCommand mysql_db_query=new MySqlCommand(sql);

to MySqlCommand mysql_db_query=new MySqlCommand(sql,mysql_db_access);