Re: C# / SQL Related - what's wrong with this Insert Into syntax?
- From: "Chris R. Timmons" <crtimmons@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 15 Feb 2006 19:09:47 -0800
brianbasquille@xxxxxxxxx wrote in
news:1140053885.815965.153530@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:
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?
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/
.
- Follow-Ups:
- References:
- C# / SQL Related - what's wrong with this Insert Into syntax?
- From: brianbasquille
- C# / SQL Related - what's wrong with this Insert Into syntax?
- Prev by Date: RE: TabPage text disapearing
- Next by Date: Encoding string as double.
- Previous by thread: Re: C# / SQL Related - what's wrong with this Insert Into syntax?
- Next by thread: Re: C# / SQL Related - what's wrong with this Insert Into syntax?
- Index(es):
Relevant Pages
|
