INSERT syntax error OleDB

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

 

[Jon Skeet [C# MVP]]

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();
}

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.

 

[Ignacio Machin \( .NET/ C# MVP \)]

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,

 

[Mythran]

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,

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

 

[Mike Newton]

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

It's unlikely that he's using SQL Server.

 

[Kavvy]

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

It's unlikely that he's using SQL Server.

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]

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

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.

 

[Mike Newton]

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.

 

[Mike Newton]

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.

 

[Kavvy]

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.

Ah yes, good point!

Thanks, I'll check it out

 

[Nick Malik]

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

 

[Ron Allen]

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

Ron Allen