Hello Guys and Gals,

 I need a little help, obviously.  I been tring to solve this problem for more than three weeks and I'm having no success.  I'm under a little presure on my job at this point.  So I have to turn to the experts in this forum.

 I'm building an appointment application.  I'm struggling with using a "CustomValidator" event proceedure to validate the value from a textbox (dateTextBox) that's entered by the end-user, via "Formview", against the values in the database.

 Example:

The end-user selects a date from the calendar and the value is passed to the "dateTextBox" I would like the "CustomValidator" event to check and see if the date exist in the database.  If date exist return true/false.  The problem I'm having is with the event proceedure args.IsValid. The return on any date selected comes back with date exist.  If you select a date for next year it will return that the date exist, when in actuality it doesn't.  I have attempted various potential solutions such as: inline coding to do "Sql Select blah blah... If Exist...blah blah",  Sql Count(blah blah) AS (blah blah), Create Sql StoreProc with If Exist and/or Cast blah blah.  All didn't work or I just did it wrong.

 Please See Codes Below.  Note: SqlDataSource Codes and Formview Templated Codes are left out for simplicity.

ASPX.CODE

<asp:FormView ID="FormView1" runat="server" DataKeyNames="ID" DataSourceID="SqlDataSource1" DefaultMode="Insert" Width="715px" onpageindexchanging="FormView1_PageIndexChanging">

 <asp:TextBox ID="dateTextBox" runat="server" Text='<%# Bind("ApptDate") %>'
        Width="141px"></asp:TextBox>
        <br />
        <asp:CustomValidator ID="DateCustomValidator" runat="server"
                    ErrorMessage="This date is not available" ControlToValidate="dateTextBox"
                    OnServerValidate="DateCustomValidator_ServerValidate" Display="Dynamic"></asp:CustomValidator>

 <div id="datePicker">
          <asp:Calendar ID="calEventDate" runat="server"
          OnSelectionChanged="calEventDate_SelectionChanged"
                      
                     ShowTitle="true" DayNameFormat="FirstTwoLetters"
                     SelectionMode="Day" BackColor="#ffffff"
                     FirstDayOfWeek="Sunday" BorderColor="#000000"
                     ForeColor="#00000" Height="80" Width="220">
                     <TitleStyle backcolor="#000080" forecolor="#ffffff" />
        <NextPrevStyle backcolor="#000080" forecolor="#ffffff" />
        <OtherMonthDayStyle forecolor="#c0c0c0" />
</asp:Calendar>
          </div>

<asp:Button ID="submitBtn" runat="server" Text="Submit"
                 ToolTip="Submit ID Form" CommandName="Insert" CausesValidation="True" Height="26px" />

 </asp:FormView>

VB SCRIPTING CODE

  Sub calEventDate_SelectionChanged(ByVal sender As Object, ByVal e As EventArgs)
        Dim dateTextBox As TextBox
        Dim calEventDate As Calendar
        dateTextBox = CType(FormView1.FindControl("dateTextBox"), TextBox)
        calEventDate = FormView1.FindControl("calEventDate")

        dateTextBox.Text = calEventDate.SelectedDate.ToString("d")
       
    End Sub

  Protected Sub DateCustomValidator_ServerValidate(ByVal source As Object, ByVal args As System.Web.UI.WebControls.ServerValidateEventArgs)

  Dim DateCustomValidator As CustomValidator
        DateCustomValidator = CType(FormView1.FindControl("DateCustomValidator"), CustomValidator)
 Dim SqlDataSource3 As SqlDataSource
        SqlDataSource3 = CType(FormView1.FindControl("SqlDataSource3"), SqlDataSource)
 Dim dateTextBox As TextBox
        dateTextBox = CType(FormView1.FindControl("dateTextBox"), TextBox)

 Dim sql As String
    Dim conn As New SqlConnection
        conn.ConnectionString = "Data Source=HQSSWEB1;Initial Catalog=HSC_Mil_ID_Appointment;Integrated Security=true"
        sql = "select ApptDate from Appointments"
        conn.Open()
        Dim ds As New DataSet
        Dim da As SqlDataAdapter
        
        da = New SqlDataAdapter(sql, conn)

da.Fill(ds, "ApptDate")

 Dim dt As DataTable
        dt = ds.Tables("ApptDate")

 conn.Close()

 args.IsValid = False

 Dim dr As DataRow

 For Each dr In ds.Tables("ApptDate").Rows

 If dr.ToString = args.Value Then
                args.IsValid = True
                Exit For

 End If
        Next

End Sub

  ------------------------------------------------------------------------------------------------

 Thank you for your help.  I greatly appreciate it.  As my .net learning knowledge reaches a higher level I will pay it forward.

NetBlazer

 Hi there

 If dr.ToString = args.Value Then
                args.IsValid = True
                Exit For

 End If

dr is a DataRow. It's ToString method will return something like 'System.Data.DataRow', which won't be equal to args.Value.

You need to extract the appropriate column value from the DataRow. For example, something like dr("ApptDate").ToString() should do it.

I'm a C# programmer, not a VB programmer, so my syntax might not be quite right, but it'll be something like that.

Cheers

David

 Hi David,

 Thank you very much, but...

 C# Code

 {
    args.IsValid = false;
    
    foreach (var dr in ds.Tables("ApptDate").Rows) {
        
        if (dr("ApptDate").ToString() == args.Value) {
            args.IsValid = true;
            break;
            
            if (true) {
                
                return;
            }
        }
        
    }
}

