Add a column to a sql server table with a asp.net form

Question

I am using C# form and need to enter a column name to the "varchar(100)" textbox and submit the form to create a column on the "Products3" table in sql server. I am getting this error "Error Creating column. Incorrect syntax near 'System.Web.UI.WebControls.TextBox'." when I click the Submit button. I am not sure why the SQL statement does not see the textbox. Please help.

========================== FrontPage ===

<form id="form1" runat="server">
<div>
<br /><br />
    <asp:button id="IP_TextBtn" onclick="btnAddColumn_Click" runat="server" text="Submit" />
    <br />
    <br />
    <asp:textbox id="txtIP_TextField" runat="server"></asp:textbox>
    <br />
    <br />
    <asp:Label id="lblResults" runat="server" Width="575px" Height="121px" Font-Bold="True"></asp:Label>
    <br />
    <br />
</div>
</form>

========================= BackPage ===

//  Creating the Method for adding a new column to the database
public virtual void btnAddColumn_Click(object sender, EventArgs args) 
    {
        {
            string alterSQL;
            alterSQL = "ALTER TABLE Products3 ";
            alterSQL += "ADD '" + txtIP_TextField + "' bool()";

            SqlConnection con = new SqlConnection(GetConnectionString());
            SqlCommand cmd = new SqlCommand(alterSQL, con);
            cmd.Parameters.AddWithValue("@txtIP_TextField ", txtIP_TextField.Text);

            int SQLdone = 0;
            try
            {
                con.Open();
                SQLdone = cmd.ExecuteNonQuery();
                lblResults.Text = "Column created.";
            }
            catch (Exception err)
            {
                lblResults.Text = "Error Creating column. ";
                lblResults.Text += err.Message;
            }
            finally
            {
                con.Close();
            }
        }
    }

John Saunders · Accepted Answer · 2011-09-26 15:18:04Z

1

You're confused about parameterized queries. txtIP_TextField is not a parameter to the query, so adding it to the Parameters collection won't help. Your query should be:

string alterSQL = "ALTER TABLE Products3  ADD @txtIP_TextField BIT";

Edit: It looks like it may not be possible to parameterize this statement. In that case, you will need to use:

string alterSQL = String.Format("ALTER TABLE Products3  ADD {0} BIT", 
                                txtIP_TextField.Text);

However, this is still subject to SQL Injection Attacks, and you will need to "clean" the txtIP_TextField.Text before using it.

edited Sep 26, 2011 at 15:18

answered Sep 26, 2011 at 14:16

John Saunders

162k26 gold badges252 silver badges403 bronze badges

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

4 Comments

JT. Over a year ago

I received this error with your suggestion: Error Creating column. Incorrect syntax near the keyword 'COLUMN'.

JT. Over a year ago

Thanks but still getting error: Error Creating column. Incorrect syntax near the keyword 'COLUMN'.

John Saunders Over a year ago

You're right. I misread the syntax. The word "COLUMN" is not used. See my updated answer.

Sнаđошƒаӽ Over a year ago

@John how to clean the text before using as column name? Any pointers?

Artem · Accepted Answer · 2011-09-26 14:14:09Z

0

Use txtIP_TextField.Text

alterSQL += "ADD '" + txtIP_TextField.Text + "' bool()";

Thats the value of your textbox

answered Sep 26, 2011 at 14:14

Artem

771 silver badge5 bronze badges

3 Comments

JT. Over a year ago

I received this error with your suggestion: Error Creating column. Incorrect syntax near 'TEST'. This is an internal tool that I will be using so I am not too worried about sql injection as it is not for public use. I entered "TEST" in the textbox.

Artem Over a year ago

Use the last sample, hope this help

JT. Over a year ago

What sample? All suggestions have failed. Help please!

Artem · Accepted Answer · 2011-09-26 14:19:21Z

0

Use this:

 string alterSQL;
 alterSQL = "ALTER TABLE Products3 ";
 alterSQL += "ADD @txtIP_TextField bool()";

 SqlConnection con = new SqlConnection(GetConnectionString());
 SqlCommand cmd = new SqlCommand(alterSQL, con);
 cmd.Parameters.AddWithValue("@txtIP_TextField ", txtIP_TextField.Text);

answered Sep 26, 2011 at 14:19

Artem

771 silver badge5 bronze badges

1 Comment

JT. Over a year ago

I received this error with your suggestion: Error Creating column. Incorrect syntax near '@txtIP_TextField'.

Collectives™ on Stack Overflow

Add a column to a sql server table with a asp.net form

3 Answers 3

4 Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related