The conversion failed to convert the varchar value 'No' to the data type tinyint

Question

The conversion failed to convert the varchar value 'No' to the data type tinyint

Navigation

#1 by (0 votes)
#2 by (0 votes)

2

I am trying to solve an error I think of converting a variable created by me to store data that is in an Excel sheet.

The error happens when I try to insert the data that is in this variable in the SQL Server , that is in the (query).

code:

variable declaration:

string valmoradafiscaligualmoradalocal = "";

Information search code:

case 47://coluna 21

if (((WS.Cells[linha, Contcoluna] as Excel.Range).Value) != null)
{
    valmoradafiscaligualmoradalocal = Convert.ToString((WS.Cells[linha, Contcoluna] as Excel.Range).Value);  
}
continue;

insertion code in sql server:

cmd.CommandText = "INSERT tabela_sacc (Morada_Fiscal_Igual_a_Morada_Local) VALUES ('" + valmoradafiscaligualmoradalocal.ToString() + "')";

connection.Open();
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
connection.Close();
valgrupo = null;

sql-server asp.net excel

asked by anonymous 03.03.2017 / 18:11

2 answers

What is the difference between the img, picture and figure elements? MySQLi vs PDO - Which is the most recommended to use?

score 0 · Answer 1

Friend, good afternoon

If the value you are trying to insert is an integer, try to take the single quotation marks out of your code

    cmd.CommandText = "INSERT tabela_sacc (Morada_Fiscal_Igual_a_Morada_Local) VALUES (" + valmoradafiscaligualmoradalocal + ")";

The restart would be to use sqlparameter link where you define what type of table

I hope to have helped

score 0 · Answer 2

Friend, try to make your code using sql parameter, follow example

 var serverName = "LAPTOP-EVEJOG4C";
        var databaseName = "MyDatabase";
        var dataString = "Não";
        using (SqlConnection conn = new SqlConnection($"Server={serverName};Database={databaseName};Trusted_Connection=True;"))
        {
            conn.Open();
            string sql = "insert Example01 (DadoTinyInt) values (@DadoTinyInt)";

            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.Parameters.Add("@DadoTinyInt", System.Data.SqlDbType.TinyInt).Value =
                    dataString == "Não" ? 0 : 1;
                cmd.ExecuteNonQuery();
            }
        }