ASP and SQL and auto update datefield

[softboy12]

Hi all

i have to fields in database (see code below)

x_gesperrt = rs("gesperrt")

and

x_sperrdatum = rs("sperrdatum")

now my question is the follwing:

if i set the field "x_gesperrt" to true how can automatically update the "x_sperrdatum" which is a datetime field?

many thanks in advance for your trouble

best regards

E.Altherr

 

[Veep]

Update it to what? Not real clear.

x_gesperrt = TRUE
myNewDateTime = Now()

IF x_gesperrt THEN
x_sperrdatum = myNewDateTime
End IF

 

[softboy12]

to the change date

which means, if i set the value (via a form) to true then the field x_sperrdatum shoud be automatically updated with the actual date (so i can see when then record was changed)

 

[Veep]

Well then the above logic will do it only you'll need to update the value in your database.

IF x_gesperrt THEN
x_sperrdatum = myNewDateTime
strSql="UPDATE myTable SET x_sperrdatum ='" & x_sperrdatum & "' WHERE myPrimaryKey=" yourPrimaryKeyVariable
conn.execute(strSql)

ELSE
'do something else
End IF

 

[softboy12]

it doesn't work (SQL does not update the date)

is the statement below right?

<% If x_gesperrt = True Then %> <x_sperrdatum = myNewDateTime strSql='"UPDATE data SET x_sperrdatum= '" & x_sperrdatum & "' WHERE WHERE [ID]=" & tkey conn.execute(strSQL) >

 

[Veep]

That SQL statement is going to cut it.

IF x_gesperrt = TRUE; redundant, not necessary
The format below will update the database providing you can supply the [ID] value for that particular record and your connection string.

<%
IF x_gesperrt THEN
   myNewDateTime = NOW()
   tKey = myIDColumn
   strSql="UPDATE myTable SET x_sperrdatum ='" &    myNewDateTime & "' WHERE [ID]=" tKey
   set conn = server.createobject("ADODB.CONNECTION")
   myConnString="Your_Connection_String_Here"
   conn.open myConnString
   conn.execute(strSql)
   conn.close
   set conn = nothing
End IF
%>

 

[Double02]

As this is one block of code, don't terminate it early!

<% If x_gesperrt = True Then [red]%> <[/red]x_sperrdatum = myNewDateTime strSql='"UPDATE data SET x_sperrdatum= '" & x_sperrdatum & "' WHERE WHERE [ID]=" & tkey conn.execute(strSQL) [red]%[/red]>

 

[softboy12]

it doesn't work

the problem is the following:

if i use the short code i can't the change the status of the field x_gesperrt to yes, it will resetted to No automatically

see code below
---------------------------------------------------------------
<input type="radio" name="x_gesperrt"><% If x_gesperrt = True Then %>< x_sperrdatum = myNewDateTime strSql='"UPDATE data SET x_sperrdatum= '" & x_sperrdatum & "' WHERE [ID]=" & tkey conn.execute(strSQL) %> checked<% End If %> value="1"><%= "Ja" %><input type="radio" name="x_gesperrt"<% If x_gesperrt = False Then %> checked<% End If %> value="0"><%= "Nein" %></font>&nbsp;</td>
------------------------------------------------------------

many thanks for your help

 

[Veep]

OK, let me see if I understand. You have two radio buttons (true, false)(1, 0). You're pulling data from a database. If the database value of x_gesperrt = TRUE then you want to update your timestamp field x_sperrdatum. Correct?

<%
x_gesperrt = rs("x_gesperrt")
tKey = rs("ID")
' Same as before
IF x_gesperrt THEN
   myNewDateTime = NOW()
   strSql="UPDATE myTable SET x_sperrdatum ='" &    myNewDateTime & "' WHERE [ID]=" tKey
   set conn = server.createobject("ADODB.CONNECTION")
   myConnString="Your_Connection_String_Here"
   conn.open myConnString
   conn.execute(strSql)
   conn.close
   set conn = nothing
End IF

%>
<input type="radio" name="x_gesperrt" <%IF x_gesperrt THEN%>CHECKED<%End If%>>&nbsp;JA &nbsp;<input type="radio" name="x_gesperrt" <%IF NOT x_gesperrt THEN%>CHECKED<%End If%>>&nbsp; NEIN &nbsp;

 

[softboy12]

exactly thats what i want

if the value of x_gesperrt is true (1) the timestamp shoud be updated, so i can see, when the record was locked

i'll try with the code above (last one) to see if it works or not

many thanks in advance

 

[softboy12]

it's very strange


if i use a else statement

see below:

-----------------------------------------------------------
..........

Else
If rs("gesperrt") = True Then
myNewDateTime = NOW()
tKey = "" & key & ""
strSQL = "UPDATE [Kunden] SET x_sperrdatum ='" &    myNewDateTime & "' WHERE [ID]=" & tKey
conn.execute(strSQL)
End If

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

the browser give the following error (if open the asp Page):

"End Expected"

 

[onpnt]

do you mean

If [i]something[/i] Then
......
ElseIf rs("gesperrt") = True Then
     myNewDateTime = NOW()
     tKey = "" & key & ""
     strSQL = "UPDATE [Kunden] SET x_sperrdatum ='" &    myNewDateTime & "' WHERE [ID]="  & tKey
     conn.execute(strSQL)
End If

___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811[/sub]

 

[softboy12]

now found the error

see code below


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

End If
If rs("gesperrt") = True Then
myNewDateTime = NOW()
tkey = "" & key & ""
strsql = "UPDATE [Kunden] SET x_sperrdatum =" & myNewDateTime & " WHERE [ID]=" & tkey
conn.execute(strsql)
End If
------------------------------------------------------------

the statement works half, but the following error appears in the Browser:

Microsoft OLE DB Provider for SQL Server- Fehler '80040e14'

Zeile 1: Wrong Syntax near to '.2004'.

so i think this i as problem with the Date Format right?

 

[softboy12]

after i modified the code below again it works half

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

If rs("gesperrt") = True Then
myNewDateTime = Now()
tkey = "" & key & ""
strsql = "UPDATE [Kunden] SET sperrdatum ='" & myNewDateTime & "' WHERE [ID]=" & tkey
conn.execute(strsql)
conn.close
Set conn = Nothing
End If

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

if i change the value "x_gesperrt" to 1 (True) and the press "Bearbeiten" to take effect of the changes, the Browser shows the following error

Timeout expired

/Kundenedit.asp, line 360

and now

 

[softboy12]

Hi all

Now (after i tried different possibilty the Code works fine

if i set the rs "gesperrt" to 1 (TRUE) the actual Server Date/Time will be filled in automatically


see code below (if someone is interested)
------------------------------------------------------------

if x_gesperrt THEN
myNewDateTime = NOW ()
tkey = "" & key & ""
updatesql = "UPDATE [Kunden] SET sperrdatum ='" & myNewDateTime & "' WHERE [ID]=" & tkey
Set conn = Server.CreateObject("ADODB.CONNECTION")
Set rs = Server.CreateObject("ADODB.Recordset")
conn.Open xDb_Conn_Str
conn.execute(updatesql)
conn.close
Set conn = Nothing
------------------------------------------------------------

i don't why it works, but i works and thats the main thing for me

many thanks for your help

 

[softboy12]

Next problem

as try to reserve the function it won't work

see code below
------------------------------------------------------------

If x_gesperrt = False THEN
tkey = "" & key & ""
updatesql = "UPDATE [Kunden] SET sperrdatum ='' WHERE [ID]=" & tkey
Set conn = Server.CreateObject("ADODB.CONNECTION")
Set rs = Server.CreateObject("ADODB.Recordset")
conn.Open xDb_Conn_Str
conn.execute(updatesql)
conn.close
Set conn = Nothing
End If
------------------------------------------------------------

so if the Value of the x_gesperrt is False the Date shoud be deleted from the field "Sperrdatum" automatically

how can i do this?

many thanks again for your help

best regards

E.Altherr