DateTime problem

[=?Utf-8?B?UGFvbG8=?=]

I have a SQL database table with rows in which the primary key is a DateTime
type (although I don't use the Time part). Have added numerous rows and now
want to delete one so I enter the date (dd/mm/yyyy) for a row I know is in
the database but my app tells me that no row exists for that date.

Am I missing something in relation to this data type?

[=?UTF-8?B?R8O2cmFuIEFuZGVyc3Nvbg==?=]

Re: DateTime problem

Paolo wrote:

I have a SQL database table with rows in which the primary key is a DateTime
type (although I don't use the Time part). Have added numerous rows and now
want to delete one so I enter the date (dd/mm/yyyy) for a row I know is in
the database but my app tells me that no row exists for that date.
>
Am I missing something in relation to this data type?

The string that you entered is probably not interpreted the way that you
think, i.e. it's parsed as mm/dd/yyyy or yyyy/mm/dd instead of dd/mm/yyyy.

Depending on whether the string is parsed in your application or by the
database, it can use completely different date formats. You should parse
the string in your application so that you can specify what format you
want to use.

--
Göran Andersson
_____

http://www.guffa.com

[=?Utf-8?B?UGFvbG8=?=]

Re: DateTime problem

Goran: I need a bit of help here. I've been 'reading' a date in string format
(dd/mm/yyyy) and converting to DateTime thus:

act_Date = DateTime.Parse( Console.ReadLin e());

This value has been entered into a SQL Server database column which has a
smalldatetime type.

When I display the contents of the database, this column is converted
ToString() and displays in dd/mm/yyyy format eg. 23/09/2008. So I assumed
there was no conflict between input dates and stored dates, hence my
puzzlement when I tried t delete a row.

If you can add any further enlightenment I'd appreciate it.


"Göran Andersson" wrote:

Paolo wrote:

I have a SQL database table with rows in which the primary key is a DateTime
type (although I don't use the Time part). Have added numerous rows and now
want to delete one so I enter the date (dd/mm/yyyy) for a row I know is in
the database but my app tells me that no row exists for that date.

Am I missing something in relation to this data type?

>
The string that you entered is probably not interpreted the way that you
think, i.e. it's parsed as mm/dd/yyyy or yyyy/mm/dd instead of dd/mm/yyyy.
>
Depending on whether the string is parsed in your application or by the
database, it can use completely different date formats. You should parse
the string in your application so that you can specify what format you
want to use.
>
--
Göran Andersson
_____

http://www.guffa.com

>

[=?UTF-8?B?R8O2cmFuIEFuZGVyc3Nvbg==?=]

Re: DateTime problem

Paolo wrote:

Goran: I need a bit of help here. I've been 'reading' a date in string format
(dd/mm/yyyy) and converting to DateTime thus:
>
act_Date = DateTime.Parse( Console.ReadLin e());
>
This value has been entered into a SQL Server database column which has a
smalldatetime type.
>
When I display the contents of the database, this column is converted
ToString() and displays in dd/mm/yyyy format eg. 23/09/2008. So I assumed
there was no conflict between input dates and stored dates, hence my
puzzlement when I tried t delete a row.
>
If you can add any further enlightenment I'd appreciate it.
>

What method are you using to delete the row?

When you have reading and writing the values, you have done the
conversion in your code. If the method to delete the row sends the date
as a string to the database, the database has done the conversion, and
the database may use a different culture setting for the conversion.

--
Göran Andersson
_____

http://www.guffa.com

[=?Utf-8?B?UGFvbG8=?=]

Re: DateTime problem

Goran: act_date (column name Activity-Date) is passed to the following
method, but is throwing the error:

public void deleteActivity( DateTime act_date)
{
string sql = string.Format(" Delete from Activities where
Activity_Date = '{0}'", act_date);
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
try
{
cmd.ExecuteNonQ uery();
}
catch (SqlException ex)
{
Exception error = new Exception("Acti vity with that date
does not exist", ex);
throw error;
}
}
}

"Göran Andersson" wrote:

Paolo wrote:

Goran: I need a bit of help here. I've been 'reading' a date in string format
(dd/mm/yyyy) and converting to DateTime thus:

act_Date = DateTime.Parse( Console.ReadLin e());

This value has been entered into a SQL Server database column which has a
smalldatetime type.

When I display the contents of the database, this column is converted
ToString() and displays in dd/mm/yyyy format eg. 23/09/2008. So I assumed
there was no conflict between input dates and stored dates, hence my
puzzlement when I tried t delete a row.

If you can add any further enlightenment I'd appreciate it.

>
What method are you using to delete the row?
>
When you have reading and writing the values, you have done the
conversion in your code. If the method to delete the row sends the date
as a string to the database, the database has done the conversion, and
the database may use a different culture setting for the conversion.
>
--
Göran Andersson
_____

http://www.guffa.com

>

[Mel Weaver]

Re: DateTime problem

First, the statement cmd.ExecuteNonQ uery() will not throw an exception if it
doesn't find a record to delete.
Second use parameters and then you don't have to worry about the datetime
format

public void DeleteActivity( DateTime act_date)
{
using (SqlCommand cmd = new SqlCommand()
{
cmd.Connection = this.sqlCn;
cmd.CommandText = "Delete from Activities where
Activity_Date = @act_date";
cmd.Paraeters.A dd("@act_date" , SqlDbType.DateT ime).Value
= act_date;
cmd.ExecuteNonQ uery();
}

}



"Paolo" <Paolo@discussi ons.microsoft.c omwrote in message
news:13CBADAE-D885-4139-AC48-C9BCCDF5BD58@mi crosoft.com...

Goran: act_date (column name Activity-Date) is passed to the following
method, but is throwing the error:
>
public void deleteActivity( DateTime act_date)
{
string sql = string.Format(" Delete from Activities where
Activity_Date = '{0}'", act_date);
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
try
{
cmd.ExecuteNonQ uery();
}
catch (SqlException ex)
{
Exception error = new Exception("Acti vity with that
date
does not exist", ex);
throw error;
}
}
}
>
"Göran Andersson" wrote:
>

>Paolo wrote:

Goran: I need a bit of help here. I've been 'reading' a date in string
format
(dd/mm/yyyy) and converting to DateTime thus:
>
act_Date = DateTime.Parse( Console.ReadLin e());
>
This value has been entered into a SQL Server database column which has
a
smalldatetime type.
>
When I display the contents of the database, this column is converted
ToString() and displays in dd/mm/yyyy format eg. 23/09/2008. So I
assumed
there was no conflict between input dates and stored dates, hence my
puzzlement when I tried t delete a row.
>
If you can add any further enlightenment I'd appreciate it.
>

>>
>What method are you using to delete the row?
>>
>When you have reading and writing the values, you have done the
>conversion in your code. If the method to delete the row sends the date
>as a string to the database, the database has done the conversion, and
>the database may use a different culture setting for the conversion.
>>
>--
>Göran Andersson
>_____
>http://www.guffa.com
>>

[=?Utf-8?B?UGFvbG8=?=]

Re: DateTime problem

Mel: thank you. At least I can now delete records following your example. Is
there any way I can provide a 'Record does not exist' message when a
non-existent date is found?

"Mel Weaver" wrote:

First, the statement cmd.ExecuteNonQ uery() will not throw an exception if it
doesn't find a record to delete.
Second use parameters and then you don't have to worry about the datetime
format
>
public void DeleteActivity( DateTime act_date)
{
using (SqlCommand cmd = new SqlCommand()
{
cmd.Connection = this.sqlCn;
cmd.CommandText = "Delete from Activities where
Activity_Date = @act_date";
cmd.Paraeters.A dd("@act_date" , SqlDbType.DateT ime).Value
= act_date;
cmd.ExecuteNonQ uery();
}
>
}
>
>
>
"Paolo" <Paolo@discussi ons.microsoft.c omwrote in message
news:13CBADAE-D885-4139-AC48-C9BCCDF5BD58@mi crosoft.com...

Goran: act_date (column name Activity-Date) is passed to the following
method, but is throwing the error:

public void deleteActivity( DateTime act_date)
{
string sql = string.Format(" Delete from Activities where
Activity_Date = '{0}'", act_date);
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
try
{
cmd.ExecuteNonQ uery();
}
catch (SqlException ex)
{
Exception error = new Exception("Acti vity with that
date
does not exist", ex);
throw error;
}
}
}

"Göran Andersson" wrote:

Paolo wrote:
Goran: I need a bit of help here. I've been 'reading' a date in string
format
(dd/mm/yyyy) and converting to DateTime thus:

act_Date = DateTime.Parse( Console.ReadLin e());

This value has been entered into a SQL Server database column which has
a
smalldatetime type.

When I display the contents of the database, this column is converted
ToString() and displays in dd/mm/yyyy format eg. 23/09/2008. So I
assumed
there was no conflict between input dates and stored dates, hence my
puzzlement when I tried t delete a row.

If you can add any further enlightenment I'd appreciate it.

>
What method are you using to delete the row?
>
When you have reading and writing the values, you have done the
conversion in your code. If the method to delete the row sends the date
as a string to the database, the database has done the conversion, and
the database may use a different culture setting for the conversion.
>
--
Göran Andersson
_____

http://www.guffa.com

>

>
>
>

[G.S.]

Re: DateTime problem

Look at the return result of ExecuteNonQuery

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

On Sep 23, 6:09 pm, Paolo <Pa...@discussi ons.microsoft.c omwrote:

...
string sql = string.Format(" Delete from Activities where Activity_Date = '{0}'", act_date);
- Show quoted text -

Off-topic - while the code you posted may be a stripped version for
illustration purposes, I'll say it, just in case it isn't, that if you
have two activities on the same day you can't delete just one of them
- you need to include your PK in the WHERE clause of the DELETE
statement.

[Mel Weaver]

Re: DateTime problem

Try this:

public void DeleteActivity( DateTime act_date)
{
using (SqlCommand cmd = new SqlCommand()
{
cmd.Connection = this.sqlCn;

cmd.CommandText = "Select Count(*) from Activities where
Activity_Date = @act_date";
cmd.Parameters. .Add("@act_date ",
SqlDbType.DateT ime).Value = act_date;
object o = cmd.ExecuteScal ar();
int numberOfRecords = Convert.ToInt32 (o);

if (numberOfRecord s == 0)
{
MessageBox.Show ("'Record does not exist'");
return;
}

cmd.Parameters. Clear();
cmd.CommandText = "Delete from Activities where
Activity_Date = @act_date";
cmd.Parameters. Add("@act_date" ,
SqlDbType.DateT ime).Value = act_date;
cmd.ExecuteNonQ uery();
}
}



"Paolo" <Paolo@discussi ons.microsoft.c omwrote in message
news:DC997D4F-9A63-4A0A-9320-83545962B94A@mi crosoft.com...

Mel: thank you. At least I can now delete records following your example.
Is
there any way I can provide a 'Record does not exist' message when a
non-existent date is found?
>
"Mel Weaver" wrote:
>

>First, the statement cmd.ExecuteNonQ uery() will not throw an exception if
>it
>doesn't find a record to delete.
>Second use parameters and then you don't have to worry about the datetime
>format
>>
>public void DeleteActivity( DateTime act_date)
>{
> using (SqlCommand cmd = new SqlCommand()
> {
> cmd.Connection = this.sqlCn;
> cmd.CommandText = "Delete from Activities where
>Activity_Dat e = @act_date";
> cmd.Paraeters.A dd("@act_date" ,
>SqlDbType.Date Time).Value
>= act_date;
> cmd.ExecuteNonQ uery();
> }
>>
>}
>>
>>
>>
>"Paolo" <Paolo@discussi ons.microsoft.c omwrote in message
>news:13CBADA E-D885-4139-AC48-C9BCCDF5BD58@mi crosoft.com...

Goran: act_date (column name Activity-Date) is passed to the following
method, but is throwing the error:
>
public void deleteActivity( DateTime act_date)
{
string sql = string.Format(" Delete from Activities where
Activity_Date = '{0}'", act_date);
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
try
{
cmd.ExecuteNonQ uery();
}
catch (SqlException ex)
{
Exception error = new Exception("Acti vity with that
date
does not exist", ex);
throw error;
}
}
}
>
"Göran Andersson" wrote:
>
>Paolo wrote:
Goran: I need a bit of help here. I've been 'reading' a date in
string
format
(dd/mm/yyyy) and converting to DateTime thus:
>
act_Date = DateTime.Parse( Console.ReadLin e());
>
This value has been entered into a SQL Server database column which
has
a
smalldatetime type.
>
When I display the contents of the database, this column is
converted
ToString() and displays in dd/mm/yyyy format eg. 23/09/2008. So I
assumed
there was no conflict between input dates and stored dates, hence my
puzzlement when I tried t delete a row.
>
If you can add any further enlightenment I'd appreciate it.
>
>>
>What method are you using to delete the row?
>>
>When you have reading and writing the values, you have done the
>conversion in your code. If the method to delete the row sends the
>date
>as a string to the database, the database has done the conversion, and
>the database may use a different culture setting for the conversion.
>>
>--
>Göran Andersson
>_____
>http://www.guffa.com
>>

>>
>>
>>

[Mel Weaver]

Re: DateTime problem

Disregard the other message,althoug h it will work, this is more efficient

public void DeleteActivity( DateTime act_date)
{
using (SqlCommand cmd = new SqlCommand()
{
cmd.Connection = this.sqlCn;
cmd.CommandText = "Delete from Activities where
Activity_Date = @act_date";
cmd.Paraeters.A dd("@act_date" , SqlDbType.DateT ime).Value=
act_date;
int i = cmd.ExecuteNonQ uery();
if (i == 0)
MessageBox.Show ("Record does not exist");
}


"Mel Weaver" <MelRemoveSpam@ Insdirect.comwr ote in message
news:u3phppyHJH A.456@TK2MSFTNG P06.phx.gbl...

Try this:
>
public void DeleteActivity( DateTime act_date)
{
using (SqlCommand cmd = new SqlCommand()
{
cmd.Connection = this.sqlCn;
>
cmd.CommandText = "Select Count(*) from Activities where
Activity_Date = @act_date";
cmd.Parameters. .Add("@act_date ",
SqlDbType.DateT ime).Value = act_date;
object o = cmd.ExecuteScal ar();
int numberOfRecords = Convert.ToInt32 (o);
>
if (numberOfRecord s == 0)
{
MessageBox.Show ("'Record does not exist'");
return;
}
>
cmd.Parameters. Clear();
cmd.CommandText = "Delete from Activities where
Activity_Date = @act_date";
cmd.Parameters. Add("@act_date" ,
SqlDbType.DateT ime).Value = act_date;
cmd.ExecuteNonQ uery();
}
}
>
>
>
"Paolo" <Paolo@discussi ons.microsoft.c omwrote in message
news:DC997D4F-9A63-4A0A-9320-83545962B94A@mi crosoft.com...

>Mel: thank you. At least I can now delete records following your example.
>Is
>there any way I can provide a 'Record does not exist' message when a
>non-existent date is found?
>>
>"Mel Weaver" wrote:
>>

>>First, the statement cmd.ExecuteNonQ uery() will not throw an exception
>>if it
>>doesn't find a record to delete.
>>Second use parameters and then you don't have to worry about the
>>datetime
>>format
>>>
>>public void DeleteActivity( DateTime act_date)
>>{
>> using (SqlCommand cmd = new SqlCommand()
>> {
>> cmd.Connection = this.sqlCn;
>> cmd.CommandText = "Delete from Activities where
>>Activity_Da te = @act_date";
>> cmd.Paraeters.A dd("@act_date" ,
>>SqlDbType.Dat eTime).Value
>>= act_date;
>> cmd.ExecuteNonQ uery();
>> }
>>>
>>}
>>>
>>>
>>>
>>"Paolo" <Paolo@discussi ons.microsoft.c omwrote in message
>>news:13CBAD AE-D885-4139-AC48-C9BCCDF5BD58@mi crosoft.com...
>Goran: act_date (column name Activity-Date) is passed to the following
>method, but is throwing the error:
>>
>public void deleteActivity( DateTime act_date)
> {
> string sql = string.Format(" Delete from Activities where
>Activity_Dat e = '{0}'", act_date);
> using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
> {
> try
> {
> cmd.ExecuteNonQ uery();
> }
> catch (SqlException ex)
> {
> Exception error = new Exception("Acti vity with that
>date
>does not exist", ex);
> throw error;
> }
> }
> }
>>
>"Göran Andersson" wrote:
>>
>>Paolo wrote:
>Goran: I need a bit of help here. I've been 'reading' a date in
>string
>format
>(dd/mm/yyyy) and converting to DateTime thus:
>>
>act_Date = DateTime.Parse( Console.ReadLin e());
>>
>This value has been entered into a SQL Server database column which
>has
>a
>smalldatetim e type.
>>
>When I display the contents of the database, this column is
>converted
>ToString() and displays in dd/mm/yyyy format eg. 23/09/2008. So I
>assumed
>there was no conflict between input dates and stored dates, hence
>my
>puzzlement when I tried t delete a row.
>>
>If you can add any further enlightenment I'd appreciate it.
>>
>>>
>>What method are you using to delete the row?
>>>
>>When you have reading and writing the values, you have done the
>>conversion in your code. If the method to delete the row sends the
>>date
>>as a string to the database, the database has done the conversion,
>>and
>>the database may use a different culture setting for the conversion.
>>>
>>--
>>Göran Andersson
>>_____
>>http://www.guffa.com
>>>
>>>
>>>
>>>

>
>

[=?Utf-8?B?UGFvbG8=?=]

Re: DateTime problem

Mel: thank you. That worked fine.

"Mel Weaver" wrote:

Disregard the other message,althoug h it will work, this is more efficient
>
public void DeleteActivity( DateTime act_date)
{
using (SqlCommand cmd = new SqlCommand()
{
cmd.Connection = this.sqlCn;
cmd.CommandText = "Delete from Activities where
Activity_Date = @act_date";
cmd.Paraeters.A dd("@act_date" , SqlDbType.DateT ime).Value=
act_date;
int i = cmd.ExecuteNonQ uery();
if (i == 0)
MessageBox.Show ("Record does not exist");
}
>
>
"Mel Weaver" <MelRemoveSpam@ Insdirect.comwr ote in message
news:u3phppyHJH A.456@TK2MSFTNG P06.phx.gbl...

Try this:

public void DeleteActivity( DateTime act_date)
{
using (SqlCommand cmd = new SqlCommand()
{
cmd.Connection = this.sqlCn;

cmd.CommandText = "Select Count(*) from Activities where
Activity_Date = @act_date";
cmd.Parameters. .Add("@act_date ",
SqlDbType.DateT ime).Value = act_date;
object o = cmd.ExecuteScal ar();
int numberOfRecords = Convert.ToInt32 (o);

if (numberOfRecord s == 0)
{
MessageBox.Show ("'Record does not exist'");
return;
}

cmd.Parameters. Clear();
cmd.CommandText = "Delete from Activities where
Activity_Date = @act_date";
cmd.Parameters. Add("@act_date" ,
SqlDbType.DateT ime).Value = act_date;
cmd.ExecuteNonQ uery();
}
}



"Paolo" <Paolo@discussi ons.microsoft.c omwrote in message
news:DC997D4F-9A63-4A0A-9320-83545962B94A@mi crosoft.com...

Mel: thank you. At least I can now delete records following your example.
Is
there any way I can provide a 'Record does not exist' message when a
non-existent date is found?
>
"Mel Weaver" wrote:
>
>First, the statement cmd.ExecuteNonQ uery() will not throw an exception
>if it
>doesn't find a record to delete.
>Second use parameters and then you don't have to worry about the
>datetime
>format
>>
>public void DeleteActivity( DateTime act_date)
>{
> using (SqlCommand cmd = new SqlCommand()
> {
> cmd.Connection = this.sqlCn;
> cmd.CommandText = "Delete from Activities where
>Activity_Dat e = @act_date";
> cmd.Paraeters.A dd("@act_date" ,
>SqlDbType.Date Time).Value
>= act_date;
> cmd.ExecuteNonQ uery();
> }
>>
>}
>>
>>
>>
>"Paolo" <Paolo@discussi ons.microsoft.c omwrote in message
>news:13CBADA E-D885-4139-AC48-C9BCCDF5BD58@mi crosoft.com...
Goran: act_date (column name Activity-Date) is passed to the following
method, but is throwing the error:
>
public void deleteActivity( DateTime act_date)
{
string sql = string.Format(" Delete from Activities where
Activity_Date = '{0}'", act_date);
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
try
{
cmd.ExecuteNonQ uery();
}
catch (SqlException ex)
{
Exception error = new Exception("Acti vity with that
date
does not exist", ex);
throw error;
}
}
}
>
"Göran Andersson" wrote:
>
>Paolo wrote:
Goran: I need a bit of help here. I've been 'reading' a date in
string
format
(dd/mm/yyyy) and converting to DateTime thus:
>
act_Date = DateTime.Parse( Console.ReadLin e());
>
This value has been entered into a SQL Server database column which
has
a
smalldatetime type.
>
When I display the contents of the database, this column is
converted
ToString() and displays in dd/mm/yyyy format eg. 23/09/2008. So I
assumed
there was no conflict between input dates and stored dates, hence
my
puzzlement when I tried t delete a row.
>
If you can add any further enlightenment I'd appreciate it.
>
>>
>What method are you using to delete the row?
>>
>When you have reading and writing the values, you have done the
>conversion in your code. If the method to delete the row sends the
>date
>as a string to the database, the database has done the conversion,
>and
>the database may use a different culture setting for the conversion.
>>
>--
>Göran Andersson
>_____
>http://www.guffa.com
>>
>>
>>
>>

>
>
>

[=?Utf-8?B?UGFvbG8=?=]

Re: DateTime problem

GS : thanks. I'm learning C#/SQL Server so the application is pretty simple.
I'm trying to avoid too many complications!

"G.S." wrote:

Look at the return result of ExecuteNonQuery

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

>
On Sep 23, 6:09 pm, Paolo <Pa...@discussi ons.microsoft.c omwrote:

...
string sql = string.Format(" Delete from Activities where Activity_Date = '{0}'", act_date);
- Show quoted text -

>
Off-topic - while the code you posted may be a stripped version for
illustration purposes, I'll say it, just in case it isn't, that if you
have two activities on the same day you can't delete just one of them
- you need to include your PK in the WHERE clause of the DELETE
statement.
>