Welcome to Tech Support Forum home to more then 136,000 problems solved. Issues have included: Spyware, Malware, Virus Issues, Windows, Microsoft, Linux, Networking, Security, Hardware, and Gaming Getting your problem solved is as easy as:
1. Registering for a free account
2. Asking your question
3. Receiving an answer

Registered members:
* Get free support
* Communicate privately with other members (PM).
* Removal of this message
* See fewer ads.
* And much more..

 



Want to know how to post a question? click here Having problems with spyware and pop-ups? First Steps
Go Back   Tech Support Forum > Design Forum > Web Design & Programming
User Name
Password
Site Map Register Donate Rules Blogs Mark Forums Read


Web Design & Programming Discussion of web design, and server-side & client-side scripting

Reply
 
LinkBack Thread Tools
Old 01-19-2006, 04:01 PM   #1 (permalink)
Registered User
 
m00nbeast's Avatar
 
Join Date: Jan 2005
Posts: 5
OS: XP


Question MS SQL - comma delimited string in nvarchar, matching with user id

Okay heres the deal (I hope I can explain this clearly enough while being as breif as I can, and I apologize if this has been covered in another thread but I saw nothing similar):

I created a trouble ticket system (ASP site using MS SQL) where the trouble tickets were assigned to only one person, but now I need tickets to be assigned to multiple people.

So in my ticket table I changed the assigned_to field to nvarchar and the user id's are comma delimited.

On the users home page, when they sign into the site I show currently open tickets assigned to that user.

Initially my SQL statement looked like this:

Quote:
strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, b.name as [Client] FROM tickets t, users u, business b where u.user_id = t.assigned_to and t.assigned_to = "& session("user_id")&" and t.status = 'In Progress' and b.business_id = t.business_id"
Now as the field type has changed I went looking for something to help me sort thru the comma delimited field to find tickets assigned to the logged in user and am somewhat close with this:

Quote:
strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, b.name as [Client] FROM tickets t, users u, business b where u.user_id = t.assigned_to and (CHARINDEX(',"& session("user_id") &",',t.assigned_to)>0 OR CHARINDEX('"& session("user_id") &",',t.assigned_to)=1 OR CHARINDEX(',"& session("user_id") &"',t.assigned_to)=LEN(t.assigned_to)-1) and t.status = 'In Progress' and b.business_id = t.business_id order by b.name, t.ticket_id"

or

Quote:
strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, b.name as [Client] FROM tickets t, users u, business b where u.user_id = "& session("user_id") &" and (PATINDEX('%,"& session("user_id") &",%',t.assigned_to)>0 OR PATINDEX('%"& session("user_id") &",%',t.assigned_to)=1 OR PATINDEX('%,"& session("user_id") &"%',t.assigned_to)=LEN(t.assigned_to)-1) and t.status = 'In Progress' and b.business_id = t.business_id order by b.name, t.ticket_id"
neither of which work that well as it will return records that aren't assigned to that user at all and I am nto sure why. I just found charindex() and patindex() and barely know how to use them properly.

Please help me as I am at my wits end.
m00nbeast is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Important Information
Join the #1 Tech Support Forum Today - It's Totally Free!

TechSupportForum.com is a leading support website for your computer needs. We offer free, friendly and personalized computer support. Why pay to have your computer fixed when you can do it for free.

Join TechSupportforum.com Today - Click Here

Reply


Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off




All times are GMT -7. The time now is 05:21 AM.



Copyright 2001 - 2009, Tech Support Forum
Home Tips Plus | Outdoor Basecamp | Automotive Support Forum

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85