搬家第五天-95.Wincc V7.3 grid表格控件修改数值
前面的博客介绍了如何使用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
