1 
private void btnChange_Click(object sender, EventArgs e)
{
    con.Open();

    SqlCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "update Customer set MembershipPoint='" + textMembershipPoint.Text + "' where NameCustomer='" + textNameCustomer.Text + "'";

    cmd.ExecuteNonQuery();

    if (cmd.ExecuteScalar() != null)
    {
        textMembershipPoint.Text = Convert.ToString(cmd.ExecuteScalar());
    }
    else if (cmd.ExecuteScalar() != )
    {
        MessageBox.Show("Invalid Name of Customer.");
    }
    else if (cmd.ExecuteScalar() != )
    {
        MessageBox.Show("Invalid Membership Point. Only Number Allowed.");
    }
    else
    {
        MessageBox.Show("Membership Point is changed.");
    }

    con.Close();

    display_data();
}

I have a database table called Customer with columns ID_Customer, NameCustomer and MembershipPoint.

When a customer inputs a name that is not in the Customer table, the output will show "Invalid Name of Customer.".

If customer input an invalid MembershipPoint, the output will show "Invalid Membership Point. Only Number Allowed.".

If all else is good, then the output will show "Membership Point is changed.".

Can anyone tell me what I need to do right for the if else statement in order to achieve that?

Improve this question
3
  • 1
    If customer name is John Paul and your database has 100 John Pauls.. are you going to update all of them? check this learn.microsoft.com/en-us/dotnet/api/… on how to properly use sql commands Commented Oct 21, 2021 at 3:39
  • 1
    You're currently executing your SQL statement multiple times - first with the .ExecuteNonQuery(), and then potentially several times with ExecuteScalar - that's definitely a big NO-NO! Execute it once and get the information you need - don't keep executing it over and over again! Commented Oct 21, 2021 at 4:02
  • 2
    SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables Commented Oct 21, 2021 at 4:04

1 Answer 1

Reset to default
1

First of all, you MUST LEARN to use parametrized queries in order to avoid the #1 vulnerability out there - SQL Injection! Do this - ALWAYS - no exceptions.

And secondly - right now, you're executing your UPDATE statement multiple times which is horribly bad.... just execute it once, record the results, and then reason just on the results - don't execute the SQL command multiple times.

And third: it's commonly accepted Best Practice to create both the SqlConnection as well as the SqlCommand just as needed - don't open the connection somewhere outside your code, keep it hanging around for an extended period of time - create it right in here, as needed (and free it, when done).

So try something like this:

private void btnChange_Click(object sender, EventArgs e)
{
    // check if the membership points text is a valid INT or not
    int membershipPoints = 0;
    
    if (!int.TryParse(textMembershipPoint.Text, out membershipPoints))
    {
        MessageBox.Show("Invalid Membership Point. Only Number Allowed.");
        return;
    }

    // use a properly parametrized query
    string updateQuery = "UPDATE dbo.Customer SET MembershipPoint = @Points WHERE NameCustomer = @CustomerName;";
    
    // put your SqlConnection and SqlCommand into a proper "using" block
    using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand (updateQuery, con))
    {
        // define the parameters and set their values
        cmd.Parameters.Add("@Points", SqlDbType.Int).Value = membershipPoints;
        cmd.Parameters.Add("@CustomerName", SqlDbType.VarChar, 100).Value = textNameCustomer.Text;
        
        // open connection, execute UPDATE, record number of rows updated, close connection
        con.Open();
        int rowsUpdated = cmd.ExecuteNonQuery();
        con.Close();
        
        // now reason just on the result
        if (rowsUpdated > 0)
        {
            // some rows were updated --> success
            MessageBox.Show("Success - rows updated");
        }
        else
        {
            // no rows were updated --> 
            MessageBox.Show("No rows updated - most likely invalid customer name");
        }
    }
    
    display_data();
}
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.