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.
– lc., Commented Aug 31, 2012 at 12:05

Community · Accepted Answer · 2017-05-23 12:30:34Z

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, 2017 at 12:30

CommunityBot

11 silver badge

answered Aug 31, 2012 at 12:11

Laoujin

10.3k7 gold badges45 silver badges72 bronze badges

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

1 Comment

Archana B.R Over a year ago

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.

Community · Accepted Answer · 2017-05-23 12:13:30Z

0

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.

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Aug 31, 2012 at 12:40

Pilgerstorfer Franz

8,3473 gold badges46 silver badges56 bronze badges

2 Comments

Archana B.R Over a year ago

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.

Pilgerstorfer Franz Over a year ago

rewrote answer as of given detail

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related