locked
[HELP][C#][SQL] Parameters RRS feed

  • Question

  • Hello,

    I'm working on an application that needs to export information trough a remote sql server. I have a GUI that all it does is gather the info required and finally, a button which creates the connection and sends the info, tho unfortunately, I'm not able to send anything because I keep having a Syntax error (Which tbh, I dont see where is it....)

    Here's the code:

    private void RegistrarBoton_Click(object sender, EventArgs e)
    {
    // Variables de captura de los datos de los textbox.
    string ID = IdBox.Text;
    string NombreReceptor = ReceptorBox.Text;
    string NombreMuestra = MuestraBox.Text;
    string NumLote = LoteBox.Text;
    string Uso = UsoBox.Text;
    string Origen = OrigenBox.Text;
    string Composicion = CompBox.Text;
    string FechaLlegada = LlegadaBox.Text;
    string FechaSalida = SalidaBox.Text;
    string CodigoBarras = CodigoBarrasBox.Text;
    // Conexion con el servidor SQL
    string connectionSQL = "server=asdasdasd(:D)";
    MySqlConnection conn = new MySqlConnection(connectionSQL);
    // Inicializamos la conexion
    conn.Open();
    // Instruccion enviada
    string query =
    "INSERT INTO samples ( id, rname, sname, batch, usage, origin, comp, adate, ddate, bcode ) VALUES ( @val1, @val2, @val3, '@val4', @val5, @val6, @val7, @val8, @val9, @val10)";
    MySqlCommand cmd = new MySqlCommand(query, conn);
    // ¿Que parametros estamos enviando?
    cmd.Parameters.AddWithValue("@val1", ID);
    cmd.Parameters.AddWithValue("@val2", NombreReceptor);
    cmd.Parameters.AddWithValue("@val3", NombreMuestra);
    cmd.Parameters.AddWithValue("@val4", NumLote);
    cmd.Parameters.AddWithValue("@val5", Uso);
    cmd.Parameters.AddWithValue("@val6", Origen);
    cmd.Parameters.AddWithValue("@val7", Composicion);
    cmd.Parameters.AddWithValue("@val8", FechaLlegada);
    cmd.Parameters.AddWithValue("@val9", FechaSalida);
    cmd.Parameters.AddWithValue("@val10", CodigoBarras);
    // Ejecutamos
    cmd.ExecuteNonQuery();
    conn.Close();
    //textBox1.Focus();
    MessageBox.Show("OK!.");

    Here's the error:

    {"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 'usage, origin, comp, adate, ddate, bcode ) VALUES ( '2', 'asdasdasd', 'asdads', ' at line 1"}

    So yeah, i would really appreciate if somenoe could help me out on this one. Thanks.

    • Moved by Amanda Zhu Wednesday, May 15, 2013 7:41 AM not VS general forum issues and not sure which appropriate forum this thread can be moved to
    Wednesday, May 8, 2013 10:01 PM

Answers

  • As we don't have your table layout and column definitions, it will be very difficult to assist you with your SQL syntax error.

    The error message "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 'usage, origin, comp, adate, ddate, bcode ) VALUES ( '2', 'asdasdasd', 'asdads', ' at line 1"

    indicates that you should check the syntax in the query for compliance with your version of MySQL by consulting the manual. Have you done that?

    Thursday, May 9, 2013 2:08 AM
  • Try @val4 without apostrophes. Also since words like usage are reserved, try [usage] or `usage`.

    Thursday, May 9, 2013 6:47 AM
  • Hello,

    Try this version of your SQL

    INSERT INTO samples (id, rname, sname, batch, usage, origin, comp, adate, ddate, bcode) VALUES ( @val1, @val2, @val3, @val4, @val5, @val6, @val7, @val8, @val9, @val10)"

    The orginal had '@val4' which should be @val4

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Thursday, May 9, 2013 12:52 PM

All replies

  • As we don't have your table layout and column definitions, it will be very difficult to assist you with your SQL syntax error.

    The error message "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 'usage, origin, comp, adate, ddate, bcode ) VALUES ( '2', 'asdasdasd', 'asdads', ' at line 1"

    indicates that you should check the syntax in the query for compliance with your version of MySQL by consulting the manual. Have you done that?

    Thursday, May 9, 2013 2:08 AM
  • Try @val4 without apostrophes. Also since words like usage are reserved, try [usage] or `usage`.

    Thursday, May 9, 2013 6:47 AM
  • Hello,

    Try this version of your SQL

    INSERT INTO samples (id, rname, sname, batch, usage, origin, comp, adate, ddate, bcode) VALUES ( @val1, @val2, @val3, @val4, @val5, @val6, @val7, @val8, @val9, @val10)"

    The orginal had '@val4' which should be @val4

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Thursday, May 9, 2013 12:52 PM
  • Hello,

    Thank you for your post.

    What is your app? Windows Forms app? or anything else.

    What about your issue now? Can you get useful information from those replies?

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, May 10, 2013 1:07 AM