CODEWITHSUNDEEP
c#syntax-errorvisual-foxpro
Joshua Cameron-Mackintosh
Joshua Cameron-Mackintosh

Reputation: 269

What's causing this syntax error? oledb INSERT statement

I posted about a different issue I had earlier, and with some help resolved that.

I am now receiving a syntax error centered around the following code:

importConnection.Open();
Console.WriteLine("Foxpro connection open");
OleDbCommand deleteOleDbCommand = new OleDbCommand(@"TRUNCATE TABLE CLIENT",
                            importConnection);

Console.WriteLine("writing to table");
Console.ReadKey();
using (OleDbCommand importCommand = new OleDbCommand(
    string.Format("INSERT INTO CLIENT (Title,Fname,Sname)" + "VALUES ({0},{1},{2})",
    exportReader.GetValue(0), exportReader.GetValue(1), exportReader.GetValue(2))))
    importCommand.ExecuteReader();

With this being the output:

Output Text

using break points I have determined that the export reader calls are receiving data. I've narrowed it down to an issue with:

"VALUES ({0},{1},{2})",
  exportReader.GetValue(0), exportReader.GetValue(1), exportReader.GetValue(2))))

I have confirmed that data can be inserted by doing the following:

using (OleDbCommand importCommand =
    new OleDbCommand(string.Format(
       "INSERT INTO CLIENT (Title,Fname,Sname)" + "VALUES (Mr,Joshua,Cameron-Mackintosh)",
           importConnection)))

This causes no problems, so I know the issue does not lie with the underlying connection or command.

Upvotes: 0

Views: 901

Answers (4)

Octanic
Octanic

Reputation: 133

You must give space to your concatenated strings.

"INSERT INTO CLIENT (Title,Fname,Sname)" + "[space here]VALUES (Mr,Joshua,Cameron-Mackintosh)"

however, it should look like this:

"INSERT INTO CLIENT (Title,Fname,Sname) VALUES (?,?,?)"

Always make use of parametrized queries. Please refer to: http://blogs.technet.com/b/neilcar/archive/2008/05/21/sql-injection-mitigation-using-parameterized-queries.aspx

Upvotes: 0

DRapp
DRapp

Reputation: 48139

Others correctly comment about SQL-Injection, however VFP is less impacted to SQL-Injection, it can be just the same. Main reason, VFP doesn't really work with multiple queries the same way other sql engines allow by a ";" identifying break between statements. However, with mismatched quotes, it CAN and WILL break your sql-statements from actually running.

Having said that, VFP OleDb provider does allow parameterizing, but does so without "named" parameters. It does it with "?" as a place-holder for where the value would be inserted by the .net framework, and you don't have to convert the data type as long as it is in the same expected format (ex: string, numeric, date)

change your OleDbCommand to

"INSERT INTO CLIENT (Title, Fname, Sname ) values ( ?, ?, ? )"

Then, set your parameters via 

importCommand.Parameters.Add( "parmForTitle", exportReader.GetValue(0));
importCommand.Parameters.Add( "parmForFName", exportReader.GetValue(1));
importCommand.Parameters.Add( "parmForSName", exportReader.GetValue(2));

Also, the parameters must be added in the exact same sequential order as they appear in the query. So, I prefixed them with "parmFor" to indicate it is the parameter placement for the corresponding field being inserted (or updated, or used in select, insert or delete too). The command objects work the same for all the commands. Even if you write a select statement and have values in a WHERE, JOIN or whatever other position.

THEN, ExecuteNonQuery()

Upvotes: 3

Cetin Basoz
Cetin Basoz

Reputation: 23797

  1. It is saying "foxpro connection string" there. If it is done against a VFP database, then "Truncate table client" wouldn't work in the first place. That command does not exist in VFP. Instead you could try using "Delete From Client" which marks the records for deletion. Or you can use "zap" command with ExecSript that would correspond to "truncate table" but then the connection needs to use the table exclusively.
  2. You should quote the string values. Better yet, for any SQL operation you should use parameters. When you use parameters, you should do that in a correct way for the connection you use. Here you are using an OLEDB connection, then you should use ? as a parameter place holder.

A revised version of your code would then be:

importConnection.Open();
Console.WriteLine("Foxpro connection open");
OleDbCommand deleteOleDbCommand = new OleDbCommand(@"Delete from CLIENT",
                            importConnection);

Console.WriteLine("writing to table");
Console.ReadKey();
using (OleDbCommand importCommand = new OleDbCommand(
    @"INSERT INTO CLIENT (Title,Fname,Sname) VALUES (?,?,?)",
    importConnection))
{
  importCommand.Parameters.AddWithValue("title","");
  importCommand.Parameters.AddWithValue("fname","");
  importCommand.Parameters.AddWithValue("sname","");

  // maybe in a loop here
  importCommand.Parameters["title"].Value = exportReader.GetValue(0);
  importCommand.Parameters["fname"].Value = exportReader.GetValue(1);
  importCommand.Parameters["sname"].Value = exportReader.GetValue(2);

  importCommand.ExecuteNonQuery();
  // ...
}

PS: You could directly feed the values on the Parameters.AddWithValue instead of creating a separate .Parameters["..."].Value = ... but then you would only be able to do that for a single insertion (not something related to VFP, it is the case for OleDb or Sql or whatever).

Upvotes: 1

Soner Gönül
Soner Gönül

Reputation: 98740

You don't need ExecuteReader for an insert statement. Just use ExecuteNonQuery.

In your case, if your columns are character typed, you need to use single quotes with them for example;

VALUES ('{0}', '{1}', '{2}')

Also use white space (not have to but as a good practice) before VALUES part.

"INSERT INTO CLIENT (Title,Fname,Sname)" + " VALUES (Mr,Joshua,Cameron-Mackintosh)",
                                       //  ^^^ here

But more important;

You should always use parameterized queries. Prepared statements automatically handle for escape characters for example and this kind of string concatenations are open for SQL Injection attacks.

Upvotes: 0

Related Questions