编译环境：VS2010 Ultimate+SQL Server 2008
VB.NET中UPDATE语句执行后没有报错，数据库也没有更新，但是把语句放在Textbox中提出来，在SQL Server中直接执行是成功的
与数据库是可以连接的，在其他窗体中也可以执行UPDATE语句、INSERT语句，SELECT语句
``` Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim con As New SqlConnection(connectstring)
Dim sqlcmd As New SqlCommand
Dim record As Integer
Dim i As Integer
Dim MEDSID As String
Dim DPQTY As String
Dim DPTIME As String
Dim MGRID As String
DPTIME = Now.ToString("yyyy/mm/dd hh:mm:ss")
MGRID = login.user1
Try
con.Open()
For i = 0 To ds.Tables(0).Rows.Count - 1
MEDSID = ds.Tables(0).Rows(i).Item("药品ID")
DPQTY = ds.Tables(0).Rows(i).Item("药品数量")
txtsql = "UPDATE dbo.MEDS" & vbCrLf & " SET STOCK=STOCK-" & DPQTY & vbCrLf & " WHERE MEDSID='" & MEDSID & "'"
TextBox1.Text = txtsql
'sqlcmd = New SqlCommand(updatetxt, con)
record = executesql(txtsql, 3)
MsgBox(Str(record))
Next
da.UpdateCommand = New SqlCommand("UPDATE dbo.LIST_ORDER SET MGRID='" & MGRID & "', STATUS='已发货' & DPTIME='" & DPTIME & "' WHERE ORDERNUMBER='" & ORDERNUMBER & "'", con)
Catch ex As Exception
MsgBox("NO")
Exit Sub
End Try
End Sub
executesql函数的代码如下：
Imports System.Data.SqlClient
Module Module1
Public Function connectstring() As String
connectstring = "Data Source=(local);Initial Catalog=PMIS;Persist Security Info=True;User ID=sa;Password=000000"
End Function
Public DBSET As DataSet
Public txtsql As String
Public Function executesql(ByVal strsql As String, ByRef errmsg As String) As Integer
Dim cnn As SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand()
Dim adpt As SqlClient.SqlDataAdapter
Dim rst As New DataSet
Dim splitsql() As String
errmsg = ""
Try
splitsql = Split(strsql)
cnn = New SqlClient.SqlConnection(connectstring)
If InStr("INSERT,DELETE,UDATE", UCase(splitsql(0))) Then
cmd.Connection = cnn
cmd.Connection.Open()
cmd.CommandText = strsql
executesql = cmd.ExecuteNonQuery()
Else
adpt = New SqlClient.SqlDataAdapter(strsql, cnn)
adpt.Fill(rst)
executesql = rst.Tables(0).Rows.Count
DBSET = rst
End If
Catch ex As Exception
errmsg = ex.Message
executesql = -1
Finally
rst = Nothing
cnn = Nothing
End Try
End Function
End Module
