database LOG in C#

[krii017]

i got a problem i have a registration and log in program
the registration is fine but on log-in ,
the 1st entry on registration is the only accessible on log in
i tried the 2nd and 3rd username with correct psw
but it got an error

heres my code
what should i add
pls message on face book

private void button2_Click(object sender, EventArgs e)
        {




            DbConnClass dc = new DbConnClass();
            dc.ConnectIt();
            MySqlConnection connection = new MySqlConnection(dc.MyConString);
            MySqlDataAdapter da = new MySqlDataAdapter();



            da.SelectCommand = new MySqlCommand("SELECT * FROM user1", connection);
            da.SelectCommand.Parameters.Add("@username", SqlDbType.VarChar).Value = textBox1.Text;
            da.SelectCommand.Parameters.Add("@password", SqlDbType.VarChar).Value = textBox2.Text;
            connection.Open();
            MySqlDataReader reader = null;

            reader = da.SelectCommand.ExecuteReader();
            reader.Read();

            if (textBox1.Text == (reader["username"].ToString()) && textBox2.Text == (reader["password"].ToString()))
            {
                timer3.Enabled = true;
                button1.Enabled = false;
                button3.Enabled = false;
                button4.Enabled = false;
            }
            else if ((textBox1.Text == "") || (textBox2.Text == ""))
            {
                MessageBox.Show("Please type a username/passsword", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);


            }

            else
            {
                MessageBox.Show("Enter correct username/password");



                textBox1.Clear();
                textBox2.Clear();

                textBox1.Focus();

            }

            connection.Close();
            

        }

[Momerath]

You SQL statement has no parameters, yet you add two to them.
Your reader returns every single userid/password in the database, yet you only read the first entry. Readers are usually in while loops.

You can fix this by using SELECT COUNT(*) FROM user1 WHERE username = @username AND password = @password as your SQL statement. Then use ExecuteScaler() instead of ExecuteReader(). ExecuteScaler returns a single value, if it is 0 then the username/password was wrong, if it is 1 then the username/password is correct.

The link shows an example of getting an int (which this solution would do) from ExecuteScaler().

[krii017]

ok ill try it.. a moment pls :D

[krii017]

it says invalid operation exception :((

DbConnClass dc = new DbConnClass();
            dc.ConnectIt();
            MySqlConnection connection = new MySqlConnection(dc.MyConString);
            MySqlDataAdapter da = new MySqlDataAdapter();



            da.SelectCommand = new MySqlCommand("SELECT COUNT(*) FROM user1 WHERE username = @username AND password = @password", connection);
            Int32 count = (Int32)da.SelectCommand.ExecuteScalar();
            da.SelectCommand.Parameters.Add("@username", SqlDbType.VarChar).Value = textBox1.Text;
            da.SelectCommand.Parameters.Add("@password", SqlDbType.VarChar).Value = textBox2.Text;
            connection.Open();
            MySqlDataReader reader = null;

[Momerath]

Line 9 should be after line 12, and get rid of line 13. You don't execute a command on a connection that isn't open.

[krii017]

so what will i replace this?
it'll check if its on the database

if (textBox1.Text == (reader["username"].ToString()) && textBox2.Text == (reader["password"].ToString()))
            {
                timer3.Enabled = true;
                button1.Enabled = false;
                button3.Enabled = false;
                button4.Enabled = false;
            }

[Momerath]

Oh, and in line 4 you need SqlCommand, not SqlDataAdapter.

if (count == 1) {
    // user is on database with correct password
}

[krii017]

another error pop out :((

Error 1 'MySql.Data.MySqlClient.MySqlCommand' does not contain a definition for 'SelectCommand' and no extension method 'SelectCommand' accepting a first argument of type 'MySql.Data.MySqlClient.MySqlCommand' could be found (are you missing a using directive or an assembly reference?)

[krii017]

[IMG]http://i56.tinypic.com/sym81f.jpg[/IMG]

[krii017]

here's the pic of the error

[Momerath]

That's because you are treating it as a dataadapter, and not a SqlCommand.

DbConnClass dc = new DbConnClass();
dc.ConnectIt();
MySqlConnection connection = new MySqlConnection(dc.MyConString);
MySqlCommand command = new MySqlCommand("SELECT COUNT(*) FROM user1 WHERE username = @username AND password = @password", connection);
command.Parameters.Add("@username", SqlDbType.VarChar).Value = textBox1.Text;
command.Parameters.Add("@password", SqlDbType.VarChar).Value = textBox2.Text;
connection.Open();
Int32 count = (Int32)command.ExecuteScalar();
if (count == 1) {
    // user logged in
}

[muzikhera]

I've already tried this one during my development and i wanna share this code to you.
Just replace the necessary variables and others to fit in to your project.
Hope that this could help you.

SqlConnection Connect = new SqlConnection();
                        
            Connect.ConnectionString = @"Data Source=BURBANK\SQLEXPRESS;" + "Initial Catalog= ERPdb;" + "Persist Security Info=True;" + "User ID=antonette;" + "Password=ilovemis";
            Connect.Open();

            SqlCommand cmd = new SqlCommand("SELECT ISNULL(username, '') AS username, employeeID, ISNULL(password, '') AS password FROM employee WHERE username='" + txtUserName.Text + "' and password='" + txtPassword.Text + "'", Connect);

            string userText = txtUserName.Text;
            string passText = txtPassword.Text;
            string fisrt = txtempID.Text;

            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                frmMain main = new frmMain();
                txtempID.Text = Convert.ToString(dr[1]);
                main.Show();
                dr.Close();
                SqlCommand cmdLogin = new SqlCommand("INSERT INTO UserLoginLogs(EmployeeID,LoginDate)VALUES ('" + txtempID.Text + "',getDate())", Connect);
                SqlDataReader dr1 = cmdLogin.ExecuteReader();
            }
            else
                MessageBox.Show("Invalid UserName or Password.");
            {
                dr.Close();
            }

            try
            {
            }
            catch (SqlException ex)
            {
                MessageBox.Show("There is an Error" + ex);
            }
            finally
            {
                Connect.Close();
            }
        }

[krii017]

tnx :DD sorry for the late reply. i already finished :DD thanks