locked
Checking for existing name RRS feed

  • 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

    Thursday, January 21, 2016 4:25 PM

Answers

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 22, 2016 12:13 AM

All replies

  • 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.

    Thursday, January 21, 2016 5:57 PM
  • User1372787940 posted

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

    Thursday, January 21, 2016 11:02 PM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 22, 2016 12:13 AM