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

[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,Movi eDirector,Movie Actors,MoviePlo t,fileLocation, contentStr
Ints: MovieYear,Movie Rating,MovieRun time

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=Micro soft.Jet.OLEDB. 4.0;Data
Source=mediaInf o.MDB";
string strSQL = "INSERT INTO MovieInfo ([Title], [Year], [Director],
[Actors], [Plot], [Runtime], [Rating], [fileLocation], [picLocation]) "
+ "VALUES '" + CleanSQL(MovieT itle) + "',"
+ MovieYear + ",'"
+ CleanSQL(MovieD irector) + "','"
+ CleanSQL(MovieA ctors) + "','"
+ CleanSQL(MovieP lot) + "',"
+ MovieRuntime + ","
+ MovieRating + ",'"
+ CleanSQL(fileLo cation) + "','"
+ CleanSQL(conten tStr) + "')";

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

try
{
myConn.Open();
myCmd.ExecuteNo nQuery();
}

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

[brianbasquille]

Apologies.. should correct this:

"VALUES "

to

"VALUES ('"

[Chris R. Timmons]

br************@ gmail.com wrote in
news:11******** **************@ g43g2000cwa.goo glegroups.com:

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,Movi eDirector,Movie Actors,MoviePlo t,fileLocation, conte
ntStr Ints: MovieYear,Movie Rating,MovieRun time

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=Micro soft.Jet.OLEDB. 4.0;Data
Source=mediaInf o.MDB";
string strSQL = "INSERT INTO MovieInfo ([Title], [Year],
[Director], [Actors], [Plot], [Runtime], [Rating],
[fileLocation], [picLocation]) " + "VALUES '" +
CleanSQL(MovieT itle) + "'," + MovieYear + ",'"
+ CleanSQL(MovieD irector) + "','"
+ CleanSQL(MovieA ctors) + "','"
+ CleanSQL(MovieP lot) + "',"
+ MovieRuntime + ","
+ MovieRating + ",'"
+ CleanSQL(fileLo cation) + "','"
+ CleanSQL(conten tStr) + "')";

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

try
{
myConn.Open();
myCmd.ExecuteNo nQuery();
}

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?

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/

[Scott C]

Chris R. Timmons wrote:

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.

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