Update a database table cell in c# asp.net

Question

I have a login page linked to a login db table with 3 columns namely ID, Pass and AccessTime. Whenever the login ID is successful, I need to update the respective record with the date and time of access.

UPDATE Login SET AccessTime = '"+DateTime.Now+"' WHERE ID = '" + id + "' ;"

And, retrieve the ID and the AccessTime in another page in a textbox.

I've used sessions for the retrieval process.

Now, how do I implement this on C# ASP.NET? I'm a newbie...

You should keep information like ID and accessTime in sessions only if you are going to require it across all or most pages of your website. Unnecessary information in session state will make your pages bulkier and hence sluggish page load time will happen eventually. If you are going to need it only once on the next page where a text box control is present then simply query the database to get the access time based on ID. Also if access time is available on first page then simply pass it in query string in the URL to pass it to the next page having that text box. — RBT, Apr 07 '16 at 10:45

score 1 · Answer 1 · edited May 23 '17 at 12:15

First, learn the basics of ASP.NET http://www.asp.net/web-pages/overview/getting-started

Second, Create a procedure

      ALTER PROCEDURE SP_TRACK_USERLOG
      @id varchar(100),
      @password varchar(100)
      AS
      BEGIN
          SET NOCOUNT ON;
          INSERT INTO LOGIN(ID,Pass ,AccessTime)  VALUES (@id,@password ,GETDATE())

       END

Third, In SERVER side call the SP as follow

     SqlCommand cmd = new SqlCommand("SP_TRACK_USERLOG", con);
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.Parameters.AddWithValue("@id", Session["id"].ToString());
     cmd.Parameters.AddWithValue("@password", Session["password"].ToString());
     cmd.ExecuteNonQuery();

Also, learn about TimeZone Daylight saving time and time zone best practices . This will help in long run.

Learn about Connection String in asp.net How to create a connection string in asp.net c#

`@id varchar(100) = NULL,` is it proper syntax to write in `stored procedure` ? — Nad, Apr 07 '16 at 10:51

score 0 · Answer 2 · edited Apr 07 '16 at 11:47

You should use using:

using System.Data;
using System.Data.SqlClient;

Then create your connection, open your connection, write your SQL command, and SQL adapter

    static void Main(string[] args)
    {
        SqlConnection conn = new SqlConnection(@"Data Source = .\SQLEXPRESS; Initial Catalog=AdventureWorks2014; Integrated Security=SSPI");
        conn.Open();

In the sql command you should use sql parameters

        //somthing like thes
        SqlParameter param = new SqlParameter();
        param.ParameterName = "@param";
        param.Value = TheValue;

Then you could use them in the sql command

        SqlCommand cmd = new SqlCommand("UPDATE Login SET AccessTime = '"+DateTime.Now+"' WHERE ID = '" + id + "' ;"", conn);
      //add parameters
       cmd.Parameters.Add(param);
       cmd.ExecuteScalar();
       conn.Close();
       conn.Dispose();
    }

Update a database table cell in c# asp.net

2 Answers2