Date Validation in ASP.net

Hello,
I posted this question a while ago.. but I still have problems with this validation.
I need to validate two dates to make sure the Date2 is bigger than Date1.
The parameters involved are Date1 from table X and the enter Date2
also the ID that has to be the same.

when I click on the button Save.. I keep getting the error "date referral cant be after date admission" even if I enter a bigger date....

Please I need some help..

thanks

I started with a try and catch

see my codes below:
protected void btnAdmit_Click(object sender, EventArgs e)
    {
        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString))
        {
            // create the check command TESTTTT
            SqlCommand cmd0 = new SqlCommand("select Datediff(r.DateOfReferral, @endDateA, r.ID) as datedifference from tbl_Referral r where r.ID = @ReferralID", myConnection);
            cmd0.Parameters.AddWithValue("@endDateA",(txtDateOfAdmission.Text.Trim()));
            cmd0.Parameters.AddWithValue("@ReferralID", (txtReferralID.Text.Trim()));
  
/*
inputs
*/
 
 
 SqlDataReader dr = null;
 
            try
            {
 
                myConnection.Open();
                dr = cmd0.ExecuteReader();
                lblMessage.Text = "";
                if (dr.Read())
                {
                    txtdatedifference.Text = dr["datedifference"].ToString();
                    if (Convert.ToInt32(dr["datedifference"]) < 0)
                    {
                        lblMessage.Text = "date referral cant be after date admission";
                    }
 
                    else
                    {
                        cmd.ExecuteNonQuery();
                        Response.Redirect("Dpatients.aspx", true);
                    }
                }
            }
            catch (Exception exc)
            {
                lblMessage.Text = "date referral cant be after date admission";
 
            }
 
 
            finally
            {
                myConnection.Close();
 
            }

Open in new window

RomacaliAsked:
Who is Participating?
 
spprivateConnect With a Mentor Commented:
select Datediff(day,r.DateOfReferral, @endDateA) as datedifference from tbl_Referral r where r.ID = @ReferralID", myConnection);
A
Again you are not passing correct parameters.You added r.id which is a wrong parameter in your datediff

Check the query below
select Datediff(day,r.DateOfReferral, @endDateA) as datedifference from tbl_Referral r where r.ID = @ReferralID", myConnection)

Open in new window

0
 
spprivateCommented:
First print the exception instead of putting your message there.
Did you debug to see what is the difference you are getting
0
 
RomacaliAuthor Commented:
I took off the message from the exception, I tried now and I didn't get any anything or save nothing ...
also I had a label to display the difference and it is blank
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
spprivateCommented:
There is some exception getting thrown.

lblMessage.Text = exc.Message;
and see what you are getting
0
 
spprivateCommented:
got it.You are passing date as the first parameter where as in your case you need to pass just day.

http://msdn.microsoft.com/en-us/library/ms189794.aspx
select Datediff(day, @endDateA, r.ID) as datedifference from tbl_Referral r where r.ID = @ReferralID"
 
I assume ID is a date(which is odd).
 
Here is the syntax
SELECT DATEDIFF(day, '2008-11-30','2009-01-01')

Open in new window

0
 
RomacaliAuthor Commented:
nope, I have to pass the full date as well the referral id,
once I have the date it can compare with the date1..

now I'm stuck because it is not saving here is the code I have:
I keep getting this error:
pointing Line 310:                 cmd.ExecuteNonQuery();

This cmd is where I shoudl save the data

for the Date I have:
  string strDateOfAdmission = txtDateOfAdmission.Text.Trim() + " " + txtHr.Text.Trim() + ":" + txtMin.Text.Trim() + ":00";
            cmd.Parameters.Add(new SqlParameter("@DateOfAdmission", strDateOfAdmission));











 Error converting data type nvarchar to datetime.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.


Exception Details: System.Data.SqlClient.SqlException: Error converting data type nvarchar to datetime.

Source Error:

Line 308:             {
Line 309:                // myConnection.Open();
Line 310:                 cmd.ExecuteNonQuery();
Line 311:                // myConnection.Close();
Line 312:                 Response.Redirect("Dpatients.aspx", true);


Source File: c:\WWWroot\nhfca\beta\recupdb-debug\DAdmission.aspx.cs    Line: 310
      DateTime DateOfAdmission = DateTime.Parse(txtDateOfAdmission.Text);
          
            DateTime DateOfReferral = DateTime.Now; // DateTime does not allow null
 
        try
        {
          myConnection.Open();
          
          using (SqlCommand command = new SqlCommand("spDate_Difference", myConnection))
             {
                command.CommandType = CommandType.StoredProcedure;
	
	              int ReferralID = int.Parse(Request.QueryString["r"]);
	
	              command.Parameters.AddWithValue("@ReferralID",ReferralID);
	              command.Parameters.Add("@DateOfReferral", SqlDbType.DateTime).Direction = ParameterDirection.Output;
	
	              command.ExecuteNonQuery();
	  
	      if (command.Parameters["@DateOfReferral"].Value == DBNull.Value)
	      throw new ApplicationException(String.Format("Date of Referral not found for {0}.", ReferralID)); // you should handle this issue instead of throwing an exception
	
	         DateOfReferral = (DateTime)command.Parameters["@DateOfReferral"].Value;
 
 
             if (DateOfAdmission < DateOfReferral)
 
                 lblMessage.Text = "date referral cant be after date admission";
             // txtDateOfAdmission.Focus = "Date of Admission can not be prior to Referral Date";
 
 
             else
             {
                // myConnection.Open();
                 cmd.ExecuteNonQuery();
                // myConnection.Close();
                 Response.Redirect("Dpatients.aspx", true);
             }
 
             
            
	       }
        }
        finally
        {
            myConnection.Close();
        }

Open in new window

0
 
RomacaliAuthor Commented:
Hello thanks,

but it is still giving me an error.. Actually I need to compare the full date..
here is the code again:
       // create the check command TESTTTT
        SqlCommand cmd0 = new SqlCommand("select Datediff(r.DateOfReferral, @endDateA) as datedifference from tbl_Referral r where r.ID = @ReferralID", myConnection);
        cmd0.Parameters.Add(new SqlParameter("@ReferralID", Request.QueryString["r"]));
 
 
        string strdateofAdmin = txtdateofend.Text.Trim();
        cmd0.Parameters.Add(new SqlParameter("@endDateA", strdateofAdmin));
        string strdateofbegin = txtstrdateofbegin.Text.Trim();
        cmd0.Parameters.Add(new SqlParameter("@beginDateR", strdateofbegin));
 
 
 
 
        SqlDataReader dr = null;
 
        try
        {
 
            myConnection.Open();
            dr = cmd0.ExecuteReader();
            lblMessage.Text = "";
            if (dr.Read())
            {
                txtdatedifference.Text = dr["datedifference"].ToString();
 
                if (Convert.ToInt32(dr["datedifference"]) < 0)
                {
                    lblMessage.Text = "date referral cant be after date admission";
                }
 
                else
                {
                    cmd.ExecuteNonQuery();
                    Response.Redirect("Dpatients.aspx", true);
                }
            }
        }
        catch (Exception exc)
        {
            //lblMessage.Text = "date referral cant be after date admission";
 
        }
 
 
        finally
        {
            myConnection.Close();
 
        }

Open in new window

0
 
RomacaliAuthor Commented:
also when I click save I got this message:

The datediff function requires 3 arguments.
what does it mean? I thought I was sending the correct info
0
 
RomacaliAuthor Commented:
I added the day in the select

and now I 'm getting this message:
There is already an open DataReader associated with this Command which must be closed first.

how can I close the reader and where? any idea
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.