C# - what is wrong with this SQL?

Basq

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

[php]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();
}[/php]

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?

d@rk l0rd

basquille wrote:

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!

Try something like this:

[php]
string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mediaInfo.MDB";

OleDbConnection myConn = new OleDbConnection(strDSN);

try
{
myConn.Open();

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) + "')", myConn);

OleDbCommand myCmd = new OleDbCommand( strSQL, myConn );

myCmd.ExecuteNonQuery();

}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);

}

finally
{
myConn.Close();
myConn.Dispose();
}

[/php]

Hope this helps.

Basq

Cheers d@rk l0rd. That worked perfectly!

Have another problem now though - the variable contentStr i'm inserting actually contains an array of bytes converted to a string. In other words, i'm storing an image to the database.

But it seems like SQL doesn't like all these little symbols in the binary data i'm storing (in a Memo type field in Access)

Any suggestions on how to get round this?

Thanks again for your help... much appreciated!

Peace

basquille wrote:

Cheers d@rk l0rd. That worked perfectly!

Have another problem now though - the variable contentStr i'm inserting actually contains an array of bytes converted to a string. In other words, i'm storing an image to the database.

But it seems like SQL doesn't like all these little symbols in the binary data i'm storing (in a Memo type field in Access)

Any suggestions on how to get round this?

Thanks again for your help... much appreciated!

Taking a wild guess as i've never done what you're doing before.... try setting the datatype of the field to nvarchar?

voxpop

try using ntext datatyp

Basq

NText / NVar not available in Access, only SQL Server etc.. But i read this about the data type Memo in Access Help:

Memo: Lengthy text or combinations of text and numbers. Up to 65,535 characters. (If the Memo field is manipulated through DAO and only text and numbers [not binary data] will be stored in it, then the size of the Memo field is limited by the size of the database.)

Arse...! Any suggestions?

stevenmu

Access 2000 has an OLE Object datatype (not sure about other versions). I know absolutly nothing about it but it's the one I'd look into to store images anyway.


Alternatly, if it's possible you could consider switching to the free SQL Server 2005 express, that should cater for images and binary data.