Relax or turn off constraints in your dataset

[tim8w]

I have created a DataSet in VB.Net. The Table it is connected to has a PrimaryKey. All works fine as long as my SQL Query is a Standard one like SELECT * from....


I want to use a GROUP BY but when I do I get "ConstraintException was unhandled" with the suggestion that I relax or turn off constraints... I can't find a way to do this. I cannot find EnforceConstraints anywhere in the interface to turn it to false...


Here is my GROUP BY which doesn't include the Primary Key value in it anywhere...

SELECT CustomerID, SUM(PPA) AS 'CustomerPay', SUM(ABS) AS 'TechPay', SUM(TaxDuty) AS 'Tax', CustDept 
FROM tabShippingItems
WHERE (InvoiceNumber = @InvoiceNumber)
GROUP BY CustomerID, CustDept
ORDER BY CustomerID

My Primary Key is called ShippingID. Any help would be appreciated...

 

[jmcilhinney]

There is absolutely no problem running any query you want. You really haven't provided all the relevant information there but I'll tell you what I think you've done, what's happening and what you need to do to fix it and if it turns out that I've wasted my time because I've guessed wrong then you can provide a full and clear explanation so that we don't have to guess.

I think that you have generated a typed DataSet by running the Data Source wizard. That will have generated a DataTable in the DataSet for each table in your database. You are now trying to add a query to one of your TableAdapters or change an existing query but the result set of that query doesn't match the schema of your DataTable. As such, the data is violating one or more constraints relating to columns that do not allow nulls.

In that case, what you need to do is to add a completely new TableAdapter to your DataSet in the designer. You can then specify the query you want and it will generate a new DataTable with the appropriate schema. Given that your ShippingItems table has a ShippingID column that is the PK and your query doesn't retrieve that column, you obviously can't populate the DataTable for that table. Given that your query is grouping by CustomerID and CustDept, you might make that combination of columns the PK for the new DataTable or you might not add a PK at all. You might add a relation to the Customer table, which is presumably where CustomerID comes from, and maybe one for CustDept if appropriate.

 

[tim8w]

jmcilhinney,
I don't mind returning the PK. I'll just ignore it. If I include the PK, it messes up the GROUP BY and I get all records returned. Is there a way around this? Here's the updated query:

SELECT        ShippingID, CustomerID, SUM(PPA) AS 'CustomerPay', SUM(ABS) AS 'TechPay', SUM(TaxDuty) AS 'Tax', CustDept
FROM            tabShippingItems
WHERE        (InvoiceNumber = @InvoiceNumber)
GROUP BY CustomerID, CustDept, ShippingID
ORDER BY CustomerID

 

[tim8w]

Sorry,
Figured it out. I removed all the queries from the TableAdapter and started fresh with my Query. Worked correctly...