Time Formatting in ASP

[joe ruggeri]

Hey All ...

I'm developing in ASP/VBScript w/ an Access 2000 DB. The source of the data is
coming from a third party, which I get as a CSV and import into the database,
updating it weekly.

Since I have no control over how the data is collected, I'm not sure how to fix
this problem, but here it is.

This is a database of movies and among the many fields I import is one for the
movie's length, expressed in HH:MM:SS. This comes into the Access DB as a
Date/Time format, which means all the dates, displayed raw in the DB, look like
"12:09:52 AM". Displaying it to the client as HH:MM:SS is no problem.

But in an update form, which we'll use to correct errors (and there always seem
to be errors in the running time of the movie), I can't figure out how to
display it in the HH:MM:SS format and have it submit and without throwing a data
type mismatch error.

Converting the field to a text type is out of the question at this point, so I
know I need to do some type of string manipulation prior to update. However, I'm
lost

Any ideas?

Thanks.

--
-----------------------------------------------------
joe ruggeri
web designer
JFmedia newport, ri

-----------------------------------------------------

[CMBergin]

Shouldn't be a problem. Please show your code.

"joe ruggeri" <joerugg@nospam.cox.net> wrote in message
news:d00e4f$60s$1@forums.macromedia.com...

> Hey All ...
>
> I'm developing in ASP/VBScript w/ an Access 2000 DB. The source of the

data is

> coming from a third party, which I get as a CSV and import into the

database,

> updating it weekly.
>
> Since I have no control over how the data is collected, I'm not sure how

to fix

> this problem, but here it is.
>
> This is a database of movies and among the many fields I import is one for

the

> movie's length, expressed in HH:MM:SS. This comes into the Access DB as a
> Date/Time format, which means all the dates, displayed raw in the DB, look

like

> "12:09:52 AM". Displaying it to the client as HH:MM:SS is no problem.
>
> But in an update form, which we'll use to correct errors (and there always

seem

> to be errors in the running time of the movie), I can't figure out how to
> display it in the HH:MM:SS format and have it submit and without throwing

a data

> type mismatch error.
>
> Converting the field to a text type is out of the question at this point,

so I

> know I need to do some type of string manipulation prior to update.

However, I'm

> lost
>
> Any ideas?
>
> Thanks.
>
> --
> -----------------------------------------------------
> joe ruggeri
> web designer
> JFmedia newport, ri
>
> -----------------------------------------------------
>
>

[Joe Ruggeri]

Hey All ...

I'm developing in ASP/VBScript w/ an Access 2000 DB. The source of the data
is coming from a third party, which I get as a CSV and import into the
database, updating it weekly.

Since I have no control over how the data is collected, I'm not sure how to
fix this problem, but here it is.

This is a database of movies and among the many fields I import is one for
the movie's length, expressed in HH:MM:SS. This comes into the Access DB as
a
Date/Time format, which means all the dates, displayed raw in the DB, look
like "12:09:52 AM". Through data farmatting, however, displaying it to the
client as HH:MM:SS is no problem. (Display "12:36:00 AM" as military time
(00:36:00) tells me the movie is 0 hrs, 36 minutes, 0 seconds long. Follow?)

But in an update form, which we'll use to correct errors (and there always
seem to be errors in the running times of movies), I can't figure out how to
display it in the HH:MM:SS format in an INPUT field and have it submit
without throwing a data type mismatch error.

Converting the field to a text type is out of the question at this point, so
I know I need to do some type of string manipulation prior to update.
However, I'm lost ...

Any ideas?

Thanks.

UPDATE query below:

<%
' *** Update Record: set variables

If (CStr(Request("MM_update")) = "update" And CStr(Request("MM_recordId"))
<> "") Then

