Exception about data type

Question

I've created an application where there is a datagridview. When I click on a button, the data written in the datagridview are sent to my database in a table. But I have an exception and I'm lost.

The error :System.Data.SqlClient.SqlException : 'Fail to convert nvarchar value 'B003382A3' to int value.'

I don't even know why he tries to convert into int because my data is nvarchar in my DB. The only suggestion I would have is that he is inverting cells.

My code:

private void metroButton1_Click(object sender, EventArgs e)
{
    SqlConnection maConnexion = new SqlConnection("Server= localhost; Database= Seica_Takaya;Integrated Security = SSPI; ");
    maConnexion.Open();

    foreach (DataGridViewRow row in dataGridView1.Rows)
    {
        if ((row.Cells[20].Value != null) && (bool)row.Cells[20].Value)
        {
            SqlCommand command = maConnexion.CreateCommand();

            command = new SqlCommand("update FailAndPass set Machine=@machine, ProgCode=@pc, BoardName=@BName, BoardNumber=@BNumber, Tester=@T, DateTest=@DT, TimeTest=@TT, TimeStart=@TS, FComponent=@FC, Message=@Mess, TotalTestProg=@TTP, ReadValue=@RV, ValueReference=@VR, PTolerance=@PT, FaultCodeByOP=@FCBO, FaultDetail=@FD,  RepairingDate=@RD, RepairingTime = @RT, ReportingOperator=@RO WHERE SerialNum=@Serial", maConnexion);
            command.Parameters.AddWithValue("@Serial", textBox1.Text);
            command.Parameters.AddWithValue("@Machine", row.Cells[0].Value != null ? row.Cells[0].Value : DBNull.Value);
            command.Parameters.AddWithValue("@pc", row.Cells[2].Value != null ? row.Cells[2].Value : DBNull.Value);
            command.Parameters.AddWithValue("@BName", row.Cells[3].Value != null ? row.Cells[3].Value : DBNull.Value);
            command.Parameters.AddWithValue("@BNumber", row.Cells[4].Value != null ? row.Cells[4].Value : DBNull.Value);
            command.Parameters.AddWithValue("@T", row.Cells[5].Value != null ? row.Cells[5].Value : DBNull.Value);
            command.Parameters.AddWithValue("@DT", row.Cells[6].Value != null ? row.Cells[6].Value : DBNull.Value);
            command.Parameters.AddWithValue("@TT", row.Cells[7].Value != null ? row.Cells[7].Value : DBNull.Value);
            command.Parameters.AddWithValue("@TS", row.Cells[8].Value != null ? row.Cells[8].Value : DBNull.Value);
            command.Parameters.AddWithValue("@FC", row.Cells[9].Value != null ? row.Cells[9].Value : DBNull.Value);
            command.Parameters.AddWithValue("@Mess", row.Cells[10].Value != null ? row.Cells[10].Value : DBNull.Value);
            command.Parameters.AddWithValue("@TTP", row.Cells[11].Value != null ? row.Cells[11].Value : DBNull.Value);
            command.Parameters.AddWithValue("@RV", row.Cells[12].Value != null ? row.Cells[12].Value : DBNull.Value);
            command.Parameters.AddWithValue("@VR", row.Cells[13].Value != null ? row.Cells[13].Value : DBNull.Value);
            command.Parameters.AddWithValue("@PT", row.Cells[14].Value != null ? row.Cells[14].Value : DBNull.Value);
            command.Parameters.AddWithValue("@FCBO", row.Cells[15].Value != null ? row.Cells[15].Value : DBNull.Value);
            command.Parameters.AddWithValue("@FD", row.Cells[16].Value != null ? row.Cells[16].Value : DBNull.Value);
            command.Parameters.AddWithValue("@RD", row.Cells[17].Value != null ? row.Cells[17].Value : DBNull.Value);
            command.Parameters.AddWithValue("@RT", row.Cells[18].Value != null ? row.Cells[18].Value : DBNull.Value);
            command.Parameters.AddWithValue("@RO", row.Cells[19].Value != null ? row.Cells[19].Value : DBNull.Value);
            command.ExecuteNonQuery();

        }
    }

    maConnexion.Close();
    this.Hide();
    Repair rep = new Repair();
    rep.Show();
}

