SQL Database If-Else statement-CodePudding

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?

CodePudding user response：

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();
}