MM_editConnection = MM_niff2005_STRING
MM_editTable = "tblWABimports"
MM_editColumn = "WABIDID"
MM_recordId = "" + Request.Form("MM_recordId") + ""
MM_editRedirectUrl = "wabListall.asp"
MM_fieldsStr =
"film|value|synopsis|value|AspectRatio|value|Forma ts_Shooting|value|Formats_
Exhibition|value|Sound|value|Color|value|Runtime|v alue|director_Fname|value|
director_Lname|value|director_company|value|direct or_addr1|value|director_ad
dr2|value|director_city|value|director_state|value |director_zip|value|direct
or_country|value|director_email|value|director_pho ne1|value|director_phone2|
value|director_web|value|Submission_Fname|value|Pr oducer1_f_name|value|Submi
ssion_Lname|value|Producer1_l_name|value|Submissio n_company|value|Producer1_
company|value|Submission_address|value|Producer1_a ddress|value|Submission_ad
dress2|value|Producer1_address2|value|Submission_c ity|value|Producer1_city|v
alue|Submission_state|value|Submission_postal|valu e|Producer1_state|value|Pr
oducer1_postal|value|Submission_country|value|Prod ucer1_country|value|Submis
sion_email|value|Submission_phone1|value|Producer1 _email|value|Producer1_pho
ne1|value|Submission_phone2|value|Producer1_phone2 |value|Submission_web|valu
e|Producer1_web|value"
MM_columnsStr =
"Title|',none,''|Synopsis_3line|',none,''|AspectRa tio|',none,''|Formats_Shoo
ting|',none,''|Formats_Exhibition|',none,''|Sound| ',none,''|Color|',none,''|
Runtime|',none,NULL|Director1_Fname|',none,''|Dire ctor1_Lname|',none,''|Dire
ctor1_company|',none,''|Director1_address1|',none, ''|Director1_address2|',no
ne,''|Director1_city|',none,''|Director1_state|',n one,''|Director1_postal|',
none,''|Director1_country|',none,''|Director1_emai l|',none,''|Director1_phon
e1|',none,''|Director1_phone2|',none,''|Director1_ website|',none,''|Submissi
on_Fname|',none,''|Producer1_f_name|',none,''|Subm ission_Lname|',none,''|Pro
ducer1_l_name|',none,''|Submission_company|',none, ''|Producer1_company|',non
e,''|Submission_address1|',none,''|Producer1_addre ss1|',none,''|Submission_a
ddress2|',none,''|Producer1_address2|',none,''|Sub mission_city|',none,''|Pro
ducer1_city|',none,''|Submission_state|',none,''|S ubmission_postal|',none,''
|Producer1_state|',none,''|Producer1_postal|',none ,''|Submission_country|',n
one,''|Producer1_country|',none,''|Submission_emai l|',none,''|Submission_pho
ne1|',none,''|Producer1_email|',none,''|Producer1_ phone1|',none,''|Submissio
n_phone2|',none,''|Producer1_phone2|',none,''|Subm ission_website|',none,''|P
roducer1_website|',none,''"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|")
MM_columns = Split(MM_columnsStr, "|")

' set the form values
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
Next

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And
Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
%>


And the INPUT field, which reads out of the "Runtime" field in the DB:

<input name="Runtime" type="text" id="Runtime"
value="<%=(rsWAB.Fields.Item("Runtime").Value)%>" size="20" />

Which shows up on the client side as "12:36:00 AM" or some other time.


Thanks ... jr


--
-----------------------------------------------------
joe ruggeri
web designer
JFmedia newport, ri

-----------------------------------------------------

[CMBergin]

In your MM_columnsStr, there's a section like this:
....snip...Runtime|',none,NULL...snip...
Change it to:
Runtime|#,none,NULL

"Joe Ruggeri" <joerugg@nospam.jfmedia.com> wrote in message
news:d02h55$c27$1@forums.macromedia.com...

> Hey All ...
>
> I'm developing in ASP/VBScript w/ an Access 2000 DB. The source of the

data

> is coming from a third party, which I get as a CSV and import into the
> database, updating it weekly.
>
> Since I have no control over how the data is collected, I'm not sure how

to

> fix this problem, but here it is.
>
> This is a database of movies and among the many fields I import is one for
> the movie's length, expressed in HH:MM:SS. This comes into the Access DB

as

> a
> Date/Time format, which means all the dates, displayed raw in the DB, look
> like "12:09:52 AM". Through data farmatting, however, displaying it to the
> client as HH:MM:SS is no problem. (Display "12:36:00 AM" as military time
> (00:36:00) tells me the movie is 0 hrs, 36 minutes, 0 seconds long.

Follow?)

>
> But in an update form, which we'll use to correct errors (and there always
> seem to be errors in the running times of movies), I can't figure out how

to

