C# insert data into SQL table

Question

I m getting sqlException was unhandled. Incorrect syntax near 'nvarchar' and Incorrect syntax near 'Name'. I used SMO to create a table like this

Table TechOpsProjectTracker = new Table(db, "TechOpsProjectTracker");

//Add column ID
DataType dt = new DataType(SqlDataType.Int);
Column idColumn = new Column(TechOpsProjectTracker, "ID", dt);
TechOpsProjectTracker.Columns.Add(idColumn);

//Add Column Alignment
DataType dt1 = new DataType(SqlDataType.NVarChar, 50);
Column Column1 = new Column(TechOpsProjectTracker, "Alignment", dt1);
TechOpsProjectTracker.Columns.Add(Column1);

//Add Column Project Name
DataType dt2 = new DataType(SqlDataType.NVarChar, 50);
Column Column2 = new Column(TechOpsProjectTracker, "Project Name", dt2);
TechOpsProjectTracker.Columns.Add(Column2);

//Add Column Project Description
DataType dt3 = new DataType(SqlDataType.NVarChar, 255);
Column Column3 = new Column(TechOpsProjectTracker, "Project Description", dt3);
TechOpsProjectTracker.Columns.Add(Column3);

//Add Column Currently Assigned To
DataType dt4 = new DataType(SqlDataType.NVarChar, 50);
Column Column4 = new Column(TechOpsProjectTracker, "Currently Assigned To", dt4);
TechOpsProjectTracker.Columns.Add(Column4);
TechOpsProjectTracker.Create();

string connectionString = "Server=GSOPS4;Database=MyDatabaseName;Trusted_Connection=True;";

System.Data.SqlClient.SqlConnection sqlConnection1 = 
  new System.Data.SqlClient.SqlConnection(connectionString);

System.Data.SqlClient.SqlCommand cmd = 
  new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "INSERT TechOpsProjectTracker (ID, Alignment, Project Name, Project Description, Currently Assigned To ) VALUES (@ID, @Alignment, @Project Name, @Project Description, @Currently Assigned To)";

SqlParameter ID = new SqlParameter("@ID", SqlDbType.Int);
ID.Value = 5;
cmd.Parameters.Add(ID);

SqlParameter Alignment = new SqlParameter("@Alignment", SqlDbType.NVarChar, 50);
Alignment.Value = "Right";
cmd.Parameters.Add(Alignment);

SqlParameter ProjectName = new SqlParameter("@Project Name", SqlDbType.NVarChar, 50);
ProjectName.Value = "Right Project Name";
cmd.Parameters.Add(ProjectName);

SqlParameter ProjectDesc = new SqlParameter("@Project Description", SqlDbType.NVarChar, 255);
ProjectDesc.Value = "Right Project Description";
cmd.Parameters.Add(ProjectDesc);

SqlParameter Assignment = new SqlParameter("@Alignment", SqlDbType.NVarChar, 50);
Assignment.Value = "corp\\shress2";
cmd.Parameters.Add(Assignment); 

cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();

I get those exceptions at cmd.ExecuteNonQuery(); Any reasons what might be causing it? TIA

If you have the option of using Sql Server Management Studio, you can always right-click on the table (in the object brower) and choose "Script Table as ... Insert" and it will generate the proper syntax for you. — Austin Salonen
– Austin Salonen, Commented May 23, 2012 at 22:36
You can't have spaces in parameter names, but this is caused because your columns have spaces (which they shouldn't - while supported it's a nightmare). — Aaron Bertrand
– Aaron Bertrand, Commented May 23, 2012 at 22:44

BeemerGuy · Accepted Answer · 2012-05-23 22:33:08Z

There's a couple of issues.

Your INSERT statement has spaces in the column name, so you have to enclose those in brackets.
Also, the parameter names cannot have spaces in them, so you need to stick the words together.

cmd.CommandText = "INSERT TechOpsProjectTracker (ID, Alignment, [Project Name], [Project Description], [Currently Assigned To] ) VALUES (@ID, @Alignment, @ProjectName, @ProjectDescription, @CurrentlyAssignedTo)";

Then, when you're adding the parameters, same thing, stick the words together:

SqlParameter ProjectName = new SqlParameter("@ProjectName", SqlDbType.NVarChar, 50);
ProjectName.Value = "Right Project Name";
cmd.Parameters.Add(ProjectName);


SqlParameter ProjectDesc = new SqlParameter("@ProjectDescription", SqlDbType.NVarChar, 255);
ProjectDesc.Value = "Right Project Description";
cmd.Parameters.Add(ProjectDesc);

Collectives™ on Stack Overflow

C# insert data into SQL table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related