MessageBox to return name

[tis9700]

I need to be pointed in the right direction....

I have a Windows Form to insert a record into an "INSTRUCTORS" table.

It works great but I need a MessageBox to appear when a Social Security Number is already in use. I also need it to display the name of the Instructor the Social Security Number is currently assigned.

I'm posting the code I have so far below...
I marked the Messagebox in RED.

Question: Should I do this type of validation at the field level or is this acceptable? Any suggestions?

Thanks

Private Sub InsertNewInstructor()

Dim cn As New SqlConnection("Data Source=BH01WF54;Initial Catalog=HUNTER_SAFETY_PROGRAM;Integrated Security=SSPI")
Dim cmd As New SqlCommand()


cmd.Parameters.AddWithValue("@InstFName", FirstName.Text)
cmd.Parameters.AddWithValue("@InstMName", MiddleName.Text)
cmd.Parameters.AddWithValue("@InstLName", LastName.Text)
cmd.Parameters.AddWithValue("@InstSuffix", Suffix.Text)
cmd.Parameters.AddWithValue("@InstSSN", SocialSecurityNumber.Text)
cmd.Parameters.AddWithValue("@InstAddress", Address.Text)
cmd.Parameters.AddWithValue("@InstCity", City.Text)
cmd.Parameters.AddWithValue("@InstState", State.SelectedItem)
cmd.Parameters.AddWithValue("@InstZipCode", ZipCode.Text)
cmd.Parameters.AddWithValue("@InstWorkCountyCode", WorkCounty.SelectedValue)
cmd.Parameters.AddWithValue("@InstHomePhone", HomePhone.Text)
cmd.Parameters.AddWithValue("@InstWorkPhone", WorkPhone.Text)
cmd.Parameters.AddWithValue("@InstEmail", Email.Text)
cmd.Parameters.AddWithValue("@InstDOB", DateOfBirth.Value)
cmd.Parameters.AddWithValue("@InstComment", Comment.Text)
cmd.Parameters.AddWithValue("@InstTWRAEmp", TWRAEmp.CheckState)
cmd.Parameters.AddWithValue("@InstBegCtDate", CertificationDate.Value)


cmd.Connection = cn
cmd.CommandText = "INSERT_NEW_INSTRUCTOR"
cmd.CommandType = CommandType.StoredProcedure


cn.Open()

Dim Affected As Integer = cmd.ExecuteNonQuery()
If Affected = 1 Then
MessageBox.Show(Affected & " ACCOUNT SUBMITTED")
ElseIf Affected = -1 Then

MessageBox.Show("SOCIAL SECURITY NUMBER ALREADY IN USE BY: ", "INVALID ENTRY", MessageBoxButtons.OK, MessageBoxIcon.Error)

cn.Close()
cn.Dispose()
End If
End Sub

 

[cjelec]

You could do a quick search first by running something like:

Public Function FindSSNum(SSN As String) As Boolean
  Dim ans As Boolean
  Dim cn As New SqlConnection("Data Source=BH01WF54;Initial Catalog=HUNTER_SAFETY_PROGRAM;Integrated Security=SSPI")
  Dim cmd As New SqlCommand("SELECT * FROM [b]??[/b] WHERE [b]SSN[/b] = '" & SSN & "'", cn)
  Dim dar As SqlDatareader

  cn.Open()

  dar = cmd.ExecuteReader()

  If dar.Read Then
    ans = True
  Else
    ans = False
  End If

  cn.Close

  Return ans
End Function

If it returns True, you know that the ssn is already in the database so you can show the Message Box.

 

[cjelec]

Or if you set up the database to not allow duplicates on the SSN field, it should throw an error on the "cmd.ExecuteNonQuery()" line. You could trap this error using some thing like:

...
  Try
    Dim Affected As Integer = cmd.ExecuteNonQuery()
    MessageBox.Show(Affected & " ACCOUNT SUBMITTED")
  Catch ex As Exception
    MessageBox.Show("SOCIAL SECURITY NUMBER ALREADY IN USE BY: ", "INVALID ENTRY", MessageBoxButtons.OK, MessageBoxIcon.Error)
  End Try

  cn.Close()
  cn.Dispose()
...

 

[tis9700]

Hello cjelec,

Thanks for the quick response and your input.

On your second suggestion......
I did create a Unique Constraint on the SSN. The Try/Catch does capture the return value of -1 from the Execute NONQuery when a SSN already exists, so the record isn't created but it throws that value into the wrong MessageBox and displays.... -1 ACCOUNT SUBMITTED

Is it not Catching it as an exception? And it does not return the Instructors Name.

Here's an example of what I'm trying to do...

I enter the SSN 555-55-5555 for JOHN DOE.
Click Submit
If the SSN 555-55-5555 is already in the database, I want to return a Message box that says
"SOCIAL SECURITY NUMBER ALREADY IN USE BY:
JANE SMITH

This probably isn't necassary for my users but it's been eating at me!!!

I'm checking out the function you sent next. Thanks a bunch for your help. I really appreciate it!

 

[cjelec]

Ok,

The only thing that I can see wrong with your code is, if there is a problem other than the SSN number that prevents the item to be added you will have the wrong message pop up to the end user. My first code snippet would help you with pre-checking the SSN number, and will give you a more accurate message if something goes wrong.