> display it in the HH:MM:SS format in an INPUT field and have it submit
> without throwing a data type mismatch error.
>
> Converting the field to a text type is out of the question at this point,

so

> I know I need to do some type of string manipulation prior to update.
> However, I'm lost ...
>
> Any ideas?
>
> Thanks.
>
> UPDATE query below:
>
> <%
> ' *** Update Record: set variables
>
> If (CStr(Request("MM_update")) = "update" And CStr(Request("MM_recordId"))
> <> "") Then
>
> MM_editConnection = MM_niff2005_STRING
> MM_editTable = "tblWABimports"
> MM_editColumn = "WABIDID"
> MM_recordId = "" + Request.Form("MM_recordId") + ""
> MM_editRedirectUrl = "wabListall.asp"
> MM_fieldsStr =
>

"film|value|synopsis|value|AspectRatio|value|Forma ts_Shooting|value|Formats_

>

Exhibition|value|Sound|value|Color|value|Runtime|v alue|director_Fname|value|

>

director_Lname|value|director_company|value|direct or_addr1|value|director_ad

>

dr2|value|director_city|value|director_state|value |director_zip|value|direct

>

or_country|value|director_email|value|director_pho ne1|value|director_phone2|

>

value|director_web|value|Submission_Fname|value|Pr oducer1_f_name|value|Submi

>

ssion_Lname|value|Producer1_l_name|value|Submissio n_company|value|Producer1_

>

company|value|Submission_address|value|Producer1_a ddress|value|Submission_ad

>

dress2|value|Producer1_address2|value|Submission_c ity|value|Producer1_city|v

>

alue|Submission_state|value|Submission_postal|valu e|Producer1_state|value|Pr

>

oducer1_postal|value|Submission_country|value|Prod ucer1_country|value|Submis

>

sion_email|value|Submission_phone1|value|Producer1 _email|value|Producer1_pho

>

ne1|value|Submission_phone2|value|Producer1_phone2 |value|Submission_web|valu

> e|Producer1_web|value"
> MM_columnsStr =
>

"Title|',none,''|Synopsis_3line|',none,''|AspectRa tio|',none,''|Formats_Shoo

>

ting|',none,''|Formats_Exhibition|',none,''|Sound| ',none,''|Color|',none,''|

>

Runtime|',none,NULL|Director1_Fname|',none,''|Dire ctor1_Lname|',none,''|Dire

>

ctor1_company|',none,''|Director1_address1|',none, ''|Director1_address2|',no

>

ne,''|Director1_city|',none,''|Director1_state|',n one,''|Director1_postal|',

>

none,''|Director1_country|',none,''|Director1_emai l|',none,''|Director1_phon

>

e1|',none,''|Director1_phone2|',none,''|Director1_ website|',none,''|Submissi

>

on_Fname|',none,''|Producer1_f_name|',none,''|Subm ission_Lname|',none,''|Pro

>

ducer1_l_name|',none,''|Submission_company|',none, ''|Producer1_company|',non

>

e,''|Submission_address1|',none,''|Producer1_addre ss1|',none,''|Submission_a

>

ddress2|',none,''|Producer1_address2|',none,''|Sub mission_city|',none,''|Pro

>

ducer1_city|',none,''|Submission_state|',none,''|S ubmission_postal|',none,''

>

|Producer1_state|',none,''|Producer1_postal|',none ,''|Submission_country|',n

>

one,''|Producer1_country|',none,''|Submission_emai l|',none,''|Submission_pho

>

ne1|',none,''|Producer1_email|',none,''|Producer1_ phone1|',none,''|Submissio

>

n_phone2|',none,''|Producer1_phone2|',none,''|Subm ission_website|',none,''|P

> roducer1_website|',none,''"
>
> ' create the MM_fields and MM_columns arrays
> MM_fields = Split(MM_fieldsStr, "|")
> MM_columns = Split(MM_columnsStr, "|")
>
> ' set the form values
> For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
> MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
> Next
>
> ' append the query string to the redirect URL
> If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
> If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And
> Request.QueryString <> "") Then
> MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
> Else
> MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
> End If
> End If
>
> End If
> %>
>
>
> And the INPUT field, which reads out of the "Runtime" field in the DB:
>
> <input name="Runtime" type="text" id="Runtime"
> value="<%=(rsWAB.Fields.Item("Runtime").Value)%>" size="20" />
>
> Which shows up on the client side as "12:36:00 AM" or some other time.
>
>
> Thanks ... jr
>
>
> --
> -----------------------------------------------------
> joe ruggeri
> web designer
> JFmedia newport, ri
>
> -----------------------------------------------------
>
>

