Question QueriesTableAdapter

[Chris De Smet]

I have a connection created to a SQL database and I want to run a query from a VS Project.

In the tabelAdapter Query configuration Adapter SELECT which returns a single value is enabled but this gives not the full result of the query. I am sure I need SELECT which returns rows but this option is not enabled. (see attached picture)

The connection to the database works and it I runt the query directly in VS it gives the correct result.

Anybody knows how to enable "SELECT which returns rows"

 

[jmcilhinney]

You would need to add a table to your DataSet. The corresponding table adapter will then execute a query and populate the DataTable with the result set.

 

[Chris De Smet]

Thansk a lot for the quick reply.

Great ,that worked ... kind a.
In the preview of the tabel adapter I can see the data.

When I connect it to the datagridview I can see the columns

but when i run the project I get an error.

Translated the error says:
System.Data.ConstraintException: ‘Constraints could not be enabled. One or more rows contain values that violate constraints related to non-null values, unique values or different keys.'

Any ideas?

 

[jmcilhinney]

You're being told what the issue is. Either you are pulling back NULL data for a column that doesn't allow it, or you are pulling back duplicate data for a column that doesn't allow it, or you are pulling back foreign key data for which there is no primary key. Which is it? It's for you to investigate and find out.

My money is on the third option. If you are populating a DataTable in a DataSet that has DataRelations, you would need to populate the parent table first in order to be able to populate the child table, otherwise you have foreign keys with no primary keys. If you only actually need the data for this one table then don't use a DataSet. Instead of calling Fill, call GetData and it will return a populated DataTable that is standalone, i.e. not part of a DataSet. In that case, there's no Datarelations so there's no need to get primary key data. If you do need multiple tables, you need to get the parent data before getting the child data.