Error writing new record in Access database

Question

I’m making a CRUD with Winforms in the C#, I have an error entering the date in the birth date field. This displays the following message when trying to save the new record:

Missing operator syntax error in query expression '10/02/1986 00:00'

Below is an excerpt from the code

string sql = "INSERT INTO Cliente (descCliente, nomefantasia, endereco, complemento, bairro, cep, estado, cidade, "
                + "telefone1, telefone2, celular1, email, CPF, RG, OEmissor, DtNasc)"
                //, CPF, RG, OEmissor, bloqueado, mensalista) "
                + "VALUES ('" + txtnome.Text + "', "
                + "'" + txtFantasia.Text + "', "
                + "'" + txtRua.Text + "', "
                + "'" + txtComplemento.Text + "', "
                + "'" + txtBairro.Text + "', "
                + "'" + mskCEP.Text + "', "
                + Convert.ToInt32(txtEstado.Text) + ", "
                + Convert.ToInt32(txtCidade.Text) + ","
                + "'" + mskFone1.Text + "', "
                + "'" + mskfone2.Text + "', "
                + "'" + mskcelular.Text + "', "
                + "'" + txtemail.Text + "', "
                + "'" + mskCPF.Text + "', "
                + "'" + mskRG.Text + "', "
                + "'" + txtEmissor.Text + "', "
                +  Convert.ToDateTime(mskDOB.Text) + "); ";    
                //+ false + ", "
                //+ false + ");";
            OleDbConnection conn = new OleDbConnection(connStr);
            OleDbCommand cmd = new OleDbCommand(sql , conn);
            cmd.CommandType = CommandType.Text;
            conn.Open();
            try
            {
                int i = cmd.ExecuteNonQuery();
                if (i>0)
                {
                    MessageBox.Show("inclusão efetuada com sucesso");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

Down with String SQL

"INSERT INTO Client (descriptionClient, namesake, address, complement, neighborhood, zip code, status, city, phone1, phone2, cellular1, email, CPF, RG, Oemissor, Dtnasc)VALUES ('Fernando', 'fer', 'street 1', '', 'lemon tree', '00125-455', 2, 1960,'(11) 1111-1111', '( ) -', '(11) 91111-1111', '[email protected]', ' . . -', '111.111.111-11', 'SSP-PS', 10/02/1986 00:00:00); "

What am I doing wrong?

Answer 1

Tip: Maybe it’s important you start working with parameters in darlings. In the current way it is very easy that some invader can make a SQL Injection (read about it, here and here) in your code.

You need to enter the date in the format YYYY-MM-DD HH:MM:SS and surrounded by hash/tic-tac-toe/Sharp/sharp (#) or single quotes.

That is, the Insert should look like this

INSERT INTO Cliente (descCliente, nomefantasia, endereco, complemento, bairro, cep, estado, 
                  cidade, telefone1, telefone2, celular1, email, CPF, RG, OEmissor, DtNasc)
VALUES ('fernando', 'fer', 'rua 1', '', 'limoeiro', '00125-455', 2, 1960,
        '(11) 1111-1111', '( ) -', '(11) 91111-1111', '[email protected]', ' . . -', 
        '111.111.111-11', 'SSP-PS', #1986-02-10 00:00:00#); 

Or (just the end so you don’t keep repeating everything)

'1986-02-10 00:00:00'

To format the date just use .ToString().

Convert.ToDateTime(mskDOB.Text).ToString("yyyy-MM-dd HH:mm:ss");

Answer 2

I advise you not to use it this way... You are concatenating the values within the query. Try to impute the values using parameters. This will avoid problems that may come to be code injection and will not give you these problems with date. The code would look like this:

string sql = "INSERT INTO Cliente (descCliente, nomefantasia, endereco, 
complemento, bairro, cep, estado, cidade, "
                + "telefone1, telefone2, celular1, email, CPF, RG, OEmissor, 
DtNasc)"
            //, CPF, RG, OEmissor, bloqueado, mensalista) "
            + "VALUES (@descCliente, @nomefantasia, @endereco, @complemento, @bairro, @cep, @estado, @cidade, "
            + "@telefone1, @telefone2, @celular1, @email, @CPF, @RG, @OEmissor, @DtNasc) ";
        //+ false + ", "
        //+ false + ");";
        OleDbConnection conn = new OleDbConnection(connStr);
        OleDbCommand cmd = new OleDbCommand(sql, conn);
        cmd.CommandType = CommandType.Text;

        cmd.Parameters.AddRange(new OleDbParameter[]
           {
               new OleDbParameter("@descCliente",  txtnome.Text),
               new OleDbParameter("@nomefantasia", txtFantasia.Text),
               ...
           });

        conn.Open();
        try
        {
            int i = cmd.ExecuteNonQuery();
            if (i > 0)
            {
                MessageBox.Show("inclusão efetuada com sucesso");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            conn.Close();
        }