Connection must be valid and open

[fadetoblack]

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
user_id='scott@scottrider.com') Next - are there other reasons for the error message.

 

[TonyGroves]

If the query works, then the SQL is not malformed, and your example is not malformed. The message clearly indicates a problem with the connection. What interface are you using?

 

[fadetoblack]

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>
/// Summary description for WebForm1.
/// </summary>
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 <a href=\"

http://www.yahoo.com\">new

account</a>";
}else{
// wrong password error message
// display link to send password to email account
error_display.Text="Invalid password <a href=\"

http://www.mama.com\">new

account</a>";
}
#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();
}
}
}

 

[fadetoblack]

MySqlConnection... to answer your question