0

I have the following create statement:

CREATE TABLE Products
(
      SKU char(255) NOT NULL PRIMARY KEY
    , Supplier int FOREIGN KEY REFERENCES Suppliers(Id) NOT NULL 
    , Category char(255) FOREIGN KEY REFERENCES ProductsCategories(name) 
    , Temp char(255) FOREIGN KEY REFERENCES StorageTemperatures(name)
    , Description char(255) NOT NULL UNIQUE 
    , Price money NOT NULL
);

I have the following C# program:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace mssqlDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World");

            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString = "Data Source=localhost;Initial Catalog=my_playground;Integrated Security=True";

                connection.Open();
                Console.WriteLine("Successfully connected to database! I'M NOT DUMB");

                SqlCommand command = new SqlCommand("SELECT * FROM Products", connection);

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    // while there is another record present
                    while (reader.Read())
                    {
                        Console.WriteLine(string.Format("There are {0} columns", reader.FieldCount));    
                        Console.WriteLine(String.Format("{0} \t | {1} \t | {2} \t | {3} \t", reader[0], reader[1], reader[2], reader[3]));
                    }
                }

                connection.Close();
            }

            Console.WriteLine("Press any key to continue...");
            Console.ReadLine();
        }
    }
}

If I try to access more than 3 columns it will throw an error:

System.IndexOutOfRangeException

Why does it do that and how do I fix this?

Note that the value of reader.FieldCount is 3.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Does this answer your question? [What is an IndexOutOfRangeException / ArgumentOutOfRangeException and how do I fix it?](https://stackoverflow.com/questions/20940979/what-is-an-indexoutofrangeexception-argumentoutofrangeexception-and-how-do-i-f) – Yong Shun May 10 '21 at 00:39
  • 3
    Either you have more than one table named "Products" or you are connected to a different database whose Products table is not what you think. Put your breakpoint on the USING statement, execute it, and then start examining all the properties of that object - just basic debugging. What does FieldCount return? – SMor May 10 '21 at 00:43
  • 1
    You can enumerate the columns like [this](https://stackoverflow.com/questions/681653/can-you-get-the-column-names-from-a-sqldatareader) – SMor May 10 '21 at 00:45
  • Can I also suggest you stop using data readers and instead use something like Dapper? So that the data is automatically mapped for you. – mjwills May 10 '21 at 00:46
  • Are you sure you are connecting to the right mdf file? Most likely you are not connected to the right database. Use SQL Server Management Studio and connect to database. Then use the SQL Server that is in the login window of SSMS in your connection string as the Data Source. Also with SSMS do a query of the Intial Catalog to verify the database has the correct number of columns. – jdweng May 10 '21 at 00:49
  • @jdwent He did that. Visual Studio reports the first 3 columns. Sql Management Studio reports all 6. The connection strings are exactly the same. – Christopher Pisz May 10 '21 at 02:08
  • `Field count is returning 3 when it should return 6.` So it is connected to the wrong database. Or it is connected to the right database but the database has two tables with the same name. Or the OP has table changes that they forgot to save. – mjwills May 10 '21 at 02:10
  • @jonathon_a The multiple database thing could be it though, did you check if you have mssqlexpress installed and running? it might also be localhost, and just a different port or something? I know you had mentioned playing with it previously as well as mysql – Christopher Pisz May 10 '21 at 02:19
  • Guys, I got on screenshare and verified there is one and only one database, at least as far as msql server management studio is concerned, there is one and only one Products table. Field count is returning 3 when it should return 6. Maybe there is some other database that is also "localhost" – Christopher Pisz May 10 '21 at 02:20
  • 1
    @ChristopherPisz If it was a different port then the connection string as shown wouldn't connect to it. How _exactly_ did you verify that there were 6 columns? This class of issue is almost always caused by the big three - `So it is connected to the wrong database. Or it is connected to the right database but the database has two tables with the same name. Or the OP has table changes that they forgot to save.` And when I say "almost always" I mean "I've never seen it be anything else other than those three" and we get this question multiple times a day. – mjwills May 10 '21 at 02:20
  • @mjwill Pretty sure he isnt using port and his conneciton string was of form: "Data Source=localhost;Initial Catalog=myplayground;Integrated Security=True"...need him to verify by selecting the db in server explorer, right click, look at properties. – Christopher Pisz May 10 '21 at 02:21
  • @mjwills yea, I think he is using integrated security. He'll need to check. – Christopher Pisz May 10 '21 at 02:27
  • I suppose we really should make a proper database user with permissions to read and update, for db and its tables you want to play with. I can help you with that during the week, Jonathon, if reboot helps or not. But let us know. There are experts here far better at databases then I. – Christopher Pisz May 10 '21 at 02:40
  • I guarantee you - it is one of the big three. It always is. – mjwills May 10 '21 at 03:04
  • Try to create a new table with different, new name, 6 columns and check if you can read data from it. – Piotr Palka May 10 '21 at 04:25
  • We're trying again today. Same symptoms on his PC, but not mine. Went through add/remove programs with him and only see the one MS Sql Server installed. No other DB is installed. Rebooted, went into Management Studio, only see the one database. Had him create a login and a user. Made them both public, associated the database in question, made default schema dbo, restarted database. Went back to Visual Studio, modified connection to use Sql Server Auth and logged in as the new user. It still only shows the three tables instead of six. – Christopher Pisz May 17 '21 at 01:40

0 Answers0