Change SQL values to plain English on ASP page? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

Change SQL values to plain English on ASP page?

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-12-11 : 10:13:19

Hi. I'm using classic ASP to manage my SQL 7.0 db. My users add, update, and delete records via ASP forms.

My question involves the passing back of data from SQL Server to the ASP page when the user is preparing to do an update.

When new records are submitted to the database, many values are listed in the ASP page by their "friendly" names, i.e. "Yes" and "No", which translate to "1" and "2" in the SQL db.

My challenge arises when I wish to pass these "1" and "2" values back to the ASP for for updating. I want the values to display as "Yes" and "No" for my users, not "1" and "2".

Currently, I'm simply capturing the value via standard ASP code <% fieldvalue %> and dropping this value in the SELECT box on the ASP page.

Anyone know of an elegant way to translate the raw SQL "1" and "2" into "Yes" and "No"? I'm fiddling with conditional statements on my ASP pages now and they're none too elegant.

thx

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-11 : 10:29:42

Here's a variation on this technique:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21858
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11019

CREATE PROCEDURE GetYesNo AS
SET NOCOUNT OFF
SELECT '<OPTION value=' + CAST(YesNoCol as varchar) + '>' +
CASE YesNoCol WHEN 1 THEN 'Yes' WHEN 2 THEN 'No' END + '</option>'
FROM myTable
WHERE YesNoCol IN (1,2)

You can these use a generic GetString call to create the option list:

rs.Open "EXECUTE GetYesNo", connObj
options=rs.GetString(,, "", "", "")
rs.Close
response.write "<SELECT name=selectlist>" & options & "</select>"

Normally you don't intermingle HTML with your SQL output, but this scenario is one of those where it works extremely well.

The option will show on the page as "Yes" or "No", but the VALUE will be passed back as 1 or 2.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-11 : 10:34:59

Why do you use 1 and 2 instead of 0 and 1 which are the norm for defining true and false or yes and no.

If you use 0 and 1 as boolean then you can write

CStr(bolVal)

When bolVal = 0 it returns "False"
When bolVal = 1 it returns "True"

or
You can write a simple function

Public Function IntToStr(ByVal intVal As Integer) As String

Select intVal
Case 1
IntToStr = "Yes"
Case 2
IntToStr = "No"
Else
IntToStr = "Unknown"
End Select

End Function

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-11 : 10:37:54

Nice one Rob.

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-12-11 : 15:30:24

Yes, Rob, that is cool.

And, the answer to why 1 and 2 instead of 0 and 1...legacy system. I may change all occurrences via monster update script...

thx

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-12-11 : 15:40:32

Rob,

Looking over your SPROC now. I guess if I want to use this to handle all Yes/No translations for all columns/tables in my DB, I just need to extend your SELECT statement with various CASE statements to cover needed columns and then add the additional tables to the FROM clause?

thx

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-11 : 16:47:58

Yes, you can extend it any way you want to. You can pass variables to the sproc to get values from different columns or even tables. If everything maps to Yes/No - 1/2 though, it doesn't make a lot of sense to keep it in a database. Same applies if the options are only a few values that won't change (or change very infrequently) In those instances there's little point in opening a connection/recordset just to read the same data, you'd be better off hard-coding it into the app.

One method I tried for a call center app was to have two tables, one with questions and the other with acceptable answers. Like this:

CREATE TABLE Questions (QuestionGroup varchar(5) NOT NULL,
Question varchar(10) NOT NULL,
QuestionText varchar(200) NOT NULL
CONSTRAINT PK_Question PRIMARY KEY (QuestionGroup, Question)

CREATE TABLE Answers (QuestionGroup varchar(5) NOT NULL,
Question varchar(10) NOT NULL,
Answer varchar(10) NOT NULL,
AnswerText varchar(100) NOT NULL
CONSTRAINT PK_Answer PRIMARY KEY (QuestionGroup, Question, Answer)

Yeah, I know, why didn't I use an ID column in these?

You don't have to stick with the exact structure, but what this one allowed me to do was create different groups of questions, all the questions for that group, and all of the answers for each question in that group. All call campaign data could be stored in the same tables, regardless of how many questions or answers each campaign had (Each question group would manage a separate campaign)

I had originally designed it to handle only one call campaign/app, but by adding the QuestionGroup column I now had a structure that could add other call campaigns simply by INSERTing more rows. Almost all reports ran from one table only, or at most 2 tables joined on two columns. You can add ID columns if you must; I didn't bother because the columns in the PK I defined had to be unique anyway.

Something like this would allow you to put all of the questions/answer combos in one table, and link it to all of the other tables where the actual answers are stored. Generic Yes/No questions can still be done in the same table, broken out by QuestionGroups/categories or using the same category, and can have more options added just by adding rows to the Answers table. It also allows a generic sproc to query one table for all the answers simply by passing the appropriate values for question group and question, without needing dynamic SQL or multiple tables.

This might be more elaborate than what you need for your database, and I would only recommend it if you can easily modify the existing tables to accommodate it.

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-12-11 : 20:10:19

Rob,

Looking at your comment re: hard coding the values into the ASP page, and then looking at the first SPROC example again, I think I see that I have not been clear about what I’m trying to accomplish.

I will, in fact, be hard coding these values using a standard HTML option box in the ADD NEW RECORD FORM, like so:

<select name = YesNoFirst>

<option value = 1>Yes</option>
<option value = 2>No</option>

</select>

My problem arises after the user has submitted a new record to the database and then decides to update this specific record. My traditional ASP coding technique would simply open the RS and then drop the raw value into the first <option> position on the form, like this:

<select name = YesNoFirst>

<option><%yesnocolumn_value%></option>
<option value = 1>Yes</option>
<option value = 2>No</option>

</select>

In this case, the user would see either a “1” or a “2” in the select box after the form loads, followed by “Yes” and “No” as the two static choices in this list.

What I’d like to do is translate this “1” or “2” on the fly so that it appears in the static option box code as “yes” or “no”.

Unless I’ve missed something, your first SPROC example creates a dynamic option box based on the available values in a specific field, but does not address the on-the-fly translation issue I mentioned above.

Again, thanks for helping with this. I hope I’m not missing something obvious.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-11 : 20:14:51

Yep, there I go again overdoing the solution

Try this:

<select name = YesNoFirst>
<option value = 1 <% if yesnocolumn_value=1 then response.write " selected" %>>Yes</option>
<option value = 2 <% if yesnocolumn_value=2 then response.write " selected" %>>No</option>
</select>

That's probably the easiest way to do it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources