'It keeps displaying the last data

I'm trying to let user to change the phone number of specific company user entered and displays the new information to user

Currently, when I click the change button it seems like it updates the data, but it displays the last phone number, not the one user just entered.

I'm not sure how to display the data that user just entered.

This is the form.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Assignment7_a.aspx.cs" Inherits="Assignment7.Assignment7_a" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">

        This page allows you to change the business phone of a specific supplier.<br />
        Enter the company name: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        <br />
        Enter a new business phone number : <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Change" />

        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>

    </form>
</body>
</html>

This is the CS file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
using System.Data;

namespace Assignment7
{
    public partial class Assignment7_a : System.Web.UI.Page
    {

        protected void Button1_Click(object sender, EventArgs e)
        {
            string connectionString = "server=localhost;user=csc313web;database=phpmyadmin;port=3306;password='dbadmin'";

            string sqlCommand = "update suppliers set business_phone='" + this.TextBox1.Text + "' where company='" + this.TextBox2.Text + "';";
 
            MySqlConnection mySqlConnection = new MySqlConnection(connectionString);
            MySqlCommand mySqlCommand = new MySqlCommand(sqlCommand, mySqlConnection);
            MySqlDataReader myReader;
            mySqlConnection.Open();
            myReader = mySqlCommand.ExecuteReader();
            mySqlConnection.Close();
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            string connectionString = "server=localhost;user=csc313web;database=phpmyadmin;port=3306;password='dbadmin'";
            MySqlConnection mySqlConnection = new MySqlConnection(connectionString);
            mySqlConnection.Open();

            string sqlCommand = "SELECT company, first_name, last_name, business_phone FROM suppliers";
            MySqlCommand mySqlCommand = new MySqlCommand(sqlCommand, mySqlConnection);
            MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter();
            mySqlDataAdapter.SelectCommand = mySqlCommand;
            DataTable dataTable = new DataTable();
            mySqlDataAdapter.Fill(dataTable);
            GridView1.DataSource = dataTable;
            GridView1.DataBind();

            int compNameColumn = GetHeaderIndexByName(GridView1, "company");
            GridView1.HeaderRow.Cells[compNameColumn].Text = "Company Name";
            int firstNameColumn = GetHeaderIndexByName(GridView1, "first_name");
            GridView1.HeaderRow.Cells[firstNameColumn].Text = "First Name";
            int lastNameColumn = GetHeaderIndexByName(GridView1, "last_name");
            GridView1.HeaderRow.Cells[lastNameColumn].Text = "Last Name";
            int businessPhoneColumn = GetHeaderIndexByName(GridView1, "business_phone");
            GridView1.HeaderRow.Cells[businessPhoneColumn].Text = "business Phone Number";

            mySqlConnection.Close();
        }

        private int GetHeaderIndexByName(GridView grid, string name)
        {
            for (int i = 0; i < grid.HeaderRow.Cells.Count; i++)
            {
                if (grid.HeaderRow.Cells[i].Text.ToLower().Trim() == name.ToLower().Trim())
                {
                    return i;
                }
            }

            return -1;
        }
    }
}
c++sharpmysqlasp.netvisual-studio


Solution 1:[1]

In Button1_Click, use MySqlCommand.ExecuteNonQuery() to execute an UPDATE query (that's not expected to return a result set):

string sqlCommand = @"
    update suppliers
    set business_phone=@phone
    where company=@company;";

using (MySqlConnection mySqlConnection = new MySqlConnection(connectionString))
using (MySqlCommand mySqlCommand = new MySqlCommand(sqlCommand, mySqlConnection))
{
    command.Parameters.AddWithValue("@phone", TextBox1.Text);
    command.Parameters.AddWithValue("@company", TextBox2.Text);

    mySqlConnection.Open();
    mySqlCommand.ExecuteNonQuery();
}

You need to use parameters in your SQL command to prevent SQL injection. (I also strongly recommend using using statements instead of manually cleaning up the connection by calling Close.)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bradley Grainger

Related Questions