We help IT Professionals succeed at work.

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

GianniP
GianniP asked
on
Medium Priority
2,367 Views
Last Modified: 2013-11-06
Hi
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;
products\thumbs\05001.jpg;'0.75';'0';0;'0';1;0;'0';05101;'0.5';'21'


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 ?

Thanks
GP  
Comment
Watch Question

HI GP

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

Commented:
Where do you declare 'c'?

Author

Commented:
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.

Author

Commented:
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.

Author

Commented:
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??

Author

Commented:
:-) :-)

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

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

Author

Commented:
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.

thanks
GP

Author

Commented:
helped to guide me to my own mistakes

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