Bringing a Timestamp into a recordset

[Aaron Bertrand - MVP]

Timestamp isn't really "data" ... it is just a binary rowversion. What are
you trying to gain by displaying it on the page?

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:ebOEGDN9DHA.2604@TK2MSFTNGP10.phx.gbl...

> Hi Guys
>
> The subject of this post might be misleading, but basically I have created

a

> basic query ASP page that will allow a user to copy and paste their SQL
> query into a textbox so that they can execute it on their SQL DB to bring
> back the result.
>
> Now I understand how to dynamically bring back the relevant data cols and
> rows, but the only bit that I fall foul on is if the user has done a

select

> * from a table and brings back a timestamp field.
>
> For some reason when my recordset tries to display this timestamp it blows
> up, which is unlike say MS Query that displays the figure.
>
> Is there a special encapsulating command that I have to wrap the recordset
> in so that it can handle the displaying of timestamps?
>
> Rgds
>
> Laphan
>
>

[Laphan]

Hi Guys

The subject of this post might be misleading, but basically I have created a
basic query ASP page that will allow a user to copy and paste their SQL
query into a textbox so that they can execute it on their SQL DB to bring
back the result.

Now I understand how to dynamically bring back the relevant data cols and
rows, but the only bit that I fall foul on is if the user has done a select
* from a table and brings back a timestamp field.

For some reason when my recordset tries to display this timestamp it blows
up, which is unlike say MS Query that displays the figure.

Is there a special encapsulating command that I have to wrap the recordset
in so that it can handle the displaying of timestamps?

Rgds

Laphan

[Laphan]

Not trying to gain anything. Because users may enter 'select * from XXX', I
wanted to format/trap the timestamp so that at least they wouldn't bomb the
page, which is what happens everytime a timestamp is involved in a query.

Rgds

Laphan

Aaron Bertrand - MVP <aaron@TRASHaspfaq.com> wrote in message
news:OZg2EFN9DHA.1672@TK2MSFTNGP12.phx.gbl...
Timestamp isn't really "data" ... it is just a binary rowversion. What are
you trying to gain by displaying it on the page?

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:ebOEGDN9DHA.2604@TK2MSFTNGP10.phx.gbl...

> Hi Guys
>
> The subject of this post might be misleading, but basically I have created

a

> basic query ASP page that will allow a user to copy and paste their SQL
> query into a textbox so that they can execute it on their SQL DB to bring
> back the result.
>
> Now I understand how to dynamically bring back the relevant data cols and
> rows, but the only bit that I fall foul on is if the user has done a

select

> * from a table and brings back a timestamp field.
>
> For some reason when my recordset tries to display this timestamp it blows
> up, which is unlike say MS Query that displays the figure.
>
> Is there a special encapsulating command that I have to wrap the recordset
> in so that it can handle the displaying of timestamps?
>
> Rgds
>
> Laphan
>
>

[Chris Hohmann]

"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:ebOEGDN9DHA.2604@TK2MSFTNGP10.phx.gbl...

> Hi Guys
>
> The subject of this post might be misleading, but basically I have

created a

> basic query ASP page that will allow a user to copy and paste their

SQL

> query into a textbox so that they can execute it on their SQL DB to

bring

> back the result.
>
> Now I understand how to dynamically bring back the relevant data cols

and

> rows, but the only bit that I fall foul on is if the user has done a

select

> * from a table and brings back a timestamp field.
>
> For some reason when my recordset tries to display this timestamp it

blows

> up, which is unlike say MS Query that displays the figure.
>
> Is there a special encapsulating command that I have to wrap the

recordset

> in so that it can handle the displaying of timestamps?

As Aaron pointed out, rowversion data is stored as binary(8). As such,
you have two options:

1. Test the data type and omit it's display if it is binary data. I
believe rowversion data is delivered as an array of bytes. As such you
would first test to see if the data was an array. Then check if the data
type of the array elements is a byte.

2. To actually display the data you will need to convert the data to a
string of some sort. I would recommend converting the data to a
hexadecimal string representation.

The "howto's" of working with binary data can be found here:
[url]http://www.pstruh.cz/tips/detpg_BinASP.htm[/url]

HTH
-Chris Hohmann

[Aaron Bertrand [MVP]]

> Not trying to gain anything. Because users may enter 'select * from XXX'

No, no, no. Don't EVER let users enter 'SELECT * from ANYTHING. Ever.
Period.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Astra]

Aaron

I understand your comments regarding select *'s, but I would rather trap the
timestamp to make the page more robust than limit the simplicity and
functionality of what I'm trying to achieve with this.

Many thanks to you and Chris for your feedback.

Rgds Robbie

"Chris Hohmann" <nospam@thankyou.com> wrote in message
news:eCa0Nhb9DHA.3880@TK2MSFTNGP11.phx.gbl...
"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:ebOEGDN9DHA.2604@TK2MSFTNGP10.phx.gbl...

> Hi Guys
>
> The subject of this post might be misleading, but basically I have

created a

> basic query ASP page that will allow a user to copy and paste their

SQL

> query into a textbox so that they can execute it on their SQL DB to

bring

> back the result.
>
> Now I understand how to dynamically bring back the relevant data cols

and

> rows, but the only bit that I fall foul on is if the user has done a

select

> * from a table and brings back a timestamp field.
>
> For some reason when my recordset tries to display this timestamp it

blows

> up, which is unlike say MS Query that displays the figure.
>
> Is there a special encapsulating command that I have to wrap the

recordset

> in so that it can handle the displaying of timestamps?

As Aaron pointed out, rowversion data is stored as binary(8). As such,
you have two options:

1. Test the data type and omit it's display if it is binary data. I
believe rowversion data is delivered as an array of bytes. As such you
would first test to see if the data was an array. Then check if the data
type of the array elements is a byte.

2. To actually display the data you will need to convert the data to a
string of some sort. I would recommend converting the data to a
hexadecimal string representation.

The "howto's" of working with binary data can be found here:
[url]http://www.pstruh.cz/tips/detpg_BinASP.htm[/url]

HTH
-Chris Hohmann

[Bob Barrows]

Astra wrote:

> Aaron
>
> I understand your comments regarding select *'s, but I would rather
> trap the timestamp to make the page more robust than limit the
> simplicity and functionality of what I'm trying to achieve with this.
>

Lazy. You are impacting the performance of your application.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Aaron Bertrand - MVP]

> Lazy. You are impacting the performance of your application.

Not to mention security... why should end users know, or even have to know,
the structure of your table(s)?

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Laphan]

Understood.

Thanks Laphan

Aaron Bertrand - MVP <aaron@TRASHaspfaq.com> wrote in message
news:uiqtcDj9DHA.2608@TK2MSFTNGP10.phx.gbl...

> Lazy. You are impacting the performance of your application.

Not to mention security... why should end users know, or even have to know,
the structure of your table(s)?

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]