CODEWITHSUNDEEP
mysqlvb.net
James Stafford
James Stafford

Reputation: 1054

inserting using parameters creating error

I am new with parameters and I have created an insert statement which basically processes and inserts data to my mysql database. currently I have 

            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

it is tossing the error: "You have an error in your SQL syntax" - on the insert statement specifically where the parameters begin

Exception:Thrown: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '&putime, &Pnumb, &Scene, &slocname, &slocadd, &sloccity, &slocstate, &Sroom, &Ss' at line 1" (MySql.Data.MySqlClient.MySqlException) A MySql.Data.MySqlClient.MySqlException was thrown: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '&putime, &Pnumb, &Scene, &slocname, &slocadd, &sloccity, &slocstate, &Sroom, &Ss' at line 1" Time: 1/17/2014 3:24:35 PM Thread:[11300]

any idea what happened?

Upvotes: 0

Views: 49

Answers (1)

Steve
Steve

Reputation: 216293

As from the Section 22.2.4.1.4. Working with Parameters of this tutorial page for the MySql NET Connector, a parameter is prefixed by the @ char to be recognized as such.

Looking at the source code of the MySql NET/Connector 6.3.6 I have seen numerous places where the char @ is used directly to build the parameter name, so while I think that it is possible to change that char to something else, it is better (also for portability with other databases) to use always the @ char for parameter names

Upvotes: 1

Related Questions