ASP, Access and time problem

Hi everyone. I am having problems trying insert a time value into an Access
table. The field in the Access table is of the the type Date/Time.

My code follows:

<%
dy = Day(request.form("date"))
mnth= Month(request.form("date"))
yr = Year(request.form("date"))
mydate= "#" & yr & "-" & mnth & "-" & dy & "#"
mytime= "#" & request.form("time") & "#"

set DBcon=Server.CreateObject("ADODB.Connection")
DBcon.ConnectionTimeout=40

cst = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("running.mdb")

DBcon.Open cst

sql= "INSERT INTO Runs(runDate,time) " & _
"VALUES " & _
"(" & mydate & "," & mytime & ")"


set NewRecord=DBcon.Execute(sql)
response.write(sql)
DBcon.Close
set DBcon=Nothing
%>

I also tried to handle the insert without the # and no luck.

Thanks in advance

Terry

Re: ASP, Access and time problem

Terry Murray wrote:
> Hi everyone. I am having problems trying insert a time value into an

What problems? Error messages? Nothing happens?

> Access table. The field in the Access table is of the the type
> Date/Time.
>
> My code follows:
>
> <%
> dy = Day(request.form("date"))
> mnth= Month(request.form("date"))
> yr = Year(request.form("date"))
> mydate= "#" & yr & "-" & mnth & "-" & dy & "#"
> mytime= "#" & request.form("time") & "#"

You're very diligent about constructing te proper date format. Why aren't
you using the same diligence for the time format?


>
> sql= "INSERT INTO Runs(runDate,time) " & _

Why use two separate fields for this?



> response.write(sql)

Good, but you need to show us the result of this...


you would probably have better success using parameters, either via saved
parameter queries:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

or by using a Command object to pass parameters to a string containing ODBC
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Try this:

<%
d=request.form("date")
if len(d)=0 then
d=null
elseif isdate(d) then
d=cdate(d)
else
'your choice ...
'return error to user?
'set d to null?
' up to you
end if

t=request.form("time")
if len(t)=0 then
t=null
elseif isdate(t) then
t=cdate(t)
else
'your choice ...
'return error to user?
'set t to null?
' up to you
end if
sql="INSERT INTO Runs(runDate,time) " & _
"VALUES (?,?)"
parms=array(d,t)

'now that all the preliminaries are completed,
'open your connection, then:

set cmd=createobject("adodb.command")
with cmd
.CommandText=sql
.ActiveConnection=DBcon
.Execute ,parms,129
end with
DBcon.close:set DBcon=nothing
%>

HTH,
Bob Barrows

--
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"

Re: ASP, Access and time problem

Thanks for the reply Bob.
The error message that I received before making your suggested change was:

Error type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/running/journal_entry.asp, line 31

I tried using the code that implement cdate to format the time before
inserting and got the same error message as above.

Thanks again
Terry

"Bob Barrows [MVP]" wrote in message
news:eE2WwComFHA.1480@TK2MSFTNGP10.phx.gbl...
> Terry Murray wrote:
> > Hi everyone. I am having problems trying insert a time value into an
>
> What problems? Error messages? Nothing happens?
>
> > Access table. The field in the Access table is of the the type
> > Date/Time.
> >
> > My code follows:
> >
> > <%
> > dy = Day(request.form("date"))
> > mnth= Month(request.form("date"))
> > yr = Year(request.form("date"))
> > mydate= "#" & yr & "-" & mnth & "-" & dy & "#"
> > mytime= "#" & request.form("time") & "#"
>
> You're very diligent about constructing te proper date format. Why aren't
> you using the same diligence for the time format?
>
>
> >
> > sql= "INSERT INTO Runs(runDate,time) " & _
>
> Why use two separate fields for this?
>
>
>
> > response.write(sql)
>
> Good, but you need to show us the result of this...
>
>
> you would probably have better success using parameters, either via saved
> parameter queries:
>
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>
> or by using a Command object to pass parameters to a string containing
ODBC
> parameter markers:
>
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
> Try this:
>
> <%
> d=request.form("date")
> if len(d)=0 then
> d=null
> elseif isdate(d) then
> d=cdate(d)
> else
> 'your choice ...
> 'return error to user?
> 'set d to null?
> ' up to you
> end if
>
> t=request.form("time")
> if len(t)=0 then
> t=null
> elseif isdate(t) then
> t=cdate(t)
> else
> 'your choice ...
> 'return error to user?
> 'set t to null?
> ' up to you
> end if
> sql="INSERT INTO Runs(runDate,time) " & _
> "VALUES (?,?)"
> parms=array(d,t)
>
> 'now that all the preliminaries are completed,
> 'open your connection, then:
>
> set cmd=createobject("adodb.command")
> with cmd
> .CommandText=sql
> .ActiveConnection=DBcon
> .Execute ,parms,129
> end with
> DBcon.close:set DBcon=nothing
> %>
>
> HTH,
> Bob Barrows
>
> --
> 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"
>
>

