Click here to Skip to main content
11,925,022 members (49,867 online)
Rate this:
Please Sign up or sign in to vote.
See more: VB SQL-Server VB.NET
Multiple Values For Single Row

hi iam totally new to databases , as a project i have to design a database of users...they have to register first like any i used stored procs and made entries to database using insert command...its working for now..

now every user will search and add other users in the every user will have a contact list...i have no idea how to implement this...

so far i created a table 'UserAccount' with column names as
UserName as varchar(50)
Password as varchar(50)
EmailID as varchar(100)
DateOfJoining as datetime
UserID as int ---> this is unique for user..i enabled automatic increment..and this is primary key..

so now every user must have a list of other userid's.. as contact list..

Any help any ideas will be great since i have no clue how to put multiple values for each row..i didnt even know how to search for this problems solution..iam sorry if this posted somewhere else..
Posted 12-Feb-13 20:50pm

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

There are two ways to do it - either your contact list is a string with contacts separated by a special character, or you have a separate table for this.
The problem is that SQL does not have a concept of arrays - so you can't have a row with a variable number of fields.
While you could set it up with a string:
THis very quickly becomes a PITA to manipulate or search, or worse - remove a user from your system completely.

The best approach is to create a separate table, "Contacts":
ID          int - This can be an identity field.
UserID      int - ID of your user
ContactID   int - ID of one of his contacts
You can then fetch the list of contacts with a simple SQL statement:
SELECT ContactID FROM Contacts WHERE UserID=100
Changing the 100 to match the ID of your user.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.151125.3 | Last Updated 13 Feb 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100