Solved

Arrays in SQL?

Posted on 2008-06-26
5
285 Views
Last Modified: 2013-12-16
I am aware there are no arrays in SQL. Instead we use tables (read it online).

here is my situtation: I want to be able to make a link between a business we service and a category of colors for their logos.

for instance...
business 1 might have red and blue
business 2 might have Green and blue
business 3 might have red
business 4 might have Yellow and blue

I have the Category table

cat ID cat name
1         red
2         yellow
3         blue
4         green

I also have a cat_business Table...

CatID    BusinessID


I am adding the buisness through a asp.net 2.0 form and I have check boxes for the colors...

I know how to get the check boxes selected...
My question is how can I go about to add them to the database...

I want to add them when I add the business in my stored procedure...

Any help would be great.
Thanks.
0
Comment
Question by:copyPasteGhost
  • 3
5 Comments
 
LVL 5

Assisted Solution

by:buraksarica
buraksarica earned 200 total points
ID: 21873770
Everything is really ready for you now :)
All you need is adding the business and categories for that business in a transaction.
if you want to do this in a stored procedure, than you should pass the business details and category id's for the bussines. But category id's has no exact count? so you may pass the cat_id's comma delimeted string. And you can split these values with a function like below one. you can use this function like

Insert Into cat_business select *,@yourNewlyAddedBusinessID from Split(@yourStoredProceduresParameter , ',')
create function Split ( 

@StringToSplit varchar(2048),

@Separator varchar(128))

returns table as return

with indices as

( 

select 0 S, 1 E

union all

select E, charindex(@Separator, @StringToSplit, E) + len(@Separator) 

from indices

where E > S 

)

select substring(@StringToSplit,S, 

case when E > len(@Separator) then e-s-len(@Separator) else len(@StringToSplit) - s + 1 end) String

,S StartIndex        

from indices where S >0

Open in new window

0
 
LVL 13

Author Comment

by:copyPasteGhost
ID: 21927157
I finally got a chance to try this out...

It's not working...

Msg 213, Level 16, State 1, Procedure usp_AddColorLink, Line 67
Insert Error: Column name or number of supplied values does not match table definition.

ALTER PROCEDURE [dbo].[usp_AddColorLink]
      -- Add the parameters for the stored procedure here
      @BID char(7),
      @Cats nvarchar(MAX)

AS
BEGIN
            
      INSERT INTO [dbo].[Category_Business]
            SELECT @BID,*
            FROM Split(@Cats , ',')
END

Any ideas?
Thanks.
0
 
LVL 22

Accepted Solution

by:
dportas earned 300 total points
ID: 21928533
The error message means that your SELECT statement is returning more columns than exist in the Category_Business table. Best practice is to list all the columns in parentheses in the INSERT statement and list all the columns instead of using *. That way you can be sure the column lists match.

Example:

INSERT INTO [dbo].[Category_Business] (col1, col2, col)
SELECT col1, col2, col3, ...
0
 
LVL 13

Author Comment

by:copyPasteGhost
ID: 21928753
right. I only have two coloumn in that table...

the catID and the Business ID.
0
 
LVL 13

Author Closing Comment

by:copyPasteGhost
ID: 31470926
>buraksarica Your functions looks really cool I didn't get it to work but thanks for the effort!
>dportas double thanks for your help!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now