How to edit and update a GridView whose headers are not static and unknown

Question

A table is created in the database with a customer name (which is entered in a TextBox). The fields of the table created are the items in the ListBox. Like:

com = new SqlCommand("CREATE TABLE " + Label6.Text + " (Locations nvarchar(20), " + ListBox3.Items[0].ToString().Trim() + " nvarchar(5))", con);
con.Open();
com.ExecuteNonQuery();
con.Close();

for (int i = 1; i <= ListBox3.Items.Count-1; i++)
{
    com = new SqlCommand("ALTER TABLE " + Label6.Text + " ADD " + ListBox3.Items[i].Text.Trim() + " nvarchar(5)", con);
    con.Open();
    com.ExecuteNonQuery();
    con.Close();
}

I am further displaying this table using GridView. Like:

SqlCommand com = new SqlCommand("SELECT * FROM " + Label6.Text + "", con);
SqlDataAdapter da = new SqlDataAdapter(com);
DataTable dt = new DataTable();
da.Fill(dt);
GridView2.DataSource = dt;
GridView2.DataBind();

The asp code for GridView is :

<Columns>
    <asp:CommandField ShowEditButton="True" />
</Columns>

Hence, I want the first column to be readonly. I want to edit the GridView and update the customer(referring to Label6.Text) table in the database. Note: I want the GridView to accept only integers.

The image of my database table and the Gridview is shown in the following link. Kindly help. Thank you.

enter image description here

You need to make sure you set `Label6.Text = Label6.Text + "(blah NVARCHAR(5)); DROP DATABASE; --"` first. Then read up on SQL injection. — lc., Aug 31 '12 at 12:05
Check [SQL Injections](http://msdn.microsoft.com/en-us/library/ms161953(v=sql.105).aspx) — Raghuveer, Aug 31 '12 at 13:43

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

To accept only numeric input, you could use a jQuery plugin. Some possibilities are presented here.

com = new SqlCommand("ALTER TABLE " + Label6.Text + " ADD " + ListBox3.Items[i].Text.Trim() + " nvarchar(5)", con);

This approach makes you vulnerable to SQL-injection. Your entire application is pretty brittle by building the SQL statements like that.

To update the database, you could work in a similar way as how you created/selected from the table.

edited May 23 '17 at 12:30

Community

1
1

answered Aug 31 '12 at 12:11

Laoujin

9,962
7
42
69

I want to update `GridView` values in the database table. I have attached an image which shows wat I want. The asp html code for the `GridView` is what I am look for and corresponding update method. Kindly help. – Archana B.R Aug 31 '12 at 17:12

score 0 · Answer 2 · edited May 23 '17 at 12:13

At first you should rethink your approach and change all your SQL statements to make use of SQL Parameters. Right now your code is extremely vulnerable to SQL-Injection! There are plenty of examples here on SO or just have a look at MSDN.

Furthermore I don't think it is a good decision to let the user create a table and data on the fly imo. Anyway - if you really want to implement this on this way you might add columns dynamically

for (int i = 0; i < dt.Columns.Count; i++)
{
    DataColumn dc = dt.Columns[i];
    BoundField bf = new BoundField();
    bf.ReadOnly = i == 0;  // will take care of setting controls to readonly in column 0
    bf.DataField = dc.ColumnName;
    GridView1.Columns.Add(bf);
}

Unfortunatelly adding a column which allows numbers only is not quite easy. In this case it would be necessary to create a templateField on the fly and add it to columns. Have a look at this example for that.

EDIT

If you try to dynamically add Values to a table, you should make use of SQL Parameters. (e.g INSERT INTO tblXYZ (abc, def) values (@valueForABC, @valueForDEF) )

Unfortunatelly there is right now no possibility to use SQL Parameters to parametherize tableNames or columNames. So try to

...restricting the characters to A-Z, a-z, 0-9, '.', '_' and ' ' - and then use whatever the appropriate bracketing is for the database (e.g. [] for SQL Server, I believe) to wrap round the whole thing.

Shown in this answer of Jon Skeet.

Could you kindly guide me as to how can I modify my code so that it is invulnerable by modifying my code. First experience in this languages. Please oblige. — Archana B.R, Aug 31 '12 at 13:12

How to edit and update a GridView whose headers are not static and unknown

2 Answers2