How to solve invalid column name?

Question

I have a Database named: "alumno" with the following columns:

"rut" (varchar 30) PK
"nombre" (varchar 30)
"apellido" (varchar 30)
"fechaN" (varchar 15)

all of them with not null function.

Whenever I type letter I get a SQLException saying the column "fechaN" has invalid name, but if I type numbers it runs just fine. Here is my code:

public Boolean insertar(SqlConnection x, alumno a) {
 String query = "INSERT INTO alumno (rut, nombre, apellido, fechaN) VALUES ('" + a.Rut + "','" + a.Nombre + "','" + a.Apellido + "'," + a.Fecha + ")";
 comando = new SqlCommand(query, x);

 int saber = comando.ExecuteNonQuery();

 if (saber > 0) {
  return true;
 }

 return false;
}


con = new conexion();
SqlConnection x = con.conectar();

String rut = txtRut.Text.Trim();
String nombre = txtNombre.Text.Trim();
String apellido = txtApellido.Text.Trim();
String fecha = txtfecha.Text.Trim();

alumno a = new alumno(rut, nombre, apellido, fecha);

try {
 if (a.insertar(x, a)) {
  MessageBox.Show("Ok");
 } else {
  MessageBox.Show("Error...");
 }
} catch (SqlException ex) {
 MessageBox.Show("..." + ex.Message);
}

}

con.desconectar();
x = null;

Do you have some of those values with an embedded single quote (nombre for example) — Steve, May 31 '16 at 20:07
You'll definitely want to use parameterized queries. It'll make your life easier down the road, and it's _much_ more secure.. — Mike Christensen, May 31 '16 at 20:07
If, in the debugger, you take the contents of the 'query' string and drop them into a query window in SQL Server Management Studio, then run it, what happens? — Marc Bernier, May 31 '16 at 20:07
[MCVE] for this case should be first couple lines of code as shown up to `ExecuteNonQuery` (rewritten with using parametrized query) and `CREATE` SQL command for the table. — Alexei Levenkov, May 31 '16 at 20:08
Side note: please use default C# guidelines for naming variables/types in public samples. Type names should start wit capital letter. — Alexei Levenkov, May 31 '16 at 20:09
You are missing two single quotes in query: ...,'" + a.Fecha + "').... Please use parameterized queries. — Jeroen Heier, May 31 '16 at 20:10
http://stackoverflow.com/questions/19956533/sql-insert-query-using-c-sharp This is what everyone is talking about — FirebladeDan, May 31 '16 at 20:15
this is why you should never build a query using concatenated strings .. use parameters also if you are using `Sql Server` create a stored procedure it's much cleaner — MethodMan, May 31 '16 at 20:17

Midori_hige · Answer 1 · 2016-05-31T20:35:56.730

1

As user Jeroen Heier pointed out in the comments.. I was missing a '

Here's the final query

String query = "INSERT INTO alumno (rut, nombre, apellido, fechaN) VALUES ('" + a.Rut + "','" + a.Nombre + "','" + a.Apellido + "','" + a.Fecha + "')";
         comando = new SqlCommand(query, x);

Using parameterized query:

String query = "INSERT INTO alumno (rut, nombre, apellido, fechaN) VALUES (@rut,@nombre,@apellido,@fechaN)";
         comando = new SqlCommand(query, x);

         comando.Parameters.AddWithValue("@rut", this.rut);
         comando.Parameters.AddWithValue("@nombre", this.nombre);
         comando.Parameters.AddWithValue("@apellido", this.apellido);
         comando.Parameters.AddWithValue("@fechaN", this.fecha);

edited May 31 '16 at 20:35

answered May 31 '16 at 20:18

Midori_hige

319
3
21

1

Well that will save you today, but just for fun try to insert an alumno with a nombre that contains a single quote (O'Hara for example) – Steve May 31 '16 at 20:18
Yep, an error appears. Crappy method my teacher taught me. – Midori_hige May 31 '16 at 20:20
check this question. http://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements – Juan Carlos Oropeza May 31 '16 at 20:22
1

And this is not all. Sql Injection is a bigger problem. Letting your user type something that goes directly inside you query could be very costly for your customers. [This famous comic](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) explain it well. Use parameterized queries. ALWAYS and show your teacher this question – Steve May 31 '16 at 20:22

Juan Carlos Oropeza · Answer 2 · 2016-05-31T20:12:25.203

0

You didnt mention rdbms, but looks like is case sensitive, so use double quotes for your field name same as your create table. Or change your field name to lower case in the table to "fechan".

"fechaN" (varchar 15)

"INSERT INTO alumno (rut, nombre, apellido, fechaN)
                                            ^^^^^^

And yes, use parameter instead, You are vulnerable to Sql Injection https://xkcd.com/327/

edited May 31 '16 at 20:12

answered May 31 '16 at 20:09

Juan Carlos Oropeza

45,789
11
74
113

SqlConnection is a strong hint about the rdbms. – Steve May 31 '16 at 20:11

score -1 · Answer 3 · answered May 31 '16 at 20:15

-1

String query = "INSERT INTO alumno (rut, nombre, apellido, fechaN) VALUES     ('" + a.Rut + "','" + a.Nombre + "','" + a.Apellido + "'," + a.Fecha + ")";

Try without single quotes:

String query = "INSERT INTO alumno (rut, nombre, apellido, fechaN) 
                VALUES (" + a.Rut + "," + a.Nombre + "," + a.Apellido + ","   
                        + a.Fecha + ")";

answered May 31 '16 at 20:15

Jeff Matthews

46
6

1

nombre as all the other fields are varchar fields. Without quotes you can pass only numbers – Steve May 31 '16 at 20:17
1

@Steve Have these people never written a SQL insert statement? I'm astounded by these answers. I'm losing points!! – FirebladeDan May 31 '16 at 20:18
Just don't go onto any forums where law is discussed. ;-) – Jeff Matthews May 31 '16 at 20:21

How to solve invalid column name?

3 Answers3