保存表格数据到sql server数据库(适合带业务单号和发生日期，比如仓库出入库单据的保存)

本文链接：https://blog.youkuaiyun.com/wf_csdn2018/article/details/79033133

这个VB子程序用于将VSFGrid中的数据保存到SQL Server数据库中，特别是带有业务单号（Ywdh）和发生日期（Fsrq）的记录。通过Ywdh和Fsrq组合来查找或新增记录，以最后一列的uniqueidentifier作为主键。程序首先根据Ywdh和fl_Id获取主键值，然后遍历表格数据，更新或插入到相应的表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

'************************************ LMS 2017.10 ( SaveVSFGrid_ByYwdhFsrq ) ************************************
'* 1、保存VSFGrid数据(有Ywdh，fl_id，fsrq)：通过ywdh+fsrq，以最后一列为主键值Pmkey（是uniqueidentifier型），并且表里面必须有"Fsrq+Ywdh+fi_id" 字段。（例如：仓库分主表Xxxx_Main和字表Xxxx_Sub，主表里面同时有Ywdh+fsrq，字表里面有Ywdh+fl_id）
'* 根据 Ywdh,按照fl_Id 排序，找到对应的Pmkey ； gridcol_fieldlx=0，该字段在表中不存在或者是uniqueidentifier型（数据库字段得到值），不需要保存 )
'* 参数：Table_Name：要保存的表；Grid_Code：对应在Xt_Grid表中的代码（要保存表的字段和VSFGrid相对应的列）；SaveMessage，是否要提示保存成功的信息
'************************************ LMS 2017.10 ( SaveVSFGrid_ByYwdhFsrq ) ************************************
Public Sub SaveVSFGrid_ByYwdhFsrq(VSFGrid As VSFlexGrid, Table_Name As String, Grid_Code As String, Ywdh As String, Fsrq As String, Optional SaveMessage As Boolean = True)
With VSFGrid
If .Rows = .FixedRows Then Exit Sub
Dim rst As New ADODB.RecordSet, SQL As String, II As Long
Dim rst1 As New ADODB.RecordSet, KK As Integer, Field_Name As String

'On Error GoTo err_cl
DataEnv.DataConn.BeginTrans
For II = .FixedRows To .Rows - 1
Set rst = Nothing
If Trim(.TextMatrix(II, .Cols - 1)) = "" Then
SQL = "select * from " + Table_Name + " where ywdh='" + Ywdh + "' and 1=2"
Else
SQL = "select * from " + Table_Name + " where ywdh='" + Ywdh + "' and Pmkey='" + Trim(.TextMatrix(II, .Cols - 1)) + "'" '-----主键值Pmkey"
End If
rst.Open SQL, DataEnv.DataConn, adOpenDynamic, adLockOptimistic
If rst.EOF And rst.BOF Then rst.AddNew
rst("ywdh") = Ywdh
rst("fsrq") = Fsrq
rst("fl_id") = IIf(Val(.TextMatrix(II, 0)) = 0, 1, Val(.TextMatrix(II, 0))) '---数据库中 fl_id字段默认值=1
For KK = 1 To .Cols - 1 '----求列对应的表中的字段名（例如：第1列--编码、第2列--名称等,还要判断该字段是什么类型：文本型=1，数值型=2，日期型=3，逻辑型=4，uniqueidentifier型 =0）
Set rst1 = Nothing
Set rst1 = DataEnv.DataConn.Execute("select * from Xt_Grid where Grid_Code='" + Trim(Grid_Code) + "' and gridcol_int=" & KK)
If Not (rst1.EOF And rst1.BOF) Then
Field_Name = Trim(rst1("gridcol_fieldname"))
If Field_Name <> "" Then
If rst1("gridcol_fieldlx") = 1 Then '----文本型=1
rst(Field_Name) = Trim(.TextMatrix(II, KK))
ElseIf rst1("gridcol_fieldlx") = 2 Then '----数值型=2
rst(Field_Name) = Val(.TextMatrix(II, KK))
ElseIf rst1("gridcol_fieldlx") = 3 Then '----日期型=3
rst(Field_Name) = IIf(Trim(.TextMatrix(II, KK)) = "", Glmk.PXtrq, Format(Trim(.TextMatrix(II, KK)), "yyyy-mm-dd"))
ElseIf rst1("gridcol_fieldlx") = 4 Then '----逻辑型（Bit）=4
If .TextMatrix(II, KK) = "" Then
rst(Field_Name) = 0
Else
rst(Field_Name) = IIf(.TextMatrix(II, KK), 1, 0)
End If '----rst(Field_Name) = IIf(.TextMatrix(II, KK), 1, 0)
ElseIf rst1("gridcol_fieldlx") = 0 Then '----该字段是PmKey型（uniqueidentifier型 =0），数据自动生成不需要保存；或者该字段在表中不存在，所以也不需要保存。

End If
End If
End If
If KK = .Cols - 2 Then Exit For '----VSFGrid最后一列是Pmkey字段
Next KK
rst.Update
Next II
'---- 以最后一列为主键值
Set rst = Nothing
SQL = "select * from " + Table_Name + " where ywdh='" + Ywdh + "' order by fl_id"
Set rst = DataEnv.DataConn.Execute(SQL)
rst.MoveFirst: II = .FixedRows
Do While Not rst.EOF
.TextMatrix(II, .Cols - 1) = rst("Pmkey") '唯一标识号
rst.MoveNext
II = II + 1
Loop
End With
DataEnv.DataConn.CommitTrans
Set rst = Nothing: Set rst1 = Nothing
If SaveMessage Then MsgBox "系统已经成功保存数据！", vbOKOnly + vbExclamation, "系统提示"
Exit Sub

err_cl:
DataEnv.DataConn.RollbackTrans
Set rst = Nothing: Set rst1 = Nothing
MsgBox Err.Description, vbOKOnly + vbCritical, "系统提示"
End Sub