前面的博客介绍了如何使用MSHFGrid和DataGrid把查询到的数据显示出来，有些情况下我们还需要对这些数据进行修改，并且把SQL Server相应记录也进行修改。本文就记录一下这两种控件如何进行数据修改以及sql数据表更新。

      1. 我们还是以用户归档那张表为例子。假设用户归档名字为UA，已经有了一些基础数据如图：

   2. 写一个全局项目模块vbs脚本，用于查询数据：

Sub SearchTable(report,DBName,ssql)
Dim ors,ocom,scon,conn
Dim PCName
Set PCName=HMIRuntime.Tags("@LocalMachineName")
scon="Provider = SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog ='" _
      & DBName & "';Data Source = " & PCName.Read  & "\WINCC"
Set conn=CreateObject("ADODB.Connection")
conn.ConnectionString=scon
conn.Cursorlocation=3
conn.open
Set ors=CreateObject("ADODB.RecordSet")
Set ocom=CreateObject("ADODB.Command")
ocom.commandtype=1
Set ocom.ActiveConnection=conn
ocom.CommandText=ssql
Set ors=ocom.Execute
Set report.datasource=ors
End Sub

   3. 新建一个管理员账户SysAdmin，修改数据必须是这个用户才行。设置ctrl+L快捷键进行用户登录。

    4. Wincc新建画面，放入MSHFGrid控件，名字修改为Report1，放置DataGrid控件，名字修改为Report2。本例简单化不做条件查询，控件里面显示所有的结果。在画面的打开事件中添加以下vbs脚本：

Sub OnOpen()
Dim DBName,ssql,scon,conn
Dim Report1,Report2
Set Report1=ScreenItems("Report1")
Set Report2=ScreenItems("Report2")
Set DBName=HMIRuntime.Tags("@DatasourceNameRT")
ssql="select Curdate as '日期',Curtime as '时间',FT101 as '流量1',FT102 as '流量2',FT103 as '流量3'," _
     & "PT101 as '压力1',PT102 as '压力2',PT103 as '压力3',TT101 as '温度1',TT102 as '温度2',TT103 as '温度3'," _
     & "LT101 as '液位1',LT102 as '液位2',LT103 as '液位3' from UA#UA"
SearchTable report1,DBName.Read ,ssql
SearchTable report2,DBName.Read ,ssql
End Sub

运行后，两个空间会自动的显示sql数据表的数据。

5. Report1控件的点击事件中添加以下vbs脚本

Sub Click(ByVal Item)            
Dim username,DefaultValue,ModifyValue,CurRow,CurCol
Dim Report,DBName,ssql,FieldName,CurID
Set Report=ScreenItems("Report1")
Set username=HMIRuntime.Tags("@CurrentUserName")
Set DBName=HMIRuntime.Tags("@DatasourceNameRT")
If Ucase(username.Read) <> "SYSADMIN" Then  '判断是不是有修改权限
   Msgbox "权限不足，请先登录"
Else
   CurRow=Report.Row
   CurCol=Report.Col
   DefaultValue=Report.TextMatrix(CurRow,CurCol)
   If CurCol=1 Or CurCol=2 Or CurCol=3 Then
     Msgbox "前三列不允许修改"
   Else
    ModifyValue=Inputbox("输入想修改的数值","修改数值",DefaultValue,300,300)
    If (Not IsNumeric(ModifyValue)) Or (ModifyValue="") Then
       Msgbox "输入的不是数字且不为空，请重新输入"
    Else
       Report.TextMatrix(CurRow,CurCol)=ModifyValue
    '更新数据表相应数值
    Select Case CurCol
    Case 4
    FieldName="FT101"
    Case 5
    FieldName="FT102"
    Case 6
    FieldName="FT103"
    Case 7
    FieldName="PT101"
    Case 8
    FieldName="PT102"
    Case 9
    FieldName="PT103"
    Case 10
    FieldName="TT101"
    Case 11
    FieldName="TT102"
    Case 12
    FieldName="TT103"
    Case 13
    FieldName="LT101"
    Case 14
    FieldName="LT102"
    Case 15
    FieldName="LT103"
    End Select
      CurID=Report.TextMatrix(CurRow,1)
      ssql="update UA#UA set " & FieldName & "='" & CStr(Report.TextMatrix(CurRow,CurCol)) & "' where ID='" & CurID & "'"
      SearchTable report,DBName.Read ,ssql
    End If
    End If  
End If
End Sub

6. Report2控件的单击事件添加以下vbs脚本

Sub Click(ByVal Item)                       
Dim username,DefaultValue,ModifyValue,CurRow,CurCol
Dim Report,DBName,ssql,FieldName,CurID
Set Report=ScreenItems("Report2")
Set username=HMIRuntime.Tags("@CurrentUserName")
Set DBName=HMIRuntime.Tags("@DatasourceNameRT")
If Ucase(username.Read) <> "SYSADMIN" Then  '判断是不是有修改权限
   Msgbox "权限不足，请先登录"
Else
   CurRow=Report.Row '当前行号,从0开始的数值
   CurCol=Report.Col '当前列号,从0开始的数值
   DefaultValue=Report.Columns.Item(CurCol).Text
   'Msgbox DefaultValue
   If CurCol=0 Or CurCol=1 Or CurCol=2 Then
     Msgbox "前三列不允许修改"
   Else
    ModifyValue=Inputbox("输入想修改的数值","修改数值",DefaultValue,300,300)
    If (Not IsNumeric(ModifyValue)) Or (ModifyValue="") Then
       Msgbox "输入的不是数字且不为空，请重新输入"
    Else
       Report.Row=CurRow
       Report.Text =CStr(ModifyValue)
     
    '更新数据表相应数值
    Select Case CurCol
    Case 3
    FieldName="FT101"
    Case 4
    FieldName="FT102"
    Case 5
    FieldName="FT103"
    Case 6
    FieldName="PT101"
    Case 7
    FieldName="PT102"
    Case 8
    FieldName="PT103"
    Case 9
    FieldName="TT101"
    Case 10
    FieldName="TT102"
    Case 11
    FieldName="TT103"
    Case 12
    FieldName="LT101"
    Case 13
    FieldName="LT102"
    Case 14
    FieldName="LT103"
    End Select
    ' 取得ID数值
      report.Row=CurRow
      report.Col=0
      CurID=Report.Text
      ssql="update UA#UA set " & FieldName & "='" & ModifyValue & "' where ID='" & CurID & "'"
      SearchTable report,DBName.Read ,ssql
      ssql="select ID,Curdate as '日期',Curtime as '时间',FT101 as '流量1',FT102 as '流量2',FT103 as '流量3'," _
     & "PT101 as '压力1',PT102 as '压力2',PT103 as '压力3',TT101 as '温度1',TT102 as '温度2',TT103 as '温度3'," _
     & "LT101 as '液位1',LT102 as '液位2',LT103 as '液位3' from UA#UA"
     SearchTable report,DBName.Read ,ssql
    End If
    End If  
End If

End Sub