The multi-part identifier “TextBox1.Text” could no

Question

I'm doing a practice project for training; my handler has specifically forbidden paramaterization and security-oriented coding for now, in the interest of getting the basics down. That being said, I've got a gridview on my homepage with a hyperlink field that takes the user to a page where they can edit the row data in textboxes. The row is displayed by the "ProductId" column, as it is autoincremented and unique. The values display perfectly, so I know my query string is fine, but when I attempt to update using the button event, I get an error message that says

The multi-part identifier "TextBox1.Text" could not be bound.

for all of my textboxes. My code is below. What am I missing? This is my first rodeo, so it may very well be basic and obvious to an experienced eye.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Data;
    using System.Data.Sql;
    using System.Data.SqlClient;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;

    public partial class ViewEdit : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string x = Request.QueryString["ProductId"];
            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
            string editQuery = "SELECT CustId, CustName, SicNaic, CustCity, CustAdd, CustState, CustZip, BroName, BroId, BroAdd, BroCity, BroState, BroZip, EntityType, Coverage, CurrentCoverage, PrimEx, Retention, EffectiveDate, Commission, Premium, Comments FROM ProductInstance WHERE ProductId =" + x;



        using (SqlConnection editConn = new SqlConnection(connectionString))
        {
            editConn.Open();

            using (SqlCommand command = new SqlCommand(editQuery, editConn))
            {

                SqlDataReader dr = command.ExecuteReader();
                dr.Read();
                TextBox1.Text = dr.GetInt32(0).ToString();
                TextBox2.Text = dr.GetString(1);
                TextBox3.Text = dr.GetString(2);
                TextBox4.Text = dr.GetString(3);
                TextBox5.Text = dr.GetString(4);
                TextBox6.Text = dr.GetString(5);
                TextBox7.Text = dr.GetInt32(6).ToString();
                TextBox8.Text = dr.GetString(7);
                TextBox9.Text = dr.GetInt32(8).ToString();
                TextBox10.Text = dr.GetString(9);
                TextBox11.Text = dr.GetString(10);
                TextBox12.Text = dr.GetString(11);
                TextBox13.Text = dr.GetInt32(12).ToString();
                TextBox14.Text = dr.GetString(13);
                TextBox15.Text = dr.GetInt32(14).ToString();
                TextBox16.Text = dr.GetInt32(15).ToString();
                TextBox17.Text = dr.GetInt32(16).ToString();
                TextBox18.Text = dr.GetInt32(17).ToString();
                TextBox19.Text = dr.GetDateTime(18).ToString();
                TextBox20.Text = dr.GetInt32(19).ToString();
                TextBox21.Text = dr.GetInt32(20).ToString();
                TextBox22.Text = dr.GetString(21);



            }

        }   
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string x = Request.QueryString["ProductId"];
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
        string updateQuery = "UPDATE ProductInstance SET CustId = TextBox1.Text, CustName = TextBox2.Text, SicNaic =TextBox3.Text, CustCity = TextBox4.Text, CustAdd = TextBox5.Text, CustState = TextBox6.Text, CustZip = TextBox7.Text, BroName = TextBox8.Text, BroId = TextBox9.Text, BroAdd = TextBox10.Text, BroCity = TextBox11.Text, BroState = TextBox12.Text, BroZip = TextBox13.Text, EntityType = TextBox14.Text, Coverage = TextBox15.Text, CurrentCoverage = TextBox16.Text, PrimEx = TextBox17.Text, Retention = TextBox18.Text, EffectiveDate = TextBox19.Text, Commission = TextBox20.Text, Premium = TextBox21.Text, Comments = TextBox22.Text WHERE ProductId =" + x; 
        using (SqlConnection updateConn = new SqlConnection(connectionString))
        {
            updateConn.Open();
            {
                using (SqlCommand command = new SqlCommand(updateQuery, updateConn))
                {
                    command.ExecuteNonQuery();
                }
            }
        }
    }
}

Answer 1

Use parameters to do this. Otherwise you are wide-open for SQL injection.

SQLCommand cmd = new SQLCommand();
cmd.CommandText = "UPDATE ProductInstance SET CustId = @CustID WHERE .... ";
cmd.Parameters.AddWithValue("@CustID", TextBox1.Text);
cmd.ExecuteNonQuery();

Answer 2

Your query will be executed as is, Textbox*.Text won't be replaced. You will have to use SQL Parameters or use a string Builder or string.Format to generate your query string.

const string queryFormat = "UPDATE ProductInstance SET CustId = {0}, CustName = '{1}', ... WHERE ProductId = {n}";
var query = string.Format(queryFormat, Textbox1.Text, 
                                       Textbox2.Text, 
                                       ..., 
                                       TextboxN.Text, x);

Make sure you generate a valid SQL Update query. Something like CustCity = TextBox4.Text will fail if Textbox4.Text is a string. You will have to add quotes where needed CustCity = '" + TextBox4.Text + "'"

Even if you can not use parameters or ORMs I would recommend you to name your textboxes other than TextboxN.

Furthermore I don't get how this code would work if you are using a grid view? You are only populating one row?

Answer 3

You have to pass the value of the Text property of the TextBox controls to the query not the "TextBox.Text" as a string:

string updateQuery = "UPDATE ProductInstance SET CustId = " + TextBox1.Text + ", CustName = '" + TextBox2.Text + "', .... " + x;

NOTE:

If the value of the "Text" property was a string the you have to place a ' on the two sides of the value like in the example above.