473,327 Members | 2,103 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 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 6710
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Simom Thorpe | last post by:
Hi, I'm trying to insert a line into a MS access DB using ASP on IIS 5. This is the line: con.execute "INSERT INTO newProds(title,desc,catcode) VALUES ('Champagne Muff Scarf','','AC304B')"...
6
by: Oli | last post by:
Hi What's wrong with this? sql="insert into tblNumbers (Number) values ('" & i & "')" I get... Error Type: Microsoft JET Database Engine (0x80040E14) Syntax error in INSERT INTO statement.
2
by: Uri Lazar | last post by:
hi, im working on this for a long time. i'm using MSsql-server2000 i have a table that records users visits to rooms. the columns are room_id, user_id, visits. i want to write a query that can...
3
by: Matik | last post by:
Hello all, I belive, my problem is probably very easy to solve, but still, I cannot find solution: declare @i int declare @z int create table bubusilala (
2
by: Fons Roelandt | last post by:
Heelo, I have to Update all fields from a table with the values of a related table, i've tried some querys i found on the internet, but nothing seems to word, i even tried to lookup the value...
10
by: Protoman | last post by:
Could you tell me what's wrong with this program, it doesn't compile: #include <iostream> #include <cstdlib> using namespace std; class Everything { public: static Everything* Instance()
5
by: strawberry | last post by:
In the function below, I'd like to extend the scope of the $table variable such that, once assigned it would become available to other parts of the function. I thought 'global $table;' would solve...
0
by: Gawn | last post by:
Dear all, Greeding from Thailand. Need help for my news script. I am trying to display news which keywords match the current page keywords. I am using Dreamweaver 8 and PhpMyAdmin to manage MySQL....
5
by: spudpeel | last post by:
Hi, I have to insert 10 pieces of data (8 from text or combo boxes, and 2 from variables), into my table. I'm sure Im getting the quotes wrong somewhere, but whatever I try I just cant see anything...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.