System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '@med_data'

1

I am developing a project and you are giving me an error :

"System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '@med_data'."

But I can't figure out what could be wrong ....

string humidade = "", temperatura = "", heatIndex = "", lpgGas = "", monoCarbo = "", smoke = "";
            var macAddr =
            (
                from nic in NetworkInterface.GetAllNetworkInterfaces()
                where nic.OperationalStatus == OperationalStatus.Up
                select nic.GetPhysicalAddress().ToString()
            ).FirstOrDefault();
            Console.WriteLine("Mac adress do pc: {0}", macAddr);

                while (true)
                {
                    var dataRx = myPort.ReadLine();

                    var underscore = dataRx.Split('_');
                    humidade = underscore[0];
                    temperatura = underscore[1];
                    heatIndex = underscore[2];
                    lpgGas = underscore[3];
                    monoCarbo = underscore[4];
                    smoke = underscore[5];

                    Console.WriteLine(humidade);
                    Console.WriteLine(temperatura);
                    Console.WriteLine(heatIndex);
                    Console.WriteLine(lpgGas);
                    Console.WriteLine(monoCarbo);
                    Console.WriteLine(smoke);
                    Console.WriteLine("___________________");

                    string connString = "Data Source=(LocalDb)\\MSSQLLocalDB;Initial Catalog=tupaidb;Integrated Security=True";
                      SqlConnection conn = new SqlConnection(connString);
                    conn.Open();
                    if (conn.State==System.Data.ConnectionState.Open)
                    {
                        SqlDataAdapter cmd = new SqlDataAdapter();
                      cmd.InsertCommand = new SqlCommand ("INSERT INTO Bi_medicao VALUES (@med_hum, @med_temp, @med_lpg, @med_co, @med_fumo, @med_data");
                        cmd.InsertCommand.Connection = conn;
                        cmd.InsertCommand.Parameters.Add("@med_hum", humidade);
                        cmd.InsertCommand.Parameters.Add("@med_temp", temperatura);
                        cmd.InsertCommand.Parameters.Add("@med_lpg", lpgGas); 
                        cmd.InsertCommand.Parameters.Add("@med_co", monoCarbo);
                        cmd.InsertCommand.Parameters.Add("@med_fumo", smoke);
                        cmd.InsertCommand.Parameters.Add("@med_data", SqlDbType.Date);
                       
                        cmd.InsertCommand.ExecuteNonQuery();
                       conn.Close();

                    }

                
                
            }
c#
sql
sql-server
asked on Stack Overflow May 13, 2021 by Pedro Tavares • edited May 13, 2021 by Larnu

1 Answer

1

a: you need a closing paren, and b: when using INSERT/VALUES, it is a really good idea to be explicit about the column order:

// note I'm guessing at names here!
new SqlCommand (@"
INSERT INTO Bi_medicao (Humidity, Temperature, Lpg, Co, Fumo, Data)
VALUES (@med_hum, @med_temp, @med_lpg, @med_co, @med_fumo, @med_data)");

It should also be noted that there's a lot of missing using here, and the data-adapter is unnecessary. Honestly, I'd just use Dapper for this:

using Dapper; // at the top of the file, with a Dapper package ref
// ...
using var conn = new SqlConnection(connString);
conn.Execute(@"
INSERT INTO Bi_medicao (Humidity, Temperature, Lpg, Co, Fumo, Data)
VALUES (@humidade, @temperatura, @lpgGas, @monoCarbo, @smoke, @data )",
    new { humidade, temperatura, lpgGas, // parameters
        monoCarbo, smoke, data = DateTime.UtcNow });

Note also that the use of SqlDbType.Date looks very wrong - there should be a value somewhere. I've guessed at DateTime.UtcNow.

answered on Stack Overflow May 13, 2021 by Marc Gravell

User contributions licensed under CC BY-SA 3.0