[Joe Ruggeri]

Nice catch! Thanks. It did the trick.

- jr

"CMBergin" <NoHarvestForYou@NoSpam.org> wrote in message
news:d04e94$b59$1@forums.macromedia.com...

> In your MM_columnsStr, there's a section like this:
> ...snip...Runtime|',none,NULL...snip...
> Change it to:
> Runtime|#,none,NULL
>
> "Joe Ruggeri" <joerugg@nospam.jfmedia.com> wrote in message
> news:d02h55$c27$1@forums.macromedia.com...

> > Hey All ...
> >
> > I'm developing in ASP/VBScript w/ an Access 2000 DB. The source of the

> data

> > is coming from a third party, which I get as a CSV and import into the
> > database, updating it weekly.
> >
> > Since I have no control over how the data is collected, I'm not sure how

> to

> > fix this problem, but here it is.
> >
> > This is a database of movies and among the many fields I import is one

for

> > the movie's length, expressed in HH:MM:SS. This comes into the Access DB

> as

> > a
> > Date/Time format, which means all the dates, displayed raw in the DB,

look

> > like "12:09:52 AM". Through data farmatting, however, displaying it to

the

> > client as HH:MM:SS is no problem. (Display "12:36:00 AM" as military

time

> > (00:36:00) tells me the movie is 0 hrs, 36 minutes, 0 seconds long.

> Follow?)

> >
> > But in an update form, which we'll use to correct errors (and there

always

> > seem to be errors in the running times of movies), I can't figure out

how

> to

> > display it in the HH:MM:SS format in an INPUT field and have it submit
> > without throwing a data type mismatch error.
> >
> > Converting the field to a text type is out of the question at this

point,

> so

> > I know I need to do some type of string manipulation prior to update.
> > However, I'm lost ...
> >
> > Any ideas?
> >
> > Thanks.

-- snip --

[CMBergin]

No problem. :)

"Joe Ruggeri" <joerugg@NOSPAM.jfmedia.com> wrote in message
news:d080ug$b6c$1@forums.macromedia.com...

> Nice catch! Thanks. It did the trick.
>
> - jr
>
> "CMBergin" <NoHarvestForYou@NoSpam.org> wrote in message
> news:d04e94$b59$1@forums.macromedia.com...

> > In your MM_columnsStr, there's a section like this:
> > ...snip...Runtime|',none,NULL...snip...
> > Change it to:
> > Runtime|#,none,NULL
> >
> > "Joe Ruggeri" <joerugg@nospam.jfmedia.com> wrote in message
> > news:d02h55$c27$1@forums.macromedia.com...

> > > Hey All ...
> > >
> > > I'm developing in ASP/VBScript w/ an Access 2000 DB. The source of the

> > data

> > > is coming from a third party, which I get as a CSV and import into the
> > > database, updating it weekly.
> > >
> > > Since I have no control over how the data is collected, I'm not sure

how

> > to

> > > fix this problem, but here it is.
> > >
> > > This is a database of movies and among the many fields I import is one

> for

> > > the movie's length, expressed in HH:MM:SS. This comes into the Access

DB

> > as

> > > a
> > > Date/Time format, which means all the dates, displayed raw in the DB,

> look

> > > like "12:09:52 AM". Through data farmatting, however, displaying it to

> the

> > > client as HH:MM:SS is no problem. (Display "12:36:00 AM" as military

> time

> > > (00:36:00) tells me the movie is 0 hrs, 36 minutes, 0 seconds long.

> > Follow?)

> > >
> > > But in an update form, which we'll use to correct errors (and there

> always

> > > seem to be errors in the running times of movies), I can't figure out

> how

> > to

> > > display it in the HH:MM:SS format in an INPUT field and have it submit
> > > without throwing a data type mismatch error.
> > >
> > > Converting the field to a text type is out of the question at this

> point,

> > so

> > > I know I need to do some type of string manipulation prior to update.
> > > However, I'm lost ...
> > >
> > > Any ideas?
> > >
> > > Thanks.

> -- snip --
>
>