添加，更新和删除功能时出现错误 [英] error comes at the time of add ,update and delete function

问题描述

Imports System.Data.SqlClient

Public Class view1
    Inherits System.Web.UI.Page


    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Button1.DataBinding
        If Not IsPostBack Then
            BindGridView()
        End If
    End Sub

    Private Function GetConnectionString() As String
        Return System.Configuration.ConfigurationManager.ConnectionStrings("nitink").ConnectionString
    End Function

    Private Sub BindGridView()
        Dim Customer As New DataTable()
        Dim connection As New SqlConnection("Data Source=KARANDE\sqlexpress;Initial Catalog=nitink;Integrated Security=True")
        Try
            connection.Open()
            Dim sqlStatement As String = "SELECT Top(10)* FROM Customer"
            Dim cmd As New SqlCommand(sqlStatement, connection)
            Dim sqlDa As New SqlDataAdapter(cmd)

            sqlDa.Fill(Customer)
            If Customer.Rows.Count > 0 Then
                GridView1.DataSource = Customer
                GridView1.DataBind()
            End If
        Catch ex As System.Data.SqlClient.SqlException
            Dim msg As String = "Fetch Error:"
            msg += ex.Message
            Throw New Exception(msg)
        Finally
            connection.Close()
        End Try
    End Sub

    Private Sub UpdateOrAddNewRecord(ByVal ID As String, ByVal Company As String, ByVal Name As String, ByVal Title As String, ByVal Address As String, ByVal CounTry As String, ByVal isUpdate As Boolean)
        Dim connection As SqlConnection = New SqlConnection("Data Source=KARANDE\sqlexpress;Initial Catalog=nitink;Integrated Security=True")
        Dim sqlStatement As String = String.Empty

        If Not isUpdate Then
            sqlStatement = "INSERT INTO Customer" + "(CustomerID,CompanyName,ContactName,ContactTitle,Address,Country)" +
"VALUES (@CustomerID,@CompanyName,@ContactName,@ContactTitle,@Address,@Country)"
        Else
            sqlStatement = "UPDATE Customer" + "SET CompanyName = @CompanyName,ContactName = @ContactName," + "ContactTitle = @ContactTitle,Address = @Address,Country = @Country" + "WHERE CustomerID = @CustomerID,"
        End If
        Try
            connection.Open()
            Dim cmd As SqlCommand = New SqlCommand(sqlStatement, connection)
            cmd.Parameters.AddWithValue("@CustomerID", ID)
            cmd.Parameters.AddWithValue("@CompanyName", Company)
            cmd.Parameters.AddWithValue("@ContactName", Name)
            cmd.Parameters.AddWithValue("@ContactTitle", Title)
            cmd.Parameters.AddWithValue("@Address", Address)
            cmd.Parameters.AddWithValue("@Country", CounTry)
            cmd.CommandType = CommandType.Text
            cmd.ExecuteNonQuery()
        Catch ex As System.Data.SqlClient.SqlException
            Dim msg As String = "Insert/Update Error:"
            msg += ex.Message
            Throw New Exception(msg)

        Finally
            connection.Close()
        End Try
    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        UpdateOrAddNewRecord(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, False)

        BindGridView()

    End Sub

    Protected Sub GridView1_RowEditing(sender As Object, e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView1.RowEditing
        GridView1.EditIndex = e.NewEditIndex ' turn to edit mode
        BindGridView()

    End Sub

    Protected Sub GridView1_RowCancelingEdit(sender As Object, e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles GridView1.RowCancelingEdit
        GridView1.EditIndex = -1 'swicth back to default mode
        BindGridView()

    End Sub

    Protected Sub GridView1_RowUpdating(sender As Object, e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
        Dim id As String = GridView1.Rows(e.RowIndex).Cells(0).Text  'ID
        Dim company As String = (CType(GridView1.Rows(e.RowIndex).Cells(1).Controls(0), TextBox)).Text  'Company
        Dim name As String = (CType(GridView1.Rows(e.RowIndex).Cells(2).Controls(0), TextBox)).Text  'Name
        Dim title As String = (CType(GridView1.Rows(e.RowIndex).Cells(3).Controls(0), TextBox)).Text  'Title
        Dim address As String = (CType(GridView1.Rows(e.RowIndex).Cells(4).Controls(0), TextBox)).Text  'Address
        Dim counTry As String = (CType(GridView1.Rows(e.RowIndex).Cells(5).Controls(0), TextBox)).Text  'Country

        Dim UpdateOrAddNewRecord(id, company, name, title, address, counTry, True)
        GridView1.EditIndex = -1
        BindGridView()
    End Sub



    Private Sub DeleteRecord(ByVal ID As String)
        Dim connection As SqlConnection = New SqlConnection("Data Source=KARANDE\sqlexpress;Initial Catalog=nitink;Integrated Security=True")
        Dim sqlStatement As String = "DELETE FROM Customer WHERE CustomerID = @CustomerID"
        Try
            connection.Open()
            Dim cmd As SqlCommand = New SqlCommand(sqlStatement, connection)
            cmd.Parameters.AddWithValue("@ID", ID)
            cmd.CommandType = CommandType.Text
            cmd.ExecuteNonQuery()
        Catch ex As System.Data.SqlClient.SqlException
            Dim msg As String = "Deletion Error:"
            msg += ex.Message
            Throw New Exception(msg)

        Finally 'this is the error spot 
            connection.Close()
        End Try
    End Sub




    Protected Sub GridView1_RowDeleting(sender As Object, e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting
        Dim id As String = GridView1.Rows(e.RowIndex).Cells(0).Text
        DeleteRecord(id) 'call delete method
        BindGridView() 'rebind grid to reflect changes made

    End Sub
End Class

推荐答案

根据您的意见，我为您的问题的一部分提供了解决方案，并为您的问题的另一部分提供了确定问题的方法.首先，简单的部分.

在DeleteRecord Sub中，您将为ID添加一个参数，如下所示:

Based upon your comments, I have a solution for part of your problem and a way to identify the issue for the other part of your problem. First, the easy part.

In the DeleteRecord Sub, you are adding a parameter for the ID like so:

cmd.Parameters.AddWithValue("@ID", ID)

但是，SQL语句正在寻找@CustomerID，而不是@ID.更新的行将如下所示:

However, the SQL statement is looking for @CustomerID, not @ID. The updated line would be like this:

cmd.Parameters.AddWithValue("@CustomerID", ID)

第二部分比较困难.看来您的代码都是正确的.但是，可能是因为运行时值使您失望.我的猜测是，其中一个字段(可能是ID字段)为空白，不应为空.在GridView1_RowUpdating Sub的开始处放置一个断点，并逐步进行操作.我敢打赌，您会立即看到问题所在.逐步浏览每行，然后查看失败的那一行.查看进入该行或正在使用的所有值.我打赌其中一个是null或另一个意外值.

The second part of this is a bit harder. It looks like your code is all correct. However, it might be that a runtime value is tripping you up. My guess would be that one of the fields (probably the ID field) is blank and it shouldn''t be. Put a breakpoint on the start of the GridView1_RowUpdating Sub and walk through what it does. I''m betting you will see the problem right away. Step through each line and then see which line it fails on. Look at all the values that are coming into that line or are being used. I''m betting one of them is a null or another unexpected value.

这篇关于添加，更新和删除功能时出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！