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

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,fil eLocation,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

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

Apologies.. should correct this:

"VALUES "

to

"VALUES ('"

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

brianbasquille@gmail.com wrote in
news:1140053885.815965.153530@g43g2000cwa.googlegr oups.com:
[color=blue]
> 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,fil eLocation,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?[/color]

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.

--
Hope this helps.

Chris.
-------------
C.R. Timmons Consulting, Inc.
http://www.crtimmonsinc.com/

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

Chris R. Timmons wrote:[color=blue][color=green]
>>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.[/color][/color]

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