mysql - 使用参数插入会产生错误

Question

我是参数新手，我创建了一个插入语句，它基本上处理数据并将其插入到我的 mysql 数据库中。目前我有

            Dim dt As String = MaskedTextBox5.Text.Replace("/", "-")
        Dim entrytime As String = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        dt = dt & " " & MaskedTextBox6.Text & ":00"
        Dim cb As New MySqlConnectionStringBuilder
        cb.Database = dbfile
        cb.Server = dbserver
        cb.UserID = dbuser
        cb.Password = dbpassw

        Using cnn As New MySqlConnection(cb.ConnectionString)
            Using cmd As New MySqlCommand("INSERT INTO `calls` (`putime`, `Pnumb`, `Scene`, `slocname`, `slocadd`, `sloccity`, `slocstate`, `Sroom`, `Ssteps`, `Dest`, `dlocname`, `dlocadd`, `dloccity`, `dlocstate`, `Droom`, `Dsteps`, `Rentry`, `Utype`, `Curgency`, `Ccomp`, `Callbacknumb`, `pickuptime`, `eta`, `addnote`, `oxygen`, `oxyrate`, `oxylpm`, `oxypct`, `oxydeliv`, `cardmon`, `iv`, `ivpump`, `ivfluid`, `ivmed`, `vent`, `ventset`, `ventrate`, `ventvol`, `ventpeep`, `bipap`, `cpap`, `iabp`, `ecmo`, `Trach`, `Suction`, `reasontx`, `medaonehund`) VALUES (&putime, &Pnumb, &Scene, &slocname, &slocadd, &sloccity, &slocstate, &Sroom, &Ssteps, &Dest, &dlocname, &dlocadd, &dloccity, &dlocstate, &Droom, &Dsteps, &Rentry, &Utype, &Curgency, &Ccomp, &Callbacknumb, &pickuptime, &eta, &addnote, &oxygen, &oxyrate, &oxylpm, &oxypct, &oxydeliv, &cardmon, &iv, &ivpump, &ivfluid, &ivmed, &vent, &ventset, &ventrate, &ventvol, &ventpeep, &bipap, &cpap, &iabp, &ecmo, &Trach, &Suction, &reasontx, &medaonehund)", cnn)
                Select Case ComboBox1.Text
                    Case "BLS Ambulance"
                        cmd.Parameters.AddWithValue("&Utype", "1")
                    Case "ALS Ambulance"
                        cmd.Parameters.AddWithValue("&Utype", "2")
                    Case "SCT Ambulance"
                        cmd.Parameters.AddWithValue("&Utype", "3")
                    Case "Wheelchair Van"
                        cmd.Parameters.AddWithValue("&Utype", "4")
                    Case "Taxi"
                        cmd.Parameters.AddWithValue("&Utype", "5")
                End Select
                Select Case ComboBox2.Text
                    Case "Scheduled"
                        cmd.Parameters.AddWithValue("&Curgency", "1")
                        cmd.Parameters.AddWithValue("&putime", dt)
                        cmd.Parameters.AddWithValue("&pickuptime", dt)
                        cmd.Parameters.AddWithValue("&eta", "")
                    Case "Non-Scheduled"
                        cmd.Parameters.AddWithValue("&Curgency", "2")
                        cmd.Parameters.AddWithValue("&putime", "1950-01-01 00:00:00")
                        cmd.Parameters.AddWithValue("&pickuptime", "1950-01-01 00:00:00")
                        cmd.Parameters.AddWithValue("&eta", TextBox26.Text)
                    Case "ASAP"
                        cmd.Parameters.AddWithValue("&Curgency", "3")
                        cmd.Parameters.AddWithValue("&putime", "1950-01-01 00:00:00")
                        cmd.Parameters.AddWithValue("&pickuptime", "1950-01-01 00:00:00")
                        cmd.Parameters.AddWithValue("&eta", TextBox26.Text)
                    Case "STAT"
                        cmd.Parameters.AddWithValue("&Curgency", "4")
                        cmd.Parameters.AddWithValue("&putime", "1950-01-01 00:00:00")
                        cmd.Parameters.AddWithValue("&pickuptime", "1950-01-01 00:00:00")
                        cmd.Parameters.AddWithValue("&eta", TextBox26.Text)
                End Select
                cmd.Parameters.AddWithValue("&Pnumb", TextBox2.Text)
                cmd.Parameters.AddWithValue("&Scene", sfid.Text)
                cmd.Parameters.AddWithValue("&slocname", sname.Text)
                cmd.Parameters.AddWithValue("&slocadd", sfaddress.Text)
                cmd.Parameters.AddWithValue("&sloccity", sfcity.Text)
                cmd.Parameters.AddWithValue("&slocstate", sfstate.Text)
                cmd.Parameters.AddWithValue("&Sroom", TextBox15.Text)
                cmd.Parameters.AddWithValue("&Ssteps", TextBox16.Text)
                cmd.Parameters.AddWithValue("&Dest", dfid.Text)
                cmd.Parameters.AddWithValue("&dlocname", dname.Text)
                cmd.Parameters.AddWithValue("&dlocadd", dfaddress.Text)
                cmd.Parameters.AddWithValue("&dloccity", dfcity.Text)
                cmd.Parameters.AddWithValue("&dlocstate", dfstate.Text)
                cmd.Parameters.AddWithValue("&Droom", TextBox17.Text)
                cmd.Parameters.AddWithValue("&Dsteps", TextBox18.Text)
                cmd.Parameters.AddWithValue("&Rentry", entrytime)
                cmd.Parameters.AddWithValue("&Ccomp", TextBox1.Text)
                cmd.Parameters.AddWithValue("&Callbacknumb", MaskedTextBox1.Text)
                cmd.Parameters.AddWithValue("&addnote", TextBox32.Text)
                If CheckBox1.Checked = False Then
                    cmd.Parameters.AddWithValue("&oxygen", "0")
                    cmd.Parameters.AddWithValue("&oxyrate", "0")
                    cmd.Parameters.AddWithValue("&oxylpm", "0")
                    cmd.Parameters.AddWithValue("&oxypct", "0")
                    cmd.Parameters.AddWithValue("&oxydeliv", "")
                Else
                    cmd.Parameters.AddWithValue("&oxygen", "1")
                    cmd.Parameters.AddWithValue("&oxyrate", TextBox28.Text)
                    If RadioButton1.Checked = False Then
                        cmd.Parameters.AddWithValue("&oxylpm", "0")
                    Else
                        cmd.Parameters.AddWithValue("&oxylpm", "1")
                    End If
                    If RadioButton2.Checked = False Then
                        cmd.Parameters.AddWithValue("&oxypct", "0")
                    Else
                        cmd.Parameters.AddWithValue("&oxypct", "1")
                    End If
                    cmd.Parameters.AddWithValue("&oxydeliv", ComboBox3.Text)
                End If
                If CheckBox2.Checked = False Then
                    cmd.Parameters.AddWithValue("&cardmon", "0")
                Else
                    cmd.Parameters.AddWithValue("&cardmon", "1")
                End If
                If CheckBox3.Checked = False Then
                    cmd.Parameters.AddWithValue("&iv", "0")
                    cmd.Parameters.AddWithValue("&ivpump", "0")
                    cmd.Parameters.AddWithValue("&ivfluid", "")
                    cmd.Parameters.AddWithValue("&ivmed", "")
                Else
                    cmd.Parameters.AddWithValue("&iv", "1")
                    cmd.Parameters.AddWithValue("&ivpump", "1")
                    cmd.Parameters.AddWithValue("&ivfluid", ComboBox5.Text)
                    cmd.Parameters.AddWithValue("&ivmed", ComboBox4.Text)
                End If
                If CheckBox4.Checked = False Then
                    cmd.Parameters.AddWithValue("&vent", "0")
                    cmd.Parameters.AddWithValue("&ventset", "")
                    cmd.Parameters.AddWithValue("&ventrate", "0")
                    cmd.Parameters.AddWithValue("&ventvol", "0")
                    cmd.Parameters.AddWithValue("&ventpeep", "0")
                Else
                    cmd.Parameters.AddWithValue("&vent", "1")
                    cmd.Parameters.AddWithValue("&ventset", ComboBox6.Text)
                    cmd.Parameters.AddWithValue("&ventrate", TextBox30.Text)
                    cmd.Parameters.AddWithValue("&ventvol", TextBox29.Text)
                    cmd.Parameters.AddWithValue("&ventpeep", TextBox31.Text)
                End If
                If CheckBox7.Checked = False Then
                    cmd.Parameters.AddWithValue("&bipap", "0")
                Else
                    cmd.Parameters.AddWithValue("&bipap", "1")
                End If
                If CheckBox8.Checked = False Then
                    cmd.Parameters.AddWithValue("&cpap", "0")
                Else
                    cmd.Parameters.AddWithValue("&cpap", "1")
                End If
                If CheckBox5.Checked = False Then
                    cmd.Parameters.AddWithValue("&iabp", "0")
                Else
                    cmd.Parameters.AddWithValue("&iabp", "1")
                End If
                If CheckBox6.Checked = False Then
                    cmd.Parameters.AddWithValue("&ecmo", "0")
                Else
                    cmd.Parameters.AddWithValue("&ecmo", "1")
                End If
                If CheckBox11.Checked = False Then
                    cmd.Parameters.AddWithValue("&Trach", "0")
                Else
                    cmd.Parameters.AddWithValue("&Trach", "1")
                End If
                If CheckBox12.Checked = False Then
                    cmd.Parameters.AddWithValue("&Suction", "0")
                Else
                    cmd.Parameters.AddWithValue("&Suction", "1")
                End If
                If CheckBox10.Checked = False Then
                    cmd.Parameters.AddWithValue("&medaonehund", "0")
                Else
                    cmd.Parameters.AddWithValue("&medaonehund", "1")
                End If
                cmd.Parameters.AddWithValue("&reasontx", TextBox27.Text)
                '   Try
                cnn.Open()
                cmd.ExecuteNonQuery()
                '  Catch ex As Exception
                '     MessageBox.Show(ex.ToString)
                ' End Try
            End Using
        End Using

