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 sizbut 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
Jun 15, 2001
45
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
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
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
379
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
342
leifoet
leifoet
leifoet
  • Locked
  • Question
How to compare (date+time) with now() ?
Replies
1
Views
150
GriffMG
GriffMG
leifoet
  • Locked
  • Question
How to display an existing date in Input type=&quot;date&quot;?
Replies
4
Views
285
leifoet
leifoet
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
973
GriffMG
GriffMG

Part and Inventory Search

Sponsor

Back
Top