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='scott@scottrider.com'"]user_id='scott@scottrider.com'[/EMAIL]) Next - are there other reasons for the error message.
scott_rider
0
Newbie Poster
Recommended Answers
Jump to Postcan you paste the surrounding code/function?
All 3 Replies
pty
882
Posting Pro
can you paste the surrounding code/function?
scott_rider
0
Newbie Poster
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=\"<A href="http://www.yahoo.com\">new">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=\"<A href="http://www.mama.com\">new">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();
}
}
}
Tschuikow
0
Newbie Poster
make: MySqlCommand mysql_db_query=new MySqlCommand(sql); to MySqlCommand mysql_db_query=new MySqlCommand(sql,mysql_db_access);
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.