Insertion problem in database

[desperateboy]

hi friends,

I have added 3 buttons of name add,save and delete.When add username in the database first heck out that already exist or not.If not existed then insert into database.I trying using this code

Plz help to sort it out.

Try
    Dim str1 As String
    str1 = txtusr.Text
    conn.Open()
    cmd = New SqlCommand("select * from usrs where uname='" & (str1) & "'", conn)
    dr = cmd.ExecuteReader
    While dr.Read()
        Str = dr.Item("uname")
        If Str <> Trim(str1) Then
            usrexist()
            Exit Sub
        Else
            Dim sYN As String
            sYN = MsgBox("User Already Exists, Want to Update", vbYesNo + vbQuestion, "Update User")
            If sYN = vbYes Then
                '' update in the database''
            End If

    End While

Catch ex As Exception
    MsgBox(ex.Message)
Finally
    cmd = Nothing
    conn.Close()
    dr.Close()
End Try

 

[Tom]

My comments in green

Try
    Dim str1 As String
    str1 = txtusr.Text
    conn.Open()
    cmd = New SqlCommand("select * from usrs where uname='" & (str1) & "'", conn)
    dr = cmd.ExecuteReader

    [COLOR="seagreen"]'Loop thru all the current users[/COLOR]
    While dr.Read()

        Str = dr.Item("uname")

        If Str <> Trim(str1) Then


         [COLOR="seagreen"]'I'm not sure what usrexist() is doing
         'since you are saying in your else statement 
         'that user exists is true.[/COLOR]
            
         usrexist()

         [COLOR="seagreen"]   
            ' If I understand this correctly, the first record it 
            ' it checks and isnt a match, it exits the sub and
            ' never checks to see if the user belongs in the rest
            ' of the reader list.
            ' 
            ' If record-1.user not equal to textbox.text, exit sub.
            '
            ' If you are exiting the sub, how do you know the user 
            ' isnt in record number-2?
          [/COLOR]

            Exit Sub
        Else

            Dim sYN As String

            sYN = MsgBox("User Already Exists, Want to Update", vbYesNo + vbQuestion, "Update User")

            If sYN = vbYes Then

               [COLOR="SeaGreen"] 

                ' This is a reader object, so how are you inserting 
                ' new records and updating it?
                '
                ' Since your looping thru every record anyway looking for 
                ' the user record, instead of using filter & find methods 
                ' of a dataviewer, why not just loop thru an dataset that will
                ' allow you to update it records?
               [/COLOR]

                '' update in the database''
            End If

    End While

Catch ex As Exception
    MsgBox(ex.Message)
Finally
    cmd = Nothing
    conn.Close()
    
     [COLOR="seagreen"]    
     ' Your datareader is connected to the command & connection objects, 
     ' What happens at this point because your dr exists but the cmd & con
     ' objects are already gone?
     '
     ' Clean-up order, dr then cmd, then conn
     ' also use the dispose method on objects that have it.
     [/COLOR]

    dr.Close()
End Try

 

[desperateboy]

thanks foe suggestion.I wil try.

 

[Tom]

If you need more help, let me know and I can write you a full example.

 

[desperateboy]

Nice Tom. Problem solved now but would ask new problem when got.

Thanks