incorrect syntax error

John Nederveen

Hello, I have being for a while searching an solution but without success. I am working on a project that did work, but i made a little change in the SQL and visual studio 2012 i dropped 2 columns and added 1 more column (ID, as Primary key). i also changed the c# script. But there is no way to make it work, as i start it and fill the form and try to save it, it tells, "incorrect syntax on IdCliente". I will copy and paste the C# script, i hope someone can help me out. thanks in advance private void buttonGuardar_Click(object sender, EventArgs e) { try { byte[] img = null; FileStream fs = new FileStream(imgloc, FileMode.Open, FileAccess.Read); BinaryReader br = new BinaryReader(fs); img = br.ReadBytes((int)fs.Length); string sql = "Insert INTO PatronImagen(Id,IdCliente,NombreCliente,DireccionEnvio,IDDirecciondelEnvio,Ciudad,CodigoPrenda,TipoPrenda,DescripcionPatron,Patron)VALUES('" + comboBoxId.Text + ",'" + comboBoxIdCliente.Text + ",'" + comboBoxNombreCliente.Text + "','" + comboBoxDireccionEnvio.Text + "','" + comboBoxIDDirecciondelEnvio.Text + "','" + comboBoxCiudad.Text + "','" + comboBoxCodigoPrenda.Text + "','" + comboBoxTipoPrenda.Text + "','" + textBoxDescripcionPatron.Text + "',@IMG)"; if (conn.State != ConnectionState.Open) conn.Open(); command = new SqlCommand(sql, conn); command.Parameters.Add(new SqlParameter("@IMG", img)); int x = command.ExecuteNonQuery(); MessageBox.Show(x.ToString() + " Gracias Su Archivo esta Guardada."); conn.Close(); comboBoxId.Text = ""; comboBoxIdCliente.Text = ""; comboBoxNombreCliente.Text = ""; comboBoxDireccionEnvio.Text = ""; comboBoxIDDirecciondelEnvio.Text = ""; comboBoxCiudad.Text = ""; comboBoxCodigoPrenda.Text = ""; comboBoxTipoPrenda.Text = ""; // comboBoxIdLogoGraf.Text = ""; // comboBoxReferencia.Text = ""; textBoxDescripcionPatron.Text = ""; picPatron.Image = null; } catch (Exception ex) { conn.Close(); MessageBox.Show(ex.Message); } } private void buttonAbrirPa

Dave Kreskowiak

Drop all the string concatenation bullshit and use proper parameters for all of your inputs, just like you did for the last item in your field list, @IMG. Also, unless you're providing the primary key value (very rare to do this!), you don't put the Id column in your INSERT statement. The database usually generates this value itself.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak

John Nederveen

