We help IT Professionals succeed at work.

SQL error message - Index was outside the bounds of the array

GianniP asked
Medium Priority
Last Modified: 2013-11-06
I have the following INSERT statement:

cmd = New OleDbCommand("INSERT INTO Products ([ProductID], [ProductName], [ManufacturerID], [CategoryID], [MainCategoryID], [SmallDesc], [Description], [Thumbnail], [Price], [MarketPrice], [Status], [Hits], [IsNew], [IsSpecial], [SpecialPrice], [ProductCode], [Weight], [VAT], [CreationDate]) VALUES ('" + s.Split(";"c)(0) + "','" + s.Split(";"c)(1) + "','" + s.Split(";"c)(2) + "','" + s.Split(";"c)(3) + "','" + s.Split(";"c)(4) + "','" + s.Split(";"c)(5) + "','" + s.Split(";"c)(6) + "','" + s.Split(";"c)(7) + "'," + s.Split(";"c)(8) + "," + s.Split(";"c)(9) + ",''" + s.Split(";"c)(10) + "," + s.Split(";"c)(11) + ",'" + s.Split(";"c)(12) + "','" + s.Split(";"c)(13) + "'," + s.Split(";"c)(14) + ",'" + s.Split(";"c)(15) + "'," + s.Split(";"c)(16) + "," + s.Split(";"c)(17) + ")")

An example of the data that will be inserted is :

M0001;SHRADER VALVE CORE;M0083;C0166;C0050;short desc;Short valve cores suitable for all Schrader type valvesRated up to 200 psiResistant to petrol, oil and other ontaminants;

I am getting this error when I try and import data (in a csv).  Index was outside the bounds of the array."

I am not sure where I am going wrong. I hope the below explanation makes sense.

Ia)  do not have a value for  [CreationDate] because in the Access DB, the date is inseretd automatically when the record is inserted.

b) [Status], [IsNew], [IsSpecial]  are yes / no values and I'm not sure if the data should have a ' ' around the 0  and 1

Can anyone see where I am going worng or have a suggestion ?

Watch Question


Ia) then you should not specify the column CreationDate in the fields list or Values List
b) the value for bit in a number to no ' around the data.

The error would imply that you are passing more eliments that defined.

Cheers, Andrew
Well, I'm not sure about your data type questions, but since the error is about an array, that's where I would start.

The first thing you should do is get all of those split calls out.  You only need to split the string once into an array, then use that array in your SQL string.  That way, you can stop the code in the debugger and see if it has the correct number of elements (18 in your case).  You could even add a line that checks the size of the array before trying to make the database call.  I also recommend building the sql in a string, so you can see it in the debugger as well.

Array a = s.Split(';');
if (a.Length != 18) throw new Exception("Invalid s array!");
String sql = "INSERT INTO Products ([ProductID], [ProductName], [ManufacturerID], [CategoryID], [MainCategoryID], [SmallDesc], [Description], [Thumbnail], [Price], [MarketPrice], [Status], [Hits], [IsNew], [IsSpecial], [SpecialPrice], [ProductCode], [Weight], [VAT], [CreationDate]) VALUES ('" + a(0) + "','" + a(1) + "','" + a(2) + "','" + a(3) + "','" + a(4) + "','" + a(5) + "','" + a(6) + "','" + a(7) + "'," + a(8) + "," + a(9) + ",''" + a(10) + "," + a(11) + ",'" + a(12) + "','" + a(13) + "'," + a(14) + ",'" + a(15) + "'," + a(16) + "," + a(17) + ")";
cmd = New OleDbCommand(sql);

Open in new window

Where do you declare 'c'?


Hi TextReport

Sorry I didn't quite get point b) do you mean I should not have a ' around the bit value for either the data and the insert statement ?

Bill-Hanson , thanks I will modify and check that.


Bill-Hanson, I change the code as suggeted and when viewing the value of   a, I can see the required values. When I F11 to carry on the Debug and I check the value of cmd it shows:

INSERT INTO Products ([ProductID], [ProductName], [ManufacturerID], [CategoryID], [MainCategoryID], [SmallDesc], [Description], [Thumbnail], [Price], [MarketPrice]) VALUE ('M0001','SHRADER VALVE CORE','M0083','C0166','C0050','short desc','Short valve cores',thumbs,'0.75')

It seems the last value is not being passed to the SQL query.....why is this ?

of course I then get an Error in SQL message.


ok that last error was because I left out the value myself...apologies for that.

I'm still getting a error in INSERT statement message: here is the value of cmd:
INSERT INTO Products ([ProductID], [ProductName], [ManufacturerID], [CategoryID], [MainCategoryID], [SmallDesc], [Description], [Thumbnail], [Price], [MarketPrice]) VALUE ('M0001','SHRADER VALVE CORE','M0083','C0166','C0050','short desc','Short valve cores','thumbs','0.75','0')

what am I missing??


:-) :-)

there's ANOTHER BIG oops... I used the word VALUE and not VALUES
Are you still getting an error with this string:

INSERT INTO Products ([ProductID], [ProductName], [ManufacturerID], [CategoryID], [MainCategoryID], [SmallDesc], [Description], [Thumbnail], [Price], [MarketPrice]) VALUES ('M0001','SHRADER VALVE CORE','M0083','C0166','C0050','short desc','Short valve cores','thumbs','0.75','0')
A Couple of typo's sorry
the value for bit IS a number SO no ' around the data in your SQL is it ,0 and NOT ,'0'
Cheers, Andrew

GianniP:  I did ask a question - or have things changed such that you are no longer using 'c'?


ok I found all my errors but I would like to give my points to Bill as his sugestion pointed me in the direction of finding the errors.



helped to guide me to my own mistakes

Explore More ContentExplore courses, solutions, and other research materials related to this topic.