VB.NET插入SQL日期时遇字符串转日期/时间失败错误求助

编程新手，向SQL表插入日期时遇到System.Data.SqlClient.SqlException错误，提示：

Conversion failed when converting date and/or time from character string.

尝试过多种日期格式（yyyy/MM/dd、dd/MM/yyyy等）均无效。程序用于月度付款控制，通过DateTimePicker选择首个付款日期后自动计算后续x次（vezes）付款记录。

SQL表结构 #

[Id]        INT           NOT NULL,
[Date]      DATE          NOT NULL,
[Vezes]     INT           NOT NULL,
[Valor]     VARCHAR (150) NOT NULL,
[Recebedor] VARCHAR (150) NOT NULL,
[Processo]  VARCHAR (150) NULL,
[Parcela]   INT           NOT NULL,
[Situação]  BIT           NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

VB.NET代码 #

If con.State = ConnectionState.Open Then
        con.Close()

    End If
    con.Open()


Dim dates As Date
Dim vezes As Double
Dim valor As String
    Dim recebedor As String
    Dim processo As String
    Dim parcela As Int32
    Dim situacao As Int32

    vezes = TextBox1.Text


While vezes > 0

        data = Convert.ToDateTime(DateTimePicker1.Value)
        valor = "R$" + TextBox3.Text
        recebedor = TextBox2.Text
        If MaskedTextBox3.Text <> "" Then
            processo = TextBox2.Text + "/" = MaskedTextBox3.Text
        Else
            processo = TextBox2.Text
        End If

        parcela = 1
        situacao = 0


    cmd = con.CreateCommand()
    cmd.CommandType = CommandType.Text
    cmd.CommandText = "insert into Pagamentos(date, vezes, valor, recebedor, processo, parcela, situação) values ('" & Dates & "','" & vezes & "','" & valor & "','" & recebedor & "','" & processo & "','" & parcela & "','" & situacao & "')"
    cmd.ExecuteNonQuery()

        vezes = -1
        parcela = +1

    End While



Form1.display()

核心错误点 #

1. 字符串拼接SQL的日期兼容性问题：直接把Date变量拼进SQL语句，会被转换成系统默认字符串格式，SQL Server的日期解析规则与系统区域设置不匹配，触发转换错误，同时还存在SQL注入风险。
2. 变量拼写混乱：声明的变量是dates，赋值用data，执行SQL时用Dates，导致变量未正确赋值，传递无效日期值。
3. 语法与逻辑错误：processo赋值语句有语法错误；循环中vezes = -1导致只执行一次，无法生成多次付款记录；vezes用Double类型对应表中INT字段，存在类型转换风险。

分步解决 #

1. 改用参数化查询（必须执行） #

参数化查询让数据库直接处理强类型变量，避免格式转换问题和SQL注入。修改后的执行代码：

cmd = con.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "INSERT INTO Pagamentos([Date], Vezes, Valor, Recebedor, Processo, Parcela, [Situação]) VALUES (@Date, @Vezes, @Valor, @Recebedor, @Processo, @Parcela, @Situacao)"

' 添加参数
cmd.Parameters.AddWithValue("@Date", data)
cmd.Parameters.AddWithValue("@Vezes", vezesInt)
cmd.Parameters.AddWithValue("@Valor", valor)
cmd.Parameters.AddWithValue("@Recebedor", recebedor)
cmd.Parameters.AddWithValue("@Processo", processo)
cmd.Parameters.AddWithValue("@Parcela", parcela)
cmd.Parameters.AddWithValue("@Situacao", situacao)

cmd.ExecuteNonQuery()

2. 修正变量拼写与类型 #

' 统一变量名，DateTimePicker.Value本身是DateTime类型，无需转换
Dim data As Date = DateTimePicker1.Value
' 确保vezes是整数类型
Dim vezesInt As Integer
If Not Integer.TryParse(TextBox1.Text, vezesInt) Then
    MessageBox.Show("请输入有效的付款次数")
    Exit Sub
End If

3. 修复processo赋值语法 #

VB.NET中字符串连接用&而非+，避免类型转换问题：

If MaskedTextBox3.Text <> "" Then
    processo = TextBox2.Text & "/" & MaskedTextBox3.Text
Else
    processo = TextBox2.Text
End If

4. 实现正确的循环逻辑 #

循环生成多次付款记录，每次日期加一个月：

For parcela = 1 To vezesInt
    ' 计算当前付款日期
    Dim currentDate As Date = DateTimePicker1.Value.AddMonths(parcela - 1)
    valor = "R$" & TextBox3.Text
    recebedor = TextBox2.Text
    ' 处理processo...

    ' 执行参数化插入（将@Date设为currentDate，@Parcela设为parcela）
Next

5. 优化资源管理 #

用Using语句自动释放数据库资源，避免泄漏：

Using con As New SqlConnection("你的数据库连接字符串")
    con.Open()
    Using cmd As SqlCommand = con.CreateCommand()
        cmd.CommandText = "INSERT INTO Pagamentos([Date], Vezes, Valor, Recebedor, Processo, Parcela, [Situação]) VALUES (@Date, @Vezes, @Valor, @Recebedor, @Processo, @Parcela, @Situacao)"
        ' 预定义参数
        cmd.Parameters.Add("@Date", SqlDbType.Date)
        cmd.Parameters.Add("@Vezes", SqlDbType.Int)
        cmd.Parameters.Add("@Valor", SqlDbType.VarChar, 150)
        cmd.Parameters.Add("@Recebedor", SqlDbType.VarChar, 150)
        cmd.Parameters.Add("@Processo", SqlDbType.VarChar, 150)
        cmd.Parameters.Add("@Parcela", SqlDbType.Int)
        cmd.Parameters.Add("@Situacao", SqlDbType.Bit)

        For parcela = 1 To vezesInt
            cmd.Parameters("@Date").Value = DateTimePicker1.Value.AddMonths(parcela - 1)
            cmd.Parameters("@Vezes").Value = vezesInt
            cmd.Parameters("@Valor").Value = "R$" & TextBox3.Text
            cmd.Parameters("@Recebedor").Value = TextBox2.Text
            cmd.Parameters("@Processo").Value = If(MaskedTextBox3.Text <> "", TextBox2.Text & "/" & MaskedTextBox3.Text, TextBox2.Text)
            cmd.Parameters("@Parcela").Value = parcela
            cmd.Parameters("@Situacao").Value = False ' 对应BIT类型的0

            cmd.ExecuteNonQuery()
        Next
    End Using
End Using

内容的提问来源于stack exchange，提问作者Kei Blackwoods