Local SQL Server database in Visual Studio 2017 C# ('Incorrect syntax near the keyword 'FROM'')

Question

I can not understand where the error, I have another method that works properly it works the same way. It could be the 'WHERE' statement the problem?

Method that generates the exception:

public string[] GetData(string name, string surname)
{
        customerInformation = new List<string>();

        Connection();

        string sqlQuery = "SELELCT " +
            "Nome, " +
            "Cognome, " +
            "Giorno_Nascita, " +
            "Mese_Nascita, " +
            "Anno_Nascita, " +
            "Luogo_Nascita, " +
            "Residenza, " +
            "Provincia_Residenza, " +
            "Indirizzo_Residenza, " +
            "Civico_Residenza, " +
            "Domicilio, " +
            "Provincia_Domicilio, " +
            "Indirizzo_Domicilio, " +
            "Civico_Domicilio, " +
            "Mail, " +
            "Telefono_Fisso, " +
            "Telefono_Mobile, " +
            "Fax, " +
            "Codice_Fiscale " +
            "FROM DatiClienti WHERE Nome LIKE " + name + " AND Cognome LIKE " + surname + "'";

        dbCommand = new SqlCommand(sqlQuery, dbConnection);
        dbReader = dbCommand.ExecuteReader(); **//Exception Here**

        if(dbReader.HasRows)
        {
            while(dbReader.Read())
            {
                customerInformation.Add(dbReader["Nome"].ToString());
                customerInformation.Add(dbReader["Cognome"].ToString());
                customerInformation.Add(dbReader["Giorno_Nascita"].ToString());
                customerInformation.Add(dbReader["Mese_Nascita"].ToString());
                customerInformation.Add(dbReader["Anno_Nascita"].ToString());
                customerInformation.Add(dbReader["Luogo_Nascita"].ToString());
                customerInformation.Add(dbReader["Residenza"].ToString());
                customerInformation.Add(dbReader["Provincia_Residenza"].ToString());
                customerInformation.Add(dbReader["Indirizzo_Residenza"].ToString());
                customerInformation.Add(dbReader["Civico_Residenza"].ToString());
                customerInformation.Add(dbReader["Domicilio"].ToString());
                customerInformation.Add(dbReader["Provincia_Domicilio"].ToString());
                customerInformation.Add(dbReader["Indirizzo_Domicilio"].ToString());
                customerInformation.Add(dbReader["Civico_Domicilio"].ToString());
                customerInformation.Add(dbReader["Mail"].ToString());
                customerInformation.Add(dbReader["Telefono_Fisso"].ToString());
                customerInformation.Add(dbReader["Telefono_Mobile"].ToString());
                customerInformation.Add(dbReader["Fax"].ToString());
                customerInformation.Add(dbReader["Codice_Fiscale"].ToString());
            }
        }
        else
        {
            MessageBox.Show("Non ci sono dati per questo cliente.");
        }

        dbReader.Close();
        Disconnect();

        return customerInformation.ToArray();
    }

And this is the method working properly:

public List<string> GetSearchableData()
    {
        Connection();

        customerName = new List<string>();

        string sqlQuery = "SELECT * FROM DatiClienti";
        dbCommand = new SqlCommand(sqlQuery, dbConnection);
        dbReader = dbCommand.ExecuteReader();

        if(dbReader.HasRows)
        {
            while(dbReader.Read())
            {
                string name = dbReader.GetString(1);
                string surname = dbReader.GetString(2);

                customerName.Add(name + " " + surname);
            }
        }

        return customerName;
    }

How can I fix this?

Thanks in advance.

Answer 1

You have 2 syntax problems as pointed out in the comments and several design issues.

Syntax Issues

1. Syntax issue - SELECT, not SELELCT
2. The LIKE parameters should be surrounded with single quotes.

Design Issues

ADO.NET

To fix your actual design you need to scrap your DAL or redesign it. Just use the ADO.NET types directly instead of trying to encapsolute them OR find a solution on GitHub that has been tested and user community approved. If you can use an ORM instead to completely remove your need on using ADO.NET directly.

Also do not reuse database connections across your application, it is best to keep these short lived so use it and dispose it. Do this with using statements.

Types

Use types, you are now treating everything as string which is very bad practice as not everything is a string. Return a new Client type, not a list of strings. Make sure the Client type has properties of the correct type like DateTime for date of birth (for example).

Code Fix

Client client = null;

string sqlQuery = "SELECT Nome, Cognome, Giorno_Nascita, Mese_Nascita, Anno_Nascita, Luogo_Nascita, Residenza, Provincia_Residenza, Indirizzo_Residenza, Civico_Residenza, Domicilio, Provincia_Domicilio, Indirizzo_Domicilio, Civico_Domicilio, Mail, Telefono_Fisso, Telefono_Mobile, Fax, Codice_Fiscale " +
"FROM DatiClienti " +
"WHERE Nome LIKE  @name AND Cognome LIKE  @surname";

using (SqlConnection con = new SqlConnection("Your Connection, ideally from an app.config"))
using (SqlCommand cmd = new SqlCommand(sqlQuery, con))
{
    con.Open();
    cmd.Parameters.Add(new SqlDbParameter("@name", SqlDbType.VarChar, 200){Value = name}); // specify the correct DbType and Length
    cmd.Parameters.Add(new SqlDbParameter("@surname", SqlDbType.VarChar, 200){Value = surname}); // specify the correct DbType and Length
    using(var reader = cmd.ExecuteReader())
    {
        if(dbReader.Read())
        {
            client = new Client();
            client.Nome = reader.GetString(0);
            client.Cognome = reader.GetString(1);
            client.GiornoNascita = reader.GetString(2);
            client.MeseNascita = reader.GetString(3);
            // etc
        }
    }
}
return client;