Insert statement weird error

1
votes

I am calling a web service in azure and to populate a DB with the following method. I don't know whats wrong..

using (SqlConnection conn = new SqlConnection(cs))
{
   using (SqlCommand command = conn.CreateCommand())
   {
      conn.Open();
      string cmdText = String.Format("INSERT INTO UserFiles VALUES('" + obj.userRef.ToString() + "','name','name','name','name','name','name'");
      command.CommandText = cmdText;
      command.ExecuteNonQuery();
      conn.Close();
   }
}

This is the error msg:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'name'.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at _4900ProjectDesktopInterface.Form1.uploadbutton_Click(Object sender, EventArgs e) in C:\Users\Ken\Documents\GitHub\MegaFileUploadConversionService\TestingTool\4900ProjectDesktopInterface\Form1.cs:line 152\r\nClientConnectionId:fb95122f-415b-484d-9438-903f0bf2aad0"

3
c#sql-servertsql
put the table definition and the content of the cmdText in the question please. - Preet Sangha
what is the value of obj.userRef.ToString()? If it contains a single quote symbol, or some other unescaped symbols, then it may break your query - Ivaylo Slavov
At the very least the closing ) for the values is missing from the sql statement. - Goran
You should always use parameterized queries. Your code is open for an SQL Injection_ attakcs. - Soner Gönül
@LeeTaylor not really - as long as the number of values and their type matches the schema. - Goran

3 Answers

3
votes

Your cmdText need one more ) at the end of;

using (SqlConnection conn = new SqlConnection(cs))
{
   using (SqlCommand command = conn.CreateCommand())
   {
      conn.Open();
      string cmdText = String.Format("INSERT INTO UserFiles VALUES(@userRef, @name1, @name2, @name3, @name4, @name5, @name6)");
      command.Parameters.AddVithValue("@userRef", obj.userRef.ToString()); 
      command.Parameters.AddVithValue("@name1", name); 
      command.Parameters.AddVithValue("@name2", name); 
      command.Parameters.AddVithValue("@name3", name); 
      command.Parameters.AddVithValue("@name4", name); 
      command.Parameters.AddVithValue("@name5", name); 
      command.Parameters.AddVithValue("@name6", name); 
      command.CommandText = cmdText;
      command.ExecuteNonQuery();
      conn.Close();
   }
}

As I said in my comment, You should always use parameterized queries. Your code is open for an SQL Injection attakcs

2
votes
using (SqlConnection conn = new SqlConnection(cs))
{
   using (SqlCommand command = conn.CreateCommand())
   {
      conn.Open();
      string cmdText = String.Format("INSERT INTO UserFiles VALUES('" + obj.userRef.ToString() + "','name','name','name','name','name','name')");
      command.CommandText = cmdText;
      command.ExecuteNonQuery();
      conn.Close();
   }
}

you forget close right parentheses

0
votes

it looks like you are not terminating the open bracket for values? and why are you using String.Format? and you can get rid of conn.close, since the using statement will implicitly do this.

using (SqlConnection conn = new SqlConnection(cs))
{
   using (SqlCommand command = conn.CreateCommand())
   {
      conn.Open();
      string cmdText = "INSERT INTO UserFiles VALUES('" + obj.userRef.ToString() + "','name','name','name','name','name','name')";
      command.CommandText = cmdText;
      command.ExecuteNonQuery();
   }
}