Convert an int to a string saved in a SQL Server database

Question

I have a SQL Server database with a table Event_Tab with an Int column Code. I want to associate this Code with a specific string and this so I don't save string variable in my table.

So, I wrote a procedure GetStringCode with In16 input parameter and it returns the string that I need, then I fill it in a listview with the other parameter saved in my table

This is my code to do it :

using (SqlConnection connection = new SqlConnection(connectionstring))
using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
{
    DataTable table = new DataTable();
    adapter.Fill(table);

    foreach(DataRow dr in table.Rows)
    {
        ListViewItem items = new ListViewItem(dr["Machine"].ToString());                    
        items.SubItems.Add(GetStringCode((short)dr["Code"]).ToString());
        items.SubItems.Add(dr["Date_time"].ToString());
        listview.Items.Add(items);
    }
}

If you notice, I did a cast to get rid of an error

Cannot convert from object to short

and so far everything seems okay. But when a try to run this code, I am getting an error

The specific cast is invalid

What is the problem because I can't seem to find it...

What is the content for the 'DataRow dr` when the error occurs? Perhaps **"Code"** is empty or null. — Stringfellow, May 21 '18 at 15:56
@Stringfellow, it's not empty i checked my table data, it has number — lawir, May 21 '18 at 16:14
Start by inspecting the value and actual type of `dr["Code"]` in the debugger. — glenebob, May 21 '18 at 16:21

score 1 · Accepted Answer · answered May 22 '18 at 03:31

You may be running into an issue with unboxing, rather than type casting. A variable of type int will cast to short. However, a boxed int will not cast directly to short. A boxed value type can be directly cast only to the exact boxed type.

int i = 100;
object o = i; // Boxed int.
short s1 = (short) i; // Works.
short s2 = (short) o; // Throws InvalidCastException.
short s3 = (short) (int) o; // Works.

DataRow stores field values as object, so value types are boxed. That means that attempting to cast a field value that is a boxed int, directly to short, is causing the issue. A two-stage cast (as in s3 above) may solve the problem.

What is the difference between boxing/unboxing and type casting?

it worked thanks, i will go check about the boxing/unboxing because it's the first time i encountered it — lawir, May 22 '18 at 13:19

score 0 · Answer 2 · answered May 21 '18 at 20:34

0

If the SQL table is defining the column for Code as below:

CREATE TABLE Event_Tab (
    Machine varchar(255),
    Code int,
    Date_time datetime
);

Then the datatype coming from SQL will not cast to C#'s short or Int16 because SQL int is equivalent to C# Int32 (Data Type Mapping)

static void Main(string[] args)
{
    DataTable table = new DataTable();
    table.Columns.Add("Machine", typeof(string));
    table.Columns.Add("Code", typeof(SqlInt32));
    table.Columns.Add("Date_time", typeof(DateTime));

    DataRow dr = table.NewRow();
    dr.ItemArray = new object[] { "machineA", 1122, DateTime.Now };

    // Works
    Int32 i32 = ((SqlInt32)dr["Code"]).Value;

    // Throws 'Specified cast is not valid.'
    Int16 i16 = (short)dr["Code"];
}

answered May 21 '18 at 20:34

Stringfellow

2,788
2
21
36

are you suggesting that a change my code column type to SqlInt32? because i don't even have this column type as a choice, and i couldn't do the cast in c# with SqlInt32 it deosn't recognize it – lawir May 22 '18 at 13:16
No, not suggesting to use `SqlInt32`. Was trying to demonstrate how the type from SQL may not be matching the type in C#, causing the invalid cast exception. Was hoping to imply using the debugger to dig deeper into the datatype returned from SQL and in the DataTable. By the way, `SqlInt32` is in `using System.Data.SqlTypes;` – Stringfellow May 22 '18 at 16:47

Convert an int to a string saved in a SQL Server database

2 Answers2