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.
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?
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
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()
)
babymonsta
Member
250 Points
649 Posts
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?
* C# Please :)
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(); } }.Net All About
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
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* C# Please :)
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(); } }.Net All About
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...
LinkId
Visit My MCTS Sharepoint 2010
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..
* C# Please :)
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
* C# Please :)
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.
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.
Hope it give you some ideas to proceed.
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:
.Net All About