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]]

KavvY <k@u.r> wrote:

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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[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,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"KavvY" <k@u.r> wrote in message
news:qv*******************@news-text.cableinet.net...

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

[Mythran]

"KavvY" <k@u.r> wrote in message
news:qv*******************@news-text.cableinet.net...

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.
Ignacio Machin ( .NET/ C# MVP ) wrote:

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]

On Fri, 06 Aug 2004 11:09:29 -0500, Mike Newton confounded his critics by
announcing:

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

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

Ignacio Machin ( .NET/ C# MVP ) wrote:

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,

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]

On Fri, 6 Aug 2004 08:31:51 -0700, Mythran confounded his critics by
announcing:

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. ;-)
Kavvy wrote:

On Fri, 06 Aug 2004 11:09:29 -0500, Mike Newton confounded his critics by
announcing:

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

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

Ignacio Machin ( .NET/ C# MVP ) wrote:

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,

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 [TABLE] -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 wrote:

On Fri, 06 Aug 2004 11:09:29 -0500, Mike Newton confounded his critics by
announcing:

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

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

Ignacio Machin ( .NET/ C# MVP ) wrote:

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,

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]

On Fri, 06 Aug 2004 14:15:27 -0500, Mike Newton confounded his critics by
announcing:

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

INSERT INTO [TABLE] -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

"Kavvy" <[e-mail]> wrote in message
news:9t*****************************@40tude.net...

On Fri, 6 Aug 2004 08:31:51 -0700, Mythran confounded his critics by
announcing:

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.

[Ron Allen]

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

Ron Allen
"KavvY" <k@u.r> wrote in message
news:qv*******************@news-text.cableinet.net...

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