Question on sql UPDATE : The Official Microsoft ASP.NET Forums

Reply

babymonsta

Member

250 Points

Question on sql UPDATE

Sep 25, 2009 05:03 AM|LINK

I have a table in my db that is used to track my email opening. The fields I placed in there has 1 field called counter which I want to update each time my email is opened. I have a code to add into the table each time it is open, but Im not sure how to make the counter number increase.

private void addtolog(String user, String id)
    {
        SqlConnection conn = new SqlConnection(GetConnectionString());


        string sql = "INSERT INTO email_tracker (username, emailid, counter, date) VALUES "
                    + " (@username, @emailid, @counter, @date)";


        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@username", user);
            cmd.Parameters.AddWithValue("@emailid", id);
            cmd.Parameters.AddWithValue("@counter", 1);
            cmd.Parameters.AddWithValue("@date", DateTime.Now.ToString());


            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string message = "Insert Error:";
            message += ex.Message;
            throw new Exception(message);
        }
        finally
        {
            conn.Close();
        }
    }

Currently that is the code, each opened email has a username and id linked to it, so thats how I add into my table a username and emailid but my major problem is with the counter. I tried to write a seperate code to update the counter, but it keeps remaining at 1. Is there a way to do INSERT with increasing the counter value each time?

Jessie

* C# Please :)

Reply

yrb.yogi

Star

14460 Points

2402 Posts

Re: Question on sql UPDATE

Sep 25, 2009 05:40 AM|LINK

use following code..

private void addtolog(String user, String id)
    {
        SqlConnection conn = new SqlConnection(GetConnectionString());




        //string sql = "INSERT INTO email_tracker (username, emailid, counter, date) VALUES "
        //            + " (@username, @emailid, @counter, @date)";


        StringBuilder sql = new StringBuilder();
        sql.Append("Declare @TotalCount int;Set @TotalCount=0;Select @TotalCount=counter+1 from email_tracker where emailid=@emailid;");
        sql.Append("INSERT INTO email_tracker (username, emailid, counter, date) VALUES");
        sql.Append("(@username,@emailid,@TotalCount,@date");
        sql.Append(")");


        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@username", user);
            cmd.Parameters.AddWithValue("@emailid", id);
            cmd.Parameters.AddWithValue("@counter", 1);
            cmd.Parameters.AddWithValue("@date", DateTime.Now.ToString());


            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string message = "Insert Error:";
            message += ex.Message;
            throw new Exception(message);
        }
        finally
        {
            conn.Close();
        }
    }

Yogesh Bhadauriya
.Net All About
MCC

Reply

ragunathjaya...

Member

83 Points

37 Posts

Re: Question on sql UPDATE

Sep 25, 2009 05:45 AM|LINK

Hi,

make the Counter field as autogenerate coloumn.

so when u insert new one it will automatically increased.

if it helpful mark as answer

Edited by ragunathjayaraman on Sep 25, 2009 05:49 AM

Reply

babymonsta

Member

250 Points

649 Posts

Re: Question on sql UPDATE

Sep 25, 2009 06:04 AM|LINK

I tried the auto increment, but the data doesnt even get added into db.

I tried the code provided above, ~~but new SqlCommand(sql, conn); is underlined red and error saying it has invalid argument..~~ but nothing gets added into my db table

Jessie

* C# Please :)

Edited by babymonsta on Sep 25, 2009 06:08 AM

Reply

yrb.yogi

Star

14460 Points

2402 Posts

Re: Question on sql UPDATE

Sep 25, 2009 06:18 AM|LINK

i made mistake on above code..please refer to following one..

