Padding values in a combobox - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Padding values in a combobox

Author

Topic

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2002-12-26 : 12:14:59

Hello all. I am trying to pad a VB combobox with results.

Here is the jist of what I am trying to do.

Value1 | Text1
Value2 | Text2
Value5 | Text223

I want the separator bar between the two columns to remain in a fixed location, no matter how many characters appear on the left (realistically, this will not be more than 30 characters).

Of course, the values on the left side are going to vary in length, so don't take what you see above too literally.

Any ideas on how to do this in my stored procedure?

Thanks all!

Aj

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-26 : 12:32:34

How about this:


CREATE TABLE #Combo(Value VARCHAR(50), Names VARCHAR(50))

INSERT INTO #Combo(Value, Names) VALUES('Value1', 'Text1')
INSERT INTO #Combo(Value, Names) VALUES('Value1234', 'Text12345')
INSERT INTO #Combo(Value, Names) VALUES('Value123456', 'Text1234556')

DECLARE @MaxLen INT
select @MaxLen = MAX(LEN(value)) FROM #Combo

select Value + SPACE(@MaxLen - LEN(value)) + ' | ' + Names
FROm #Combo

DROP TABLE #Combo

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

verronep
Starting Member

15 Posts

Posted - 2002-12-26 : 14:11:09

Another way you can do this is to just cast the values to char datatypes with the length you want.

(Ex.) SELECT CAST(Value as char(30)) + ' | ' + CAST(Name as char(30))

HTH
Paul

"I have not failed. I have just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2002-12-26 : 15:05:41

Thanks guys, but it is not quite working.

I tried both suggestions and got really close. I think this is what the problem is.

Compare the actual, physical length of the following in Query Analyzer:

AAA
aaa
''

These are all different lengths. I don't mean, Len(values) but instead the actual physical length as they appear on the screen. It takes roughly 1 1/2 empty strings to equal the length of a capital A. I think that this is the reason these may never format the way I want them too.

I tried both of your suggestions, hell even tried a combination of the two, but still got wiggly results!

Thanks again! I am still open to suggestion!

Aj

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-26 : 15:22:17

Well, why not UPPER() or LOWER() the data? I think you'll need to set the font of the combo box to a fixed width font like courier

 
CREATE TABLE #Combo(Value VARCHAR(50), Names VARCHAR(50))

INSERT INTO #Combo(Value, Names) VALUES('Value1', 'Text1')
INSERT INTO #Combo(Value, Names) VALUES('Value1234', 'Text12345')
INSERT INTO #Combo(Value, Names) VALUES('Value123456', 'Text1234556')
INSERT INTO #Combo(Value, Names) VALUES('MICHAEL1', 'MICHAEL1')
INSERT INTO #Combo(Value, Names) VALUES('michael1', 'michael1')
INSERT INTO #Combo(Value, Names) VALUES('MiChAeL1', 'MiChAeL1')


SELECT UPPER(CAST(Value as char(30)) + ' | ' + CAST(Names as char(30))) FROM #Combo

DROP TABLE #Combo

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

verronep
Starting Member

15 Posts

Posted - 2002-12-26 : 15:22:44

I misunderstood your question. ;)

I don't know of any way that you can do this through SQL Server, as if I understand what you're asking right, the real problem is the font type being used.

For example, open up Word (or some other text editor with fonts) and type in the below using Times New Roman. Then try the same thing as Courier New.
AAAA
aaaa

If you try either of our suggestions and use a 'fixed-width' font on the client end, it should work. Other than that, I don't know any other way to do it.

HTH
Paul

Sorry Michael, was writing my post when you posted.

"I have not failed. I have just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

Edited by - verronep on 12/26/2002 15:41:15

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2002-12-26 : 15:54:47

I went and changed the font to Courier and tried it again. It works! But it looks kind of silly against the rest of the controls.

So I think I will just have to do some formatting of the data to make it look as uniformed as possible.

Thanks!

Aj

Edited by - ajthepoolman on 12/26/2002 15:57:06

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-26 : 16:05:50

Here's another thought. This really depends on your data though.
Have 2 dependant drop down boxes.

If you data looks like

value1 | Mike
value1 | Bob
value1 | Sue
value2 | Joe-Bob
value2 | Harry

The first box could control "value1" vs "value2" and when you select onf of those, the second box would only show the choices for that selected item.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2002-12-26 : 17:54:02

That is a possiblity, but not one that I would like to use.

This is one of those fancy datapanels that allows admin users to build custom forms (properties of the controls are stored in a table) which can be used by end users to fill out.

If I used a linked control like that, the form would have to save itself, and then go out to the database and pull in the values for the second control, repaint the form and do it all within the blink of an eye (this is a terminal services app). I just don't like the idea of having to do that, especially since the table it hits is over 30,000 records strong and growing daily.

For now, I am just going to stick with the ugly formatting that I have and try to talk the boss into going to a fixed length font in the comboboxes.

Thanks for the help!

Aj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources