Databinding: 'system.data.datarowview' does not contain a property with the name 'sports'.

Question

5.00/5 (1 vote)

See more:

I have coded a chatroom using asp.net, C# & MS SQL. I have two Dropdowns for selecting Sports and then another for Sorting the chatrooms based on Users online/total users.

When I select "All sports" (default value) and "Users online", the query works fine.
But when I select "All sports" (default value) and "total users", the query gives some error.

"SELECT roo.[Sports] As Sport, roo.[Name] AS ChatRoomName, COUNT(DISTINCT liu.[LoggedInUserID]) AS OnlineUsers, COUNT(DISTINCT chu.ChatUserLogId) AS TotalUsers FROM Room AS roo LEFT JOIN LoggedInUser AS liu ON roo.RoomID = liu.RoomID LEFT JOIN ChatUserLog AS chu ON roo.RoomID = chu.RoomID AND chu.LoggedInTime >= DATEADD(DAY,-30,GETDATE()) GROUP BY roo.[Sports], roo.[Name]";

The results should be like this

                   Chatroom Name      Online users              Total Users
Football 
              Best football player ever?    0                       5671
              Messi or Ronaldo?             5                       346
              Who'll win EURO this year?    0                       134
Swimming 
              Best ever Swimmer?            0                       3343
              Phelps or Spitz?              0                       1056
              Lochte or Phelps for gold?    0                       45
              
Tennis
              Serena or Steffi graf?        0                       432
              Federer or Djokovic?          0                       298
              Best server ever?             0                       43

My results are not getting sorted properly as well as the sports having online users are shown twice too.

SQL

CREATE TABLE [dbo].[ChatUserLog] (
    [ChatUserLogId] BIGINT   IDENTITY (1, 1) NOT NULL,
    [MId]           INT      NOT NULL,
    [RoomId]        INT      NOT NULL,
    [LoggedInTime]  DATETIME DEFAULT (getdate()) NOT NULL,
    [LogOutTime]    DATETIME DEFAULT (getdate()) NULL,
    PRIMARY KEY CLUSTERED ([ChatUserLogId] ASC)
);
 
CREATE TABLE [dbo].[LoggedInUser] (
    [LoggedInUserID] INT           IDENTITY (1, 1) NOT NULL,
    [MId]            INT           NOT NULL,
    [RoomID]         INT           NOT NULL,
    [NickName]       VARCHAR (50)  NOT NULL,
    [Icon]           VARCHAR (MAX) NOT NULL,
    CONSTRAINT [PK_LoggedInUser] PRIMARY KEY CLUSTERED ([LoggedInUserID] ASC)
);

SQL

CREATE TABLE [dbo].[Message] (
    [MessageID] INT           IDENTITY (1, 1) NOT NULL,
    [RoomID]    INT           NOT NULL,
    [MId]       INT           NOT NULL,
    [ToUserID]  INT           NULL,
    [Text]      VARCHAR (MAX) NOT NULL,
    [TimeStamp] DATETIME      DEFAULT (getdate()) NOT NULL,
    [Color]     VARCHAR (50)  NULL,
    [NickName]  VARCHAR (50)  NOT NULL,
    [Icon]      VARCHAR (MAX) NOT NULL,
    PRIMARY KEY CLUSTERED ([MessageID] ASC)
);
 
CREATE TABLE [dbo].[Room] (
    [RoomID]        INT           IDENTITY (6666, 1) NOT NULL,
    [Name]          VARCHAR (100) NOT NULL,
    [Sports]        VARCHAR (50)  NOT NULL,
    [CreatedDate]   DATETIME      DEFAULT (getdate()) NOT NULL,
    [CreatedBy]     VARCHAR (50)  NOT NULL,
    [CreatedUserID] INT           NOT NULL,
    PRIMARY KEY CLUSTERED ([RoomID] ASC)
);

Thanks in advance and have a nice day ahead.

What I have tried:

I am trying

