无法更新/编辑数据库 [英] having trouble updating/edit database

问题描述

我想创建一个可以注册个人信息的应用程序。
，但是我在更新/编辑gridview中的数据时遇到问题。
下面是我创建的代码集。

I want to create an application where I can register person info. but I am having a problem updating/edit the data in my gridview. Below is the set of code which I've created.

Imports System.Data.SqlClient
Public Class Form1
Dim connectionString As String
Dim cnn As SqlConnection
Dim cmd As SqlCommand
Dim sql As String

下面是我的ADD语句，它很好用

below is my ADD statement and it work just fine

Private Sub btnADD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADD.Click

    connectionString = "Data Source=XXXX;Initial Catalog=XXXX;User ID=XXXX;Password=XXXX"
    cnn = New SqlConnection(connectionString)
    Try
        cnn.Open()
        cmd = New SqlCommand("INSERT INTO tbl_info (ID,Name) VALUES (@ID,@Name)")
        cmd.Connection = cnn
        With cmd.Parameters
            .AddWithValue("ID", txtID.Text)
            .AddWithValue("Name", txtName.Text)
        End With
        cmd.ExecuteNonQuery()
        MsgBox("has been inserted successfully")

    Catch ex As Exception
        MsgBox(ex.Message())
    End Try


    txtID.Clear()
    txtName.Clear()
End Sub

下面是gridview女巫是链接到我的数据库

below is gridview witch is link to my database

  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'TODO: This line of code loads data into the 'TestDataSet.tbl_info' table. You can move, or remove it, as needed.
    Me.Tbl_infoTableAdapter.Fill(Me.TestDataSet.tbl_info)

End Sub

下面是我的更新声明，我很难判断出什么问题。

below is my update statement witch i'm having a hard time to figure what is wrong.

 Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
    cnn = New SqlConnection(connectionString)
    If Not cnn.State = ConnectionState.Open Then
        cnn.Open()
    End If

    Try
        cmd = New SqlCommand("Update tble_info set Name = @Name Where ID = @ID)")
        cmd.Connection = cnn
        With cmd.Parameters
            .AddWithValue("Name", txtName.Text)
            .AddWithValue("ID", txtID.Text)
        End With
        cmd.ExecuteNonQuery()
        MsgBox("has been update successfully")
    Catch ex As Exception
        MsgBox(ex.Message())
    End Try
   End Sub
End Class

这是我在执行程序时遇到的错误

and this is the error I encountered when I execute the program

InvalidOperationExeption was unhadled
The connectionString property has not been initialize

其指向cnn.open（）

its pointing to cnn.open()

推荐答案

我不确定到底是什么问题，我只能想象 connectionStringI Null 是您尝试使用它时的某种方式，但是我认为这是每次使用完后都关闭连接的好习惯。

I'm not sure what exactly is wrong, I can only imagine that connectionStringI is somehow Null when you are trying to use it, but I think it is good practice to close the connection every time you are done using it.

因此，当您获取数据而不是像现在这样打开连接时，请执行类似的操作

So when you fetch the data instead of leaving the connection open as you do now, do something like

With New SqlConnection(connectionString)
    Try
        .Open()
        ' Do Stuff
    Catch
        ' Handle your exception
    Finally
         .Close()
    End Try
End With

这样，您的连接将总是封闭，你不必担心检查它是否已经打开。

That way your connection will always be closed and you won't have to worry about checking whether or not it is already open.

UPDATE

我已经接受了可以自由地重写代码。您能否尝试一下，看看它是否对您有用/

I have taken the liberty of rewriting your code the way I would do it. Can you please give this a try and see if it works for you/

您的连接字符串属性：

Private ReadOnly Property connectionString As String
    Get
        Return "Data Source=XXXX;Initial Catalog=XXXX;User ID=XXXX;Password=XXXX"
    End Get
End Property

插入数据的子项：

Private Sub btnADD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADD.Click

    Dim transaction As SqlTransaction = Nothing

    With New SqlConnection(connectionString)
        Try
            Call .Open()
            transaction = .BeginTransaction()
            With .CreateCommand()
                .Transaction = transaction
                .CommandText = "INSERT INTO [tbl_info] ([ID], [Name]) VALUES (@ID,@Name);"
                With .Parameters
                    .AddWithValue("ID", txtID.Text)
                    .AddWithValue("Name", txtName.Text)
                End With
                Call .ExecuteNonQuery()
                Call transaction.Commit()
                Call MessageBox.Show("has been inserted successfully")
            End With
        Catch ex As Exception
            Call transaction.Rollback()
            Call MessageBox.Show(ex.Message, "Error")
        Finally
            Call .Close()
        End Try
    End With

    Call txtID.Clear()
    Call txtName.Clear()

End Sub

用于更新数据的子项：

Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click

    Dim transaction As SqlTransaction = Nothing

    With New SqlConnection(connectionString)
        Try
            Call .Open()
            transaction = .BeginTransaction()
            With .CreateCommand()
                .Transaction = transaction
                .CommandText = "UPDATE [tble_info] SET [Name]=@Name WHERE [ID]=@ID;"
                With .Parameters
                    .AddWithValue("Name", txtName.Text)
                    .AddWithValue("ID", txtID.Text)
                End With
                Call .ExecuteNonQuery()
                Call transaction.Commit()
                Call MessageBox.Show("has been update successfully")
            End With
        Catch ex As Exception
            Call transaction.Rollback()
            Call MessageBox.Show(ex.Message, "Error")
        Finally
            Call .Close()
        End Try
    End With

End Sub

如果将连接字符串设为（只读）属性，则假定不会改变，那么您在使用它时就不必担心它是否有价值。然后，您只需要确保连接字符串正确即可。

If you make the connection string a (read-only) property, assuming it doesn't change, then you won't have to worry about whether it is has a value or not when you use it. Then all you have to worry about is make sure that the connection string is correct.

请注意，我尚未测试此代码，但是它应该可以正常工作

Note that I haven't tested this code, but it should work as is I think.

这篇关于无法更新/编辑数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！