C# / SQL Related - what's wrong with this Insert Into syntax?

  • Thread starter Thread starter brianbasquille
  • Start date Start date
B

brianbasquille

Hello all,

Strange little problem here... am just trying to insert some basic
information into an Access Database using OleDB.

I'm getting a "Syntax error in Insert Into statement" when it tries to
execute the SQL. The strange thing is if i take the exact SQL being
executed from the debugger and insert and execute it using the MS
Access query engine, it works fine!

What you need to know is regarding the C# Data Types:

Strings:
MovieTitle,MovieDirector,MovieActors,MoviePlot,fileLocation,contentStr
Ints: MovieYear,MovieRating,MovieRuntime

What you need to know is regarding the Access Data Types:

Text: Title, Director, fileLocation
Memo: Actors, Plot, picLocation (due to being possibly larger than 255
chars)
Number: Year, Runtime, Rating

string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=mediaInfo.MDB";
string strSQL = "INSERT INTO MovieInfo ([Title], [Year], [Director],
[Actors], [Plot], [Runtime], [Rating], [fileLocation], [picLocation]) "
+ "VALUES '" + CleanSQL(MovieTitle) + "',"
+ MovieYear + ",'"
+ CleanSQL(MovieDirector) + "','"
+ CleanSQL(MovieActors) + "','"
+ CleanSQL(MoviePlot) + "',"
+ MovieRuntime + ","
+ MovieRating + ",'"
+ CleanSQL(fileLocation) + "','"
+ CleanSQL(contentStr) + "')";

OleDbConnection myConn = new OleDbConnection(strDSN);
OleDbCommand myCmd = new OleDbCommand( strSQL, myConn );

try
{
myConn.Open();
myCmd.ExecuteNonQuery();
}

Oh, and the CleanSQL method is just replacing all instances of
apostrophe's in the parameters with double apostrophe's to prevent
confusion with the SQL.

Also the Access DB is stored in the Debug folder of my project so no
path is necessary to it.

Any suggestions as to where i'm going wrong?

Regards,

Brian
 
(e-mail address removed) wrote in
Hello all,

Strange little problem here... am just trying to insert some
basic information into an Access Database using OleDB.

I'm getting a "Syntax error in Insert Into statement" when it
tries to execute the SQL. The strange thing is if i take the
exact SQL being executed from the debugger and insert and
execute it using the MS Access query engine, it works fine!

What you need to know is regarding the C# Data Types:

Strings:
MovieTitle,MovieDirector,MovieActors,MoviePlot,fileLocation,conte
ntStr Ints: MovieYear,MovieRating,MovieRuntime

What you need to know is regarding the Access Data Types:

Text: Title, Director, fileLocation
Memo: Actors, Plot, picLocation (due to being possibly larger
than 255 chars)
Number: Year, Runtime, Rating

string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=mediaInfo.MDB";
string strSQL = "INSERT INTO MovieInfo ([Title], [Year],
[Director], [Actors], [Plot], [Runtime], [Rating],
[fileLocation], [picLocation]) " + "VALUES '" +
CleanSQL(MovieTitle) + "'," + MovieYear + ",'"
+ CleanSQL(MovieDirector) + "','"
+ CleanSQL(MovieActors) + "','"
+ CleanSQL(MoviePlot) + "',"
+ MovieRuntime + ","
+ MovieRating + ",'"
+ CleanSQL(fileLocation) + "','"
+ CleanSQL(contentStr) + "')";

OleDbConnection myConn = new OleDbConnection(strDSN);
OleDbCommand myCmd = new OleDbCommand( strSQL, myConn );

try
{
myConn.Open();
myCmd.ExecuteNonQuery();
}

Oh, and the CleanSQL method is just replacing all instances of
apostrophe's in the parameters with double apostrophe's to
prevent confusion with the SQL.

Also the Access DB is stored in the Debug folder of my project
so no path is necessary to it.

Any suggestions as to where i'm going wrong?
Click to expand...

Brian,

Nothing of a syntax nature immediately jumps out.

Using the Visual Studio debugger, examine the value of strSQL after
it's been assigned. Copy that value into a query window in Access
and execute it. Access might give you more info as to what's wrong
w/ the statement.

I would also suggest using parameters instead of dynamically
building a string. OleDbParameter does a better job of ensuring the
parameter value gets formatted and inserted correctly than
do homegrown methods like CleanSQL. Parameters also prevent most
kinds of SQL injection attacks.
 
I recommend rewriting this to use OleDbParameter's. It's a bit more
typing (code), but it becomes more robust and you don't have to recreate
the (admittedly small) wheel.

<aside>
I remember I was using the same Clean() function for MySQL for a while,
but then a strange problem cropped up: I was getting errors INSERTing
into MySQL. Turned out I was trying to insert \0, which Mysql doesn't like.

I switched to MySqlParameter's and then looked at their implementation
of Clean() --turned out I was missing a bunch of stuff and was doing it
slower than the implementation version.
</aside>


Scott
 
Back
Top