ASP returning null sized field from MS SQL database

We currently have an SQL db running on a web server.
One of these fields is a large(ish) amount of text data up to 400
characters and has been cast variously as varchar, nchar and text
type to overcome a problem. The problem appears to be in retrieving
the data via ASP. I understand that ASP can handle string data of this
size so I am okay there.
When the records are retrieved from the db, the data string length =0.
I know the data is there because I have written a Delphi data manager
which interrogates the db and shows all records and their contents.
So if ASP can handle strings this size and the data is there, why do I
get a data length of zero bytes returned when I interrogate the record
set?
Whichever way I cast this field I get the same result.

I know the code is sound as it works locally through a MS SQL server
on my PS.
Anyone have this problem or know what's causing it? I have logged a
support call with my hosting company, but they haven't replied as yet
and I am stuck on an urgent project.

Any suggestions?

Cheers
GrantGrant Davis (grant@.gbdavis.com) writes:
> We currently have an SQL db running on a web server.
> One of these fields is a large(ish) amount of text data up to 400
> characters and has been cast variously as varchar, nchar and text
> type to overcome a problem. The problem appears to be in retrieving
> the data via ASP. I understand that ASP can handle string data of this
> size so I am okay there.
> When the records are retrieved from the db, the data string length =0.
> I know the data is there because I have written a Delphi data manager
> which interrogates the db and shows all records and their contents.
> So if ASP can handle strings this size and the data is there, why do I
> get a data length of zero bytes returned when I interrogate the record
> set?
> Whichever way I cast this field I get the same result.
> I know the code is sound as it works locally through a MS SQL server
> on my PS.
> Anyone have this problem or know what's causing it? I have logged a
> support call with my hosting company, but they haven't replied as yet
> and I am stuck on an urgent project.
> Any suggestions?

Yes one: Post the relevant pieces of your code. Today it is a beautiful
day here in Stockholm, the sun is shining from blue skies. Nevertheless,
I am completely in the dark about what you are doing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

ASP only shows fields which contain numeric data

Hi

I'm using an SQL server database to run a website with ASP.

A new page I'm making will only display the contents on fields containing numeric data and it won't display text.

Other pages work fine. Does anyone have any ideas as to why this is happening - I've never seen in before.

Dave

http://pink-football.com/gossip.aspCould you use one of your ASP variables to be use with a number operator? like "+" or sumthin?

or

How are you calling your SQL server scripts? Through inline with your ASP codes or trough store procedures.

In either case, check your passing of ASP variable into your SQL Script.
Maybe you could miss a '' character.

What is the actual ASP error msg??|||Is it an error or just no data is returned ? Is your query returning both numerics and string data and only the numeric data is showing ? Have you tried to do a response.write to display all fields being returned ? Is it possible that you have single quotes in your string data ? On your web site - what are we looking for - and boy is it pink !!!

Arrays in SQL database

Hi;
Can MS-SQL support Arrays? I need a float that occurs 72 times in a
table and I don't want to have 72 seperate fields but can't see a way of
creating an array?
Thanks in advance.
David
*** Sent via Developersdex http://www.examnotes.net ***Hi,
There is no array concept in SQL Server. But you could use Dynamic SQL's to
provide an equalent.
Look into the great article:-
http://www.sommarskog.se/arrays-in-sql.html
Thanks
Hari
SQL Server MVP
"DavidC" <Cervelli@.Adelphia.Net> wrote in message
news:OOBP485XFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi;
> Can MS-SQL support Arrays? I need a float that occurs 72 times in a
> table and I don't want to have 72 seperate fields but can't see a way of
> creating an array?
> Thanks in advance.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***|||No, SQL Server does not support arrays
Yo can use either dynamic sql or udf
CREATE PROCEDURE array_method_1
@.array nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nsql nvarchar(4000)
SET @.nsql = '
SELECT *
FROM sysobjects
WHERE name IN ( ' + @.array + ')'
PRINT @.nsql
EXEC sp_executesql @.nsql
END
GO
EXEC array_method_1
@.array = '''sysobjects'',''sysindexes'',''syscolu
mns'''
GO
"DavidC" <Cervelli@.Adelphia.Net> wrote in message
news:OOBP485XFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi;
> Can MS-SQL support Arrays? I need a float that occurs 72 times in a
> table and I don't want to have 72 seperate fields but can't see a way of
> creating an array?
> Thanks in advance.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You can share them by (global) temporary tables, just follow the link Hari
presented.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"DavidC" <Cervelli@.Adelphia.Net> schrieb im Newsbeitrag
news:OOBP485XFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi;
> Can MS-SQL support Arrays? I need a float that occurs 72 times in a
> table and I don't want to have 72 seperate fields but can't see a way of
> creating an array?
> Thanks in advance.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***|||
> I need a float that occurs 72 times in a
> table and I don't want to have 72 seperate fields but can't see a way of
> creating an array?
Having just spent 20+ hours days undoing an "Array" column (i.e., column
containing a string with comma separated values), I can assure you that
having 72 columns is much better than having a CSV-string.
An "array" is a terrible hack and a huge design flaw created by a lazy
programmer.
Yeah, it's more work. Yeah, it's not fun. But, this is why they pay you the
big bucks.
Just do it.
Perhaps if you share the problem domain we may be able to help with a
cleaner design for this (aparantly) complex data.
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis|||>> Can MS-SQL support Arrays?
No
Perhaps you can make your overall requirement clearer. Most likely your
logical design is flawed which forces you to come up with such complex table
structures. Are you sure these are truly 72 distinct attributes of the
entity type you are modeling? Or does the collective 72 repetitions form a
single attribute from a conceptual POV?
Anith|||Even if the number of float values will be constant at 72 per record, it
seems the logical place to store them in a relational database would be a
foreign key table. Or perhaps we are talking about a lookup table with 72
records. Loading the values into an array would be something you implement
on the presentation / GUI side. Am I understanding what it is you want to
do?
"DavidC" <Cervelli@.Adelphia.Net> wrote in message
news:OOBP485XFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi;
> Can MS-SQL support Arrays? I need a float that occurs 72 times in a
> table and I don't want to have 72 seperate fields but can't see a way of
> creating an array?
> Thanks in advance.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***