private void addtolog(String user, String id)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[0].ToString());




        //string sql = "INSERT INTO email_tracker (username, emailid, counter, date) VALUES "
        //            + " (@username, @emailid, @counter, @date)";


        StringBuilder sql = new StringBuilder();


        sql.Append("Declare @TotalCount int;Set @TotalCount=0;Select @TotalCount=counter+1 from email_tracker where emailid=@emailid;");
        // notice where emailid=@emailid..u have to pass primary key to find the row distincly
        // i assume that emailid nd useranme give me unique row each time
        // or u can pass yr primary key value


        sql.Append("if @TotalCount=0 Begin Set @TotalCount=1; End; ");
        sql.Append("INSERT INTO email_tracker (username, emailid, counter, date) VALUES");
        sql.Append("(@username,@emailid,@TotalCount,@date");
        sql.Append(")");


        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql.ToString(), conn);
            cmd.Parameters.AddWithValue("@username", user);
            cmd.Parameters.AddWithValue("@emailid", id);
            cmd.Parameters.AddWithValue("@counter", 1);
            cmd.Parameters.AddWithValue("@date", DateTime.Now.ToString());


            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string message = "Insert Error:";
            message += ex.Message;
            throw new Exception(message);
        }
        finally
        {
            conn.Close();
        }
    }

Yogesh Bhadauriya
.Net All About
MCC

Edited by yrb.yogi on Sep 25, 2009 06:24 AM
Edited by yrb.yogi on Sep 25, 2009 06:26 AM

Reply

AvanthaSiriw...

Participant

863 Points

211 Posts

Re: Question on sql UPDATE

Sep 25, 2009 06:26 AM|LINK

use an auto generated column for that field...

Avantha Siriwardana

LinkId

Visit My MCTS Sharepoint 2010

Reply

babymonsta

Member

250 Points

649 Posts

Re: Question on sql UPDATE

Sep 25, 2009 06:26 AM|LINK

Somehow my db is still empty, nothing is being added into it..

Jessie

* C# Please :)

Reply

babymonsta

Member

250 Points

649 Posts

Re: Question on sql UPDATE

Sep 25, 2009 06:27 AM|LINK

I tried using auto generate the column but somehw no data is added into my db table

Jessie

* C# Please :)

Reply

kpyap

Contributor

5212 Points

989 Posts

Re: Question on sql UPDATE

Sep 25, 2009 06:31 AM|LINK

Hi,

For Identity field, please do not include in insert statement. (unless SET IDENTITY_INSERT ON)

E.g.

/* auto number */
create table #email_tracker(
	username varchar(20),
	emailid varchar(60),
	counter int identity(1,1),
	date datetime
)
declare @username as varchar(20)
declare @emailid as varchar(60)
declare @date as datetime


set @username = 'bruce_lee'
set @emailid = 'bruce.lee@abc.com'
set @date = '27 nov 1940'


INSERT INTO #email_tracker (username, emailid, date)
VALUES (@username, @emailid, @date)


INSERT INTO #email_tracker (username, emailid, date)
VALUES (@username, @emailid, @date)


INSERT INTO #email_tracker (username, emailid, date)
VALUES (@username, @emailid, @date)


set @username = 'jet_li'
set @emailid = 'jet.li@abc.com'
set @date = '26 apr 1963'


INSERT INTO #email_tracker (username, emailid, date)
VALUES (@username, @emailid, @date)


INSERT INTO #email_tracker (username, emailid, date)
VALUES (@username, @emailid, @date)


select * from #email_tracker


drop table #email_tracker
go

Notes:
Identity column will auto increase base on start number and increment value specify, and is per row basis.

If you want the counter per username and email, you need to specify the counter value.

Example below show insert counter based on username and emailId.

/* counter number */
create table #email_tracker(
	username varchar(20),
	emailid varchar(60),
	counter int,
	date datetime
)
declare @username as varchar(20)
declare @emailid as varchar(60)
declare @date as datetime

set @username = 'bruce_lee'
set @emailid = 'bruce.lee@abc.com'
set @date = '27 nov 1940'

INSERT INTO #email_tracker (username, emailid, counter, date)
SELECT @username, @emailid, count(counter) + 1, @date
FROM #email_tracker
WHERE username = @username AND emailid = @emailid

INSERT INTO #email_tracker (username, emailid, counter, date)
SELECT @username, @emailid, count(counter) + 1, @date
FROM #email_tracker
WHERE username = @username AND emailid = @emailid

INSERT INTO #email_tracker (username, emailid, counter, date)
SELECT @username, @emailid, count(counter) + 1, @date
FROM #email_tracker
WHERE username = @username AND emailid = @emailid

set @username = 'jet_li'
set @emailid = 'jet.li@abc.com'
set @date = '26 apr 1963'

