VB.NET导入Excel数据到SQL Server时@harga_jual语法错误修复咨询

核心错误原因 #

你的INSERT语句在VALUES子句末尾缺少闭合的右括号，这就是报错“'@harga_jual'附近语法不正确”的直接原因。

1. 修复后的SQL语句 #

insert into Pr_masterItem
(
    item_id,
    item_name,
    inventory,
    subtitutes_exist,
    assembly_bom,
    satuan,
    cost_is_adjusted,
    harga_beli,
    harga_jual
)
values
(
    @item_id,
    @item_name,
    @inventory,
    @subtitutes_exist,
    @assembly_bom,
    @satuan,
    @cost_is_adjusted,
    @harga_beli,
    @harga_jual
) -- 补上缺失的右括号

2. 优化后的VB.NET代码 #

除修复SQL语法，还优化了连接管理、空值处理和参数类型匹配，避免潜在问题：

Sub import2DB()
    ' 使用Using语句自动释放连接资源，防止泄漏
    Using conn2 As New SqlConnection("Data Source=172.16.250.3;Initial Catalog=KIAS;User ID=sa;Password=p4yr0ll;MultipleActiveResultSets=True")
        conn2.Open()
        ' 循环外定义SQL语句，重复利用Command提升效率
        Dim sql As String = "insert into Pr_masterItem
                            (item_id, item_name, inventory, subtitutes_exist, assembly_bom, satuan, cost_is_adjusted, harga_beli, harga_jual)
                            values (@item_id, @item_name, @inventory, @subtitutes_exist, @assembly_bom, @satuan, @cost_is_adjusted, @harga_beli, @harga_jual)"
        
        Using cmd2 As New SqlCommand(sql, conn2)
            ' 提前定义参数并匹配SQL字段类型（需根据你的表结构调整）
            cmd2.Parameters.Add("@item_id", SqlDbType.VarChar)
            cmd2.Parameters.Add("@item_name", SqlDbType.VarChar)
            cmd2.Parameters.Add("@inventory", SqlDbType.Int)
            cmd2.Parameters.Add("@subtitutes_exist", SqlDbType.Bit)
            cmd2.Parameters.Add("@assembly_bom", SqlDbType.Bit)
            cmd2.Parameters.Add("@satuan", SqlDbType.VarChar)
            cmd2.Parameters.Add("@cost_is_adjusted", SqlDbType.Bit)
            cmd2.Parameters.Add("@harga_beli", SqlDbType.Decimal)
            cmd2.Parameters.Add("@harga_jual", SqlDbType.Decimal)

            For Each row As DataGridViewRow In DataGridView1.Rows
                ' 跳过DataGridView默认的新增空行
                If row.IsNewRow Then Continue For

                ' 处理空值，避免空引用异常
                cmd2.Parameters("@item_id").Value = If(row.Cells("item_id").Value Is DBNull.Value Or row.Cells("item_id").Value Is Nothing, DBNull.Value, row.Cells("item_id").Value.ToString)
                cmd2.Parameters("@item_name").Value = If(row.Cells("item_name").Value Is DBNull.Value Or row.Cells("item_name").Value Is Nothing, DBNull.Value, row.Cells("item_name").Value.ToString)
                cmd2.Parameters("@inventory").Value = If(row.Cells("inventory").Value Is DBNull.Value Or row.Cells("inventory").Value Is Nothing, DBNull.Value, Convert.ToInt32(row.Cells("inventory").Value))
                cmd2.Parameters("@subtitutes_exist").Value = If(row.Cells("subtitutes_exist").Value Is DBNull.Value Or row.Cells("subtitutes_exist").Value Is Nothing, DBNull.Value, Convert.ToBoolean(row.Cells("subtitutes_exist").Value))
                cmd2.Parameters("@assembly_bom").Value = If(row.Cells("assembly_bom").Value Is DBNull.Value Or row.Cells("assembly_bom").Value Is Nothing, DBNull.Value, Convert.ToBoolean(row.Cells("assembly_bom").Value))
                cmd2.Parameters("@satuan").Value = If(row.Cells("satuan").Value Is DBNull.Value Or row.Cells("satuan").Value Is Nothing, DBNull.Value, row.Cells("satuan").Value.ToString)
                cmd2.Parameters("@cost_is_adjusted").Value = If(row.Cells("cost_is_adjusted").Value Is DBNull.Value Or row.Cells("cost_is_adjusted").Value Is Nothing, DBNull.Value, Convert.ToBoolean(row.Cells("cost_is_adjusted").Value))
                cmd2.Parameters("@harga_beli").Value = If(row.Cells("harga_beli").Value Is DBNull.Value Or row.Cells("harga_beli").Value Is Nothing, DBNull.Value, Convert.ToDecimal(row.Cells("harga_beli").Value))
                cmd2.Parameters("@harga_jual").Value = If(row.Cells("harga_jual").Value Is DBNull.Value Or row.Cells("harga_jual").Value Is Nothing, DBNull.Value, Convert.ToDecimal(row.Cells("harga_jual").Value))

                cmd2.ExecuteNonQuery()
            Next
        End Using
    End Using
    MessageBox.Show("Import Berhasil")
End Sub

优化说明 #

• 连接管理：用Using自动释放连接，避免手动关闭遗漏导致的连接泄漏；将连接打开放在循环外，减少数据库连接开销。
• 参数匹配：提前指定参数的SQL类型，避免AddWithValue可能引发的类型不匹配问题。
• 空值处理：判断单元格值是否为空，避免空值转换引发的异常。
• 跳过空行：过滤DataGridView默认的新增空行，避免无效插入。

内容的提问来源于stack exchange，提问作者Ari's