Create table in database on button click

Rahul Lakshakar

10y1.6k.NET

I am creating an system using Asp.Net c#. I want create a table in the database on submit the details. I am taking the name of the table from dropdown selected item. I am using the following code but it is not working please help me.....

string constr = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

SqlConnection con = new SqlConnection(constr);

con.Open();

string insertData = "insert into ExamDetail (ExamName, SubjectName, Duration, StartTime, EndTime, NegativeMark, QusNo, QusMarks) values (@EName, @SubName, @DUR, @ST, @ET, @NM, @QN,@QM)";

SqlCommand com = new SqlCommand(insertData, con);

com.Parameters.AddWithValue("@EName", DropExamName.SelectedItem.Text);

com.Parameters.AddWithValue("@SubName", DropSubName.SelectedItem.Text);

com.Parameters.AddWithValue("@DUR", DropDuration.SelectedItem.Text);

com.Parameters.AddWithValue("@ST", Text_Stime.Text);

com.Parameters.AddWithValue("@ET", Text_Etime.Text);

com.Parameters.AddWithValue("@NM", RadioNegativeMarks.SelectedItem.Text);

com.Parameters.AddWithValue("@QN", Text_question_no.Text);

com.Parameters.AddWithValue("@QM", text_question_marks.Text);

string createExamTable = "BEGIN";

createExamTable += "CREATE TABLE [dbo]. ['"+ DropExamName.SelectedItem.Text +"'](";

createExamTable += "[ExamId] INT IDENTITY(1,1) NOT NULL,";

createExamTable += "[QusNo] INT NOT NULL,";

createExamTable += "[QusDetail] VARCHAR(MAX) NOT NULL";

createExamTable += "[Optn1] VARCHAR(MAX) NOT NULL";

createExamTable += "[Optn2] VARCHAR(MAX) NOT NULL";

createExamTable += "[Optn3] VARCHAR(MAX) NOT NULL";

createExamTable += "[Optn4] VARCHAR(MAX) NOT NULL";

createExamTable += "[CorrectOptn] INT NOT NULL";

createExamTable += ")";

createExamTable += " END";

SqlCommand ComCreateTable = new SqlCommand(createExamTable, con);

com.ExecuteNonQuery();

ComCreateTable.ExecuteNonQuery();

Response.Write("DONE");

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ananth G10y ago
Small mistake your query .. comma is missing in end of the Parameters
createExamTable += "[QusDetail] VARCHAR(MAX) NOT NULL";
createExamTable += "[Optn1] VARCHAR(MAX) NOT NULL";
createExamTable += "[Optn2] VARCHAR(MAX) NOT NULL";
createExamTable += "[Optn3] VARCHAR(MAX) NOT NULL";
createExamTable += "[Optn4] VARCHAR(MAX) NOT NULL";

Correct code is
string createExamTable = "BEGIN ";
createExamTable += "CREATE TABLE [dbo]. ['"+ DropExamName.SelectedItem.Text +"'](";
createExamTable += "[ExamId] INT IDENTITY(1,1) NOT NULL,";
createExamTable += "[QusNo] INT NOT NULL,";
createExamTable += "[QusDetail] VARCHAR(MAX) NOT NULL,";
createExamTable += "[Optn1] VARCHAR(MAX) NOT NULL,";
createExamTable += "[Optn2] VARCHAR(MAX) NOT NULL,";
createExamTable += "[Optn3] VARCHAR(MAX) NOT NULL,";
createExamTable += "[Optn4] VARCHAR(MAX) NOT NULL,";
createExamTable += "[CorrectOptn] INT NOT NULL";
createExamTable += ")";
createExamTable += " END";

you can refer below side

http://www.aspsnippets.com/Articles/Create-and-Drop-SQL-Server-Table-programmatically-using-ADONet-C-and-VBNet.aspx
0
Rohitash Kumar Dixit10y ago
Hi,
First of all your create table query is incorrect as put space after "BEGIN" as
string createExamTable = "BEGIN";
string createExamTable = "BEGIN ";
and put comma (,) after all the command lines as:
createExamTable += "[QusDetail] VARCHAR(MAX) NOT NULL,";
createExamTable += "[Optn1] VARCHAR(MAX) NOT NULL, ";
createExamTable += "[Optn2] VARCHAR(MAX) NOT NULL,";
createExamTable += "[Optn3] VARCHAR(MAX) NOT NULL,";
createExamTable += "[Optn4] VARCHAR(MAX) NOT NULL,";
createExamTable += "[CorrectOptn] INT NOT NULL";
createExamTable += ")";
Second If you're using the same table to store the data (insert query) then your sequence of execution is incorrect. Try this:
ComCreateTable.ExecuteNonQuery();
com.ExecuteNonQuery();
Hope this will help you :-)
+1
Manas Mohapatra10y ago
There is some problem with sequnce of executing the statements.

Means, first it needs to create the table then you need to insert data. Follow below code:

SqlCommand ComCreateTable = new SqlCommand(createExamTable, con);
ComCreateTable.ExecuteNonQuery();
com.ExecuteNonQuery();
Response.Write("DONE");

http://www.c-sharpcorner.com/article/create-a-sql-server-database-dynamically-in-C-Sharp/

Note: If you are getting any exception please share the details.
0
Midhun Tp10y ago
Hi,
Is your insert query working?
You should try with single command and check whether it is working or not. Sometimes it may be issue with your coding logic.

Also have a look at below article -
http://www.aspsnippets.com/Articles/Create-and-Drop-SQL-Server-Table-programmatically-using-ADONet-C-and-VBNet.aspx
0