DateTime problem

[Paolo]

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?

 

[Göran Andersson]

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.

 

[Paolo]

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.ReadLine());

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]

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.ReadLine());

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.

 

[Paolo]

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.ExecuteNonQuery();
}
catch (SqlException ex)
{
Exception error = new Exception("Activity with that date
does not exist", ex);
throw error;
}
}
}

 

[Mel Weaver]

First, the statement cmd.ExecuteNonQuery() 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.Add("@act_date", SqlDbType.DateTime).Value
= act_date;
cmd.ExecuteNonQuery();
}

}

 

[Paolo]

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?

 

[G.S.]

Look at the return result of ExecuteNonQuery
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

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

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.DateTime).Value = act_date;
object o = cmd.ExecuteScalar();
int numberOfRecords = Convert.ToInt32(o);

if (numberOfRecords == 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.DateTime).Value = act_date;
cmd.ExecuteNonQuery();
}
}

 

[Mel Weaver]

Disregard the other message,although 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.Add("@act_date", SqlDbType.DateTime).Value=
act_date;
int i = cmd.ExecuteNonQuery();
if (i == 0)
MessageBox.Show("Record does not exist");
}

 

[Paolo]

Mel: thank you. That worked fine.

Disregard the other message,although 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.Add("@act_date", SqlDbType.DateTime).Value=
act_date;
int i = cmd.ExecuteNonQuery();
if (i == 0)
MessageBox.Show("Record does not exist");
}

 

[Paolo]

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