Retain form changes after SQLException

R

rickb

Member
Joined
Dec 31, 2002
Messages
17
Location
St. Louis, Missouri
Heres what I have and what Id 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. Heres my code for the messagebox in my Catch statement:

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

This doesnt 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 said:
Do you have any code immediately after the If statement?
Click to expand...

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.
 
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.
 
PlausiblyDamp said:
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.
Click to expand...

Heres the code:

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. Thats something Ill work on, but Im 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.
 
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; Im on a roll now. . .
 
Back
Top