异常消息显示两次 [英] Exception message showing twice

问题描述

我正在尝试向数据库插入数据时显示特定sql异常代码的消息，我正在使用以下代码.我正在尝试从表单中向数据库插入15位用户，而我的目标是显示一条针对所有失败插入的单条消息，并带有行号(因为我的表单看起来像一张excel工作表)，我编写了以下代码:出现两次，并且很清楚为什么会发生这种情况，但是我无法实现我的目标-由于我是.net的新手，请提供帮助，请检查以下代码以获得更好的理解

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

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

现在，我将其称为proc:

Now i m calling this proc:

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

推荐答案

为什么不简单地定义一个变量来保存错误消息，使用换行符(或更好的格式)将错误连接到该字符串，并在异常处理程序的末尾显示此完整错误.下面是一个简单的例子来说明这一点，但要注意，这段代码确实很糟糕！！！

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!

首先，您是否注意到代码中有重复?

通常，如果您必须编写多次相似的代码，则需要编写一个函数/子程序或使用一些循环来处理所有内容，而不必重写15次并每次都更改数字. />
这是一个简单的建议.将每种类型的控件放在一个列表中.然后，您可以编写for循环来循环遍历每个循环.这是一个简单的例子.它是带有9个CheckBoxes和9个TextBoxes的表单.

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.

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

就您的问题而言，这将是一个选择.您可以让函数返回Exception.然后，您的外观如下:

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:

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:

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

最终的想法是，您滥用了Try/Catch块.如果您想要旧的VB6 On Error GoTo，仍然可以使用它...将它们带到.Net. Try/Catch旨在捕获特定的语句.如果您遇到错误，则使用它的方法不会总是告诉您所需的信息.这需要花费更多的精力，但这是更好的方法.

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.

这篇关于异常消息显示两次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！