|
|
数据显示表格
'================================================================================================================
Function GetDbStr()
'连接数据库字符串定义
GetDbStr = "Provider=MSDASQL;DSN=mydb;UID=;PWD=;"
End Function
'================================================================================================================
Function GetSqlSelectStr(Byval strTableName)
'查询表格字符串
GetSqlSelectStr = "SELECT * FROM [" & strTableName & "]"
End Function
'=================================================================================
'品牌表格样式
'=================================================================================
Sub FlexGridTitle(Byval FlexGrid)
Dim i
With FlexGrid
.Clear'清除内容
.Rows = 10
.Cols =23
.ColWidth(0) = 1
For i = 1 To 21 Step 1
.ColWidth(i) = 2300
Next
.ColWidth(1) = 5000
.ColWidth(22) = 5000
.ForeColor = RGB(0,114,197)
.TextMatrix(0,0) = "序号No"
.TextMatrix(0,1) = "名称Name"
.TextMatrix(0,2) = "角刀H-Knife"
.TextMatrix(0,3) = "AB刀AB-Knife"
.TextMatrix(0,4) = "BC刀BC-Knife"
.TextMatrix(0,5) = "CD刀CD-Knife"
.TextMatrix(0,6) = "DE刀DE-Knife"
.TextMatrix(0,7) = "宽度Wide"
.TextMatrix(0,8) = "厚度Thick"
.TextMatrix(0,9) = "总长Long"
.TextMatrix(0,10) = "开槽相位Box-Slot"
.TextMatrix(0,11) = "开槽箱高Box-Height"
.TextMatrix(0,12) = "左挡板Left"
.TextMatrix(0,13) = "右挡板Right"
.TextMatrix(0,14) = "印刷1相位Printer1"
.TextMatrix(0,15) = "印刷2相位Printer2"
.TextMatrix(0,16) = "印刷3相位Printer3"
.TextMatrix(0,17) = "印刷4相位Printer4"
.TextMatrix(0,18) = "印刷5相位Printer5"
.TextMatrix(0,19) = "印刷6相位Printer6"
.TextMatrix(0,20) = "模切相位Die-Cutter"
.TextMatrix(0,21) = "计数Count"
.TextMatrix(0,22) = "说明Note"
For i = 0 To 22 Step 1
.ColAlignment(i) = 4'列内容中间居中。
Next
.ColAlignment(22) = 1
'
'.MergeRow(0) = True
'.MergeRow(1) = True
''.MergeRow(2) = True
'
'.MergeCol(0) = True
'.MergeCol(1) = True
'.MergeCol(2) = True
'.MergeCol(3) = True
'.MergeCol(4) = True
'.MergeCol(5) = True
'.MergeCol(6) = True
'.MergeCol(7) = True
'.MergeCol(8) = True
'.MergeCol(9) = True
'.MergeCol(10) = True
'.MergeCells = 1
' .CellBackColor = vbBlue
' .CellForeColor = vbWhite
End With
End Sub
'=================================================================================
'品牌数据查询
'=================================================================================
Function sqlSelect(Byval strSQL, Byval FlexGrid)
Dim cn
Set cn = CreateObject("ADODB.Connection")
cn.Open GetDbStr(),1,3
If (cn.State = 0) Then
MsgBox("数据库连接失败，请重试！")
Exit Function
Else
'MsgBox("DB Connection Success" & vbNewLine)
End If
Dim rs
Set rs = CreateObject("ADODB.Recordset")
rs.open strSQL, cn, 1, 3
'If rs.eof And rs.bof Then
'MsgBox("没有符合要求的记录")
'cn.Close()
'cn = Nothing
'Exit Sub
'End If
Dim Brand_id
Dim Brand_brand_id
Dim Brand_code
Dim i,j
i = 0
j = 0
flexGrid.Rows = rs.recordcount+2
'HMIRuntime.Trace rs.recordcount
For i = 0 To (rs.recordcount - 1) Step 1
For j = 0 To 21 Step 1
flexGrid.TextMatrix(i+1, j) = rs.Fields(j).Value
'HMIRuntime.Trace rs.Fields(j).Value & vbNewLine
Next
rs.MoveNext
Next
sqlSelect = rs.recordcount
rs.close
cn.Close
Set cn = Nothing
End Function
'=================================================================================
'品牌数据编辑
'=================================================================================
Sub sqlEdit( Byval ID,Byval brand_id, Byval BrandName, Byval SmokeLong , Byval SmokeWide, Byval SmokeHigh, Byval CartonLong, Byval CartonWide, Byval CartonHigh, Byval Weight, Byval Code)
Dim cn
Set cn= CreateObject("ADODB.Connection")
cn.Open GetDbStr()
If (cn.State = 0) Then
HMIRuntime.Trace "DB Connection Fail" & vbNewLine
MsgBox "数据库连接失败，请重试！"
Exit Sub
Else
'HMIRuntime.Trace "DB Connection Success" & vbNewLine
End If
Dim strSQL
StrSQL = "UPDATE [myDatabase].[dbo].[dt_brand] SET"
StrSQL = StrSQL + "[brand_id] = '" & brand_id & "',[brand] = '" & BrandName & "',[SmokeLong] = '" & SmokeLong & "',[SmokeWide] = '" & SmokeWide & "',[SmokeHigh] = '" & SmokeHigh
StrSQL = StrSQL + "',[CartonLong] = '" & CartonLong & "',[CartonWide] = '" & CartonWide & "',[CartonHigh] = '" & CartonHigh
StrSQL = StrSQL + "',[Weight] = '" & Weight & "' ,[code] = '" & Code & "' WHERE [id] = '" & ID & "' "
StrSQL = StrSQL + " delete dt_brand where id not in (select max(id) as id from dt_brand group by code) "
'StrSQL = StrSQL + " alter table dt_brand drop column id alter table dt_brand add id int identity(1,1)"
'HMIRuntime.Trace StrSQL
cn.Execute strSQL
cn.Close
Set cn = Nothing
End Sub
'=================================================================================
'品牌数据插入
'=================================================================================
Sub sqlInsertBrand( Byval ID,Byval brand_id, Byval BrandName, Byval SmokeLong , Byval SmokeWide, Byval SmokeHigh, Byval CartonLong, Byval CartonWide, Byval CartonHigh, Byval Weight, Byval Code)
Dim cn
Set cn= CreateObject("ADODB.Connection")
cn.Open GetDbStr()
If (cn.State = 0) Then
' HMIRuntime.Trace "DB Connection Fail" & vbNewLine
MsgBox "数据库连接失败，请重试！"
Exit Sub
Else
'HMIRuntime.Trace "DB Connection Success" & vbNewLine
End If
Dim strSQL
StrSQL = "INSERT INTO [myDatabase].[dbo].[dt_brand] ([brand_id],[brand],[SmokeLong],[SmokeWide],[SmokeHigh],[CartonLong],[CartonWide],[CartonHigh],[Weight],[Code]) "
StrSQL = StrSQL + "VALUES ('" & brand_id & "' ,'" & BrandName & "' ,'" & SmokeLong & "' ,'" & SmokeWide & "' ,'" & SmokeHigh & "' ,"
StrSQL = StrSQL + "'" & CartonLong & "' ,'" & CartonWide & "' ,'" & CartonHigh & "' ,'" & Weight & "' ,'" & Code &"')"
'StrSQL = StrSQL + " delete dt_brand where id not in (select max(id) as id from dt_brand group by code) "'(select max(id) as UserID from dt_brand group by name,code) "
'StrSQL = StrSQL + " alter table dt_brand drop column id alter table dt_brand add id int identity(1,1)"
'HMIRuntime.Trace StrSQL & vbNewLine
cn.Execute strSQL
cn.Close
Set cn = Nothing
End Sub |
|
发表于 2023-3-10 18:32:25