Date format confusion

[Eager_Beever]

I have an ASP.net 2.0 website with SQL Server as database and C# 2005 as the language. On my webpage I have a calender from which I choose a date and display only the date part in a textbox (readonly) in the format dd/mm/yyyy. But internally SQL Server uses m/d/y date format. I am using parameterised queries to insert and select records. But when I checked the database using the management tool and link supplied to me by the hosting service, I found that a few records have been saved in dd/mm/yyyy format while the rest have been saved the the default mm/dd/yyyy format. I suspect, it happened when I made some changes in the date format handling code and saved some records. But since the no. of records are not many, I am ready to delete all existing records and use the entry page again and add ALL records. But how should I ensure that even if I display the date in dd/mm/yyyy format on my webpage it gets saved in only one format (either m/d/y or d/m/y). The date format is very important to me, because most of the time the records will be fetched based on a specific date.

Is there any way, by which I can choose the date format to be d/m/y while saving and retrieving the records from the database??

Lalit Kumar Barik
India

[dickersonka]

Are you sure you set the column's data type to datetime? It sounds like you are using varchar

[Eager_Beever]

The RDate field's datatype is smalldatetime. My requirement is that a calender is displayed on the page along with a collection of times (displayed in a dropdown list and which contains values like 09:00 AM, 09:30 AM, etc )for the RTime field. The record contains RDate, DTime and other values. For the same RDate, there can be multiple records each having a different RTime. No two records can exist with the same combination of RDate & RTime. I am facing problems in implementing this simple requirements. When the user chooses a date from the calender, I am storing it in a DateTime variable as it is and also displaying it in a readonly textbox in the format "dd/MM/yyyy" for user's reference. While inserting the record I am checking against the DateTime variable and also using that value in the record. But I am facing problem while checking for unique record. Even if I change the Date by choosing a different date from the calender and select the same RTime value as an earlier choosen one, I am getting an error for duplicate entry. My code to check duplicates is as follows:

DateTime dtCurrentDate ;
CalendarRDate.SelectedDate = DateTime.Today;
dtCurrentDate = CalendarRDate.SelectedDate;


string strConnection = ConfigurationManager.ConnectionStrings["SuperConnString"].ConnectionString;
string strSQLQueryCheckDuplicates = @"Select Count(*) from Results where RDate = @RDate And RTime = @RTime";

        SqlConnection sqlConnCheckDuplicates = null;

        try
        {
            sqlConnCheckDuplicates = new SqlConnection(strConnection);
            sqlConnCheckDuplicates.Open();

            SqlCommand sqlCmdCheckDuplicates = new SqlCommand(strSQLQueryCheckDuplicates, sqlConnCheckDuplicates);
            sqlCmdCheckDuplicates.Parameters.Add(new SqlParameter("@RDate", dtCurrentDate));
            sqlCmdCheckDuplicates.Parameters.Add(new SqlParameter("@ResultTime", ddRTime.Text.Trim()));

            if (Convert.ToInt32(sqlCmdCheckDuplicates.ExecuteScalar()) > 0)
            {
                lblMessage.Text = "Results for choosen timeslot has been entered already";
                ddRTime.Focus();
                return;
            }
            else
                lblMessage.Text = string.Empty;
        }
        finally
        {
            sqlConnCheckDuplicates.Close();
        }

Where am I making the mistake??

Lalit Kumar barik
India

[dickersonka]

For starters lets start off with syntax:

Did you see this line? SqlParameter("@ResultTime", ddRTime.Text.Trim())); your parameter is named @RTime

[Eager_Beever]

For starters lets start off with syntax:

Did you see this line?
SqlParameter("@ResultTime", ddRTime.Text.Trim()));

your parameter is named @RTime

I acknowledge my mistake. But in the real life coding it was rectified already. I didn't notice it when I did some copy paste job.

[dickersonka]

Ok, just wanted to make sure we aren't chasing a ghost

Rather than just adding the parameters with a name and value, try to add them with a name and type

SqlParameter param1 = new SqlParameter("@RDate", SqlDbType.DateTime);
param1.Value = dtCurrentDate;
sqlCmdCheckDuplicates.Parameters.Add(param1);

and do the same for the other one

[Eager_Beever]

Ok, I will revise my code to implement this feature at ALL places.

Thank you for your suggestions.

Lalit Kumar Barik
India