Here is a pic of my DB:

I've used the debugger and it seems my cells aren't right. The first column in my datagridview is Machine, and the corresponding cell in the code is row.cells1.

It seems that all my cells are not in the right order as they seem to be. What the hell is this? I mean, normally BoardName is the third cell, but I put row.cell[2] I don't have the error anymore. And it's not the only one cell to have this error.

All columns in the Table `FailAndPass` are of type `NVARCHAR`? — Romano Zumbé, Jul 26 '17 at 08:30
I assumed all columns except `SerialNum` are nullables. Try using `DbType` to specify data type for string parameters, e.g. `command.Parameters.AddWithValue("@Machine", row.Cells[0].Value != null ? row.Cells[0].Value : DBNull.Value).DbType = DbType.String;`. — Tetsuya Yamamoto, Jul 26 '17 at 08:35
side note: `private void AddParameter(SqlCommand command, string ParameterName, object value) { command.Parameters.AddWithValue(ParameterName, value != null ? value : DBNull.Value); }` — Zohar Peled, Jul 26 '17 at 08:39
@RomanoZumbé Not all the columns, like the one concern BoardName is nvarchar, but the next one to it BoardNumber is int. — Alexis Rin, Jul 26 '17 at 08:45
I know it is not an int haha ! And i know i can't parse. But i've never said in my code or in my database that this column is int ! — Alexis Rin, Jul 26 '17 at 09:04
https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ - quote from blog post "There is a problem with the AddWithValue() function: it has to infer the database type for your query parameter. Here’s the thing: sometimes it gets it wrong." — Nenad Zivkovic, Jul 26 '17 at 09:09
You're said that `BoardName` has `NVARCHAR` data type, so that you can write like this: `command.Parameters.Add("@BName", SqlDbType.NVarChar).Value = row.Cells[3].Value != null ? row.Cells[3].Value.ToString() : DBNull.Value`. I prefer `Add` to `AddWithValue` on this assignment. — Tetsuya Yamamoto, Jul 26 '17 at 09:19
I've used your code, i have an error saying it can't determine the type of the expression because there is no implicit conversion between "String" and "System.DBNull" — Alexis Rin, Jul 26 '17 at 09:26
Ah, I'm slipped a little bit. How about casting `DbNull.Value` to `object` type: `command.Parameters.Add("@BName", SqlDbType.NVarChar).Value = row.Cells[3].Value == null ? (object)DBNull.Value : row.Cells[3].Value`? — Tetsuya Yamamoto, Jul 26 '17 at 09:33
Back to error from the beginning with the nvarchar and int...... — Alexis Rin, Jul 26 '17 at 09:36
How about cast all to same object & then do ToString: `command.Parameters.Add("@BName", SqlDbType.NVarChar).Value = (row.Cells[3].Value == null ? (object)DBNull.Value : (object)row.Cells[3].Value).ToString()`? — Tetsuya Yamamoto, Jul 26 '17 at 09:42

score 1 · Answer 1 · 2017-07-26T10:27:04.690

Update with your update:

The cell you've been selecting has a corresponding int value int he database. I suggest you check your datagridview population - as for the positions within the row to change, there must have been a change in the code.

For future reference, it's better not to deny that there is an int within the database table, when there is, as it really helps with answering the question and can provide a much simpler solution and to guide you where to look.

The rest of the answer - while helpful - is window dressing at this stage.

Use

row.Cells[0].Value.ToString() // wherever you are getting the value and it's not null.

And also use

