Solved

asp.net, C# Insert date into database table on Page_Load

Posted on 2012-04-10
1
759 Views
Last Modified: 2012-04-12
Hello,
I have a page that contains a lable which displays the current date and time.
I'm attempting to insert the date and time displayed on this label to a database table on Page_Load.

My *.aspx code follows:
----------------------
<table>
<tr>
<td colspan="2" style="width:100%; vertical-align:top; text-align:left;">&nbsp;
<asp:Label ID="lblHits" runat="server" Text="Label"></asp:Label>
</td>
</tr>
<tr>
<td colspan="2" style="width:100%; vertical-align:top; text-align:left;">&nbsp;
<asp:Label ID="lblHitCounterDate" runat="server" Text="Label"></asp:Label>
</td>
</tr>
</table>
----------------------------------

My codebehind follows:

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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Configuration;
using System.Collections;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Drawing;
using System.Drawing.Imaging;
using System.Net.Mail;
using System.IO;

  string strConnStringHitCounterNew = ConfigurationSettings.AppSettings["HitCounterNew"];

 protected void Page_Load(object sender, EventArgs e)
        {

            //objects we will need to work with the db    
            SqlConnection conn;
            SqlCommand cmd;    

            lblHitCounterDate.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss ");

            if (!Page.IsPostBack)
            {
                //connect to the db        
                conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["HitCounterNew"].ConnectionString);

                //the sql command to increment hits by 1        
                cmd = new SqlCommand("UPDATE HitCounterNew SET HitCounter = HitCounter+1 WHERE PageName=@PageName", conn);

                cmd.CommandType = CommandType.Text;
                //update where Name is 'Default' which corresponds to this page        
                cmd.Parameters.AddWithValue("@PageName", "warrants_search_results");
               
                using (conn)
                {
                    //open the connection            
                    conn.Open();

                    string insertSQL = "INSERT INTO HitCounterNew (";
                    insertSQL += "HitCounterDate)";
                    insertSQL += "VALUES (";
                    insertSQL += "@HitCounterDate)";
                    cmd.Parameters.AddWithValue("@HitCounterDate", lblHitCounterDate.Text);

                    //send the query            
                    cmd.ExecuteNonQuery();
                }

            }

            //DISPLAY HITS IN THE LABEL    
            //connect to the db    
            conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["HitCounterNew"].ConnectionString);
            //the sql command to select the row of hits corresponding to this page    
            cmd = new SqlCommand("SELECT * FROM HitCounterNew WHERE PageName=@PageName", conn);
            cmd.CommandType = CommandType.Text;
            //select where Name is 'Default' which corresponds to this page    
            cmd.Parameters.AddWithValue("@PageName", "warrants_search_results");
            using (conn)
            {   //open the connection        
                conn.Open();
                //send the query and store the results in a sqldatareader        
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.Read())
                {
                    //set the text of our label to the current # of hits            
                    lblHits.Text = "Counter Pageviews: - " + rdr["HitCounter"].ToString();
                    lblHitCounterDate.Text = "HitCounter Date: - " + rdr["HitCounterDate"];
                }
            }
   
                     
       
            }
        }

------------------------
database table ( HitCounterNew)
database table structure as follows:

PageName (nvarchar(50))
HitCounter (int)
HitCounterDate (nvarchar(50))

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

ANY help in getting the date and time to write to the table "HitCounterNew" would be greatly appreciated.
0
Comment
Question by:Codeaddict7423
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 20

Accepted Solution

by:
BuggyCoder earned 500 total points
ID: 37830091
using (conn)
                {
                    //open the connection            
                    conn.Open();

                    //send the query  -- To Update Hit Counter
                    cmd.ExecuteNonQuery();

                    //Another command To Store Date
                    cmd=new SQLCommand("Insert Into HitCounterNew (HitCounterDate) Values(@HitCounterDate)",conn);
                    cmd.Parameters.Add("@HitCounterDate",lblHitCounterDate.Text);
                    cmd.ExecuteNonQuery();

                }

Open in new window

0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question