INSERT command doesn't work in Access

  • Thread starter Thread starter MW
  • Start date Start date
M

MW

Hello,

I have a problem with calling INSERT command. It throws everytime a SQL
exception, even though the syntax is proper. At the begining I've tried
using OleDbCommand, and it worked for 2 tables, but it doesn't work for
third final one. These are really easy tables, so I'm really shocked it
doesn't work. Lately I've tried simple OleDbCommand, but it still doesn't
work. Did anyone bump into this problem earlier ?

Table has three columns:
Name : DataType
1. RowID : AutoNumber
2. ContactID : Text
3. Date : Date/Time

The last version of the code, that still generates error is:

OleDbCommand comm = new OleDbCommand("INSERT INTO DataTable(ContactID, Date)
VALUES(dfsfsdfdf, 3/3/2002)",

this.databaseConnection);

comm.ExecuteNonQuery();


I appreciete any help,
Marcin Waligora
 
and what type dfsfsdfdf is of? :) shouldn't you put it into ''? also, though
i didn't check, the way you pass date in is somewhat suspicious to me.

peter
 
Try

OleDbCommand comm = new OleDbCommand("INSERT INTO DataTable(ContactID, Date)
VALUES('dfsfsdfdf', #3/3/2002#)", this.databaseConnection);

HTH

Elton Wang
(e-mail address removed)
 
I think the problem is the date, you should escape it or better, use
Parameters. Also, it's probably an OleDbException that's being thrown, if
you're getting a SqlException, something else must be the culprit.
 
Hey,

This is the previous version of the code that also doesn't work. I get with
that OleDbException, that says "Syntax error in SQL statement". Any ideas?

this.databaseConnection.Open();


this.insertDataTableCommand.Connection = this.databaseConnection;

this.insertDataTableCommand.CommandText = "INSERT INTO DataTable(ContactID,
Date) VALUES (@ContactID, @Date)";



this.insertDataTableCommand.Parameters.Add("@ContactID", OleDbType.VarWChar,
50);

this.insertDataTableCommand.Parameters.Add("@Date", OleDbType.Date);

//now set all parameters properly

this.insertDataTableCommand.Parameters["@ContactID"].Value = "Test";

this.insertDataTableCommand.Parameters["@Date"].Value = DateTime.Now;

this.insertDataTableCommand.ExecuteNonQuery();


this.databaseConnection.Close();
 
It works !!!!
Thank you so much!!!!


Rogas69 said:
Date is reserved word. use [Date] or "Date" or better other name.
HTH
Peter
this.insertDataTableCommand.CommandText = "INSERT INTO
DataTable(ContactID, Date) VALUES (@ContactID, @Date)";
Click to expand...
Click to expand...
 
Yes, you are using Date as a column name and in Access for instance, it
doesn't like it b/c it's areserved word. Try using [Date] instead of
Date - or better, change it to a nonreserved word ;-) Let me know if that
doesns't fix it.

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
MW said:
Hey,

This is the previous version of the code that also doesn't work. I get
with that OleDbException, that says "Syntax error in SQL statement". Any
ideas?

this.databaseConnection.Open();


this.insertDataTableCommand.Connection = this.databaseConnection;

this.insertDataTableCommand.CommandText = "INSERT INTO
DataTable(ContactID, Date) VALUES (@ContactID, @Date)";



this.insertDataTableCommand.Parameters.Add("@ContactID",
OleDbType.VarWChar, 50);

this.insertDataTableCommand.Parameters.Add("@Date", OleDbType.Date);

//now set all parameters properly

this.insertDataTableCommand.Parameters["@ContactID"].Value = "Test";

this.insertDataTableCommand.Parameters["@Date"].Value = DateTime.Now;

this.insertDataTableCommand.ExecuteNonQuery();


this.databaseConnection.Close();




W.G. Ryan eMVP said:
I think the problem is the date, you should escape it or better, use
Parameters. Also, it's probably an OleDbException that's being thrown, if
you're getting a SqlException, something else must be the culprit.
Click to expand...
Click to expand...
 
I see Roger just answered it. Didn't mean to step on his toes by answering
it afterward ;-) but glad you got it fixed

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
MW said:
It works !!!!
Thank you so much!!!!


