kenbaum
Posts: 8
|
| Posted: 11/18/2005, 4:51 PM |
|
I am a newbie to .NET and am having trouble figuring out how to properly construct a SQL update. I am trying to have the record of the logged in user datestamped after logging in. So on the page the login page goes to I have added:
'Page Event BeforeShow. Action Custom Code @4-73254650
' -------------------------
Dim Request As HttpRequest = HttpContext.Current.Request
Dim NewDao As DataAccessObject = Settings.ArchiveDataAccessObject
Dim Sql As String = "UPDATE registration SET last_access = '1/1/2005' WHERE reg_id = 1"
NewDao.RunSql(Sql)
in the beforeshowevet, This works but is not the current date or user. I want it to be something like this:
Dim Sql As String = "UPDATE registration SET last_access = [currentdatetime] WHERE reg_id = [userid]"
I tried adding "DateTime.Now.ToString()" and "DateTime.Now()" but get SQL errors. I also don't know how to express the current userid in this statement.
Anyone got an answer?
TIA
Ken
_________________
Ken Baum Multimedia
http://www.kenbaum.com
web - cd - dvd - kiosks |
 |
 |
jeden
Posts: 20
|
| Posted: 11/19/2005, 12:55 AM |
|
The correct format would be
DateTime.Now.ToString ("yyyy-MM-dd HH:mm:ss")
so, you should construct your sql statement as follows:
sql.Format ("UPDATE registration SET last_access = '{0}' WHERE reg_id={1}", DateTime.Now.ToString ("yyyy-MM-dd HH:mm:ss"), userId);
where userId is the variable that holds the user id value
This words for C# - sorry, not enough experience with VB.
Ant.
_________________
Antonio Bello
Elapsus - Software & Solutions
http://www.elapsus.com
Developer's Corner
http://www.developer-corner.com |
|
|
|
kenbaum
Posts: 8
|
| Posted: 11/19/2005, 6:37 AM |
|
Thansk for the reply.
That syntax doesn't seem to work in VB though.
Ken
_________________
Ken Baum Multimedia
http://www.kenbaum.com
web - cd - dvd - kiosks |
|
|
|
jeden
Posts: 20
|
| Posted: 11/19/2005, 7:58 AM |
|
Maybe it was the trailing semicolon at the end....required in C#, but not in VB
I tried to compile this code and it is compiled
Dim sql As String
sql.Format("UPDATE registration SET last_access = '{0}' WHERE reg_id={1}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), UserId)
Of course, you have to declare and set the UserId variable
Let me know if it solves the problem
Ant.
_________________
Antonio Bello
Elapsus - Software & Solutions
http://www.elapsus.com
Developer's Corner
http://www.developer-corner.com |
|
|
|
kenbaum
Posts: 8
|
| Posted: 11/19/2005, 8:18 AM |
|
Ant,
I tried this:
Dim UserId As Integer = 1
Dim Request As HttpRequest = HttpContext.Current.Request
Dim NewDao As DataAccessObject = Settings.ArchiveDataAccessObject
Dim sql As String
sql.Format("UPDATE registration SET last_access = '{0}' WHERE reg_id={1}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), UserId)
NewDao.RunSql(Sql)
And it compiles but I get this error when I run it:
ExecuteReader: CommandText property has not been initialized
Ken
_________________
Ken Baum Multimedia
http://www.kenbaum.com
web - cd - dvd - kiosks |
|
|
|
kenbaum
Posts: 8
|
| Posted: 11/19/2005, 10:41 AM |
|
Ant,
I just found teh problem ; A missing "=" in
Dim sql As String
sql.Format("UPDATE registration SET last_access = '{0}' WHERE reg_id={1}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), UserId)
It works now. Any idea how I set the userid to be the logged in userid?
thanks
Ken
_________________
Ken Baum Multimedia
http://www.kenbaum.com
web - cd - dvd - kiosks |
|
|
|
peterr
Posts: 5971
|
| Posted: 11/19/2005, 8:12 PM |
|
UserID is created automatically as a session variable when a user logs in. I haven't tested this but as I understand that the way to retrieve session variable would be:
System.Web.HttpContext.Current.Session("UserID")
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
kenbaum
Posts: 8
|
| Posted: 11/19/2005, 8:19 PM |
|
Thanks Peter.
I was able to get it to work with the following:
Dim Request As HttpRequest = HttpContext.Current.Request
Dim NewDao As DataAccessObject = Settings.ArchiveDataAccessObject
Dim UserId As Integer = NewDao.ToSql(DBUtility.UserId.ToString(),FieldType._Integer)
Dim sql As String = sql.Format("UPDATE registration SET last_access = '{0}' WHERE reg_id={1}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), UserId)
NewDao.RunSql(Sql)
I found this part:
NewDao.ToSql(DBUtility.UserId.ToString(),FieldType._Integer)
in the help file under "Execute Custom SQL"
I'm going to add your info to my notes for next time.
Ken
_________________
Ken Baum Multimedia
http://www.kenbaum.com
web - cd - dvd - kiosks |
|
|
|
Benjamin Krajmalnik
|
| Posted: 11/30/2005, 12:15 PM |
|
Update registration SET last_access = getdate() where regid = 1
This assumes you are using SQL server on the back end.
getdate() is an internal SQL functio which will place the datetime of the
SQL server.
I prefer using this method since then client time issues do not come into
play.
|
|
|
|
