Retain form changes after SQLException

[rickb]

Here's what I have and what I'd like to do:

 

A new User is added to a sql database via databound textboxes. The primary key is UserProfile field. I have Try/Catch set to Catch a SQLException when a duplicate UserProfile name is attempted to be entered. What I want to happen is give the user of the program a choice: Cancel the entire transaction (I have no problem here), or choose to attempt a different UserProfile, but preserving the changes so far and focus on the UserProfile.

 

I used an OKCancel button, and attempted to set the OK option to allow the program user to attempt a new UserProfile name. Here's my code for the messagebox in my Catch statement:

 

If MessageBox.Show("Duplicate UserProfile. Choose another name.", & _
"DUPLICATE USER PROFILE", MessageBoxButtons.OKCancel, " & _
"MessageBoxIcon.Error)=DialogResult.OK Then
txtUserProfile.focus()
End If

 

This doesn't work; clicking OK cancels the edit and resets all changes. Is there a way to just close the message box, set the focus, and allow the user to try again?

 

Thanks for any help.

[PlausiblyDamp]

Do you have any code immediately after the If statement?

[rickb]

Do you have any code immediately after the If statement?

 

Nothing within the Try/Catch block. After "End Try," I open the connection,

set the INSERT command for the dataadapter,run executeNonQuery, close the connection, fill the dataset, close connection.

[PlausiblyDamp]

Could you post the entire method here? It looks as though you are catching the error, setting focus back to th control in question and then continuing to run the rest of the code as if nothing had happened - you may want to exit the routine after setting the focus to the textbox.

[rickb]

Could you post the entire method here? It looks as though you are catching the error' date=' setting focus back to th control in question and then continuing to run the rest of the code as if nothing had happened - you may want to exit the routine after setting the focus to the textbox.[/quote']

 

Here's the code:

 

Private Sub AddUser()
Dim strInsert As New SqlCommand("INSERT INTO AS400Users & _
(LastName,FirstName,LOC,Building,UserProfile,DeviceID) VALUES & _
(@LastName,@FirstName,@LOC,@Building,@UserProfile,@DeviceID)", conn1)

       strInsert.Parameters.Add("@LastName", SqlDbType.VarChar, 30, & _
"LastName").Value = Me.txtLastName.Text
       strInsert.Parameters.Add("@FirstName", SqlDbType.VarChar, 30, & _
"FirstName").Value = Me.txtFirstName.Text
       strInsert.Parameters.Add("@LOC", SqlDbType.VarChar, 10, & _
"LOC").Value = Me.txtLOC.Text
       strInsert.Parameters.Add("@Building", SqlDbType.VarChar, 30, & _
"Building").Value = Me.txtBldg.Text
       strInsert.Parameters.Add("@UserProfile", SqlDbType.VarChar, 20, & _
"UserProfile").Value = Me.txtUserProfile.Text
       strInsert.Parameters.Add("@DeviceID", SqlDbType.VarChar, 20, & _
"DeviceID").Value = Me.txtDeviceID.Text

       conn1.Open()

       Try
           strInsert.ExecuteNonQuery()
       Catch xSQL As SqlException
           If MessageBox.Show("This User Profile already exists." & _
vbCr & "Please enter a different Profile name.", "DUPLICATE USER PROFILE", & _
MessageBoxButtons.OKCancel, MessageBoxIcon.Error) = DialogResult.OK Then
               Me.txtUserProfile.Focus()
               [color=Red]Exit Sub[/color] [color=Green]'I just entered this bit based on your thought 
'of the the code continuing.[/color]            
End If
       End Try

       sdaAS400.InsertCommand = strInsert
       sdsAS400.Clear()
       sdaAS400.Fill(sdsAS400, "AS400Users")
       conn1.Close()

 

I included "EXIT SUB" and this placed the focus on the UserProfile field without losing most of my changes, which is almost what I wanted. Of course, fixing this caused some other issues: two of the textboxes and all my buttons went back to their default state. That's something I'll work on, but I'm also open to any suggestions on corrections or improvements.

 

Thanks again for the help; I had forgotten about the EXIT SUB in a Try/Catch block.

[rickb]

FYI: Figured out the textboxes and buttons issue. The SAVE button reset everything to the Page_Load settings; I had to EXIT SUB there, too, in my Try/Catch block.

 

Thanks, PlausiblyDamp, for the help; I'm on a roll now. . .