CODEWITHSUNDEEP
c#arrays
Mr.Smithyyy
Mr.Smithyyy

Reputation: 1329

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

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();
}

Upvotes: 0

Views: 67

Answers (1)

James Thorpe
James Thorpe

Reputation: 32212

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.

Upvotes: 4

Related Questions