Rogas69 said:
Date is reserved word. use [Date] or "Date" or better other name.
HTH
Peter
this.insertDataTableCommand.CommandText = "INSERT INTO
DataTable(ContactID, Date) VALUES (@ContactID, @Date)";
Click to expand...
Click to expand...
Click to expand...
 
Rogas69 said:
Date is reserved word. use [Date] or "Date" or better other name.
Click to expand...

Its not recommended to use [Date]. I do not believe this is part of the SQL standard, but an MS
extension. "Date" however is part of the SQL standard and portable.
 
Yes that is why I adviced to use completely different identifier. I think
that there really is no need of using identifiers that are reserved words.

Peter
 
Rogas69 said:
Yes that is why I adviced to use completely different identifier. I
think that there really is no need of using identifiers that are
reserved words.
Click to expand...

In general its the best solution to rename it. However when supporting multiple DB's, often other
DB's use differnet reserved words. I use Date and others quite a bit, but I only use quoted syntax
which essentially allows me to be free with my names and not worry about if its a reserved word
or not.
 
In general its the best solution to rename it. However when supporting
multiple DB's, often other
DB's use differnet reserved words. I use Date and others quite a bit, but
I only use quoted syntax
which essentially allows me to be free with my names and not worry about
if its a reserved word
or not.
Click to expand...

I agree absolutely. I have however habit to use kind of hungarian notation
so Date becomes dtDate and this is not a reserved word in any database I
could imagine :)

Peter
 
Rogas69 said:
I agree absolutely. I have however habit to use kind of hungarian
notation so Date becomes dtDate and this is not a reserved word in any
database I could imagine :)
Click to expand...

I'll admit I've seen a lot of stuff in DB's but I've never seen hungarian in a DB. I'd recommend
against this if you have DB's that will be used by other systems. If they DB's are internal to your
system, its your personal preference I guess.

But then again - to me this goes back to where Im really going with Indy.Data. You use Hungarian - to
help you avoid type mismatches at runtime. Something thats not possible if you use a typed approach
like Indy.Data, because it checks all types during the build. :)
 
The reason I am using this is rather to allow quick assessment of meaning of
fields in complex query at design time not at run time. Why should I not use
it? It is one of best methods IMO to avoid mistakes. Date is rather trivial
but imagine that someone uses date in text format for some reason. If you
call the field 'MyDate' it is meaningless. If you call it strMyDate you know
that this is string - I am not using 'strict' hungarian notation, only some
hints that alleviate coding.

One of reasons Microsoft advices against hungarian notation now (yet 3-5
years ago it was completely different) is that tools can show you the type
of a member variable. Yes they can. But what if you view the code on a web
page, or in notepad? And if you are reading the code you not necessarily
want to mouse over each identifier to see its type. But, once again, it is a
matter of convention.

Peter
 
Rogas69 said:
The reason I am using this is rather to allow quick assessment of
meaning of fields in complex query at design time not at run time. Why
Click to expand...

Yes, I understand why you are using it. But its very non standard.

You are likely using it becuase your "tools" are not typesafe. ie DataReaders etc. But if you use
something like Indy.Data or LLBLGEN then your data access becomes typesafe, so type
mismatches are not possible. If this is the case, then you are using it to help fulfill a short
coming in your tools.
should I not use it? It is one of best methods IMO to avoid mistakes.
Click to expand...

Its better to used typed access and not just help to avoid, but eliminate instead.
One of reasons Microsoft advices against hungarian notation now (yet
3-5 years ago it was completely different) is that tools can show you
the type of a member variable. Yes they can. But what if you view the
code on a web page, or in notepad? And if you are reading the code you
Click to expand...

Why would I use notepad for any real development?
But, once again, it is a matter of convention.
Click to expand...

As I said, if you are the only one use a database its merely a personal preference. However if
others are using your database as well, you will receive a lot of negative feedback regarding this.
 
Rogas,

You know in my idea well enough that non English speakers use often the
fieldnames in their own language to get a good distinct between keywords and
own fields.

That is an advantage from non English speakers above those.

Therefore probalby you use something as
DateTime DataCzas;

Where the last nothing has to do with Chad.

:-))

Cor
 
Therefore probalby you use something as
DateTime DataCzas;
Click to expand...

:)
maybe I would use something similar though I'd rather use also a prefix dt.
I can see that your'e quite familiar with this language ;)

Piotrek
 
Back
Top