Specific cast is not valid while converting SQL Server data to Array

Question

Database:

I try to convert SQL Server data to an array, but I got an error:

Specific cast is not valid

This is my code:

string query = "select part1,part2,part3 from Table_3 where id ='" + textBox1.Text + "'";
int[] arr = new int[] { };
SqlCommand cmd = new SqlCommand(query, con);

SqlDataReader myReader;

try
{
    con.Open();

    myReader = cmd.ExecuteReader();

    while (myReader.Read())
    {
        arr[i] = myReader.GetInt16(1);
        i = i+1;
    }

    con.Close();
    MessageBox.Show(arr[0].ToString());
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) — marc_s, Sep 10 '19 at 06:42
Also: where have you **defined** and **initialised** the `arr` array variable?? — marc_s, Sep 10 '19 at 06:42
https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection — mjwills, Sep 10 '19 at 06:44
what is the datatype of columns? if integer, then try GetInt32(1) method, then you might get "index out of exception" exception as the array length is zero. So you need specify some length to array — Vinit, Sep 10 '19 at 07:00
Note: from the data shown, it is *also* possible that `part1`, `part2` and `part3` are `bit` columns, in which case you'll want `bool` / `GetBoolean` — Marc Gravell, Sep 10 '19 at 07:04

Marc Gravell · Answer 1 · 2019-09-10T07:04:45.250

There are a couple of problems here. The most likely immediate problem is that the data is not in fact Int16 - usually Int32 (int) is more common, so the GetInt16 is probably wrong.

Arrays are not resizeable. If you create a zero-length array, it will always be zero length. You cannot add any values to it.

I find it curious that you're reading four columns but only consuming the second one - but that won't actually cause a failure.

You only seem to actually want the first value, not all of them - is an array even needed?

There are a few things relating to disposing the reader, but they're easily fixed.

And: never ever concatenate input into SQL.

The easiest way to fix all of these is with a tool like "Dapper":

it knows how to convert between primitive types
it makes it easy to correctly handle readers
it makes it easy to correctly parameterize inputs
it makes it easy to handle results as lists, arrays, etc

int[] arr = con.Query<int>("select part2 from Table_3 where id = @id",
    new { id = textBox1.Text }).ToArray();

or for just the single (first) result:

int val = con.QueryFirst<int>("select part2 from Table_3 where id = @id",
    new { id = textBox1.Text });

Note: if the data is actually defined as bit, then you'll want bool/Boolean in .NET.

@Harish I literally did - the code shown does use "Dapper", though - which is free and readily available from nuget: https://www.nuget.org/packages/Dapper/ — Marc Gravell, Sep 10 '19 at 06:54

Specific cast is not valid while converting SQL Server data to Array

1 Answers1