Programmatically Accessing an SQLDataSource with a "SELECT COUNT(*)" query.

I've found example code of accessing an SQLDataSource and even have it working in my own code - an example would be

Dim datastuff As DataView = CType(srcSoftwareSelected.Select(DataSourceSelectArguments.Empty), DataView)

Dim row As DataRow = datastuff.Table.Rows(0)
Dim installtype As Integer = row("InstallMethod")
Dim install As String = row("Install").ToString
Dim notes As String = row("Notes").ToString

The above only works on a single row, of course. If I needed more, I know I can loop it.

The query in srcSoftwareSelected is something like "SELECT InstallMethod, Install, Notes FROM Software"

My problem lies in trying to access the data in a simliar way when I'm using a SELECT COUNT query.

Dim datastuff As DataView = CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), DataView)
Dim row As DataRow = datastuff.Table.Rows(0)
Dim count As Integer = row("rowcnt")

The query here is "SELECT COUNT(*) as rowcnt FROM Software"

The variable count is 1 every time I query this, no matter what the actual count is. I know I've got to be accessing the incorrect data member in the 2nd query because a gridview tied to srcSoftwareUsage (the SQLDataSource) always displays the correct value.

Where am I going wrong here?

The following should work.

Dim datastuffAs System.Data.DataView =CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), System.Data.DataView)

Dim drAs System.Data.DataRow = datastuff .Table.Rows(0)

Dim mycountAsString = Convert.ToInt32(dr("rowcnt")).ToString()

'Label1.Text = mycount

|||

Hi there,

Aren't you getting the row count from your SELECT COUNT query (1 row obviously)? Instead of getting the result value from that query?

gonzzas

|||

It's very similar to what I've tested out, but that exact code will show that mycount = "1" instead of the actual value.

What is interesting is the GridView control I set up on the test page is outputting the correct result.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="srcSoftwareUsage" Width="527px">
<Columns>
<asp:BoundField DataField="rowcnt" HeaderText="rowcnt" ReadOnly="True" SortExpression="rowcnt" />
</Columns>
</asp:GridView>

Now, it's obviously accessing the column named rowcnt. I can debug my code and manually look at the column values in DataView.Table.Rows(0) and it shows the value 1 and nothing more.

|||

Yes, I can use either code to get the correct count from my query. What is your SqlDataSource code?

Here is what I tested:

Dim dvAs System.Data.DataView =CType(SqlDataSource2.Select(DataSourceSelectArguments.Empty), System.Data.DataView)

Dim rowAs System.Data.DataRow = dv.Table.Rows(0)

' For Each row As System.Data.DataRow In dv.Table.Rows

Dim mycountAsString = Convert.ToInt32(row("rowcnt")).ToString()

Label2.Text = mycount

' Next

|||

Murphy's Law probably applies as I didn't give you thecomplete story - I naively thought this part shouldn't matter as it'sthe table output is identical.

It appears to be my sql query.

I'm not actually looking for the count of rows in the Software table, but I'm looking for the count of times a particular row in Software is referenced by 2 other tables - RoleSoft and TeamSoft

With my simple query above, both the code and GridView worked. With this one - the GridView works, the code doesn't.

SELECT COUNT(*) AS rowcnt FROM (SELECT Role, Software FROM RoleSoft WHERE (Software = @.Id) UNION ALL SELECT Team, Software FROM TeamSoft WHERE (Software = @.Id)) AS derivedtbl_1

I thought it was a moot point as the table output appears identical from each query. Obviously I'm wrong. I'm imagining the derivedtbl_1 is probably where I'm getting bogus data in the code.

1> SELECT COUNT(*) AS rowcnt FROM (SELECT Role, Software FROM RoleSoft WHERE (Software = 2) UNION ALL SELECT Team, Software FROM TeamSoft WHERE (Software = 2))
AS derivedtbl_1
2> go
rowcnt
----
3

(1 rows affected)
1> SELECT COUNT(*) as rowcnt FROM Software
2> go
rowcnt
----
8

(1 rows affected)

|||

Bah. I figured it out. It wasn't even the SQL statement. I had updated the @.Id parameter in the srcSoftwareUsage_Selecting event handler and I misused a global variable. It kept setting @.Id to 1 and the count for that Id was always 1.

Now I feel stupid for wasting your time and mine on this. Thanks for the help, though.