Compare values from data source to string

Question

Hey folks, I'm just stumped on what to do with this code, I'm just trying to implement a 'no duplicates' catch on my insert customer form, but it just slips through my if statement to the else everytime. This is the source. Also I tried a .Equals with the same results :(

 Protected Sub srcAllClients_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles srcAllClients.Inserting
        'Establish Variables
        Dim emailAddress As String
        Dim srcUsers As SqlDataSource = New SqlDataSource()

        srcUsers.ConnectionString = ConfigurationManager.ConnectionStrings("ISSD21ConnectionString").ConnectionString

        Dim view As DataView
        view = DirectCast(srcUsers.Select(DataSourceSelectArguments.Empty), DataView)
        srcUsers.SelectCommand = "SELECT EmailAddress FROM ISSDClients"
        srcUsers.DataSourceMode = SqlDataSourceMode.DataReader
        Dim reader As IDataReader
        reader = DirectCast(srcUsers.Select(DataSourceSelectArguments.Empty), IDataReader)

        emailAddress = FormView1.FindControl("txtEmail").ToString

        While reader.Read()
            If reader("EmailAddress") = (emailAddress) Then
                lblError.Text = "Your Email is NOT Unique!"
                'this is where we cancel the update and return an error
            Else
                lblError.Text = "Your Email is Unique!"
                'nothing needs to happen, maybe just tell them that it went through
            End If
        End While

        reader.Close()
    End Sub

Answer 1

In addition to Womp's answer...

In the while loop that is running through the email records, you need to break out of the loop once you find a matching email and alert the user.

if reader("EmailAddress") = (emailAddress) then
  '1.  Break from the Loop
End if

Answer 2

emailAddress = FormView1.FindControl("txtEmail").ToString

is just going to return the string "System.Web.UI.WebControls.TextBox". You're not accessing the actual property of the control that would hold the text value, you're just calling ToString() on the control itself.

Try this:

Dim emailBox As TextBox = CType(FormView1.FindControl("txtEmail"), TextBox);
emailAddress = emailBox.Text

Answer 3

I would recommend that you pass the emailAddress to the SQL Server as a parameter.

Select Count(EmailAddress) From ISSDClients
Where EmailAddress = @EmailAddress

Execute this statement using ExecuteScalar and cast the result as an integer. If the result is zero then you are ok, otherwise display an error.

Doing it this way avoids using the while loop and should be much quicker if your table has lots of rows.

You also need to get the Text property from the Email Text box.

emailAddress = FormView1.FindControl("txtEmail").Text.ToString

Answer 4

You may want to take a look at the String.Compare method, which will make it easier to compare without respect to things like case sensitivity and culture. It does consider whitespace to be part of your string, so you may wish to trim the string prior to calling it, to help normalize.

For example, the following strings would be considered equal:

var firstString  = "some StrinG to Compare  ";
var secondString = "  somE string to COMPARE";

var equal = (String.Compare(firstString.Trim(), secondString.Trim(), StringComparison.InvariantCultureIgnoreCase) == 0);