Dynamically create table and insert data in asp.net

Question

Dynamically create table and insert data into the created table, I'm using following command to create a table dynamically and insert the values. I'm commented the sample value for the variable respectively.

Dim question, opt1, opt2, opt3, opt4, ans, paper As String
          question = Request.QueryString("question") '<p>This is my first question</p>
          opt1 = Request.QueryString("option1") ' option 1
          opt2 = Request.QueryString("option2") ' option 2
          opt3 = Request.QueryString("option3") ' option 3
          opt4 = Request.QueryString("option4") ' option 4
          ans = Request.QueryString("answer")    ' 2
          paper = Request.QueryString("paper")  ' cs-5-cder-2012
          'Response.Write("....")
          Dim cs As String = ConfigurationManager.ConnectionStrings("eExamSolutionConnection").ConnectionString
          Dim cn As New SqlConnection(cs)
      Dim cmd As New SqlCommand

      Dim cmdText As String
      cmdText = "IF EXISTS (SELECT * FROM   INFORMATION_SCHEMA.TABLES  WHERE  TABLE_SCHEMA = 'RAVI' AND TABLE_NAME = '" & paper & "') "
      cmdText = cmdText & " BEGIN"
      cmdText = cmdText & " INSERT INTO " & paper & "(question,option1,option2,option3,option4,answer) VALUES('" & question & "','" & opt1 & "','" & opt2 & "','" & opt3 & "','" & opt4 & "','" & ans & "');"
      cmdText = cmdText & " END"
      cmdText = cmdText & " ELSE"
      cmdText = cmdText & " BEGIN"
      cmdText = cmdText & " CREATE TABLE " & paper & "(question_Id int AUTO_INCREMENT,question varchar(MAX),option1 varchar(255),option2 varchar(255),option3 varchar(255),option4 varchar(255),answer varchar(2));"
      cmdText = cmdText & " INSERT INTO " & paper & "(question,option1,option2,option3,option4,answer) VALUES('" & question & "','" & opt1 & "','" & opt2 & "','" & opt3 & "','" & opt4 & "','" & ans & "');"
      cmdText = cmdText & " END"
      Try
          cmd.CommandText = cmdText.ToString
          cmd.Connection = cn
          cmd.Connection.Open()
          cmd.ExecuteNonQuery()
          cmd.Connection.Close()
          cmd.Dispose()
          cn.Dispose()
          Response.Write("Question Added !!")
      Catch ex As Exception
          Response.Write(ex.ToString)
      End Try

But, I'm getting exception

System.Data.SqlClient.SqlException: Incorrect syntax near '-'. Incorrect syntax near '-'. Incorrect syntax near '-'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ASP.admin_addquestion_aspx._Render_control1(HtmlTextWriter __w, Control parameterContainer) in D:\EWA\eExam\admin\addQuestion.aspx:line 40

Vikash Sinha · Accepted Answer · 2012-07-25 05:18:42Z

Please use

    Dim cmdText As String
    cmdText = "IF EXISTS (SELECT * FROM   INFORMATION_SCHEMA.TABLES  WHERE  TABLE_SCHEMA = 'RAVI' AND TABLE_NAME = '" & paper & "') "
    cmdText = cmdText & " BEGIN"
    cmdText = cmdText & " INSERT INTO [" & paper & "](question,option1,option2,option3,option4,answer) VALUES('" & question & "','" & opt1 & "','" & opt2 & "','" & opt3 & "','" & opt4 & "','" & ans & "');"
    cmdText = cmdText & " END"
    cmdText = cmdText & " ELSE"
    cmdText = cmdText & " BEGIN"
    cmdText = cmdText & " CREATE TABLE [" & paper & "](question_Id int IDENTITY(1,1),question varchar(MAX),option1 varchar(255),option2 varchar(255),option3 varchar(255),option4 varchar(255),answer varchar(2));"
    cmdText = cmdText & " INSERT INTO [" & paper & "](question,option1,option2,option3,option4,answer) VALUES('" & question & "','" & opt1 & "','" & opt2 & "','" & opt3 & "','" & opt4 & "','" & ans & "');"
    cmdText = cmdText & " END"

Instead of

   Dim cmdText As String
  cmdText = "IF EXISTS (SELECT * FROM   INFORMATION_SCHEMA.TABLES  WHERE  TABLE_SCHEMA = 'RAVI' AND TABLE_NAME = '" & paper & "') "
  cmdText = cmdText & " BEGIN"
  cmdText = cmdText & " INSERT INTO " & paper & "(question,option1,option2,option3,option4,answer) VALUES('" & question & "','" & opt1 & "','" & opt2 & "','" & opt3 & "','" & opt4 & "','" & ans & "');"
  cmdText = cmdText & " END"
  cmdText = cmdText & " ELSE"
  cmdText = cmdText & " BEGIN"
  cmdText = cmdText & " CREATE TABLE " & paper & "(question_Id int AUTO_INCREMENT,question varchar(MAX),option1 varchar(255),option2 varchar(255),option3 varchar(255),option4 varchar(255),answer varchar(2));"
  cmdText = cmdText & " INSERT INTO " & paper & "(question,option1,option2,option3,option4,answer) VALUES('" & question & "','" & opt1 & "','" & opt2 & "','" & opt3 & "','" & opt4 & "','" & ans & "');"
  cmdText = cmdText & " END"

Problem was:

1 Table name should be[] at create and insert time. 2 For autoincremnet there will be IDENTITY(1,1) in SQL server.

Kinexus · Accepted Answer · 2012-07-25 04:41:19Z

0

You could try adding [ and ] around the table name, so it would become;

"IF EXISTS (SELECT * FROM   INFORMATION_SCHEMA.TABLES  WHERE  TABLE_SCHEMA = 'RAVI' AND TABLE_NAME = ['" & paper & "']) "

answered Jul 25, 2012 at 4:41

Kinexus

12.9k6 gold badges43 silver badges63 bronze badges

1 Comment

Ravi Over a year ago

Not helpful, same as it was earlier :-(

Umesh · Accepted Answer · 2012-07-25 06:12:23Z

0

Hyphen character "-" is not allowed in table name. You must replace it with either underscore "_" or completely remove it.

answered Jul 25, 2012 at 6:12

Umesh

2,74221 silver badges24 bronze badges

2 Comments

Umesh Over a year ago

Can you debug the program and provide the value of cmd please?

Ravi Over a year ago

I found the mistake, there was space in between the table name. That's why it showing the error

Collectives™ on Stack Overflow

Dynamically create table and insert data in asp.net

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related