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?
&character. Are you sure you shouldn't be using@instead?