-
Apr 1st, 2012, 10:24 AM
#1
Thread Starter
Registered User
[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
-
Apr 1st, 2012, 10:32 AM
#2
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.
-
Apr 1st, 2012, 10:32 AM
#3
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
-
Apr 2nd, 2012, 05:38 PM
#4
Thread Starter
Registered User
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|