@@rowcount variable and ASP.NET

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

@@rowcount variable and ASP.NET

Author

Topic

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-11 : 10:56:07

Hi all,
in my ASP.NET page I have a SELECT query that retrieves some rows...
for showing the number of rows in a simple TextBox I can make a new stored procedure with an output parameter then make a new SqlParameter(in my Commadobject) and then set the Dirrection to Output....and show the number of the rows
can someone tell me how I can achieve my goal without using stored procedures...
That means I want to show @@rowcount variable in a simple TextBox in my ASP.NET page without using stored procedures.
Any help appreciated.

---------------------------------------
Mehdi.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-11 : 11:11:36

well since @@rowcount works only for the last executed statement in a session, you'd have to put the @@rowcount into a global temp table, get it from there and drop the table.

you could also do
sqlCommand.text = "select ... from table;@@rowcount"

but i don't know how that would be returned, so you would have to try it yourself.

Go with the flow & have fun! Else fight the flow :)

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-11 : 12:27:04

Thank you spiriti1 for the reply
I tryed below code but it gives me error


string select="select categoryname,categoryid from categories;select @@rowcount;";
SqlCommand objCommand=new SqlCommand(select,objConnection);
SqlParameter sp=new SqlParameter("@@rowcount",SqlDbType.Int);
objCommand.Parameters.Add(sp);
sp.Direction=ParameterDirection.Output;
//i.e DataGrid1.DataSource=objCommand.ExecuteReader();
//i.e DataGrid1.DataBind();

The error is "Incorrect syntax near '@@rowcount'" while it gets objCommand.ExecuteReader();
can you again help me?
Thanks in advance.

---------------------------------------
Mehdi.

MorningZ
Starting Member

44 Posts

Posted - 2004-08-11 : 12:30:09

why not just put the data into a DataSet and then you can say:

ds.Tables(0).Rows.Count

and that's the # of records returned, without any of the "@@rowcount" stuff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-11 : 12:33:24

MorningZ: of course.... :))) how on earth do i miss something so obvious????

Go with the flow & have fun! Else fight the flow :)

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-08-11 : 12:37:46

You shouldn't have to put it in a dataset, just use the datagrid properties.

item count = datagrid.Items.Count

Dustin Michaels

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-11 : 12:47:40

quote:

MorningZ: of course.... :))) how on earth do i miss something so obvious????

but still I have this question suppose I want to use only @@rowcount I think it could be safer and better.
(maybe our users change our datagrid or our dataset in disconnected enviroment)

---------------------------------------
Mehdi.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-11 : 12:51:02

it doesn't matter. after the executeReader, set the textbox.Text to ds.Tables(0).Rows.Count
and then they can do what they want with the dataset.

Go with the flow & have fun! Else fight the flow :)

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-11 : 13:09:23

No,spirit1 I want to retrieve the number of rows in my datasorce evry time I want
maybe i.e user deletes a rows or adds new one ,after it if I want to get the number of rows it would get me a wrong number...

---------------------------------------
Mehdi.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-11 : 13:16:19

ok, so what is stopping you to set the textbox.Text after every trip to db?

Go with the flow & have fun! Else fight the flow :)

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-11 : 13:26:29

well,I dont know how I can set TextBox1.Text with @@rowcount obtained from my query(without using stored procedure)
I told you a method but it doesnt work......

---------------------------------------
Mehdi.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-11 : 13:31:27

no, no, i mean, after every trip to db set the textbox.Text to ds.Tables(0).Rows.Count.
if you update data use
DataAdapter.Update(ds);
textbox.Text = ds.Tables(0).Rows.Count.

if you select data use:
DataAdapter.Fill(ds);
textbox.Text = ds.Tables(0).Rows.Count.

Go with the flow & have fun! Else fight the flow :)

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-11 : 13:35:27

I dont want to use this method,is there any better way?consider every time you should write it...

---------------------------------------
Mehdi.

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-11 : 13:37:08

it still has some problems maybe your dataset doesnt update correctly.

---------------------------------------
Mehdi.

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-11 : 13:41:22

I think we should not work outside our datasource we should get the result every time from datasource.

---------------------------------------
Mehdi.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-11 : 13:44:58

well doing
textbox.Text = resultOfRowCount
takes the same amount of work.
if the dataset doesnt update correctly, you simply don't change the value in textbox. a dataset is updated all succesfully or not at all.

using select and @@rowcount outside the stored procedure is probably not possible in the way you want it to.
i said in the first reply that you'd have to try it becasue i'm not sure if it works...

all in all getting the rowcount in the way you want it is probably best in the application and not on the server.

Go with the flow & have fun! Else fight the flow :)

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-11 : 14:12:01

I think its much better to use SP but i wanted to see if its possible without SP
it seems not..
Thanks anyway spirit1
(but nothing is impossible Hope I find the way)

---------------------------------------
Mehdi.

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-12 : 12:54:07

can anyone help me?
I and spirit1 didn't get any result yesterday.
Thanks

---------------------------------------
Mehdi.

chadmat
The Chadinator

1974 Posts

Posted - 2004-08-12 : 16:23:12

1. You should probably be using stored procs.
2. You should defitely use the DataSets Rows.Count properety, that is what it is for. You can't worry about "What if it doesn't update correctly", if the DataSet doesn't update correctly, then I think having an invalid rowcount is the least of your problems.

Just my 2 cents.

Seems to me you and spirit1 got a result, maybe not the one you wanted, but from what I can tell, probably the best one.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-12 : 16:33:10

yes,using SP is much better.
Thank you Chad.

---------------------------------------
Mehdi.

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-09-15 : 06:01:37

I found ...


SqlConnection objConnection=new SqlConnection(strConnection);
//strSql is your select query
SqlCommand objCommand=new SqlCommand(strSql,objConnection);
objConnection.Open();
DataGrid1.DataSource=objCommand.ExecuteReader();
DataGrid1.DataBind();
objConnection.Close();
objCommand.CommandText="select @@rowcount";
objConnection.Open();
TextBox1.Text=objCommand.ExecuteScalar().ToString();
objConnection.Close();

---------------------------------------
Mehdi.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources