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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Bill-HansonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GRayLCommented:
Where do you declare 'c'?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.