How to replace commas in returned value from sql query before binding to control

Posted on 2011-03-11
Last Modified: 2012-05-11
I have a query from one DB that is used to populate a listbox.  When the values are bound to the listbox for each value that has a , in it I want to replace it with a ;

This because my listbox is a multi select and if I don't remove those commas on the list coming in things are going to get very confused.
Question by:thamilto0410
  • 4
  • 2
  • 2
LVL 33

Expert Comment

ID: 35111888
If you need to do the replacement in SQL, we might need to know about the back end.  

If you're doing the replacement in VB.NEt you should just be able to
     Replace(value, ",", ";")
LVL 51

Expert Comment

ID: 35111918
similar thing if you iuse it in sql

select replace('Hain,Kurt',',',';')
--> Hain;Kurt
LVL 51

Expert Comment

ID: 35111941
how do you bind it to listbox? if you have something like this

select id, name from mytable

and bind it to selectbox, use id for value, and name for Text

then when you get it back, you will end up with numbers like '1,3,5,8' instead of text... do not use text columns for values...

Author Comment

ID: 35112114
HainKurt and paulmacd,
I have an oracle backend and when I tried using select replace the codebehind objected.

I thought I could make the change in I am filling a dataset from an oralcedataadapter and binding to the listbox in codebehind DataValueField, DataValueText and Databind.

When I tried the replace on the datavaluefield ie:  

files_processed.DataValueField = replace("thecolumnfromsql", ",", ";")

That also did not work.  What to do next?
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.

LVL 51

Expert Comment

ID: 35112177
you cannot do it in codebehind liek that

either use sql (update your select statement - i prefer this one, easy)
in a loop change all item text

for each i as ListItem in dd.Items
LVL 51

Accepted Solution

HainKurt earned 250 total points
ID: 35112196
do this after data binding... again, if you have control over sql, use sql solution

select id, name from mytable
select id, replace(name,',',';') as name from mytable
LVL 33

Expert Comment

ID: 35112317
[HainKurt] is right:  your best bet is to do the replace in the SQL.  You can do the replace on the individual list items, but that's slow and messy.

Author Closing Comment

ID: 35124056
Thank you to hainkurt and paulmacd.  I figured out the way to do it in the sql and it is working great.  All you experts rocks.

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Convert string to date 3 40
JavaScript error 1 34
Open form in the top right hand corner of screen 5 20
VB.Net - For Loop Error 5 26
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…
User art_snob ( encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

24 Experts available now in Live!

Get 1:1 Help Now