优化 VBA 代码：使用参数化查询插入数据到 TestNo 表

以下代码展示了如何使用参数化查询将数据插入到 TestNo 表中，并对代码进行了优化，以提高可读性和安全性。

Dim sql As String
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter

Set conn = New ADODB.Connection
conn.Open "ConnectionString" '替换为实际的连接字符串

sql = "INSERT INTO TestNo (TestNo, PlayTime, TestPreceptName, SaveFileName, sampleShape, Area, GaugeLength, IsLogicDel, photoPixJishu, ElasticBeginDotPos, ElasticEndDotPos, UpYieldDotPos, DownYieldDotPos, MaxDotPos, RpPos, RtPos, RpValue, RtValue, SampleWidth, SampleThick, SampleDia, SampleMinDia, SampleOutDia, SampleInnerDia, DeformSensorName, ExtSensorTotal, ExtSensorDeviceName1, ExtSensorDeviceName2, ExtSensorDeviceName3, ExtSensorDeviceName4, ExtSensorDeviceName5, ExtSensorDeviceName6) VALUES (?, ?, ?, ?, 1, ?, ?, ?, ?, ?, ?, 0, ?, ?, ?, 35, 0.2, 0.5, ?, ?, 0, 0, 0, 0, 3, 0, '扩展设备9', '扩展设备10', '扩展设备11', '扩展设备12', '扩展设备13', '扩展设备14')"

Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = conn
    .CommandText = sql
    Set param = .CreateParameter("TestNo", adVarChar, adParamInput, 255, Range("G" & i).Value)
    .Parameters.Append param
    Set param = .CreateParameter("PlayTime", adVarChar, adParamInput, 255, pt)
    .Parameters.Append param
    Set param = .CreateParameter("TestPreceptName", adVarChar, adParamInput, 255, Range("M4").Value)
    .Parameters.Append param
    Set param = .CreateParameter("SaveFileName", adVarChar, adParamInput, 255, Range("M5").Value)
    .Parameters.Append param
    Set param = .CreateParameter("Area", adVarChar, adParamInput, 255, area)
    .Parameters.Append param
    Set param = .CreateParameter("GaugeLength", adVarChar, adParamInput, 255, Range("D" & i).Value)
    .Parameters.Append param
    Set param = .CreateParameter("IsLogicDel", adVarChar, adParamInput, 255, myBoolean)
    .Parameters.Append param
    Set param = .CreateParameter("photoPixJishu", adVarChar, adParamInput, 255, Sheets("参考数据").Range("M" & i).Value)
    .Parameters.Append param
    Set param = .CreateParameter("ElasticBeginDotPos", adVarChar, adParamInput, 255, Sheets("参考数据").Range("N" & i).Value)
    .Parameters.Append param
    Set param = .CreateParameter("ElasticEndDotPos", adVarChar, adParamInput, 255, Sheets("参考数据").Range("O" & i).Value)
    .Parameters.Append param
    Set param = .CreateParameter("DownYieldDotPos", adVarChar, adParamInput, 255, Sheets("参考数据").Range("P" & i).Value)
    .Parameters.Append param
    Set param = .CreateParameter("MaxDotPos", adVarChar, adParamInput, 255, Sheets("参考数据").Range("Q" & i).Value)
    .Parameters.Append param
    Set param = .CreateParameter("RpPos", adVarChar, adParamInput, 255, Sheets("参考数据").Range("P" & i).Value)
    .Parameters.Append param
    Set param = .CreateParameter("SampleWidth", adVarChar, adParamInput, 255, Range("E" & i).Value)
    .Parameters.Append param
    Set param = .CreateParameter("SampleThick", adVarChar, adParamInput, 255, Range("F" & i).Value)
    .Parameters.Append param
    .Execute
End With

conn.Close
Set conn = Nothing
Set cmd = Nothing
Set param = Nothing

代码优化点：

• 使用参数化查询：将 SQL 语句中的值替换为参数，避免 SQL 注入攻击，提高代码安全性。
• 提取重复代码：将重复的 CreateParameter 代码提取到一个循环中，减少代码冗余，提高代码可读性。
• 使用常量：将重复出现的数字值提取为常量，方便修改和维护。
• 使用 With 语句：简化代码结构，提高代码可读性。

注意事项：

• 将代码中的 "ConnectionString" 替换为实际的连接字符串。
• 确保代码中使用的变量类型和数据类型与数据库表中的字段类型匹配。
• 仔细检查代码逻辑，确保代码能够正确地插入数据。

示例：

以下代码展示了如何使用循环提取重复代码，并使用常量：

Dim sql As String
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Dim i As Integer

'定义常量
Const SAMPLE_SHAPE As Integer = 1
Const UP_YIELD_DOT_POS As Integer = 0
Const DEFORM_SENSOR_NAME As Integer = 3
Const EXT_SENSOR_TOTAL As Integer = 0
Const RP_POS As Integer = 35
Const RT_POS As Integer = 0.2
Const RT_VALUE As Integer = 0.5
Const SAMPLE_DIA As Integer = 0
Const SAMPLE_MIN_DIA As Integer = 0
Const SAMPLE_OUT_DIA As Integer = 0
Const SAMPLE_INNER_DIA As Integer = 0

'连接字符串
Set conn = New ADODB.Connection
conn.Open "ConnectionString"

sql = "INSERT INTO TestNo (TestNo, PlayTime, TestPreceptName, SaveFileName, sampleShape, Area, GaugeLength, IsLogicDel, photoPixJishu, ElasticBeginDotPos, ElasticEndDotPos, UpYieldDotPos, DownYieldDotPos, MaxDotPos, RpPos, RtPos, RpValue, RtValue, SampleWidth, SampleThick, SampleDia, SampleMinDia, SampleOutDia, SampleInnerDia, DeformSensorName, ExtSensorTotal, ExtSensorDeviceName1, ExtSensorDeviceName2, ExtSensorDeviceName3, ExtSensorDeviceName4, ExtSensorDeviceName5, ExtSensorDeviceName6) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = conn
    .CommandText = sql

    '循环添加参数
    For i = 0 To 31
        Set param = .CreateParameter("value" & i, adVarChar, adParamInput, 255, ...)
        .Parameters.Append param
    Next i

    .Execute
End With

conn.Close
Set conn = Nothing
Set cmd = Nothing
Set param = Nothing

通过以上优化，代码的可读性和安全性得到提升，更容易维护和扩展。建议在实际开发中使用参数化查询，并尽量使用常量，以提高代码的质量和效率。