article visit not display category id wise

Question

1.00/5 (2 votes)

See more:

in this code it calculates sum of article visit for all article category. But i want to display if article category 1 has 500 visit and article category 2 has 1000 visit so on. How can i do this. Please help me

SqlConnection con = Database.GetConnection();
        SqlCommand com = new SqlCommand("SELECT * FROM category WHERE article_allow = 1", con);
       int article_visit = 0;
       
        SqlDataReader rs = com.ExecuteReader();
        SqlConnection connect = Database.GetConnection();
        while (rs.Read())
        {
            SqlCommand cmd = new SqlCommand("SELECT article_visit FROM article_list WHERE cat_ID = @catID AND article_status=1", connect);
            cmd.Parameters.AddWithValue("catID", rs["cat_ID"]);
            //Label1.Text=rs["cat_ID"].ToString();
            SqlDataReader rst = cmd.ExecuteReader();
            while (rst.Read())
            {
                article_visit = Convert.ToInt32(rst["article_visit"]) + article_visit;
            }
            rst.Close();
            rst.Dispose();
            cmd.Dispose();
        }
        rs.Close();
        rs.Dispose();

I have used label1 to just check the cat_id, its not related to my question i have comment this. now if any one can tell me what i can do for get the result

Posted 19-Jun-11 21:30pm

rahul dev123

Updated 20-Jun-11 1:01am

v5

Add a Solution

Comments

Sergey Alexandrovich Kryukov 20-Jun-11 3:41am

Huh? :-)
--SA

SvenMe 20-Jun-11 3:58am

How insulting...

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2011-06-19T21:49:00

Solution 1

At a cursory glance (i.e. I haven't cut'n'pasted your code into my app and run it) it looks ok. Have you checked that you get a range of catID values?
Perhaps change

Label1.Text=rs["cat_ID"].ToString();

To

Label1.Text+=rs["cat_ID"].ToString();

for testing and see what you get?

Posted 19-Jun-11 21:49pm

OriginalGriff

Comments

rahul dev123 20-Jun-11 4:11am

This give the result like this:
Label12356789101141445253
but how can i solve my problem

OriginalGriff 20-Jun-11 4:18am

Perhaps putting a separator character would make it easier - sorry I should have thought of that...
Label1.Text+= "," + rs["cat_ID"].ToString();

SvenMe 20-Jun-11 4:17am

Honestly, you didn't wrote a single line of working code by your own. I assume that didn't even think about your problem. And still are you expecting people to do all your work.

rahul dev123 20-Jun-11 6:52am

If i get this id separated by comma then what will be do....because i need this id to sql command to view the article visit related to this id

OriginalGriff 20-Jun-11 7:00am

This isn't to solve the problem, this is to work out what the problem is... The comma just lets us know how many cat_ID values you are processing.

Christoph Keller · Answer 2 · 2011-06-20T01:51:00

Hi,

Unfortunately I don't know exactly what you try to do.. ;)
But anyway, from the SQL point of view, you should optimize your query that you don't require 2 SQL connections.

Try to execute the following script (replace both selects with the following):

SELECT		B.cat_ID as [cat_ID],
		A.article_visit AS [Article_Visit]

FROM		article_list A

LEFT JOIN	category B
ON		A.cat_ID = B.cat_ID

WHERE		A.article_status = 1
	AND	B.article_allow = 1

This will return a recordset containing the category id (name: [cat_ID]) in the first row and the article-visit count (name: [Article_Visit]) (if I understand your code correctly) in the second row.

Please be aware of the fact that the above SQL script is untested and coud require modifications according your SQL infrastructure. ;)

Hope this helps in finding a solution to your problem.

Best regards,
Stops

UPDATE: I forgot to add the where clause in the script.

article visit not display category id wise

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

article visit not display category id wise

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us