Resolved RowFilter of DataView with multiple column values

[Govind Sankar]

Hi I created a sql server database and is using a dataset in visual studio to connect to the sql server database and I am using wpf application with a datagrid. Now in one section i need to use dataview.rowfilter.
But i need to use multiple columns to filter row in dataview. For eg I use the dataview like this
DataView View = SchoolDataset.Marks.DefaultView;
View.RowFilter = string.Format("ID = '{0}' ", ID};
this works correctly. But this will show multiple rows with the same ID.
or
DataView View = SchoolDataset.Marks.DefaultView;
View.RowFilter = string.Format("ExtracurricularActivity = '{0}' ", EA};
and this will also work, but will again show multiple rows with the same extracurricularactivity EN.

I need it to show it as one row. That if ID is 1 and extracurricularactivity is drama. Then I need the datagrid to show the row with both ID as 1 and EN as drama. There will only be one row. So how can I do that. Thank you.

 

[Skydiver]

Why are you doing the filtering on the client side instead of the server side? The database on the server side is custom built to quickly handle all kinds of filtering operations, why do the work on your local machine?

 

[Govind Sankar]

Why are you doing the filtering on the client side instead of the server side? The database on the server side is custom built to quickly handle all kinds of filtering operations, why do the work on your local machine?

This is a software in visual studio for a client to use. The client doesnt know how to work on the server side. So the client needs to click on button and it should show the row with the values

 

[Skydiver]

Yes, but you can do a query to fill a DataSet where the query already has the filters applied and get back just the data to be displayed. Currently you are doing a query to get all the data, and then you are applying a filter on the data to limit what is displayed.

As an aside, are you actually distributing Visual Studio to your client for them to compile and run your code? Or are you using Visual Studio to compile your code, and just giving the binaries to your client? Your post in #3 seems to say the former: the client has to compile and run your code in Visual Studio. That's a terrible deployment/distribution model.

 

[Govind Sankar]

Yes, but you can do a query to fill a DataSet where the query already has the filters applied and get back just the data to be displayed. Currently you are doing a query to get all the data, and then you are applying a filter on the data to limit what is displayed.

As an aside, are you actually distributing Visual Studio to your client for them to compile and run your code? Or are you using Visual Studio to compile your code, and just giving the binaries to your client? Your post in #3 seems to say the former: the client has to compile and run your code in Visual Studio. That's a terrible deployment/distribution model.

I created a query by going to the table adapter and creating a new query
SELECT Mark1, Mark2, TotalMarks FROM dbo.Marks Where(ID = @ID, TypeOfExtraCurricular = @TypeOfExtraCurricular).

Then I go to the program and try to use this new query to fill data using the code
MarksTA.FillBy(SchoolDataset.Marks, ID, TypeOfExtraCurricular);

Then when I run the program and clicked the button an error pops up which I dont understand. The error says this.

'Restrictions could not be activated. At least one line contains values that violate the restrictions non-null, unique or foreign-key. '

They are both values that can be null. I havent done anything about unique or foreign key and I dont know what they are. What is the problem. Kindly help me. Thank you.

 

[Govind Sankar]

Yes, but you can do a query to fill a DataSet where the query already has the filters applied and get back just the data to be displayed. Currently you are doing a query to get all the data, and then you are applying a filter on the data to limit what is displayed.

As an aside, are you actually distributing Visual Studio to your client for them to compile and run your code? Or are you using Visual Studio to compile your code, and just giving the binaries to your client? Your post in #3 seems to say the former: the client has to compile and run your code in Visual Studio. That's a terrible deployment/distribution model.

I just did the same process again but then I didnt remove ID and TypeOfExtraCurricular from the Select Query. That is my selectquery is
SELECT ID, TypeOfExtraCurricular Mark1, Mark2, TotalMarks FROM dbo.Marks Where(ID = @ID, TypeOfExtraCurricular = @TypeOfExtraCurricular).

And then I run the program it works. So what is the problem here, is it possible to just fill the datagrid with the other 3 column values based on ID and TypeOfExtraCurricular. Is that not possible.

Thank you.

 

[jmcilhinney]

That second query is invalid because it's missing a comma. Also, that's a SQL syntax that I haven't seen before. The error message you're getting seems to suggest that it's working but I've always seen a WHERE clause written like this:

SELECT ID, TypeOfExtraCurricular, Mark1, Mark2, TotalMarks FROM dbo.Marks WHERE ID = @ID AND TypeOfExtraCurricular = @TypeOfExtraCurricular

 

[jmcilhinney]

The problem is that, if you have an existing DataTable and table adapter then any queries you add to that table adapter MUST produce a result set with the same schema as the DataTable. When the wizard creates the DataTables and table adapters for you, all default queries are effectively a SELECT *, i.e. the result set includes all the columns from the database table. That means that any queries you add must also include ALL columns from the database table. Remember that you are populating the same DataTable as the default query and that contains all those DataColumns. That's why the error message is talking about violating non-null constraints. If you don't pull data from the ID column then that column in your DataTable will contain nulls and that is not allowed.

Basically, your additional queries MUST include all columns or else you need to create a new DataTable and table adapter with the new schema.