Exception message showing twice

Question

0.00/5 (No votes)

See more:

.NET3.5

Hi , I am trying to show a message for the particular sql exception code while inserting data into the DB, i m using the following code for the same. I m trying to insert 15 user into DB from my form, and my goal is to show one single message for the all failure insert with the row number (as my form looks like an excel sheet), i have written the following code which is appearing twice and it is quite understood why its happening , but i m not able to achieve my goal- please help as i am new to .net, please check the following code to get a better understanding

VB

Public Sub rtrnQry(ByVal usrNm As String, ByVal psWd As String, ByVal fNm As String, _
                   ByVal lNm As String, ByVal empId As Integer, ByVal proNm As String, _
                   ByVal aDm As String, ByVal rowno As Integer)
  Try

    Using cn As New SqlConnection(sqlConnStr)
      cn.Open()
      Using cmd As New SqlCommand("InsrtLogin", cn)
        With cmd
          .CommandType = CommandType.StoredProcedure
          .Parameters.AddWithValue("@empid", empId)
          .Parameters.AddWithValue("@username", usrNm)
          .Parameters.AddWithValue("@password", psWd)
          .Parameters.AddWithValue("@firstname", fNm)
          .Parameters.AddWithValue("@lastname", lNm)
          .Parameters.AddWithValue("@adminpri", aDm)
          .Parameters.AddWithValue("@proName", proNm)
          .ExecuteNonQuery()
        End With
      End Using
    End Using

  Catch ex As SqlException
    If chkBox1_Crtusr.Checked Then
      If ex.Number.Equals(2627) Then 
        'Exception code for Primary Key Violation
         MsgBox("Duplicate Employee ID cannot be inserted. Violation of Primary Key " & _
                "Constraint Occured. Enter an unique value.Please check row number : 1", _
                MsgBoxStyle.Critical, "Error")
      Else
        MsgBox(ex.ToString)
      End If
      If ex.Number.Equals(208) Then
        MsgBox("Invalid Table Name. Check the Sql String.", MsgBoxStyle.Critical, _
               "Error")
      End If

    End If
    If chkBox2_Crtusr.Checked Then
      If ex.Number.Equals(2627) Then 
        'Exception code for Primary Key Violation
        MsgBox("Duplicate Employee ID cannot be inserted. Violation of Primary Key " & _
               "Constraint Occured. Enter an unique value.Please check row number : 2", _
               MsgBoxStyle.Critical, "Error")
      Else
        MsgBox(ex.ToString)
      End If
      If ex.Number.Equals(208) Then
        MsgBox("Invalid Table Name. Check the Sql String.", MsgBoxStyle.Critical, _
               "Error")
      End If
      'same for next 13 users

Now i m calling this proc:

VB

Private Sub btnSubmit_CrtMul_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit_CrtMul.Click
  Dim cnt As Integer = 0
  Try
    If chkBox1_Crtusr.Checked Then
      rtrnQry(txtUsrNm_1.Text, txtPswd_1.Text, txtFName_1.Text, txtLName_1.Text, _
              txtEmpID_1.Text, cmbProNm1.SelectedItem, cmbAdmin1.SelectedItem, _
              1)
      Dim sqlLogin As String = "Select * from login where user_name ='" & txtUsrNm_1.Text & "'"
      Dim usrVerified As String = DataStore.ExecuteScalar(sqlLogin, sqlConnStr)
      If usrVerified.Length > 0 Then
        Call ClearData(1)
        cnt = cnt + 1
      End If
    End If
    If chkBox2_Crtusr.Checked Then
      rtrnQry(txtUsrNm_2.Text, txtPswd_2.Text, txtFName_2.Text, txtLName_2.Text, _
              txtEmpID_2.Text, cmbProNm2.SelectedItem, cmbAdmin2.SelectedItem, _
              2)
      Dim sqlLogin As String = "Select * from login where user_name ='" & txtUsrNm_2.Text & "'"
      Dim usrVerified As String = DataStore.ExecuteScalar(sqlLogin, sqlConnStr)
      If usrVerified.Length > 0 Then
        Call ClearData(2)
        cnt = cnt + 1
      End If
    End If
    If chkBox3_Crtusr.Checked Then
      rtrnQry(txtUsrNm_3.Text, txtPswd_3.Text, txtFName_3.Text, txtLName_3.Text, _
              txtEmpID_3.Text, cmbProNm3.SelectedItem, cmbAdmin3.SelectedItem, _
              3)
      Dim sqlLogin As String = "Select * from login where user_name ='" & txtUsrNm_3.Text & "'"
      Dim usrVerified As String = DataStore.ExecuteScalar(sqlLogin, sqlConnStr)
      If usrVerified.Length > 0 Then
        Call ClearData(3)
        cnt = cnt + 1
      End If
    End If

Posted 26-Jul-10 4:46am

