Click here to Skip to main content
14,735,205 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i m doing this but i m not getting the table created in my database.
aspx. code

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
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
Updated 13-Jan-14 22:40pm
v2

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?)
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?
   
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...
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.
   
v2
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 .

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 .
   
Comments
Shantanu sinha 14-Jan-14 10:11am
   
getting Error here
sqldbtype.varchar,20);
it is showing invalid item
pallelokanathareddy 10-May-14 9:26am
   
use visual studio editor properly , which will prompt you to add the namespace System.Data or System.Data.Common
Chetan EY 28-May-15 5:25am
   
Not in a proper way.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900