SqlDataReader does not have the correct columns count

Question

I'm designing a website using asp.net (webforms) and C#. I have a table which has 23 columns.

In the code below, reader is a SqlDataReader and command is a SqlCommand object. I'm using the following code to output query result from it:

command.CommandText = "SELECT * FROM devices WHERE device_level='" + ACCESS_LVL + "'" + "ORDER BY device_name";
reader = command.ExecuteReader();

and then I loop through it using

while(reader.Read());

to output the results.

However accessing some columns causes an index out of range exception (indexes above 17). My table has 23 columns but reader.FieldCount returns only 18.

This issue is not happening on my local machine when debugging the code. It only happens when I upload the project to my web server.

Using mylittleadmin database management panel (installed on the remote server) I can see all of the 23 columns.

Please learn to parametrise your SQL. "WHERE device_level='" + ACCESS_LVL + "'" is wide open to injection and needs to be changed ASAP. — Thom A
– Thom A ♦, Commented Nov 28, 2018 at 15:36
You should also not use SELECT *, instead list the columns that you expect explicitly — Tim Schmelter
– Tim Schmelter, Commented Nov 28, 2018 at 15:37
Well that is a pretty clear indication that your two databases do not have the same columns in those tables. — Sean Lange
– Sean Lange, Commented Nov 28, 2018 at 15:44
You can't possibly query the same table on the same database using the same query and get different columns. — Sean Lange
– Sean Lange, Commented Nov 28, 2018 at 15:47
@SeanLange you can if you query it as two different logins, and there are login-specific objects (meaning: objects in two different schemas - maybe abolfa.devices and dbo.devices) — Marc Gravell
– Marc Gravell, Commented Nov 28, 2018 at 15:51

Marc Gravell · Accepted Answer · 2018-11-28 15:55:55Z

7

The SQL server is going to be correct here. If SELECT * FROM devices ... returns 18 columns, then: the devices object on the database being connected to: has 18 columns. It sounds like you've let your schemas diverge between different environments / databases. Alternatively: it is possible that you have multiple logins, and login-specific objects (i.e. objects in two different database schemas in the same database), so: when you query it as you you see one object as devices - and when you query it as the application account you see a different object. Typically, this would be dbo.devices vs abolfa.devices (for example).

edited Nov 28, 2018 at 15:55

answered Nov 28, 2018 at 15:50

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

Collectives™ on Stack Overflow

SqlDataReader does not have the correct columns count

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related