Results 1 to 4 of 4

Thread: [RESOLVED] Need help with error checking on an insert statement

  1. #1

    Thread Starter
    Registered User
    Join Date
    Nov 2009
    Location
    New England
    Posts
    122

    Resolved [RESOLVED] Need help with error checking on an insert statement

    Hello,

    I am currently trying to make an insert statement that will first check for exceptions before it is sent to the SQL server, I am new to this and would like to know what errors I should be looking out for and also what code should I use to ensure the error will be caught.

    Here is the code I am currently using...

    Code:
    Try
                If contactIsValid() Then
                    Me.Validate()
                    Me.AddressTableAdapter.Insert(fnameTxBx.Text, lnameTxBx.Text, _
                                             emailTxBx.Text, phoneTxBx.Text, _
                                             streetTxBx.Text, cityTxBx.Text, _
                                             CStr(stateCoBx.SelectedValue.ToString), zipTxBx.Text, _
                                             CInt(Group_NameComboBox.SelectedValue))
    
    
                    'Dim getresults As New groupfilladd
                    'If getresults.addcontact(fnameTxBx.Text, lnameTxBx.Text, _
                    '                         emailTxBx.Text, phoneTxBx.Text, _
                    '                         streetTxBx.Text, cityTxBx.Text, _
                    '                         CStr(stateCoBx.SelectedItem), zipTxBx.Text, _
                    '                         CInt(Group_NameComboBox.SelectedValue)) Then
                    Dim another As Integer = MessageBox.Show("Contact Added sucessfully. Add Another?", "Contact Added", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
    
                    If another = vbYes Then
                        fnameTxBx.Text = ""
                        lnameTxBx.Text = ""
                        emailTxBx.Text = ""
                        phoneTxBx.Text = ""
                        streetTxBx.Text = ""
                        cityTxBx.Text = ""
                        stateCoBx.SelectedItem = "AL"
                        zipTxBx.Text = ""
                        Group_NameComboBox.SelectedItem = "Default Contacts"
                        fnameTxBx.Focus()
                        'Runs refreshinfo sub from parent instance.
                        Call myParent.refreshinfo()
                    Else
                        'Runs refreshinfo sub from parent instance.
                        Call myParent.refreshinfo()
                        Me.Close()
                    End If
    
                Else
                    MessageBox.Show("Please check entered data and try agian", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End If
    
                'ADO.net Error
            Catch ex As ConstraintException
                MessageBox.Show("Constraint Error, Please Try agian", "Constraint Error")
    
                'ADO.net Error
            Catch ex As NoNullAllowedException
                MessageBox.Show("Empty Fields not allowed, Please Try agian", "Null Error")
    
                'General ADO.net Error
            Catch exADO As DataException
                MessageBox.Show(exADO.Message, exADO.GetType.ToString)
    
                'General SQL Error
            Catch exSQL As SqlException
                MessageBox.Show("SQL Error #" & exSQL.Number _
                                & " :" & exSQL.Message, exSQL.GetType.ToString)
    
                'General .net Error
            Catch exNET As Exception
                MessageBox.Show(".Net Message: " _
                                & exNET.Message, exNET.GetType.ToString)
            End Try
        End Sub
    These are the exceptions I think need to be addressed by an insert statement. Is it over kill? If so, what should I be looking for?

    Thanks for anyone's help with this.

    Mike

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Need help with error checking on an insert statement

    There are many situations where exceptions might be thrown that can be avoided by pre-validating. One that jumps out at me immediately is NoNullAllowed. You already know which columns allow nulls and which don't so why would you ever try to insert data that could easily check first to make sure that there were no nulls where there shouldn't be? In fact, there's no way that you could ever be inserting a null value because all the arguments you're passing cannot possibly be null. All those Text properties will always return a String object, which may be an empty string but that's not null. The other two are the result of ToString and CInt, which both cannot possibly be null either. In those two cases, if the original value is Nothing then the exception will be thrown at that point, not when inserting the data.

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Need help with error checking on an insert statement

    Well, that certainly catches everything, but I disagree with the first two. Neither of those are exceptional situations. You could use If statements to determine whether any necessary field is Null before you pass the values to the Insert method. Those checks will be FAR faster than catching an exception. After all, catching an exception is the slowest thing you can do, so you don't want to be catching exceptions when you can prevent the exceptions in the first place. Both of the first two exceptions you are catching are only possible if you haven't done any validating on the inputs. Do the validating, and you won't need either of them. That may also be true of the third one, as well.

    EDIT: Crossed up the insomniac.
    My usual boring signature: Nothing

  4. #4

    Thread Starter
    Registered User
    Join Date
    Nov 2009
    Location
    New England
    Posts
    122

    Re: Need help with error checking on an insert statement

    Thanks for helping me with this....appreciate your insight.

    Mike

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