它抛出错误:“您的 SQL 语法中有错误” - 在插入语句上，特别是参数开始的地方

异常:抛出:“您的 SQL 语法有错误；请检查与您的 MySQL 服务器版本相对应的手册，了解在 '&putime、&Pnumb、&Scene、&slocname、&slocadd、&sloccity、&slocstate、&Sroom 附近使用的正确语法, &Ss' 在第 1 行”(MySql.Data.MySqlClient.MySqlException)抛出 MySql.Data.MySqlClient.MySqlException:“您的 SQL 语法有错误；请检查与您的 MySQL 服务器版本相对应的手册，以获取在 '&putime、&Pnumb、&Scene、&slocname、&slocadd、&sloccity 附近使用的正确语法。 、&slocstate、&Sroom、&Ss' 在第 1 行”时间:2014年1月17日下午3:24:35主题:[11300]

知道发生了什么吗？

Answer 1

来自第 22.2.4.1.4 节。使用 this tutorial page for the MySql NET Connector 的参数 ，参数以 @ 字符为前缀，以便被识别。

查看 MySql NET/Connector 6.3.6 的源代码，我看到很多地方直接使用 char @ 来构建参数名称，所以虽然我认为可以将该 char 更改为其他内容，最好始终使用 @ 字符作为参数名称(也为了与其他数据库的可移植性)