command.Parameters.Add(.../...

for example

parameter = command.Parameters.Add("@InputParm", OleDbType.VarChar, 12);  
// which explicitly types the datatype - with the number of char.
parameter.Value = "Sample Value";

There can be issues with datatype conversions using AddWithValue.

I've tested your code upside, doesnt work still the same error. Dont think it would come from add. — Alexis Rin, Jul 26 '17 at 09:15

Önder Öğretmen · Answer 2 · 2017-07-26T10:04:42.323

I' m always using this style of code for working with SQL

 SqlConnection connection = new 
 SqlConnection(ConfigurationManager.AppSettings["SQLConnectionString"]);

 SqlCommand command = new SqlCommand();
 command.CommandTimeout = 1000;
 command.Connection = connection;
 command.CommandType = CommandType.StoredProcedure;
 command.CommandText = "KULLANICI_OKU";

 SqlParameter parameter = new SqlParameter();
 parameter.Direction = ParameterDirection.Input;
 parameter.ParameterName = "@KULLANICI";
 parameter.DbType = DbType.String;
 parameter.Value = user;
 command.Parameters.Add(parameter);

In my opinion working with store procedures is better way for sql.

score 0 · Answer 3 · answered Jul 26 '17 at 10:07

First thing you should know: you can't assign DBNull.Value into a string (nvarchar in SQL) or other struct value types because they're incompatible each other. Common way to use ternary operator between DBNull.Value & string (or struct types) is casting DBNull.Value to object type.

Here is an example how to perform cast against DBNull.Value in ternary operator:

// a & b are cell numbers for respective cells
// change to any number representing the cell order

foreach (DataGridViewRow row in dataGridView1.Rows)
{
    if ((row.Cells[20].Value != null) && (bool)row.Cells[20].Value)
    {
        SqlCommand command = maConnexion.CreateCommand();

        command = new SqlCommand("update FailAndPass set Machine=@machine, ProgCode=@pc, BoardName=@BName, BoardNumber=@BNumber, Tester=@T, DateTest=@DT, TimeTest=@TT, TimeStart=@TS, FComponent=@FC, Message=@Mess, TotalTestProg=@TTP, ReadValue=@RV, ValueReference=@VR, PTolerance=@PT, FaultCodeByOP=@FCBO, FaultDetail=@FD,  RepairingDate=@RD, RepairingTime = @RT, ReportingOperator=@RO WHERE SerialNum=@Serial", maConnexion);
        // other parameters

        // integer value parameter example
        command.Parameters.Add("@pc", SqlDbType.Int).Value = row.Cells[a].Value != null ? row.Cells[a]Value : (object)DBNull.Value;

        // string value parameter example
        command.Parameters.Add("@BName", SqlDbType.NVarChar).Value = (row.Cells[b].Value != null ? (object)row.Cells[b].Value : (object)DBNull.Value).ToString();

        // other parameters

        command.ExecuteNonQuery();

    }
}

// other stuff

If the compiler supports null-coalescing operator, it's even more simpler:

// integer parameter
command.Parameters.Add("@pc", SqlDbType.Int).Value = row.Cells[a].Value ?? (object)DBNull.Value;

// string parameter
command.Parameters.Add("@BName", SqlDbType.NVarChar).Value = (row.Cells[b].Value ?? (object)DBNull.Value).ToString();

Reference:

How do I Parameterize a null string with DBNull.Value clearly and quickly

When i'm using this way i have this error : System.FormatException : fail converting value string in int32.' — Alexis Rin, Jul 26 '17 at 11:19

score 0 · Answer 4 · answered Jul 26 '17 at 12:22

Okay so i've found the solution of my problem.

Here is the code :

private void metroButton1_Click(object sender, EventArgs e)
    {
        SqlConnection maConnexion = new SqlConnection("Server= localhost; Database= Seica_Takaya;Integrated Security = SSPI; ");
        maConnexion.Open();

        foreach (DataGridViewRow row in dataGridView1.Rows)
        {


            if ((row.Cells[20].Value != null) && (bool)row.Cells[20].Value)
            {

                SqlCommand command = maConnexion.CreateCommand();


                command = new SqlCommand("update FailAndPass set Machine=@machine, ProgCode=@pc, BoardName=@BName, BoardNumber=@BNumber, Tester=@T, DateTest=@DT, TimeTest=@TT, TimeStart=@TS, FComponent=@FC, Message=@Mess, TotalTestProg=@TTP, ReadValue=@RV, ValueReference=@VR, PTolerance=@PT, FaultDetail=@FD, RepairingDate=@RD, RepairingTime=@RT, ReportingOperator=@RO, FaultCodeByOP=@FCBO  WHERE SerialNum=@Serial", maConnexion);

                command.Parameters.AddWithValue("@Machine", row.Cells[1].Value != null ? row.Cells[1].Value : DBNull.Value);
                command.Parameters.AddWithValue("@Serial", textBox1.Text);
                command.Parameters.AddWithValue("@pc", row.Cells[3].Value != null ? row.Cells[3].Value : DBNull.Value);
                command.Parameters.AddWithValue("@BName", row.Cells[4].Value != null ? row.Cells[4].Value : DBNull.Value);
                command.Parameters.AddWithValue("@BNumber", row.Cells[5].Value != null ? row.Cells[5].Value : DBNull.Value);
                command.Parameters.AddWithValue("@T", row.Cells[6].Value != null ? row.Cells[6].Value : DBNull.Value);
                command.Parameters.AddWithValue("@DT", row.Cells[7].Value != null ? row.Cells[7].Value : DBNull.Value);
                command.Parameters.AddWithValue("@TT", row.Cells[8].Value != null ? row.Cells[8].Value : DBNull.Value);
                command.Parameters.AddWithValue("@TS", row.Cells[9].Value != null ? row.Cells[9].Value : DBNull.Value);
                command.Parameters.AddWithValue("@FC", row.Cells[11].Value != null ? row.Cells[11].Value : DBNull.Value);
                command.Parameters.AddWithValue("@Mess", row.Cells[10].Value != null ? row.Cells[10].Value : DBNull.Value);                   
                command.Parameters.AddWithValue("@TTP", row.Cells[12].Value != null ? row.Cells[12].Value : DBNull.Value);
                command.Parameters.AddWithValue("@RV", row.Cells[13].Value != null ? row.Cells[13].Value : DBNull.Value);
                command.Parameters.AddWithValue("@VR", row.Cells[14].Value != null ? row.Cells[14].Value : DBNull.Value);
                command.Parameters.AddWithValue("@PT", row.Cells[15].Value != null ? row.Cells[15].Value : DBNull.Value);
                command.Parameters.AddWithValue("@FD", row.Cells[16].Value != null ? row.Cells[16].Value : DBNull.Value);
                command.Parameters.AddWithValue("@RD", row.Cells[17].Value != null ? row.Cells[17].Value : DBNull.Value);
                command.Parameters.AddWithValue("@RT", row.Cells[18].Value != null ? row.Cells[18].Value : DBNull.Value);
                command.Parameters.AddWithValue("@RO", row.Cells[19].Value != null ? row.Cells[19].Value : DBNull.Value);
                command.Parameters.AddWithValue("@FCBO", row.Cells[20].Value != null ? row.Cells[20].Value : DBNull.Value);

                command.ExecuteNonQuery();




            }
        }

}

Now i can replace all addwithvalue by add.

Explanation : It seems that a Cell does not being count as you would with a column or index. I though that the cell was waiting for an index [0] -> [max]. Or, i saw that row.cells[0] didn't contain the name of the machine but was NULL. In consequences, if my cell was starting at index[0], all my data were off-set. Then, sure enough, BoardName was in the ProgCode Cell so inserted in the ProgCode column , which is int-type, in the database and so raised the error !

That was mind blowing, and i've done this step by step. But thank you everyone for your help because at least you've learned me some things !

As far as I remember, Cell[0] is the row selector. – Zohar Peled Jul 26 '17 at 13:11 — Zohar Peled, Jul 26 '17 at 13:11
Oh so that's explaining a lot – Alexis Rin Jul 26 '17 at 13:21 — Alexis Rin, Jul 26 '17 at 13:21

Exception about data type

4 Answers4