Hey All,

 

I'm working on a form that returns a listing of the products contained in the database.  I want the list (that gets returned) to be selectable.

 

The data is coming back just fine, but my problem is the formatting needs some adjustment.  Whatever formatting that gets specified in SQL, is not coming through and being displayed correctly.  The following is an example:

 

In SQL, I wrote a function that creates a return string that is formatted as well: SELECT @r = ModelNum + space(4) + Frequency + space(4) + [Isolation] FROM dbo.MTC_Products WHERE ModelNum

 

In the form, the control ignores the "space(4)" commands, and replaces them with a single space.  I have tried all the possible selection controls, and the formatting does not come through.  I have even tried using spaces within a single quoation...nothing seems to work.

 

Is there another way?

 

Thanks in Advance,

-Shane

HI, your SQL statement should be something like:
select ((firstname)+' ' +(lastname)) as questionoption, userid as questionoptionvalue from {objectQualifier}users
So in your case, try the following SQL statement:
select (ModelNum + space(4) + Frequency + space(4) + [Isolation]) as questionoption, (ModelNum + space(4) + Frequency + space(4) + [Isolation]) as questionoptionvalue from dbo.MTC_Products

-- David

David,

 

Thanks for the fast response...

 

Actually, my SQL statement was as follows:

 

SELECT ModelNum as QuestionOptionValue, dbo.getMTC_Products(ModelNum) as QuestionOption FROM dbo.MTC_Products WHERE FreqCode = '$(Freq)'

 

If I am not mistaken, that follows the necessary criteria for retrieving data.  Within the "getMTCProducts()" function is where I was formatting the return string. 

 

I just tried your suggestion, and the formatting did not return still.  I am using version 2.70.05.

 

Do you have any other ideas?

 

Thanks,

-Shane

Hi, QuestionOption should come before QuestionOptionValue. Also, can you try executing this statement under HOST / SQL and see what it returns, although under HOST / SQL you may have to hardcode in the value for '$(Freq)'
-- David

David,

 

I made some corrections to the SQL statement as follows:

 

select (ModelNum + space(4) + Frequency + space(4) + [Isolation]) as questionoption, (ModelNum + space(4) + Frequency + space(4) + [Isolation]) as questionoptionvalue from dbo.MTC_Products

 

I tried running this in the SQL editor within DNN, and the formatting still does not come through properly.  Everywhere it states "space(4)", it only puts a single space.  In SQL Server, the return looks properly formatted...

 

Do you have any other suggestions?

 

Thanks,

-Shane

HI Shane, nothing wrong with the SQL statement. It works fine when I tried the space(4) under SQL Management Studio but when I tried it in DNN HOST / SQL, all the spacing gets stripped. Maybe it's a DNN configuration setting somewhere. -- David

Thanks Dave for the help.  I found out what the solution is and I thought I would report back to let everyone else know as well.

 

In Host/SQL, it actually posts back the results in HTML.  I replaced the space(4) with 'nbsp' (with the andpersand and semi colon) and everything works like a champ.  The spacing comes through and with a little time, I can get everything lined up as it should.

 

Thanks again,

-Shane