SQL, C# - Can't convert int32 to string?

Question

Alright, so I have the following SQL query:

 query.CommandText = "SELECT FirstName, LastName, Age FROM Characters WHERE LastName LIKE   '"+name+"' ORDER BY Age";

And this loop that stores the result in the string "output"

while (reader.Read()) {
    output = output + reader.GetString(0) + reader.GetString(1) + reader.GetString(2).ToString();
}

However, for the third attribute age, which is an int, I get an error

could not convert System.Int32 to type System.String

As you see, I've already tried to solve this by using the int.ToString() function but I still get the same error. What am I doing wrong here? Thanks in advance!

Please look up "SQL Injection" in your search engine of choice. — Joe Enos
– Joe Enos, Commented Jul 3, 2014 at 15:19
If you read the documentation, it states that no conversion is performed, so the data retrieved must already be a string, Int32, etc. — Tim
– Tim, Commented Jul 3, 2014 at 15:23

Tim Schmelter · Accepted Answer · 2014-07-03 16:12:37Z

6

reader.GetString(2).ToString() is pointless, it either is already a string or you use the wrong method. Since you've mentioned that it's an int use reader.GetInt32:

string firstName = reader.GetString(0);
string larstName = reader.GetString(1);
int age = reader.GetInt32(2);
output = output + string.Format("{0}{1}{2}", firstName, lastName, age);

If the column Age is nullable you need to use IsDBNull first, you could also use an int? then:

int? age = null;
if(!reader.IsDBNull(2))
    age = reader.GetInt32(2);

If you want to access a field by it's name rather than via index, use GetOrdinal:

int ageIndex = reader.GetOrdinal("Age");
int? age = null;
if(!reader.IsDBNull(ageIndex))
    age = reader.GetInt32(ageIndex);

As an important aside, you're open for sql-injection here:

query.CommandText = @"SELECT FirstName, LastName, Age 
                      FROM Characters 
                      WHERE LastName LIKE '" + name + "' ORDER BY Age";

because you're concatenating the sql query. Instead use sql-parameters:

query.CommandText = @"SELECT FirstName, LastName, Age 
                      FROM Characters 
                      WHERE LastName LIKE '%' + @LastName + '%' 
                      ORDER BY Age";
query.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = name;

(if you don't want to find also substrings you don't need to use LIKE, then you can use =)

edited Jul 3, 2014 at 16:12

answered Jul 3, 2014 at 15:18

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1531921 Over a year ago

Right, that makes sense! Never used the reader before so I didn't research which methods it has

tschmit · Accepted Answer · 2014-07-03 15:24:37Z

1

agree with Tim but something like

reader[2].ToString();

may be more generic and less buggy if you change your select order.

following Tim Comment:

reader["Age"].ToString();

answered Jul 3, 2014 at 15:24

tschmit

7,8402 gold badges38 silver badges45 bronze badges

2 Comments

Tim Schmelter Over a year ago

If you change the select it is better to get an exception at GetInt32 instead of an incorrect result.

tschmit Over a year ago

@TimSchmelter true also

Collectives™ on Stack Overflow

SQL, C# - Can't convert int32 to string?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related