INSERT INTO #email_tracker (username, emailid, counter, date)
SELECT @username, @emailid, count(counter) + 1, @date
FROM #email_tracker
WHERE username = @username AND emailid = @emailid

INSERT INTO #email_tracker (username, emailid, counter, date)
SELECT @username, @emailid, count(counter) + 1, @date
FROM #email_tracker
WHERE username = @username AND emailid = @emailid

select * from #email_tracker

drop table #email_tracker

Hope it give you some ideas to proceed.

Edited by kpyap on Sep 25, 2009 06:33 AM

Reply

yrb.yogi

Star

14460 Points

2402 Posts

Re: Question on sql UPDATE

Sep 25, 2009 06:41 AM|LINK

use the following one or u can use the store procedure too....

for inline query use this...

private void addtolog(String user, String id)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[0].ToString());


        //string sql = "INSERT INTO email_tracker (username, emailid, counter, date) VALUES "
        //            + " (@username, @emailid, @counter, @date)";


        StringBuilder sql = new StringBuilder();


        sql.Append("Declare @TotalCount int;Set @TotalCount=0;Select @TotalCount=counter+1 from email_tracker where emailid=@emailid;");
        // notice where emailid=@emailid..u have to pass primary key to find the row distincly
        // i assume that emailid nd useranme give me unique row each time
        // or u can pass yr primary key value


        sql.Append("if @TotalCount=0 Begin Set @TotalCount=1; End; ");
        sql.Append("INSERT INTO email_tracker (username, emailid, counter, date) VALUES");
        sql.Append("(@username,@emailid,@TotalCount,@date");
        sql.Append(")");


        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql.ToString(), conn);
            cmd.Parameters.AddWithValue("@username", user);
            cmd.Parameters.AddWithValue("@emailid", id);
            cmd.Parameters.AddWithValue("@counter", 1);
            cmd.Parameters.AddWithValue("@date", DateTime.Now.ToString());


            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string message = "Insert Error:";
            message += ex.Message;
            throw new Exception(message);
        }
        finally
        {
            conn.Close();
        }
    }

For using store procedure use below code..

private void addtolog(String user, String id)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[0].ToString());

        try
        {
            conn.Open();            
            SqlCommand cmd = new SqlCommand(); ;
            cmd.Parameters.AddWithValue("@username", user);
            cmd.Parameters.AddWithValue("@emailid", id);
            cmd.Parameters.AddWithValue("@counter", 1);
            cmd.Parameters.AddWithValue("@date", DateTime.Now.ToString());

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            cmd.CommandText = "InsertIntoEmailTracker"; // your store procedure name

            cmd.ExecuteNonQuery();
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string message = "Insert Error:";
            message += ex.Message;
            throw new Exception(message);
        }
        finally
        {
            conn.Close();
        }
    }

and your store procedure is as:

Create Proc InsertIntoEmailTracker
(
	@username varchar(50), -- your datatype of table
	@emailid varchar(50), -- your datatype of table
	@counter int, -- your datatype of table
	@date datetime
)
As
Declare @TotalCount int -- counter for increment
Set @TotalCount=0; -- initially zero

Select @TotalCount=Counter+1 from email_tracker where emailid=@emailid and username=@username

-- look where emailid=@emailid and username=@username
-- i assume both column gives u unique row
-- so that they are primary key
-- or u can use primary key
if @TotalCount=0
Begin
	Set @TotalCount=1;
End

Insert into email_tracker
(
	username,emailid, counter, date
)
Values
(
	@username,@emailid,@TotalCount,GetDate()
)

Yogesh Bhadauriya
.Net All About
MCC

Question on sql UPDATE RSS

90 replies

babymonsta

Question on sql UPDATE

yrb.yogi

Re: Question on sql UPDATE

ragunathjaya...

Re: Question on sql UPDATE

babymonsta

Re: Question on sql UPDATE

yrb.yogi

Re: Question on sql UPDATE

AvanthaSiriw...

Re: Question on sql UPDATE

babymonsta

Re: Question on sql UPDATE

babymonsta

Re: Question on sql UPDATE

kpyap

Re: Question on sql UPDATE

yrb.yogi

Re: Question on sql UPDATE

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft