Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update SQL for date and time

Status
Not open for further replies.
paulcarey

paulcarey

Programmer
Joined
Jun 15, 2001
Messages
45
Location
GB
what's wrong with this? The table has a tim and date field both set to shortdate and shorttime. The variants sDate and sTime are assigned correctly as I can response.write using the variants. All the other fields in the SQL update fine!

sql3="UPDATE tblSystemStatus SET Status= '"& status &"', StatusText='"& theFix(note) &"'" & _
", StatusDate=#" & sDate & "#, StatusTime=#" & sTime & "# WHERE System = '" & system & "'"

Pulling teeth

paul
 
Sort by date Sort by votes
try response.writing the SQL to the page to see if the times and dates are being picked up correctly...

Bastien

There are many ways to skin this cat,
but it still tastes like chicken
 
Upvote 0 Downvote
What exactly is going wrong Paul?? Mighty :-)
 
Upvote 0 Downvote
Hi Mighty!

The respective fields in the Access database just dont update at all.
 
Upvote 0 Downvote
Can you post some more of the code. Are you sure that the sql command is actually getting executed? Mighty :-)
 
Upvote 0 Downvote
Here's the whole page

<html>

<head>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=windows-1252&quot;>
<meta name=&quot;GENERATOR&quot; content=&quot;Microsoft FrontPage 4.0&quot;>
<meta name=&quot;ProgId&quot; content=&quot;FrontPage.Editor.Document&quot;>
<title>System Status</title>
<link rel=&quot;stylesheet&quot; type=&quot;text/css&quot; href=&quot;css/status.css&quot;>
<%Language = VBScript%>
</head>
<body>
<h1>System Status</h1>
<%
'Define variables
Dim WorkDirectory
Dim I
Dim J
Dim DatabaseName
Dim SDate
Dim sTime


system = request.form(&quot;system&quot;)
status = request.form(&quot;status&quot;)
note = request.form(&quot;note&quot;)
sDate = date()
sTime = time()
%>
<%
FUNCTION theFIX(theVariable)
theFIX=Replace(theVariable,&quot;'&quot;, &quot;~&quot;)
End Function
%>


<%
'Determine the top-level directory for this URL
WorkDirectory = Request.ServerVariables(&quot;Path_Translated&quot;)
J = 0
For I = len(WorkDirectory) to 1 step -1
If mid(WorkDirectory, I, 1) = &quot;\&quot; Then
J = J + 1
If J = 2 Then
WorkDirectory = left(WorkDirectory, I)
Exit For
End If
End If
Next

'Retrieve the name of the database from the initialisation file
Set Fso = CreateObject(&quot;Scripting.FileSystemObject&quot;)
Set Inifile = Fso.opentextfile(WorkDirectory & &quot;storephotodsn.ini&quot;, 1)
DatabaseName = Inifile.ReadLine
Set Inifile = Nothing
Set Fso = Nothing

'Open the control and administration database
Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot;)
Conn.Open DatabaseName

'capture errors
On Error Resume Next


set rs = Server.CreateObject(&quot;ADODB.recordset&quot;)
sql1=&quot;Select System from tblSystemStatus Order by system asc&quot;
rs.Open sql1, conn
%>
<form method=&quot;POST&quot; action=&quot;systemmodNew.asp&quot;>

<table>
<td width = 150>Select System</td>
<td>
<Select Name = &quot;System&quot; size=&quot;1&quot;>
<%
If not(rs.EOF AND rs.BOF) Then
rs.MoveFirst
Do until rs.EOF
Response.Write &quot;<option value='&quot; & rs(&quot;System&quot;) & &quot;'> &quot; & rs(&quot;System&quot;) & &quot; </option>&quot;
rs.MoveNext
Loop
End If
rs.close
%>
</select>
<td><%response.write(&quot;Date: &quot; & sdate &&quot; & Time: &quot;& sTime)%>
</td>

</td>
<tr>
<tr>

<%
set rs = Server.CreateObject(&quot;ADODB.recordset&quot;)
sql2=&quot;Select * from tblStatus&quot;
rs.Open sql2, conn
%>
<td width = 150>Select Status</td>
<td>
<Select Name = &quot;Status&quot; size=&quot;1&quot;>
<%
If not(rs.EOF AND rs.BOF) Then
rs.MoveFirst
Do until rs.EOF
Response.Write &quot;<option value=&quot; & rs(&quot;StatusImage&quot;) & &quot;>&quot; & rs(&quot;Status&quot;) & &quot;</option>&quot;
rs.MoveNext
Loop
End If
rs.close
%>
</select>
</td>
<tr>
<tr>

<td width = 150>Status Note</td>
<td>
<input type=&quot;text&quot; name=&quot;Note&quot; size=&quot;50&quot;>
</td>
<tr>
<tr>

<td>
<input type=&quot;submit&quot; value=&quot;Submit&quot; name=&quot;B1&quot;>
</td>
<tr>
</table>

<%
set rs = Server.CreateObject(&quot;ADODB.recordset&quot;)
sql3=&quot;UPDATE tblSystemStatus SET Status= '&quot;& status &&quot;', StatusText='&quot;& theFix(note) &&quot;'&quot; & _
&quot;, StatusDate=#&quot; & sDate & &quot;#, StatusTime=#&quot; & sTime & &quot;# WHERE System = '&quot; & system & &quot;'&quot;
rs.Open sql3, conn

'next
rs.close
conn.close

%>


</body>
 
Upvote 0 Downvote
Try commenting out the On Error Resume Next line and see do you get an error message. Mighty :-)
 
Upvote 0 Downvote
Or you could try response.write(sql3)
and paste the query into your database and try it directly from there.
 
Upvote 0 Downvote
Whay use the recordset object?
Try the following:

sql3=&quot;UPDATE tblSystemStatus SET Status= '&quot;& status &&quot;', StatusText='&quot;& theFix(note) &&quot;'&quot; & _
&quot;, StatusDate=#&quot; & sDate & &quot;#, StatusTime=#&quot; & sTime & &quot;# WHERE System = '&quot; & system & &quot;'&quot;
conn.Execute sql3, , 1
'next
conn.close
Mighty :-)
 
Upvote 0 Downvote
comment out resum next did nothing.

The sql looks like this:

UPDATE tblSystemStatus SET Status= 'Red.gif', StatusText='jhsdfksdhjk', StatusDate=#5/2/02#, StatusTime=#4:11:29 PM# WHERE System = 'Central Orders'

ignoring the time for the moment, nothing seems wrong to me
 
Upvote 0 Downvote
Mighty

just tried your code with no difference i'm afraid
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
724
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
638
leifoet
leifoet
leifoet
  • Locked
  • Question Question
How to compare (date+time) with now() ?
Replies
1
Views
372
GriffMG
GriffMG
leifoet
  • Locked
  • Question Question
How to display an existing date in Input type=&quot;date&quot;?
Replies
4
Views
592
leifoet
leifoet
how2trainyourdragon
  • Locked
  • Question Question
Request.Server variables blank for classic ASP
Replies
12
Views
2K
GriffMG
GriffMG

Part and Inventory Search

Sponsor

Back
Top