VB.NET CODE

args.IsValid = False

For Each dr In ds.Tables("ApptDate").Rows
           
            If dr("ApptDate").ToString() = args.Value Then
                args.IsValid = True
                Exit For
 
                If True Then
      
                    Return
                End If
 
            End If
        Next

The event is still working incorrect.  If I enter a date that I know is not in the database it still return indicating the date exist.  I'm at a road block with this problem.  I have been exhausting hours and hours trying to solve what should be a simple solution.  Any additional help you can give me or if anyone else offer help it would be much appreciated.

 NetBlazer

Thank you... Thank you... Thank you...

Using >>   if(Convert.ToDateTime(dr["ApptDate"]) == Convert.ToDateTime(args.Value)) args.IsValid = true;

Did the trick... It worked. 

 But I did have to go back into the database and change the data type of the "ApptDate" column to DateTime.   I was prompt to change the data type when you made the statement "if your args.Value is a valid DateTime..."  I had the data type originally set to char.  So thank you very, very much.

For those folks working with VB.Net the code is as follows:

If Convert.ToDateTime(dr("ApptDate")) = Convert.ToDateTime(args.Value) Then

The complete code solution in VB.Net is as follows: (a Formview)

 Protected Sub DateCustomValidator_ServerValidate(ByVal source As Object, ByVal args As System.Web.UI.WebControls.ServerValidateEventArgs)

 Dim DateCustomValidator As CustomValidator
        DateCustomValidator = CType(FormView1.FindControl("DateCustomValidator"), CustomValidator)
    Dim SqlDataSource3 As SqlDataSource
        SqlDataSource3 = CType(FormView1.FindControl("SqlDataSource3"), SqlDataSource)
    Dim TextBox1 As TextBox
        TextBox1 = CType(FormView1.FindControl("TextBox1"), TextBox)
    Dim dateTextBox As TextBox
        dateTextBox = CType(FormView1.FindControl("dateTextBox"), TextBox)

Dim sql As String
    Dim conn As New SqlConnection
        conn.ConnectionString = "Data Source=HQSSWEB1;Initial Catalog=HSC_Mil_ID_Appointment;Integrated Security=true"
        sql = "select ApptDate from Appointments"
        conn.Open()
        Dim ds As New DataSet
        Dim da As SqlDataAdapter

 da = New SqlDataAdapter(sql, conn)

da.Fill(ds, "ApptDate")

For Each dr In ds.Tables("ApptDate").Rows
           
            If Convert.ToDateTime(dr("ApptDate")) = Convert.ToDateTime(args.Value) Then
               
                args.IsValid = False
                Exit For
 
                If True Then
      
                    Return
                End If
            End If
        Next

End Sub

C#  Solution

 protected void DateCustomValidator_ServerValidate(object source, System.Web.UI.WebControls.ServerValidateEventArgs args)
{
   
    CustomValidator DateCustomValidator = default(CustomValidator);
    DateCustomValidator = (CustomValidator)FormView1.FindControl("DateCustomValidator");
    SqlDataSource SqlDataSource3 = default(SqlDataSource);
    SqlDataSource3 = (SqlDataSource)FormView1.FindControl("SqlDataSource3");
    TextBox TextBox1 = default(TextBox);
    TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
    TextBox dateTextBox = default(TextBox);
    dateTextBox = (TextBox)FormView1.FindControl("dateTextBox");
   
    string sql = null;
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = "Data Source=HQSSWEB1;Initial Catalog=HSC_Mil_ID_Appointment;Integrated Security=true";
    sql = "select ApptDate from Appointments";
    conn.Open();
    DataSet ds = new DataSet();
    SqlDataAdapter da = default(SqlDataAdapter);
   
    da = new SqlDataAdapter(sql, conn);
   
    da.Fill(ds, "ApptDate");
    conn.Close();
   
    args.IsValid = true;
   
    DataRow dr = default(DataRow);
   
    foreach (var dr in ds.Tables("ApptDate").Rows) {
       
        if (Convert.ToDateTime(dr("ApptDate")) == Convert.ToDateTime(args.Value)) {
           
            args.IsValid = false;
            break; // TODO: might not be correct. Was : Exit For
           
            if (true) {
               
                return;
            }
        }
    }
}
 

---------------------------

Again, thank you very much.

 If you're comparing two string representations of a date, you need to make sure the formats are the same. There's no use comparing "25/12/2008" with "2008-12-25" for example.

The moral of the story is: have you checked the format of the date in the text box (which I assume is passed to args.Value) to see if it is the same format you are drawing out of the DataTable?

If not, modify the formatting of the date when you draw it out of the DataTable. You will need to cast the value to a DateTime first, and then use its ToString() method to set the format of your choosing.

Bear in mind if your datetime column in the table you're querying accepts nulls, you are open to a casting error if you try to convert a null value in the DataTable to DateTime.

Let me know if this was over your head.

Cheers

David

 Yes, I do understand.

 The strings must match, the format of the date in textbox must match the date in the DataTable and if not cast it befor using the ToString() method.

 I did change the null value in the database to not accepts nulls.  You're right, accepting nulls will through an error.

 This is not to much over my head but I'm still learning.  I think I'm nearing the intermediate level of .net with writing code in both VB and C#.  I just hope I can get to the level of being able to help other in this forum as you and others have helped me.

 Thank you very much.

Believe me, I'm not expert. I can only comment on stuff I've done, which isn't much.

Being 'bilingual' in C# and VB is a very good idea.