Adding data from SQL Server table into textbox when entering data in another textbox

Question

Here is my program:

What I want to do: when I enter 1 in ProductID textbox, I want category, name and price for ProductID = 1 to be filled into their textboxes.

I have tried to read from product table where ProductID = ProductIDTB.Text and then changed the other textboxes to show the data inside the other columns

Here is my code when ProductID textbox is changed:

protected void ProductIDTB_TextChanged(object sender, EventArgs e)
{
        string connectionString1;
        SqlConnection cnn1;

        connectionString1 = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=Greenwich_Butchers;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
        cnn1 = new SqlConnection(connectionString1);

        string selectSql1 = "SELECT * FROM [Product] WHERE ProductID = ('" + Convert.ToInt32(ProductIDTB.Text) + "') ";

        SqlCommand com1 = new SqlCommand(selectSql1, cnn1);

        try
        {
            cnn1.Open();

            using (SqlDataReader read = com1.ExecuteReader())
            {
                while (read.Read())
                {
                    String productcategory = Convert.ToString(read["ProductCategory"]);
                    ProductCategoryTB.Text = productcategory;
                    String productname = Convert.ToString(read["ProductName"]);
                    ProductNameTB.Text = productname;
                    String productprice = Convert.ToString(read["ProductPrice"]);
                    ProdPriceTB.Text = productprice;
                }
            }
        }
        catch (Exception ex)
        {
            Response.Write("error" + ex.ToString());
        }
        finally
        {
            cnn1.Close();
        }
    }

Work-around: as textbox_textchanged event was not working, I decided to add a button which finds product using the ID:

protected void FindProductBtn_Click(object sender, EventArgs e)
{
        string connectionString1;
        SqlConnection cnn1;

        connectionString1 = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=Greenwich_Butchers;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

        cnn1 = new SqlConnection(connectionString1);

        string selectSql1 = "SELECT * FROM [Product] WHERE ProductID = (" + Convert.ToInt32(ProductIDTB.Text) + ") ";

        SqlCommand com1 = new SqlCommand(selectSql1, cnn1);

        try
        {
            cnn1.Open();

            using (SqlDataReader read = com1.ExecuteReader())
            {
                while (read.Read())
                {
                    String productcategory = Convert.ToString(read["ProductCategory"]);
                    ProductCategoryTB.Text = productcategory;
                    String productname = Convert.ToString(read["ProductName"]);
                    ProductNameTB.Text = productname;
                    String productprice = Convert.ToString(read["ProductPrice"]);
                    ProdPriceTB.Text = productprice;
                }
            }
        }
        catch (Exception ex)
        {
            Response.Write("error" + ex.ToString());
        }
        finally
        {
            cnn1.Close();
            ProductCategoryTB.ReadOnly = true;
            ProductNameTB.ReadOnly = true;
            ProdPriceTB.ReadOnly = true;
        }
    }

it doesnt work, i input 1 into ProductID and the other textboxes dont fill up automatically (as it should do when textchanged event) — BingoDingo
– BingoDingo, Commented Apr 3, 2019 at 3:37
Put a breakpoint in the routuine and check its executing correctly — Programnik
– Programnik, Commented Apr 3, 2019 at 3:40
Did you debug the code? The code inside while loop gets executed? — Chetan
– Chetan, Commented Apr 3, 2019 at 4:06
Try debugging the code, the function may not be getting called. There are multiple ways to implement this, async call (ajax) will be the best option to auto-populate the data asynchronously. — VikrantMore
– VikrantMore, Commented Apr 3, 2019 at 6:29

Michael Buen · Accepted Answer · 2019-04-03 04:22:16Z

2

Set the textbox's AutoPostBack attribute to true

More info: https://meeraacademy.com/textbox-autopostback-and-textchanged-event-asp-net/

<asp:TextBox ID="ProductIDTB" runat="server" AutoPostBack="True" 
OnTextChanged="ProductIDTB_TextChanged"></asp:TextBox>

By the way, use SqlParameter to use parameterized query. Aside from sql-injection attack prevention, parameterized query can help the RDBMS store and re-use the execution plan of similar queries, to ensure better performance. See: https://dba.stackexchange.com/questions/123978/can-sp-executesql-be-configured-used-by-default

string selectSql1 = "SELECT * FROM [Product] WHERE ProductID = @productIdFilter";

int productIdFilter = Convert.ToInt32(ProductIDTB.Text);

SqlCommand com1 = new SqlCommand(selectSql1, cnn1);
com1.Parameters.AddWithValue("productIdFilter", productIdFilter);

edited Apr 3, 2019 at 4:22

answered Apr 3, 2019 at 4:01

Michael Buen

39.6k10 gold badges99 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dale K · Accepted Answer · 2019-04-03 06:55:51Z

0

OnTextChanged Event in Code Behind

protected void txtProductId_TextChanged(object sender, EventArgs e) 
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["_connect"].ToString());

    int ProductId = Convert.ToInt32(txtProductId.Text);

    SqlCommand com = con.CreateCommand();
    com.CommandText = "sp_ProductGetData";
    com.CommandType = CommandType.StoredProcedure;

    com.Parameters.AddWithValue("@Mode", 1);
    com.Parameters.AddWithValue("@ProductId", ProductId);   

    con.Open();

    SqlDataReader dr = com.ExecuteReader();

    if (dr.HasRows)
    {
        dr.Read();
        txtProductCategory.Text = Convert.ToString(dr["ProductCategory"]);
        txtProductName.Text = Convert.ToString(dr["ProductName"]);
        txtPrice.Text = Convert.ToString(dr["Price"]);
    }  
    else
    {
        ScriptManager.RegisterClientScriptBlock((Page)(HttpContext.Current.Handler), typeof(Page), "alert", "javascript:alert('" + Convert.ToString(("No Record Found with Prodcut Id: "+ProductId)) + "');", true);
        return;
    }

    con.Close();
}

Stored Procedure

CREATE PROCEDURE sp_ProductGetData
(
  @Mode INT=NULL,
  @ProductId INT=NULL   
)
AS
BEGIN   
  IF(@Mode=1)
  BEGIN
    SELECT ProductCategory,ProductName,Price FROM Product
    WHERE ProductId=@ProductId
  END       
END

edited Apr 3, 2019 at 6:55

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Apr 3, 2019 at 5:36

Akhileshkumar singh

1,5322 gold badges10 silver badges21 bronze badges

1 Comment

Akhileshkumar singh Over a year ago

Alignment is not Proper, Sorry for that: But this will definitely work for you

Collectives™ on Stack Overflow

Adding data from SQL Server table into textbox when entering data in another textbox

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related