souvikd

Updated 26-Jul-10 7:50am

William Winner

v3

Add a Solution

Comments

William Winner 28-Jul-10 11:44am

Now you've gone and deleted the "answer" you posted where I responded to your comments.

You don't put every single TextBox into a single List. You have a separate List for each type of TextBox as in:

Private txtUsrNmArr As List(Of TextBox)
Private txtPswdArr As List(Of TextBox)

Each control that you've numbered would get its own List.

Also, you don't need to write a different For Loop to iterate through each one. I know that I did that in my answer, but its unnecessary and you need to learn to think for yourself.

For i = 1 To 15
txtUsrNmArr.Add(Me.Controls.Find("txtUsrNm_" & i, False)(0))
...
cmbAdminArr.Add(Me.Controls.Find("cmdAdmin" & i, False)(0))
Next

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

E.F. Nijboer · Accepted Answer · 2010-07-26T05:32:00

Why don't you simply define an variable to hold the error messages, concatenate the errors to that string using a newline (or better formatting) and show this complete error at the end of your exception handler. Below a simple example to illustrate this but be aware that this code is really poor!!!

Dim strError as string
strError = ""
If txtName = "" then strError = strError & "Name expected!" & ControlChars.NewLine
if txtStreet = "" then strError = strError & "Street expected!" & ControlChars.NewLine

if strError <> "" then MsgBox strError

Good luck!

William Winner · Accepted Answer · 2010-07-26T08:23:00

First of all, do you notice any repetition in your code?

Generally if you have to code anything that is similar more than once, then you need to write a function/sub or use some loops to handle it all, instead of re-writing it 15 times and just changing the numbers each time.

Here's a simple suggestion. Put each type of control in a List. Then, you can just write for loops to cycle through each of them. Here's a simple example. It's a form with 9 CheckBoxes and 9 TextBoxes on it.

VB

Public Class Form1
    Private checkBoxes As List(Of CheckBox)
    Private textBoxes As List(Of TextBox)

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        checkBoxes = New List(Of CheckBox)
        textBoxes = New List(Of TextBox)

        For i = 1 To 9
            checkBoxes.Add(Me.Controls.Find("CheckBox" & i, False)(0))
        Next

        For i = 1 To 9
            textBoxes.Add(Me.Controls.Find("TextBox" & i, False)(0))
        Next
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    For i = 0 To checkBoxes.Count - 1
        If checkBoxes(i).Checked Then
            MessageBox.Show(textBoxes(i).Text)
        End If
    Next
End Sub

End Class

As far as your question goes, here would be one option. You could have the function return an Exception. Then, you just have it look like:

VB

Public Function rtrnQry(ByVal usrNm As String, ByVal psWd As String, ByVal fNm As String, _
                        ByVal lNm As String, ByVal empId As Integer, ByVal proNm As String, _
                        ByVal aDm As String, ByVal rowno As Integer) As Exception
    Using cn As New SqlClient.SqlConnection(sqlConnStr)
        Try
            cn.Open()
        Catch ex As Exception
            MessageBox.Show("Error Opening Connection")

            Return ex
        End Try

        Using cmd As New SqlClient.SqlCommand("InsertLogin", cn)
            With cmd
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@empid", empId)
                .Parameters.AddWithValue("@username", usrNm)
                .Parameters.AddWithValue("@password", psWd)
                .Parameters.AddWithValue("@firstname", fNm)
                .Parameters.AddWithValue("@lastname", lNm)
                .Parameters.AddWithValue("@adminpri", aDm)
                .Parameters.AddWithValue("@proName", proNm)

                Try
                    .ExecuteNonQuery()
                Catch ex As SqlClient.SqlException
                    Return ex
                End Try
            End With
        End Using
    End Using

    Return Nothing
End Function

Then, when you call it, change it to:

VB

Dim returnEx As Exception
returnEx = rtrnQry(txtUsrNm_1.Text, txtPswd_1.Text, txtFName_1.Text, txtLName_1.Text, _
                   txtEmpID_1.Text, cmbProNm1.SelectedItem, cmbAdmin1.SelectedItem, _
                    1)
If returnEx IsNot Nothing Then
    If TypeOf returnEx Is SqlClient.SqlException Then
        If CType(returnEx, SqlClient.SqlException).Number = 2627 Then
            'Add duplicate employee id to duplicateId array
        ElseIf CType(returnEx, SqlClient.SqlException).Number = 208 Then
            'add invalid table name to invalidTableName array (if that's what you want to do)
        End If
    End If
End If

As a final thought, you are misusing the Try/Catch block. If you want the old VB6 On Error GoTo, you can still use it...they've carried it over to .Net. Try/Catch is meant to catch a specific statement. Using it the way you've done won't always tell you the information you need if you had an error. It takes a little bit more effort, but it's the better way to go.

Exception message showing twice

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0