CODEWITHSUNDEEP
c#sqldatabasedatatablecreate-table
Tom
Tom

Reputation: 154

Create Table SQL query - select table name from string

I'm attempting to programmatically create a SQL table. I can create a table with a query, this is no issue at all. But I'd like the table name to have some relevance to the data inserted into it, as it's being used for quotations and invoices. Data entered from a DataGridView will be inserted into it (probably via bulkcopy, or something similar). 

using (SqlCeCommand command = new SqlCeCommand(
                  "CREATE TABLE table1' (Weight INT, Name NVARCHAR, Breed NVARCHAR)", con))

works perfectly. However I'd like this code to work: 

using (SqlCeConnection con = new SqlCeConnection(@"Data Source=|DataDirectory|\LWADataBase.sdf"))
{
   con.Open();
   try
   {
      string tableName = "" + quotenameTxt.Text + "-" +firstTxt.Text+ "-" + surenameTxt.Text;

      using (SqlCeCommand command = new SqlCeCommand(
         "CREATE TABLE '"+tableName.ToString()+"' (Weight INT, Name NVARCHAR, Breed NVARCHAR)", con))
      {
         command.ExecuteNonQuery();
      }
   }
   catch (Exception ex)
   {
      MessageBox.Show(ex.Message);
   }
}

Any suggestions? I get an error (as expected) but are unsure what I need to do.

I'm using SqlCe (and before anyone says "BulkCopy isn't supported", I know, I've got a reference that allows it)

The error I get is :

There was an error parsing the query. [ Token line number = 1,Token line offset = 16,Token in error = 1-2-3 ]

// "1-2-3" being the textbox values.

Upvotes: 0

Views: 2254

Answers (2)

Brandon
Brandon

Reputation: 645

As was mentioned in comments above, make the following changes:

using (SqlCeCommand command = new SqlCeCommand(
     "CREATE TABLE '"+tableName+"' (Weight INT, Name NVARCHAR, Breed NVARCHAR)", con))

tableName is already a string. No need to use .ToString() on it.

Also, you have a leading white space in your declaration of tableName:

string tableName = "" + quotenameTxt.Text + "-" + firstTxt.Text + "-" 
                      + surenameTxt.Text;

This makes the string " 1-2-3", not the "1-2-3" you are expecting.

Lastly, surround your tableName with [] to get it to work correctly:

using (SqlCeCommand command = new SqlCeCommand(
   "CREATE TABLE '[" + tableName + "]' (Weight INT, Name NVARCHAR, Breed NVARCHAR)", con))

Upvotes: 2

BateTech
BateTech

Reputation: 6496

Change the dashes to underscores or surround the entire table name with [square brackets]

Upvotes: 3

Related Questions