Results 1 to 19 of 19

Thread: [RESOLVED] Check Name does it exist first

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Resolved [RESOLVED] Check Name does it exist first

    How to make this code
    If Name exist in database to display messagebox (Account name already exist try another)

    Code:
    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
            Using myconnection As SqlConnection = New SqlConnection("Server=127.0.0.1;Database=MuOnline;Integrated Security=True")
                myconnection.Open()
    
                Using mycommand As SqlCommand = myconnection.CreateCommand
                    mycommand.CommandText = "INSERT INTO MEMB_INFO (memb___id, memb__pwd, memb_name, sno__numb, mail_addr,bloc_code,ctl1_code) VALUES (@nAccName, @nPass, @nAccName, @nIDCode, @nEmail, 0, 1)"
                    mycommand.Parameters.AddWithValue("@nAccName", TextBox2.Text)
                    mycommand.Parameters.AddWithValue("@nPass", TextBox3.Text)
                    mycommand.Parameters.AddWithValue("@nIDCode", TextBox5.Text)
                    mycommand.Parameters.AddWithValue("@nEmail", TextBox4.Text)
    
                    Dim rowsAffected As Integer = mycommand.ExecuteNonQuery()
                    ' This would be one always in this case unless the statement failed
                    MessageBox.Show("Information Saved")
    
                End Using
    
                myconnection.Close()
            End Using
        End Sub
    Because now create the new account but make dublicate account
    Example in database i have account with name "User"
    And i create new account with name "User" and he insert the new account and in database has now 2 accounts with "User"

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Check Name does it exist first

    You can use an SQL command to return the amount of rows that have the same username. If it's greater than zero, then don't insert the row. Here is a quick example that will return if there is already a row that has the same value in memb_name:
    Code:
    Private Function DuplicateUser(ByVal username As String) As Boolean
        Dim connection As SqlConenction = Nothing
        Dim duplicate As Boolean = True
        Try
            connection = New SqlConnection("Server=127.0.0.1;Database=MuOnline;Integrated Security=True")
    
            Using cmd As SqlCommand = New SqlCommand("SELECT COUNT(memb_name) FROM MEMB_INFO WHERE memb_name = @name")
                cmd.Parameters.AddWithValue("@name", username)
    
                duplicate = Convert.ToInt32(cmd.ExecuteScalar) > 0
    
                connection.Close()
        Catch ex As Exception
            duplicate = True
            Console.WriteLine(ex.Message)
        Finally
            If connection IsNot Nothing Then
                If connection.State = ConnectionState.Open Then
                    connection.Close()
                End If
    
                connection.Dispose()
            End If
        End Try
    
        Return duplicate
    End Function
    Last edited by dday9; Jun 23rd, 2015 at 09:16 AM.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Check Name does it exist first

    ah i can't manage it with your code maybe i make it wrong, can you tell me how should it become in my code:
    Code:
    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
            Using myconnection As SqlConnection = New SqlConnection("Server=127.0.0.1;Database=MuOnline;Integrated Security=True")
                myconnection.Open()
    
                If TextBox2.Text = "" Then
                    MessageBox.Show("Please Enter Name", "Error")
                ElseIf TextBox3.Text = "" Then
                    MessageBox.Show("Please Enter Password", "Error")
                ElseIf TextBox4.Text = "" Then
                    MessageBox.Show("Please Enter Email", "Error")
                ElseIf TextBox5.Text = "" Then
                    MessageBox.Show("Please Enter PersonalID", "Error")
                Else
    
                    Using mycommand As SqlCommand = myconnection.CreateCommand
                        mycommand.CommandText = "INSERT INTO MEMB_INFO (memb___id, memb__pwd, memb_name, sno__numb, mail_addr,bloc_code,ctl1_code) VALUES (@nAccName, @nPass, @nAccName, @nIDCode, @nEmail, 0, 1)"
                        mycommand.Parameters.AddWithValue("@nAccName", TextBox2.Text)
                        mycommand.Parameters.AddWithValue("@nPass", TextBox3.Text)
                        mycommand.Parameters.AddWithValue("@nIDCode", TextBox5.Text)
                        mycommand.Parameters.AddWithValue("@nEmail", TextBox4.Text)
    
                        Dim rowsAffected As Integer = mycommand.ExecuteNonQuery()
                        ' This would be one always in this case unless the statement failed
                        MessageBox.Show("Information Saved", "Sucsess")
    
                    End Using
                    myconnection.Close()
                End If
            End Using
        End Sub

  4. #4
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Check Name does it exist first

    You would use a conditional statement to check if the value returned by the function is False, if it is then continue on with your code.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Check Name does it exist first

    i try it with your example but i got a lot of errors cause not match
    i'm not sure how should it become if i add after Else check for empty fields
    can you add the missing part with red in my code so i can continued with code thanks

  6. #6
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Check Name does it exist first

    I'm not in the mood to spoon feed you. You have all the information in front of you, if you do not know how to create a conditional statement to check if the value returned by the function is False then you have more things to worry about.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Check Name does it exist first

    thats why i post here, for help omg..
    ok so maybe someone else would help me how to add it in my code..

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Check Name does it exist first

    any help please

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Check Name does it exist first

    please help

  10. #10
    PowerPoster SJWhiteley's Avatar
    Join Date
    Feb 2009
    Location
    South of the Mason-Dixon Line
    Posts
    2,256

    Re: Check Name does it exist first

    What have you done to integrate the code? Copy into your app, call the method: if it returns true then there is a duplicate user.
    "Ok, my response to that is pending a Google search" - Bucky Katt.
    "There are two types of people in the world: Those who can extrapolate from incomplete data sets." - Unk.
    "Before you can 'think outside the box' you need to understand where the box is."

  11. #11
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Check Name does it exist first

    To use dday9 method you need to add his code to your app and then,
    Code:
        Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
            Using myconnection As SqlConnection = New SqlConnection("Server=127.0.0.1;Database=MuOnline;Integrated Security=True")
                myconnection.Open()
    
                If TextBox2.Text = "" Then
                    MessageBox.Show("Please Enter Name", "Error")
                ElseIf TextBox3.Text = "" Then
                    MessageBox.Show("Please Enter Password", "Error")
                ElseIf TextBox4.Text = "" Then
                    MessageBox.Show("Please Enter Email", "Error")
                ElseIf TextBox5.Text = "" Then
                    MessageBox.Show("Please Enter PersonalID", "Error")
                Else
                    'CHECK IF NAME EXISTS
                    If Not DuplicateUser(nAccName) Then
                        Using mycommand As SqlCommand = myconnection.CreateCommand
                            mycommand.CommandText = "INSERT INTO MEMB_INFO (memb___id, memb__pwd, memb_name, sno__numb, mail_addr,bloc_code,ctl1_code) VALUES (@nAccName, @nPass, @nAccName, @nIDCode, @nEmail, 0, 1)"
                            mycommand.Parameters.AddWithValue("@nAccName", TextBox2.Text)
                            mycommand.Parameters.AddWithValue("@nPass", TextBox3.Text)
                            mycommand.Parameters.AddWithValue("@nIDCode", TextBox5.Text)
                            mycommand.Parameters.AddWithValue("@nEmail", TextBox4.Text)
    
                            Dim rowsAffected As Integer = mycommand.ExecuteNonQuery()
                            ' This would be one always in this case unless the statement failed
                            MessageBox.Show("Information Saved", "Sucsess")
    
                        End Using
                        myconnection.Close()
                    Else
                        MessageBox.Show("User Name Already Exists")
                    End If
                End If
            End Using
        End Sub
    If "mem_name" is meant to be unique or primarykey then this would work fine. But if "mem_id" or some other field is the primarykey then you would need to adjust DuplicateUser and the parameter you send to the function accordingly.

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Check Name does it exist first

    what it means: If Not DuplicateUser(nAccName) Then
    what i need to replace there i try with Textbox2.text(@nAccName) but its underlined

  13. #13
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Check Name does it exist first

    what it means: If Not DuplicateUser(nAccName) Then
    Your sending nAccName to the function that dday9 wrote for you, which returns either True of False.

    After looking at your code again, it should be,
    Code:
    If Not DuplicateUser(Textbox2.Text) Then

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Check Name does it exist first

    i got underline in DuplicateUser

  15. #15
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Check Name does it exist first

    Quote Originally Posted by diablo21 View Post
    i got underline in DuplicateUser
    That is because you are not implementing the code that I provided.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  16. #16
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Check Name does it exist first

    Did you add the function to your project?

  17. #17

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Check Name does it exist first

    yes but i got underline about this
    aa maybe i forgot add : Dim duplicate As Boolean = True
    edit nope..not from this maybe its because i use button ?
    Last edited by diablo21; Jun 26th, 2015 at 01:14 PM.

  18. #18
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    6,582

    Re: Check Name does it exist first

    Where did you put the function dday9 provided?
    You can't call it in your code if you didn't add it to your code. I just put it after the Button2_Click Sub and there is no underline under the call. {p.s. did have to fix a misspelling and add an End Using, but little things like that are to be expected with code written ad-hoc in the forum}.
    Code:
      Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Using myconnection As SqlConnection = New SqlConnection("Server=127.0.0.1;Database=MuOnline;Integrated Security=True")
          myconnection.Open()
    
          If TextBox2.Text = "" Then
            MessageBox.Show("Please Enter Name", "Error")
          ElseIf TextBox3.Text = "" Then
            MessageBox.Show("Please Enter Password", "Error")
          ElseIf TextBox4.Text = "" Then
            MessageBox.Show("Please Enter Email", "Error")
          ElseIf TextBox5.Text = "" Then
            MessageBox.Show("Please Enter PersonalID", "Error")
          Else
            'CHECK IF NAME EXISTS
            If Not DuplicateUser(TextBox2.Text) Then
              Using mycommand As SqlCommand = myconnection.CreateCommand
                mycommand.CommandText = "INSERT INTO MEMB_INFO (memb___id, memb__pwd, memb_name, sno__numb, mail_addr,bloc_code,ctl1_code) VALUES (@nAccName, @nPass, @nAccName, @nIDCode, @nEmail, 0, 1)"
                mycommand.Parameters.AddWithValue("@nAccName", TextBox2.Text)
                mycommand.Parameters.AddWithValue("@nPass", TextBox3.Text)
                mycommand.Parameters.AddWithValue("@nIDCode", TextBox5.Text)
                mycommand.Parameters.AddWithValue("@nEmail", TextBox4.Text)
    
                Dim rowsAffected As Integer = mycommand.ExecuteNonQuery()
                ' This would be one always in this case unless the statement failed
                MessageBox.Show("Information Saved", "Sucsess")
    
              End Using
              myconnection.Close()
            Else
              MessageBox.Show("User Name Already Exists")
            End If
          End If
        End Using
      End Sub
    
      Private Function DuplicateUser(ByVal username As String) As Boolean
        Dim connection As SqlConnection = Nothing
        Dim duplicate As Boolean = True
        Try
          connection = New SqlConnection("Server=127.0.0.1;Database=MuOnline;Integrated Security=True")
    
          Using cmd As SqlCommand = New SqlCommand("SELECT COUNT(memb_name) FROM MEMB_INFO WHERE memb_name = @name")
            cmd.Parameters.AddWithValue("@name", username)
    
            duplicate = Convert.ToInt32(cmd.ExecuteScalar) > 0
    
            connection.Close()
          End Using
    
        Catch ex As Exception
          duplicate = True
          Console.WriteLine(ex.Message)
        Finally
          If connection IsNot Nothing Then
            If connection.State = ConnectionState.Open Then
              connection.Close()
            End If
    
            connection.Dispose()
          End If
        End Try
    
        Return duplicate
      End Function

  19. #19

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Check Name does it exist first

    aa now i understand it how it should be thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width