sql-server - 基于文本框的 SQL Server 删除/插入不起作用。- 对比 2010

Question

我的 VS 2010 程序有一个 Datagridview，它从 SQL Server 数据库加载数据，现在我添加了几个文本框来添加新行和更新现有行。

这是按钮的子：

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.IO


Public Class macmaster

'variables to be used'  
Dim myCmd As SqlCommand         'create SQL command'
Dim myReader As SqlDataReader   'pull data'
Dim dt As New DataTable         'store data'
Dim intResult As MsgBoxResult
Dim strSQL As String

 Private Sub MacSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MacSave.Click

    Dim myConn = New SqlConnection("server=server;database=SQL;uid=pro;pwd=abcd") 'form connection'

    Try 'alert user if any fields not filled in'

        If Me.MacNm.Text.Trim = "" Then

            '**this portion of code not relevant**'

        Else 'proceed next step if all fields ok.'

            intResult = MsgBox("Are you sure to Add/Update?", MsgBoxStyle.OkCancel)
            If intResult = MsgBoxResult.Ok Then

                'call connection (myConn)'
                myConn.Open()

                myCmd.CommandText = "DELETE FROM [SQL].[dbo].[Mac] WHERE Machine='" & MacNm.Text & "'"

                myCmd.CommandText = "INSERT INTO [SQL].[dbo].[Mac](Machine, PIC, Die) VALUES('" & MacNm.Text & "','" & MacPIC.Text & "','" & MacDie.Text & "')"

                MsgBox("Record Added!")

                myConn.Close()

            End If

        End If

    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, "btnAdd_Click Exception")
    End Try

End Sub
End Class

在测试期间，我尝试了按钮。代码一直运行，直到我收到消息框说记录已添加。

但是检查 SQL Server 数据库，我发现数据甚至没有改变。两部分 SQL 查询均未触发。我什至还在 SQL Server 上测试了这两个查询。工作正常。是我如何引用有问题的文本框吗？

标签： sql-servervb.netvisual-studio-2010

Answer 1

您缺少 ExecuteNonQuery()。

myCmd = New SqlCommand()

myCmd.Connection= myConn

mnyCmd.CommandType= CommandType.Text

Dim commands As String
commands= "DELETE FROM [SQL].[dbo].[Mac] WHERE Machine='" & MacNm.Text & "';"&"INSERT INTO [SQL].[dbo].[Mac](Machine, PIC, Die) VALUES('" & MacNm.Text & "','" & MacPIC.Text & "','" & MacDie.Text & "')"

myCmd.CommandText = commands

myCmd.ExecuteNonQuery()

MsgBox("Record Added!")