Re: ASP, Access and time problem

Terry Murray wrote:
> Thanks for the reply Bob.
> The error message that I received before making your suggested change
> was:
>
> Error type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
> /running/journal_entry.asp, line 31
>
> I tried using the code that implement cdate to format the time before
> inserting and got the same error message as above.
>
Please show the revised code. I'm not clear if you were still trying to use
dynamic sql, or if you switched to using a Command object to pass parameters
to the statement.

Bob Barrows


--
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"

Re: ASP, Access and time problem

Terry Murray wrote:
>> sql="INSERT INTO Runs(runDate,time) " & _

I suspect your problem is due to your use of a reserved keyword, "time", for
a column name. I suggest you change the name of that column, but, if that's
impossible for some reason, you will need to remember to surround it with
brackets [] when using it in your queries called via ADO.

Bob Barrows
http://www.aspfaq.com/show.asp?id=2080
--
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"

Re: ASP, Access and time problem

Hi again Bob. My code is as follows:

<%
d = request.form("date")
d = cdate(d)

t = request.form("duration") 'please note duration field refers to
length of run not time run occurred
t= cdate(t)

sql = "INSERT INTO Runs(runDate, duration) " & _
"VALUES (?, ?)"

parms = array(d,t)

set cmd=createobject("adodb.command")
with cmd
.CommandText = sql
.ActiveConnection = DBcon
.Execute, parms, 129
end with
DBcon.Close:set DBcon=Nothing
%>


"Bob Barrows [MVP]" wrote in message
news:OU7Yx%231mFHA.3144@TK2MSFTNGP12.phx.gbl...
> Terry Murray wrote:
> >> sql="INSERT INTO Runs(runDate,time) " & _
>
> I suspect your problem is due to your use of a reserved keyword, "time",
for
> a column name. I suggest you change the name of that column, but, if
that's
> impossible for some reason, you will need to remember to surround it with
> brackets [] when using it in your queries called via ADO.
>
> Bob Barrows
> http://www.aspfaq.com/show.asp?id=2080
> --
> 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"
>
>

Re: ASP, Access and time problem

So what's the result now?

Terry Murray wrote:
> Hi again Bob. My code is as follows:
>
> <%
> d = request.form("date")
> d = cdate(d)
>
> t = request.form("duration") 'please note duration field
> refers to length of run not time run occurred
> t= cdate(t)
>
> sql = "INSERT INTO Runs(runDate, duration) " & _
> "VALUES (?, ?)"
>
> parms = array(d,t)
>
> set cmd=createobject("adodb.command")
> with cmd
> .CommandText = sql
> .ActiveConnection = DBcon
> .Execute, parms, 129
> end with
> DBcon.Close:set DBcon=Nothing
> %>
>
>
> "Bob Barrows [MVP]" wrote in message
> news:OU7Yx%231mFHA.3144@TK2MSFTNGP12.phx.gbl...
>> Terry Murray wrote:
>>>> sql="INSERT INTO Runs(runDate,time) " & _
>>
>> I suspect your problem is due to your use of a reserved keyword,
>> "time", for a column name. I suggest you change the name of that
>> column, but, if that's impossible for some reason, you will need to
>> remember to surround it with brackets [] when using it in your
>> queries called via ADO.
>>
>> Bob Barrows
>> http://www.aspfaq.com/show.asp?id=2080
>> --
>> 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"

