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

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  
GianniPAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Bill-HansonConnect With a Mentor Commented:
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

0
 
TextReportCommented:
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
0
 
GRayLCommented:
Where do you declare 'c'?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
GianniPAuthor 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.
0
 
GianniPAuthor 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.
0
 
GianniPAuthor 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??

0
 
GianniPAuthor Commented:
:-) :-)

there's ANOTHER BIG oops... I used the word VALUE and not VALUES
0
 
Bill-HansonCommented:
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')
0
 
TextReportCommented:
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
0
 
GRayLCommented:
GianniP:  I did ask a question - or have things changed such that you are no longer using 'c'?
0
 
GianniPAuthor 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
0
 
GianniPAuthor Commented:
helped to guide me to my own mistakes
0
All Courses

From novice to tech pro — start learning today.