need some help retrieving a record count data

[justapimp]

I have this peace of code that I am working on and I would like to get the record count data returned by this querrey bellow.

SELECT    COUNT( *) AS 'TotalGeofence'
FROM         GeofenceData
WHERE (DeviceUID = @DeviceUID AND
Time BETWEEN @StartTime AND @EndTime)

I then used this C# implementation to get the count and it is not working.

protected Int32 TotalGeofenceLocate()
    {
        int MonthlyFenceCount = 0;
        Int32 DeviceUID = 227;
        DateTime FromDate = new DateTime(StartingDate.Year, StartingDate.Month, 23);
        DateTime EndingDate = new DateTime(StartingDate.Year, StartingDate.Month, 22);
        GeofenceDataTableAdapter TotalFenceLocate = new GeofenceDataTableAdapter();
        JeanPierre.GeofenceDataDataTable GeofenceDataTable = TotalFenceLocate.GetGeofenceCountByDeviceUIDTime(DeviceUID, FromDate, EndingDate);
        foreach (JeanPierre.GeofenceDataRow FenceRows in GeofenceDataTable)
        {
            MonthlyFenceCount = Convert.ToInt32(FenceRows....);
        }
        //MonthlyFenceCount = GeofenceDataTable.Rows.Count;
        return (MonthlyFenceCount);
    }

Can anyone help me with this one?

[a_shym41]

Your query will always 1 record because it just returns the count and not the full set of records you may need. If you want to use your query you will have to change the code in C# like this:
MonthlyFenceCount = Convert.ToInt32(GeofenceDataDataTable.Rows(0)(0))
and dont use that foreach loop.

Thanks,
Shyam

[justapimp]

would you provide some clarification on the suggested implementation.

[a_shym41]

your code should be

protected Int32 TotalGeofenceLocate()
{
int MonthlyFenceCount = 0;
Int32 DeviceUID = 227;
DateTime FromDate = new DateTime(StartingDate.Year, StartingDate.Month, 23);
DateTime EndingDate = new DateTime(StartingDate.Year, StartingDate.Month, 22);
GeofenceDataTableAdapter TotalFenceLocate = new GeofenceDataTableAdapter();
JeanPierre.GeofenceDataDataTable GeofenceDataTable = TotalFenceLocate.GetGeofenceCountByDeviceUIDTime(DeviceUID, FromDate, EndingDate);
foreach (JeanPierre.GeofenceDataRow FenceRows in GeofenceDataTable)
{
MonthlyFenceCount = Convert.ToInt32(FenceRows....);
}
//MonthlyFenceCount = GeofenceDataTable.Rows.Count;
return (MonthlyFenceCount);
}protected Int32 TotalGeofenceLocate()
{
int MonthlyFenceCount = 0;
Int32 DeviceUID = 227;
DateTime FromDate = new DateTime(StartingDate.Year, StartingDate.Month, 23);
DateTime EndingDate = new DateTime(StartingDate.Year, StartingDate.Month, 22);
GeofenceDataTableAdapter TotalFenceLocate = new GeofenceDataTableAdapter();
JeanPierre.GeofenceDataDataTable GeofenceDataTable = TotalFenceLocate.GetGeofenceCountByDeviceUIDTime(DeviceUID, FromDate, EndingDate);
MonthlyFenceCount = Convert.ToInt32(GeofenceDataTable.Rows(0)(0));
return (MonthlyFenceCount);
}

[SheSaidImaPregy]

he's right pimp. Your query only returns one record, a record count of your specifications. If you want a record count from each, you need to pull something along with it. Pull a dummy field so the query isn't just a record count. Try this as your query:

SELECT DISTINCT   DeviceUID, COUNT(DeviceUID) AS 'TotalGeofence'
FROM         GeofenceData
WHERE (DeviceUID = @DeviceUID AND
Time BETWEEN @StartTime AND @EndTime)

This should work for you just fine. I switched out "*" for DeviceUID only because I see it pointless to count everything rather than only one column. But do what you wish :)

Enjoy.

[justapimp]

i used the Count(*) because our application is capable of pulling Total Geofence entries for multiple devices hence the reason why I used the foreach()...

[SheSaidImaPregy]

the for each is fine, don't worry about that. The reason you only have one record is that you are only pulling a count. It is counting all records between start and end time and where your deviceID is equal to the parameter. There is nothing there indicating that it should pull each total for each device id.

[SheSaidImaPregy]

wow did I miss read that. You don't want a record count for each device uid you pull.. ha.

But as I look at it too, you don't need the foreach. You are only going to be calling that function once at a time right? the for each would just be pointless.

By yes, the GeofenceDataTable.Rows(0)(0) should return the right amount.

Why did you convert over to C#? I believe I remember you only using VB.NET.

[justapimp]

I see what you're saying now. I am going to implement the suggested solution to see what I will get with this project addition.

78% of this project is build in C#