Index was outside the bounds of the array - Inserting into database

Question

From what I understand this error happens if the number of fields in the array doesn't match what is expected? This confuses me because I have 8 fields in my database and 8 parameters being inserted.

sample.txt

"105"|2015-01-01 00:00:00|"500"|"John Walsh"|"Facebook"|"Joe"|"Schmoe"|"(555) 555-5555"
"555"|2016-05-20 12:40:00|"780"|"Justin Smith"|"Twitter"|"Daniel"|"Smith"|"(000) 000-0000"

Code

static void Main(string[] args)
{
    String line;
    try
    {
        string sConnectionString = "Data Source=localhost\\SQLEXPRESS;Initial Catalog=MyDB;User ID=MyUsername;Password=MyPassword";

        SqlConnection objConn = new SqlConnection(sConnectionString);
        objConn.Open();

        StreamReader sr = new StreamReader("C:\\Users\\ME\\Documents\\sample.txt");

        line = sr.ReadLine();
        char delimiterChar = '|';

        while (line != null)
        {
            string[] words = line.Split(delimiterChar);
            foreach (string s in words)
            {
                Console.WriteLine(s);
                string sSQL = "INSERT INTO Details " + "(Id, Date, customerId, customerName, profile, shopOwnerFirstName, shopOwnerLastName, shopOwnerPhone) " + "VALUES (@Id, @Date, @customerId, @customerName, @profile, @shopOwnerFirstName, @shopOwnerLastName, @shopOwnerPhone)";
                SqlCommand objCmd = new SqlCommand(sSQL, objConn);
                objCmd.Parameters.AddWithValue("@Id", s[0]);
                objCmd.Parameters.AddWithValue("@Date", s[1]);
                objCmd.Parameters.AddWithValue("@customerId", s[2]);
                objCmd.Parameters.AddWithValue("@customerName", s[3]);
                objCmd.Parameters.AddWithValue("@profile", s[4]);
                objCmd.Parameters.AddWithValue("@shopOwnerFirstName", s[5]);
                objCmd.Parameters.AddWithValue("@shopOwnerLastName", s[6]);
                objCmd.Parameters.AddWithValue("@shopOwnerPhone", s[7]);
                objCmd.ExecuteNonQuery();
            }
            line = sr.ReadLine();
        }
        sr.Close();
        Console.ReadLine();
}
catch (Exception e)
{
    Console.WriteLine("Exception: " + e.Message);
    Console.ReadKey();
}
finally
{
    Console.WriteLine("Executing Finally Block");
    Console.ReadKey();
}

And you are sure there isn't an empty line at the end of the file? — Patrick Hofman
– Patrick Hofman, Commented Jan 29, 2016 at 16:53
Not sure if it helps but the command line does write out "105" which is the first value, before throwing the error. — Mr.Smithyyy
– Mr.Smithyyy, Commented Jan 29, 2016 at 16:55
you sure that each line in your csv file has 8 entries? e.g. if there's a line with only 7, then s[7] is going to produce your warning. — Marc B
– Marc B, Commented Jan 29, 2016 at 16:56
You don't need to foreach the words. s[0] will be a character not a string. You should be using word[0] instead. — juharr
– juharr, Commented Jan 29, 2016 at 16:57

James Thorpe · Accepted Answer · 2016-01-29 16:57:13Z

You're looping over the words when you don't need to. s is a single entry from your array, and when you use indexers on it, you're getting individual characters. Since "500", for example, doesn't have 8 characters, you're going out of bounds. Your code should be more like:

while (line != null)
{
    string[] words = line.Split(delimiterChar);

    string sSQL = "INSERT INTO Details " + "(Id, Date, customerId, customerName, profile, shopOwnerFirstName, shopOwnerLastName, shopOwnerPhone) " + "VALUES (@Id, @Date, @customerId, @customerName, @profile, @shopOwnerFirstName, @shopOwnerLastName, @shopOwnerPhone)";
    SqlCommand objCmd = new SqlCommand(sSQL, objConn);
    objCmd.Parameters.AddWithValue("@Id", words[0]);
    objCmd.Parameters.AddWithValue("@Date", words[1]);
    objCmd.Parameters.AddWithValue("@customerId", words[2]);
    objCmd.Parameters.AddWithValue("@customerName", words[3]);
    objCmd.Parameters.AddWithValue("@profile", words[4]);
    objCmd.Parameters.AddWithValue("@shopOwnerFirstName", words[5]);
    objCmd.Parameters.AddWithValue("@shopOwnerLastName", words[6]);
    objCmd.Parameters.AddWithValue("@shopOwnerPhone", words[7]);
    objCmd.ExecuteNonQuery();

    line = sr.ReadLine();
}

You might also want to be aware of the implications of using AddWithValue.

Collectives™ on Stack Overflow

Index was outside the bounds of the array - Inserting into database

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