"SELECT roo.[Sports] As Sport, roo.[Name] AS ChatRoomName, COUNT(DISTINCT liu.[LoggedInUserID]) AS OnlineUsers, COUNT(DISTINCT chu.ChatUserLogId) AS TotalUsers FROM Room AS roo LEFT JOIN LoggedInUser AS liu ON roo.RoomID = liu.RoomID LEFT JOIN ChatUserLog AS chu ON roo.RoomID = chu.RoomID AND chu.LoggedInTime >= DATEADD(DAY,-30,GETDATE()) GROUP BY roo.[Sports], roo.[Name]";

Posted 12-Oct-16 20:36pm

Bigprey

Updated 12-Oct-16 20:57pm

Add a Solution

Comments

Suvendu Shekhar Giri 13-Oct-16 2:42am

This is probably the 4th or 5th time I saw the similar question from you?
Just want to know, if this is the same question?
If not, how it is different from others?
Why not updating the same question?

Bigprey 13-Oct-16 2:45am

It's different... If you could read or compare it with the 1st one, you would have known it already.

I am trying to post this different question 2-3 times already and got deleted... That's why I have put the error itself as the title over here... Please understand and let it be.

Suvendu Shekhar Giri 13-Oct-16 2:53am

ok. :)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Suvendu Shekhar Giri · Answer 1 · 2016-10-12T20:57:00

Solution 1

From the error, it looks like you are trying to access a value in the application from datarowview where the result of the shared query is stored but with wrong key.

SQL

SELECT roo.[Sports] As Sport,...
...

]
The column is Sport but looks like you are trying to access with Sports and that's probably causing the error.
Look at the relevant code and try removing the last "s" from "Sports".

Hope, it helps :)
Please let me know if I am missing something obvious here.

Posted 12-Oct-16 20:57pm

Suvendu Shekhar Giri

Comments

Bigprey 13-Oct-16 3:07am

But ain't the column "Sports" is created in the Room table?

CREATE TABLE [dbo].[Room] (
[RoomID] INT IDENTITY (6666, 1) NOT NULL,
[Name] VARCHAR (100) NOT NULL,
[Sports] VARCHAR (50) NOT NULL,
[CreatedDate] DATETIME DEFAULT (getdate()) NOT NULL,
[CreatedBy] VARCHAR (50) NOT NULL,
[CreatedUserID] INT NOT NULL,
PRIMARY KEY CLUSTERED ([RoomID] ASC)
);

Thanks in advance and have a nice day ahead.

Suvendu Shekhar Giri 13-Oct-16 3:27am

True. But you are aliasing the column Sports to Sport

SELECT roo.[Sports] As Sport..

so you will be able to access it via Sport

Bigprey 13-Oct-16 3:39am

So, I need to change Sports to Sport in Select (Aliasing) part or in Group BY part at the last? Thanks

Suvendu Shekhar Giri 13-Oct-16 3:46am

Just remove the alias in the select query and check

SELECT roo.[Sports], roo.[Name] AS ChatRoomName....

Bigprey 13-Oct-16 5:48am

And have to remove "ChatRoomName" alias as well for roo.[Name], right? It is being used at the last.

One more doubt. Don't I have to use ISNULL for Count, since it has more chance to be coming 0 too?

Thanks

Suvendu Shekhar Giri 13-Oct-16 5:58am

NULL values are ignored by AGGREGATE FUNCTIONS

Bigprey 17-Oct-16 5:16am

For the corrected query

SELECT DISTINCT roo.[Sports], roo.[Name], COUNT(DISTINCT chu.ChatUserLogId) AS TotalUsers, COUNT(DISTINCT liu.[LoggedInUserID]) AS UserOnline

FROM Room AS roo

LEFT JOIN LoggedInUser AS liu ON roo.RoomID = liu.RoomID
LEFT JOIN ChatUserLog AS chu ON roo.RoomID = chu.RoomID AND chu.LoggedInTime >= DATEADD(DAY,-30,GETDATE())

GROUP BY roo.[Sports]

ORDER BY TotalUsers DESC";

I am getting say, Basketball repeated n number of times for n number of Roomnames and Football repeated m number of times for m number of Roomnames in Football category.

What are the mistakes, I have done here?