Hello Dave, Thank you for your response, i made some changes as you mentioned in your reply. the syntax error is gone also i correct the bullsh*t. But i need to find out more, because now i have got this error,"must declare the scalable variable @IdCliente". thank you very much for your help, this is how i made it now. private void buttonGuardar_Click(object sender, EventArgs e) { try { byte[] img = null; FileStream fs = new FileStream(imgloc, FileMode.Open, FileAccess.Read); BinaryReader br = new BinaryReader(fs); img = br.ReadBytes((int)fs.Length); string sql = "Insert INTO PatronImagen(IdCliente,NombreCliente,DireccionEnvio,IDDirecciondelEnvio,Ciudad,CodigoPrenda,TipoPrenda,DescripcionPatron,Patron)VALUES(@IdCliente,@NombreCliente,@DireccionEnvio,@DirecciondelEnvio,@Ciudad,@CodigoPrenda,@TipoPrenda,@DescripcionPatron,@IMG)"; if (conn.State != ConnectionState.Open) conn.Open(); command = new SqlCommand(sql, conn); command.Parameters.Add(new SqlParameter("@IMG", img)); int x = command.ExecuteNonQuery(); MessageBox.Show(x.ToString() + " Gracias Su Archivo esta Guardada."); conn.Close(); command.Parameters.AddWithValue("@IdCliente", comboBoxIdCliente); command.Parameters.AddWithValue("@NombreCliente", comboBoxNombreCliente); command.Parameters.AddWithValue("@DireccionEnvio", comboBoxDireccionEnvio); command.Parameters.AddWithValue("@IDDirecciondelEnvio", comboBoxIDDirecciondelEnvio); command.Parameters.AddWithValue("@Ciudad", comboBoxCiudad); command.Parameters.AddWithValue("@CodigoPrenda", comboBoxCodigoPrenda); command.Parameters.AddWithValue("@TipoPrenda", comboBoxTipoPrenda); command.Parameters.AddWithValue("@DescripcionPatron", textBoxDescripcionPatron); picPatron.Image = null; }

Dave Kreskowiak

You really need to put spaces in your SQL. Right now your SQL statement looks like this:

Insert INTO PatronImagen(IdCliente,NombreCliente,DireccionEnvio,IDDirecciondelEnvio,Ciudad,CodigoPrenda,TipoPrenda,DescripcionPatron,Patron)VALUES(@IdCliente,@NombreCliente,@DireccionEnvio,@DirecciondelEnvio,@Ciudad,@CodigoPrenda,@TipoPrenda,@DescripcionPatron,@IMG)

No spaces anywhere, even before and after keywords! Your SQL should look more like this:

Insert INTO PatronImagen (IdCliente, NombreCliente, DireccionEnvio, IDDirecciondelEnvio, Ciudad, CodigoPrenda, TipoPrenda, DescripcionPatron, Patron) VALUES (@IdCliente, @NombreCliente, @DireccionEnvio, @DirecciondelEnvio, @Ciudad, @CodigoPrenda, @TipoPrenda, @DescripcionPatron, @IMG)

SQL isn't completely insane when it comes to whitespace characters but it does freak out if there are no spaces before or after keywords, like INSERT INTO and VALUES.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak

John Nederveen

Hello Dave, Thanks again for your good advise!! i made the changes as you mentioned, also made more changes in the C# script, but still not working, i looked up for information for to "declare the scalar variable "IdCliente" but without results.".here is my last modification. thank again Dave for your help! private void buttonGuardar_Click(object sender, EventArgs e) { try { byte[] img = null; FileStream fs = new FileStream(imgloc, FileMode.Open, FileAccess.Read); BinaryReader br = new BinaryReader(fs); img = br.ReadBytes((int)fs.Length); string sql = "Insert INTO PatronImagen (IdCliente, NombreCliente, DireccionEnvio, IDDirecciondelEnvio, Ciudad, CodigoPrenda, TipoPrenda, DescripcionPatron, Patron) VALUES (@IdCliente, @NombreCliente, @DireccionEnvio, @DirecciondelEnvio, @Ciudad, @CodigoPrenda, @TipoPrenda, @DescripcionPatron, @IMG))"; if (conn.State != ConnectionState.Open) conn.Open(); command = new SqlCommand(sql, conn); command.Parameters.Add(new SqlParameter("@IMG", img)); int x = command.ExecuteNonQuery(); MessageBox.Show(x.ToString() + " Gracias Su Archivo esta Guardada."); conn.Close(); command.Parameters.AddWithValue("@IdCliente", SqlDbType.NVarChar).Value = comboBoxIdCliente.Text; command.Parameters.AddWithValue("@NombreCliente", SqlDbType.NVarChar).Value = comboBoxNombreCliente.Text; command.Parameters.AddWithValue("@DireccionEnvio", SqlDbType.NVarChar).Value = comboBoxDireccionEnvio.Text; command.Parameters.AddWithValue("@IDDirecciondelEnvio", SqlDbType.NVarChar).Value = comboBoxIDDirecciondelEnvio.Text; command.Parameters.AddWithValue("@Ciudad", SqlDbType.NVarChar).Value = comboBoxCiudad.Text; command.Parameters.AddWithValue("@CodigoPrenda", SqlDbType.NVarChar).Value = comboBoxCodigoPrenda.Text; command.Parameters.AddWithValue("@TipoPrenda", SqlDbType.NVarChar).Value = comboBoxTipoPrenda.Text; command.Parameters.AddWithValue("@DescripcionPatron", SqlDbType.NVarChar).Value = textBoxDescripcionPatron.Text; picPatron.Image = null; } catch (Exception ex) { conn.Close(); MessageB

Dave Kreskowiak

Your @IdCliente in your table is defined as an varbinary, but the parameter in your code is defined as an nvarchar. Why on earth did you define your fields as varbinary? Planning on putting a picture in every single field?

A guide to posting questions on CodeProject

Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak

thatraja

Move the remaining parameters code before the ExecuteNonQuery line like below.

string sql = "Insert INTO PatronImagen (IdCliente, NombreCliente, DireccionEnvio, IDDirecciondelEnvio, Ciudad, CodigoPrenda, TipoPrenda, DescripcionPatron, Patron) VALUES (@IdCliente, @NombreCliente, @DireccionEnvio, @DirecciondelEnvio, @Ciudad, @CodigoPrenda, @TipoPrenda, @DescripcionPatron, @IMG))";
if (conn.State != ConnectionState.Open)
conn.Open();
command = new SqlCommand(sql, conn);
command.Parameters.Add(new SqlParameter("@IMG", img));
command.Parameters.AddWithValue("@IdCliente", SqlDbType.NVarChar).Value = comboBoxIdCliente.Text;
command.Parameters.AddWithValue("@NombreCliente", SqlDbType.NVarChar).Value = comboBoxNombreCliente.Text;
command.Parameters.AddWithValue("@DireccionEnvio", SqlDbType.NVarChar).Value = comboBoxDireccionEnvio.Text;
command.Parameters.AddWithValue("@IDDirecciondelEnvio", SqlDbType.NVarChar).Value = comboBoxIDDirecciondelEnvio.Text;
command.Parameters.AddWithValue("@Ciudad", SqlDbType.NVarChar).Value = comboBoxCiudad.Text;
command.Parameters.AddWithValue("@CodigoPrenda", SqlDbType.NVarChar).Value = comboBoxCodigoPrenda.Text;
command.Parameters.AddWithValue("@TipoPrenda", SqlDbType.NVarChar).Value = comboBoxTipoPrenda.Text;
command.Parameters.AddWithValue("@DescripcionPatron", SqlDbType.NVarChar).Value = textBoxDescripcionPatron.Text;
int x = command.ExecuteNonQuery();
MessageBox.Show(x.ToString() + " Gracias Su Archivo esta Guardada.");
conn.Close();
command.Parameters.AddWithValue("@IdCliente", SqlDbType.NVarChar).Value = comboBoxIdCliente.Text;
command.Parameters.AddWithValue("@NombreCliente", SqlDbType.NVarChar).Value = comboBoxNombreCliente.Text;
command.Parameters.AddWithValue("@DireccionEnvio", SqlDbType.NVarChar).Value = comboBoxDireccionEnvio.Text;
command.Parameters.AddWithValue("@IDDirecciondelEnvio", SqlDbType.NVarChar).Value = comboBoxIDDirecciondelEnvio.Text;
command.Parameters.AddWithValue("@Ciudad", SqlDbType.NVarChar).Value = comboBoxCiudad.Text;
command.Parameters.AddWithValue("@CodigoPrenda", SqlDbType.NVarChar).Value = comboBoxCodigoPrenda.Text;
command.Parameters.AddWithValue("@TipoPrenda", SqlDbType.NVarChar).Value = comboBoxTipoPrenda.Text;
command.Parameters.AddWithValue("@DescripcionPatron", SqlDbType.NVarChar).Value = textBoxDescripcionPatron.Text;
picPatron.Image = null;

thatraja

John Nederveen

Hello Dave, Thank you for your response, (a little bit late). The fact is that i started a whole new project that is working well and better, then the one you have seen. again thanks really appreciate your help!. John Nederveen

John Nederveen

Hello Thatraja, Thank you for your response, as told to Dave, i have started a new project that is working well. The fact is that the project that i was making, was not giving the results that i wanted, so i looked up more forward and started a new one that is working as i wanted. Later on i will restart this project and i will have the "responses" in mind . thank you again. john