OK so not sure if this is the right section, however my problem is that I have created an SQL statement within my DBdatabase.cs and what I want to do is call the select statement to my default.aspx.cs to check the user input from a true or false statment
so what would I need to change from the following code below:
DBdatabase.cs
public static string surnameSelect(string Ssession)
{
string surname = string.Empty;
cmd = new SqlCommand("Select Surname from Users Where Username =@username", con);
cmd.Connection = con;
cmd.Parameters.AddWithValue("@username", Ssession);
con.Open();
surname = cmd.ExecuteScalar().ToString();
con.Close();
return surname;
}
default.aspx.cs
protected bool SurnameCheck()
{
con.Open();
cmd = new SqlCommand("Select Surname from Users Where Username =@username'", con);
dr = cmd.ExecuteReader();
if (dr.Read())
{
Label2.Text = "";
con.Close();
return false;
}
else
{
Label2.Text = "Password doesn't Exist";
this.Label2.ForeColor = Color.Red;
con.Close();
return true;
}
}
Marked as answer by tim_m_91 on Dec 01, 2012 05:16 PM
public class Database
{
public static bool surnameSelect(string Ssession)
{
int surname = 0;
SqlConnection con = new SqlConnection(ConnecStringHere);
cmd = new SqlCommand("Select count(Surname) from Users Where Username =@username", con);
cmd.Connection = con;
cmd.Parameters.AddWithValue("@username", Ssession);
con.Open();
surname = cmd.ExecuteScalar();
con.Close();
if (surname>0) return true;
else return false;
}
}
Call the above method in your default.aspx.cs page like:
if (Database.surnameSelect("passUserNameHere"))
{
//do what you want to in true case
}
else
{
//else part
}
Please Mark As Answer if it helped.
MCPD ASP.NET 4.0 and 3.5, MCTS WSS, MOSS, SharePoint 2010, MCT
Microsoft Community Contributor Award 2011
With this SQL; remove the cmd.Parameters. Also, unless the first column returned is an integer, it will give you the error, so I would change select * to select count(*). So try:
public static bool SurnameAvaliable(string Sname)
{
int surname = 0;
cmd = new SqlCommand("select count(*) from Users Where Surname ='" + Sname + "'", con);
cmd.Connection = con;
con.Open();
surname = cmd.ExecuteScalar();
con.Close();
if (surname > 0) return true;
else return false;
}
public static int CheckIfPhotoHasBeenRated(string OwnerID, int PhotoID)
{
string sql = "SELECT RatingID FROM MMImageGallery_PhotoRatings WHERE (OwnerID=@OwnerID AND PhotoID=@PhotoID)";
SqlParameter[] p = new SqlParameter[2];
p[0] = new SqlParameter("@OwnerID", OwnerID);
p[1] = new SqlParameter("@PhotoID", PhotoID);
SqlDataReader reader = SqlHelper.ExecuteReader(sql, p);
int result = -1;
if (reader.HasRows)
{
result = 1;
}
else
{
result = 0;
}
reader.Close();
return result;
}
Best Regards
Primillo
http://www.facebook.com/programandopuntonet
tim_m_91
0 Points
27 Posts
SQL true,false statement check
Dec 01, 2012 11:47 AM|LINK
OK so not sure if this is the right section, however my problem is that I have created an SQL statement within my DBdatabase.cs and what I want to do is call the select statement to my default.aspx.cs to check the user input from a true or false statment so what would I need to change from the following code below:
DBdatabase.cs
public static string surnameSelect(string Ssession) { string surname = string.Empty; cmd = new SqlCommand("Select Surname from Users Where Username =@username", con); cmd.Connection = con; cmd.Parameters.AddWithValue("@username", Ssession); con.Open(); surname = cmd.ExecuteScalar().ToString(); con.Close(); return surname; }default.aspx.cs
protected bool SurnameCheck() { con.Open(); cmd = new SqlCommand("Select Surname from Users Where Username =@username'", con); dr = cmd.ExecuteReader(); if (dr.Read()) { Label2.Text = ""; con.Close(); return false; } else { Label2.Text = "Password doesn't Exist"; this.Label2.ForeColor = Color.Red; con.Close(); return true; } }adeelehsan
All-Star
18593 Points
2789 Posts
Re: SQL true,false statement check
Dec 01, 2012 12:23 PM|LINK
Use the following:
public class Database { public static bool surnameSelect(string Ssession) { int surname = 0; SqlConnection con = new SqlConnection(ConnecStringHere); cmd = new SqlCommand("Select count(Surname) from Users Where Username =@username", con); cmd.Connection = con; cmd.Parameters.AddWithValue("@username", Ssession); con.Open(); surname = cmd.ExecuteScalar(); con.Close(); if (surname>0) return true; else return false; } }Call the above method in your default.aspx.cs page like:
if (Database.surnameSelect("passUserNameHere")) { //do what you want to in true case } else { //else part }MCPD ASP.NET 4.0 and 3.5, MCTS WSS, MOSS, SharePoint 2010, MCT
Microsoft Community Contributor Award 2011
tim_m_91
0 Points
27 Posts
Re: SQL true,false statement check
Dec 01, 2012 01:02 PM|LINK
Thanks for reply but 'surname = cmd.ExecuteScalar();' is producing an error 'cannot convert object to int' how do I fix this:
public static bool SurnameAvaliable(string Sname) { int surname = 0; cmd = new SqlCommand("select * from Users Where Surname ='" + Sname + "' collate SQL_Latin1_General_Cp1_CS_AS", con); cmd.Connection = con; cmd.Parameters.AddWithValue("@Surname", Sname); con.Open(); surname = cmd.ExecuteScalar(); con.Close(); if (surname > 0) return true; else return false; }paindaasp
Star
12252 Points
2070 Posts
Re: SQL true,false statement check
Dec 01, 2012 02:41 PM|LINK
With this SQL; remove the cmd.Parameters. Also, unless the first column returned is an integer, it will give you the error, so I would change select * to select count(*). So try:
public static bool SurnameAvaliable(string Sname) { int surname = 0; cmd = new SqlCommand("select count(*) from Users Where Surname ='" + Sname + "'", con); cmd.Connection = con; con.Open(); surname = cmd.ExecuteScalar(); con.Close(); if (surname > 0) return true; else return false; }tim_m_91
0 Points
27 Posts
Re: SQL true,false statement check
Dec 01, 2012 03:02 PM|LINK
surname = cmd.ExecuteScalar(); is still producing an error 'cannot convert object to Int' how do I fix this?
Primillo
Star
8841 Points
1701 Posts
Re: SQL true,false statement check
Dec 01, 2012 03:47 PM|LINK
Hi
Sample
public static int CheckIfPhotoHasBeenRated(string OwnerID, int PhotoID) { string sql = "SELECT RatingID FROM MMImageGallery_PhotoRatings WHERE (OwnerID=@OwnerID AND PhotoID=@PhotoID)"; SqlParameter[] p = new SqlParameter[2]; p[0] = new SqlParameter("@OwnerID", OwnerID); p[1] = new SqlParameter("@PhotoID", PhotoID); SqlDataReader reader = SqlHelper.ExecuteReader(sql, p); int result = -1; if (reader.HasRows) { result = 1; } else { result = 0; } reader.Close(); return result; }Primillo
http://www.facebook.com/programandopuntonet
tim_m_91
0 Points
27 Posts
Re: SQL true,false statement check
Dec 01, 2012 04:31 PM|LINK
I fixed it by doing:
public static string UsernameCheck(string Sname) { string surname = string.Empty; cmd = new SqlCommand("select count(*) from Users Where Username ='" + Sname + "' collate SQL_Latin1_General_Cp1_CS_AS", con); cmd.Connection = con; cmd.Parameters.AddWithValue("@Surname", Sname); con.Open(); surname = cmd.ExecuteScalar().ToString(); con.Close(); return surname; }And:
protected bool SurnameAvaliable() { string a = TextBox8.Text; string sur = DBdatabase.SurnameCheck(a); int SurConvert = Convert.ToInt32(sur); int result = SurConvert; if (result >= 1) { Label4.Text = "Surname Not Avaliable"; return false; } else { Label4.Text = "Surname Avaliable "; return true; } }Catherine Sh...
All-Star
23382 Points
2490 Posts
Microsoft
Re: SQL true,false statement check
Dec 03, 2012 04:19 AM|LINK
Hi,
Very glad to hear that you have resolved your issue and thank you for sharing you solution.
Best wishes,
Feedback to us
Develop and promote your apps in Windows Store