CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> .NET

 Need Proper .NET SQL string to datestamp page access

Print topic Send  topic

Author Message
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
View profile  Send private message
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
View profile  Send private message
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
View profile  Send private message
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
View profile  Send private message
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
View profile  Send private message
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
View profile  Send private message
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
View profile  Send private message
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
View profile  Send private message
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.


Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.