Checking for existing name

Question

User1372787940 posted

In the code below, when i check for an existing name, it only works for the first person in the database?

    Public Sub CheckForExistingName()

        'Check if First Name is In Database
        EXISTINGNAME.SelectCommand = "SELECT EMPFIRSTNAME, EMPLASTNAME FROM EMPLOYEES"
        Dim dv1 As DataView = CType(EXISTINGNAME.Select(DataSourceSelectArguments.Empty), DataView)
        If dv1.Count > "0" Then
            Dim i As Integer = 0
            Do Until i = dv1.Count
                Dim dr As DataRowView = dv1.Item(i)
                If dr.Item(0).ToString = txtFirstName.Text Then
                    'FIRST NAME ALRADY EXISTS NOW CHECK TO SEE IF LAST NAME MATCHES
                    If dr.Item(1).ToString = txtLastName.Text Then
                        lblError.Visible = True
                        Exit Sub
                    Else
                        'IF DOES NOT MATCH ADD EMPLOYEE
                        AddEmployee()
                        Response.Redirect("~/MainMenu.aspx", False)
                        Exit Sub
                    End If
                Else
                    'NAME DOES NOT EXIST
                    AddEmployee()
                    Response.Redirect("~/MainMenu.aspx", False)
                    Exit Sub
                End If
                i = i + 1
            Loop
        End If

    End Sub

Answer 1

User475983607 posted

The code redirects as soon as it does not find a match which is probably the first record   If it does find a match on the first record then it exists the sub.

Have you tried single stepping through the code?

https://msdn.microsoft.com/en-us/library/y740d9d3.aspx

Generally, this kind of logic, set logic, is better handled in SQL.

Answer 2

User1372787940 posted

How would I go about fixing the code? I tried everything and cannot figure it out.

Answer 3

User475983607 posted

runner15

How would I go about fixing the code? I tried everything and cannot figure it out.

Well, you're not using the right tools for the job.  Your code is retrieving every single record for the DB and moving the data to the web server.  The web server code is looping over each record and checking for equality.  You can do the same thing with one line of SQL.

This example is using the AdventureWorks2012 database.  The code queries the Person.Person table by first and last name and very similar to what you're doing.

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If (GetEmployeeByFirstLastName("Terri", "Duffy")) Then
            'Record found
        Else
            'Not found
        End If
    End Sub

    Protected Function GetEmployeeByFirstLastName(firstname As String, lastname As String) As Boolean
        Dim EmpId As Int32 = 0
        Dim connectionString As String = "Data Source=WorkStation\SQLInstance;Initial Catalog=AdventureWorks2012;Integrated Security=True"
        Dim query As String = "SELECT BusinessEntityID AS Person " _
            & "FROM Person.Person " _
            & "WHERE FirstName = @firstname " _
            & " AND LastName = @lastname"

        Using connection As New SqlConnection(connectionString)
            Dim cmd As New SqlCommand(query, connection)

            cmd.Parameters.Add("@firstname", SqlDbType.VarChar)
            cmd.Parameters("@firstname").Value = firstname

            cmd.Parameters.Add("@lastname", SqlDbType.VarChar)
            cmd.Parameters("@lastname").Value = lastname

            Try
                connection.Open()
                EmpId = Convert.ToInt32(cmd.ExecuteScalar())

                Return EmpId > 0
            Catch ex As Exception
                Throw New ArgumentException("Exception Occured")
            End Try

        End Using
    End Function

The ExecuteScalar() method returns the first field from invoking query.  In this case, the field is the primary key which is an integer.  If ExecuteScalar() returns no records the Convert.ToInt32 will convert the result to zero.  Therefore, if a record is found the GetEmployeeByFirstLastName method will return true otherwise it will return false due to the line Return EmpId > 0.

Consider spending some time learning basic SQL.  This will greatly enhance your ability to retrieve data.