--
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"

Re: ASP, Access and time problem

Terry Murray wrote:
> Hi again Bob. My code is as follows:
>
> <%
> d = request.form("date")
> d = cdate(d)
>
> t = request.form("duration") 'please note duration field
> refers to length of run not time run occurred

Oh, I missed that. A Date/Time field is the wrong datatype to use for
duration. Use a numeric field to store the number of seconds. When
retrieving the data, use a custom function to format it in hours, minutes
and seconds. if the user is inputting the data in hours, minutes and
seconds, then use a custom function to convert that input into the correct
number of seconds to be stored in your duration field.


Bob Barrows
--
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"

Re: ASP, Access and time problem

Thanks again Bob. I will make the necessary changes. I do have another
field, tofrun, that should store the time the run was started.

I tried the parms stuff and got this error message:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Number of query values and destination fields are not the same.
/running/journal_entry.asp, line 25

My code is as follows:

<%
d = request.form("date")
d = cdate(d)
t = request.form("tofrun")
t = cdate(t)


set DBcon=Server.CreateObject("ADODB.Connection")
DBcon.ConnectionTimeout=40

cst = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("running.mdb")

DBcon.Open cst

sql= "INSERT INTO Runs(runDate) " & _
"VALUES (?,?)"
parms = array(d,t)


set cmd=createobject("adodb.command")
with cmd
.CommandText = sql
.ActiveConnection = DBcon
.Execute, parms, 129
end with
DBcon.Close
set DBcon=Nothing
%>

"Bob Barrows [MVP]" wrote in message
news:%23wYupr2mFHA.2860@TK2MSFTNGP15.phx.gbl...
> Terry Murray wrote:
> > Hi again Bob. My code is as follows:
> >
> > <%
> > d = request.form("date")
> > d = cdate(d)
> >
> > t = request.form("duration") 'please note duration field
> > refers to length of run not time run occurred
>
> Oh, I missed that. A Date/Time field is the wrong datatype to use for
> duration. Use a numeric field to store the number of seconds. When
> retrieving the data, use a custom function to format it in hours, minutes
> and seconds. if the user is inputting the data in hours, minutes and
> seconds, then use a custom function to convert that input into the correct
> number of seconds to be stored in your duration field.
>
>
> Bob Barrows
> --
> 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"
>
>

Re: ASP, Access and time problem

Terry Murray wrote:
> Thanks again Bob. I will make the necessary changes. I do have
> another field, tofrun, that should store the time the run was started.

Again, there is no need for a separate field. you can store both values in a
Date/time field ...

>
> I tried the parms stuff and got this error message:
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Number of query values and destination fields are not the same.
> /running/journal_entry.asp, line 25
>
> My code is as follows:
>
> <%
> d = request.form("date")
> d = cdate(d)
> t = request.form("tofrun")
> t = cdate(t)
>
>


> sql= "INSERT INTO Runs(runDate) " & _
> "VALUES (?,?)"
> parms = array(d,t)

In this case, you're only inserting one parameter, so change it to:

sql= "INSERT INTO Runs(runDate) " & _
"VALUES (?)"
parms = array(d)

Bob Barrows
--
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"

Re: ASP, Access and time problem

Bob Barrows [MVP] wrote:
> Terry Murray wrote:
>> Thanks again Bob. I will make the necessary changes. I do have
>> another field, tofrun, that should store the time the run was
>> started.
>
> Again, there is no need for a separate field. you can store both
> values in a Date/time field ...
>
>> <%
>> d = request.form("date")
>> d = cdate(d)
>> t = request.form("tofrun")
>> t = cdate(t)
>>
>>
>
>
>> sql= "INSERT INTO Runs(runDate) " & _
>> "VALUES (?,?)"
>> parms = array(d,t)
>
> In this case, you're only inserting one parameter, so change it to:
>
> sql= "INSERT INTO Runs(runDate) " & _
> "VALUES (?)"
> parms = array(d)

Unless you actually are trying to store them both in the single field ... in
which case it should look like this:

sql= "INSERT INTO Runs(runDate) " & _
"VALUES (?)"
parms = array(d + t)


Bob Barrows
--
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"