471,319 Members | 1,740 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,319 software developers and data experts.

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

Feb 16 '06 #1
3 6558
Apologies.. should correct this:

"VALUES "

to

"VALUES ('"

Feb 16 '06 #2
br************@gmail.com wrote in
news:11**********************@g43g2000cwa.googlegr oups.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,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?


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/
Feb 16 '06 #3
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
Feb 16 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Oli | last post: by
2 posts views Thread by Uri Lazar | last post: by
10 posts views Thread by Protoman | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.