how can we create dynamic table in data base using asp.net c#

Question

0.00/5 (No votes)

See more:

i m doing this but i m not getting the table created in my database.
aspx. code

ASP.NET

table name:<asp:TextBox ID="TextBox1" runat="server">
column1 Name:<asp:TextBox ID="TextBox2" runat="server">
Column2 name:<asp:TextBox ID="TextBox3" runat="server">
    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Create"
        Width="96px" />

my .cs code

C#

string Create;
      SqlConnection con = new SqlConnection("Data Source=SHANTANU\\SQLEXPRESS;Initial Catalog=dynamicdata;Integrated Security=True");
        Create="'create table' '"+TextBox1.Text+"''('"+TextBox2.Text+"'+'varchar(200)','"+TextBox3.Text+"'+'varchar(200)'+')'";
        SqlCommand cmd = new SqlCommand(Create,con);
        try
	            {
	                con.Open();
	                cmd.ExecuteNonQuery();
	                Response.Write("<script>alert('Table Created')<script>");
	            }
	            catch (System.Exception ex)
	            {
                    Response.Write("<script>alert('Table Created failed')<script>");
                    ex.ToString();
	                
	            }
	            
	                    con.Close();
	                
	            }
    }

[edit]Code block added - OriginalGriff[/edit]

Posted 13-Jan-14 21:02pm

Shantanu sinha

Updated 13-Jan-14 21:40pm

OriginalGriff

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-01-13T21:47:00

Solution 1

I'm not surprised.
Take your SQL string, and manually feed in your textbox data: (and we will ignore the massive dangers to your Database integrity that your code represents - have you never heard of SQL INjection Attacks?)

C#

Create="'create table' '"+TextBox1.Text+"''('"+TextBox2.Text+"'+'varchar(200)','"+TextBox3.Text+"'+'varchar(200)'+')'";

Assume each textbox has it's name in it, that evaluates to a string:

'create table' 'TextBox1''('TextBox2'+'varchar(200)','TextBox3'+'varchar(200)'+')'

Which is nowhere near valid SQL.
What SQL is expecting is:

create table TextBox1(TextBox2 varchar(200),TextBox3 varchar(200))

See the difference?

Posted 13-Jan-14 21:47pm

OriginalGriff

Comments

Shantanu sinha 14-Jan-14 9:30am

not working

OriginalGriff 14-Jan-14 9:38am

That's a spectacularly helpful error report!
Care to share any more details with us?

Shantanu sinha 14-Jan-14 10:04am

when i am executing this i am getting error that TextBox1 is already in the database
but when i am seeing the database Textbox1 is not there.
and it is not taking the values in Textbox1.
rather i have to use the TextBox1.text with it.

OriginalGriff 14-Jan-14 10:59am

Read what I said:
"Assume each textbox has it's name in it,"
That doesn't mean that you should just copy the string into your code and hope it works.
Instead, it means that by comparing what your code generates with what SQL expects, you should be able to see which data you need to remove from your code. Namely, most of the single quote characters...

Christian Graus · Answer 2 · 2014-01-14T10:50:00

What you are doing, is utterly retarded. Create one table with the following columns

TableName
Col1Name
Col2Name
Col1
Col2

In this way you can store the names you give your two string columns and the names of your 'tables' and have only one table, which you can do aggregated queries on, which is well known to your system as a whole, and which makes it far easier to avoid having to do the AWFUL and unsafe string mashing you were doing in the code you posted.

If, as I suspect, you're 'just learning', it's good to learn how to do proper things, not stupid things. Creating tables on the fly is pretty much ALWAYS a 'stupid thing'.\\

The other thing worth noting is, your question has nothing to do with ASP.NET. If you were using winforms, or a console app, the answer would be the same.

pallelokanathareddy · Answer 3 · 2014-01-13T22:31:00

SQL

Create an sp as shown below 
create procedure [dbo].[PDynamicTable]
(
@tname varchar(20),
@col1 varchar(20),
@col2 varchar(20)
)
as 
begin
declare @a varchar(2000);declare @b varchar(200);
set @a='create table [dbo].['+@tname+'](';
set @b=@a+@col1+ ' varchar(200),'+@col2+''+ ' varchar(200);
--print @b;
exec(@b);
end

then write the following asp.net code in your button click .

C#

SqlConnection con = new SqlConnection("Data Source=SHANTANU\\SQLEXPRESS;Initial Catalog=dynamicdata;Integrated Security=True");
  string qry="PDynamicTable";
        SqlCommand cmd = new SqlCommand(qry,con);
        try
	            {
sqlparameter p1=new sqlparameter("@tname",sqldbtype.varchar,20);
sqlparameter p2=new sqlparameter("@col1",sqldbtype.varchar,200);
sqlparameter p3=new sqlparameter("@col2",sqldbtype.varchar,200);
p1.value=TextBox1.Text;
p2.value=TextBox2.Text;
p3.value=TextBox3.Text;
cmd.parameters.Add(p1);
cmd.parameters.Add(p2);
cmd.parameters.Add(p3);
	                con.Open();
	                cmd.ExecuteNonQuery();
	                Response.Write("<script>alert('Table Created')</script>");
	            }
	            catch (System.Exception ex)
	            {
                    Response.Write("<script>alert('Table Created failed')</script>");
                    ex.ToString();
	                
	            }
finally{
	            
	                    con.Close();
}
	                
	            }
    }

Note: this code is not verified in visual studio . I always use c# interview questions and answers present in http://skillgun.com website .

how can we create dynamic table in data base using asp.net c#

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0