Array

We are creating a report that we view detail records and find a row in the
list of records and want to grab one of the fields associated with that
record.
Example:
I have a loan number and principle balance.
I count the number of loans for a borrower.
Here is the data:
LN NUM Prin
1 5.00
2 10.00
3 15.00
The middle record is the one I want to use. But what I want as well is the
prin amount. How do I tell a function to look at the prin amount instead of
the ln num field?Hi Susan,
I've run into a similar issue. Were you able to resolve your issue? If so
please tell.
THanks,
"Susan" wrote:
> We are creating a report that we view detail records and find a row in the
> list of records and want to grab one of the fields associated with that
> record.
> Example:
> I have a loan number and principle balance.
> I count the number of loans for a borrower.
> Here is the data:
> LN NUM Prin
> 1 5.00
> 2 10.00
> 3 15.00
> The middle record is the one I want to use. But what I want as well is the
> prin amount. How do I tell a function to look at the prin amount instead of
> the ln num field?
>|||No I actually was looking for a way to calculate the median. I had to write
the report in Crystal.
"DigitalVixen" wrote:
> Hi Susan,
> I've run into a similar issue. Were you able to resolve your issue? If so
> please tell.
> THanks,
>
> "Susan" wrote:
> > We are creating a report that we view detail records and find a row in the
> > list of records and want to grab one of the fields associated with that
> > record.
> > Example:
> >
> > I have a loan number and principle balance.
> > I count the number of loans for a borrower.
> > Here is the data:
> >
> > LN NUM Prin
> > 1 5.00
> > 2 10.00
> > 3 15.00
> >
> > The middle record is the one I want to use. But what I want as well is the
> > prin amount. How do I tell a function to look at the prin amount instead of
> > the ln num field?
> >

Arithmetic operator

I'm using Sql Server reporting tools and creating a report.
How can I display the difference of two fields on a report?

you have 3 text boxes on a form..

say you have two fields...

textbox1.value = fields!myfield1.value

textbox2.value = fields!myfield2.value

then in the value for textbox3 put

= fields!myfield1.value - fields!myfield2.value

QED

Daryl

arguments to sub reports

i want to use parent report's fields as a paramenter to sub reports. how can i do that??Firstly, read the help on subreports! Secondly, right click on the subreport and choose subreport links. Choose the field you want to pass down as a parameter, and then choose whether this should also be used in the subreport's record selection.|||thank you very much. i got my problem solved.

Are there no "Computed" column types? For storing expressions (results of other fields

I just read something interesting here: http://www.informit.com/library/content.asp?b=STY_Sql_Server_7&seqNum=101

A column type that helds an expression, and in queries returns the results.

That sounds excelent in my database, to save some code in my client applications. E.g adding price totals, and taxes of an order.

I can't find any info about this in later versions of SQL server. Is this not possible anymore?

not really necessary. . . create a view.

create table LineItem(itemId, price, quantity)

create view LineItemWTax as select itemid, price, quantity, price*quantity*0.045 as Tax

select * from LineItemWTax

you can also create a function:

create function CalculateTax(@.price money, @.quantity int, @.rate as decimal (3,3))
returns money
as
begin
return @.price * @.quantity * @.rate
end
go
select itemid, price, quantity, dbo.CalculateTax(price, quantity, .045) from LineItem

|||Thanks, thats probably a better way i guess :)