Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i need to query a mysql database to check if a particular string exists in a table then perform some operation afterwards. this is what i have tried
C#
protected void toNokReg_Click(object sender, EventArgs e)
        {
            try
            {
                MySqlConnection conn = new MySqlConnection(connection);
                MySqlCommand cmd = new MySqlCommand("select number from table.alladmin where number = '" + number.Text + "'", conn);
                MySqlDataReader read;
                conn.Open();
                read = cmd.ExecuteReader();
                while (read.Read())
                {
                    if (read.GetString("number").Equals(""))
                    {
                        insertStaffDetails();
                    }
                    else
                    {
                        Label23.Text = "Incorrect Mobile Number";
                    }
                }
                read.Close();
                conn.Close();
            }
            catch (MySqlException ex)
            {
                Label23.ForeColor = System.Drawing.Color.Red;
                //Label23.Text = "MySql: Failed Upload, Please Try Agin";
                Label23.Text = ex.Message + "\n" + ex.StackTrace;
            }
            catch (Exception)
            {
                Label23.ForeColor = System.Drawing.Color.Red;
                Label23.Text = "Failed Upload, Please Try Agin";
            }
        }
the code above is in an event of a button, the table number comprises various mobile numbers stored in the varchar type.
i want it to check if the number currently in a textbox with id number is already in the database, if it isn't it should perform
insertStaffDetails();
else, it should display an error, but it isn't working my best guess of error is from the while statement. Please i need your assistance, thanks a lot
Posted
Updated 25-Feb-15 20:56pm
v2

Try this:

MySqlConnection conn = new MySqlConnection(connection);
MySqlCommand cmd = new MySqlCommand("select count(*) from table.alladmin where number = '" + number.Text + "'", conn);
conn.Open();
int a=(int)cmd.ExecuteScalar();
conn.Close();
if (a>0) { //do your staff];


Edit
Before someone else tell you,it's NEVER a good idea to compose your sql command concatenating strings, it exposes you to SQL Injection.
 
Share this answer
 
v3
I think the suggestions in previous comment are all correct.

I would just refactor the code a bit as following:

C#
public class SomeDataAccessClass
{
	public bool IsPhoneNumberPresent(string number)
	{	
		bool returnVal = false;
		using(MySqlConnection conn = new MySqlConnection(connection))
		{
			using (SqlCommand cmd = con.CreateCommand())
            {
				cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select count(*)from table.alladmin where number = @number";
				
				SqlParameter param = new SqlParameter("@number", number);
				cmd.Add(param);
				
				conn.Open();
				
				int result = (int)cmd.ExecuteScalar();
				returnVal = result >= 1;
			}
		}
	
		return returnVal;
	}
}


Still Not perfect but I could live with this code. I hope you will understand the code. If not, ask.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900