INSERT syntax error OleDB

  • Thread starter Thread starter KavvY
  • Start date Start date
K

KavvY

Hi

Can anyone tell me why I get a SQL syntax error with the following code?

string strInsert = "INSERT INTO dateEntry (entry, date) VALUES ('test3',
'17/08/2004')";

OleDbCommand cmd = new OleDbCommand(strInsert, oleDbConnection1);

try
{
oleDbConnection1.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
tbxOutput.Text = ex.Message;
}
finally
{
oleDbConnection1.Close();
}
 
KavvY said:
Can anyone tell me why I get a SQL syntax error with the following code?

string strInsert = "INSERT INTO dateEntry (entry, date) VALUES ('test3',
'17/08/2004')";

OleDbCommand cmd = new OleDbCommand(strInsert, oleDbConnection1);

try
{
oleDbConnection1.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
tbxOutput.Text = ex.Message;
}
finally
{
oleDbConnection1.Close();
}
Click to expand...

My guess is that the date parameter is in a dodgy format. Rather than
rely on getting it right in your SQL text, use parameters. See
OleDbParameter or OleDbCommand.Parameters for more information.
 
Hi,

First of all, what is the error message you are getting?
Can you execute the same query in the query analyser without problem?

the query seems to be ok though.


cheers,
 
KavvY said:
Hi

Can anyone tell me why I get a SQL syntax error with the following code?

string strInsert = "INSERT INTO dateEntry (entry, date) VALUES ('test3',
'17/08/2004')";

OleDbCommand cmd = new OleDbCommand(strInsert, oleDbConnection1);

try
{
oleDbConnection1.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
tbxOutput.Text = ex.Message;
}
finally
{
oleDbConnection1.Close();
}
Click to expand...

KavvY,

Does your exception or message look like the following?

The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated.

This is because the datetime value as a string you entered is in the form of
dd/mm/yyyy. SqlServer can be set up to accept those dates but it is more the
wiser to just use the format of mm/dd/yyyy.

Hope this helps :)

Mythran
 
If he had query analyzer, he'd be using SqlCommand and SqlConnection.

It's unlikely that he's using SQL Server.
 
If he had query analyzer, he'd be using SqlCommand and SqlConnection.

It's unlikely that he's using SQL Server.
Click to expand...

The error message is along the lines of "The Sql statement contains a
syntax error.". The exact error escapes me as I'm at home now. but its no
more infomative than that!.

I am accessing an access database using the OLEDB and both fields which I
am trying to write to (date & entry) are strings (or text in Access).

Thanks,
Rich.
 
KavvY,

Does your exception or message look like the following?

The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated.

This is because the datetime value as a string you entered is in the form of
dd/mm/yyyy. SqlServer can be set up to accept those dates but it is more the
wiser to just use the format of mm/dd/yyyy.

Hope this helps :)

Mythran
Click to expand...

Mythran,

Thanks for your reply, the fields I am writing to are both strings (see
other reply for more details). I have to agree though with your suggestion
about updating dates on an SQL server, with another project I found that I
had to write to SQL using US format, and SQL would then proceed to save it
as UK format. Very confusing!

Thanks,
Rich.
 
It's nice to have critics. ;-)

The error message is along the lines of "The Sql statement contains a
syntax error.". The exact error escapes me as I'm at home now. but its no
more infomative than that!.

I am accessing an access database using the OLEDB and both fields which I
am trying to write to (date & entry) are strings (or text in Access).

Thanks,
Rich.
Click to expand...
 
Doesn't access have a wacko version of SQL, or did they fix that?
Wouldn't it be something like...

INSERT INTO
-with the literal [ ] being there?

This is how the SQL designer in access works, if you don't select "Use
SQL Server compatible syntax" in the options->tools menu.
 
Doesn't access have a wacko version of SQL, or did they fix that?
Wouldn't it be something like...

INSERT INTO
-with the literal [ ] being there?

This is how the SQL designer in access works, if you don't select "Use
SQL Server compatible syntax" in the options->tools menu.
Click to expand...


Ah yes, good point!

Thanks, I'll check it out :)
 
This is why it's better to use a SQL Parameter. You can declare it as a
type of date, convert the date on your machine (where you can see the date
time settings), and set it to the parameter.

No more confusion.

SQL Server does care. If you code a date in a string, it needs to be in the
format "mm/dd/yyyy" unless you made some other provisions when setting it
up.

--- Nick
 
KavvY,
date is a reserved word so you should have it quoted ([date] or `date`)
in your text.

Ron Allen
 
Back
Top