Syntax problem of the SQL statement in Visual Studio C #, with Access

Question

Syntax problem of the SQL statement in Visual Studio C #, with Access

Navigation

#1 by (3 votes)

1

I am new in the world of programming; This semester the professor has put us a project in C# , managing the database in Access (I use the Access 16, since I have the license of it).

The problem is that when I save the data of one of the students in the database, I receive a Visual error informing me that the SQL statement has an error of sentaxis.

This is the error:

Unhandled exception of type 'System.Data.OleDb.OleDbException' in System.Data.dll

Additional information:

Syntax error in the INSERT INTO statement.

This is the code of the function:

public bool Agregar_Estudiantes(string Codigo, string Apellidos, string Nombres, string Edad, string Direccion, string Correo, string Sexo, string Tel_Res, string Tel_Movil, string Curso, string Mensualidad)
        {

            int Eda = Convert.ToInt32(Edad);
            //Conexion
            OleDbConnection Conexion = new OleDbConnection();
            Conexion.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = D:\Proyecto Final POO\Proyecto Final POO\Proyecto Final POO.mdb; Persist Security Info = false;";

            //Instruccion SQL
            string CadenaSQL = "INSERT INTO Personal (Id, Apellidos, Nombres, Edad, Direccion, Correo, Sexo, Tel_Res, Tel_Movil, Curso, Mensualidad) ";
            CadenaSQL = CadenaSQL + " VALUES (  " + Codigo    + ",";
            CadenaSQL = CadenaSQL + "          '" + Apellidos + "',";
            CadenaSQL = CadenaSQL + "          '" + Nombres   + "',";
            CadenaSQL = CadenaSQL + "           " + Eda      + ",";
            CadenaSQL = CadenaSQL + "          '" + Direccion + "',";
            CadenaSQL = CadenaSQL + "          '" + Correo   + "',";
            CadenaSQL = CadenaSQL + "          '" + Sexo     + "',";
            CadenaSQL = CadenaSQL + "          '" + Tel_Res  + "',";
            CadenaSQL = CadenaSQL + "          '" + Tel_Movil + "',";
            CadenaSQL = CadenaSQL + "          '" + Curso     + "',";
            CadenaSQL = CadenaSQL + "          '" + Mensualidad + "')";


            //Crear comando
            OleDbCommand Comando = Conexion.CreateCommand();
            Comando.CommandText = CadenaSQL;

            //Ejecutar la consulta de accion
            Conexion.Open();
            Comando.ExecuteNonQuery();
            Conexion.Close();

            return true;
        }

The data is received in a TexBox , and sent from a Button , who assigns the values to the function that I have shown above.

Thanks for your help!

c# visual-studio-2015 ms-access

asked by Abraham Rosa Vargas 14.07.2017 в 00:43

source

1 answer

Change one text element of the DOM for another using Javascript How I generate a .jar in eclipse with all the libraries [closed]

score 3 · Answer 1

It's a bad way to persist data, to start do not use a class Connection, sooner or later will bring you problems. Declare the connection in the App.config file, as I show in this example:

<connectionStrings>
<add name="default" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Biblioteca\DB\Biblioteca.accdb;
Persist Security Info=False;"/>
</connectionStrings>

You must declare the connection in a Using ... End Using block, a good practice is to use parameters.

public static void Guardar(AlumnoEntity alumno)
    {
        using (var cn = new OleDbConnection(ConfigurationManager.ConnectionStrings["default"].ToString()) )
        {
            cn.Open();
            using (OleDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO Alumnos(AlumnoId, Nombre, Direccion, Telefono, Email) VALUES(@AlumnoId, @Nombre, @Direccion, @Telefono, @Email)";

                cmd.Parameters.AddWithValue("@AlumnoId", alumno.AlumnoId);
                cmd.Parameters.AddWithValue("@Nombre", alumno.Nombre);
                cmd.Parameters.AddWithValue("@Direccion", alumno.Direccion);
                cmd.Parameters.AddWithValue("@Telefono", alumno.Telefono);
                cmd.Parameters.AddWithValue("@Email", alumno.Email);
                cmd.ExecuteNonQuery();
            }
        }
    }

Check this link where they teach how to program in layers Programming in 3 layers , if you are learning, learn good programming practices.

Recommendation: By enclosing the declaration of the Connection object between a Using ... End Using block, we will be making sure that close the connection and destroy the resources used by the object, at the end of that block (when End Using is executed). With we do not have to be aware of calling the Close method (for close the connection), or to the Dispose method (to destroy the object), therefore, it is not necessary to have a procedure external for such tasks.

I hope the information provided will be useful.