Hi there,
I'm using the very convenient graphical "TableAdapterDesigner" of VisualStudio 2005.
I create my SQL-Statement for query No.1: (Simplified)
SELECT name, age, city, street, zipCode
FROM person
WHERE name = @name
and for query No.2: (Simplified)
SELECT zipCode
FROM person
WHERE name = @name;
I use MS SQL as db-Server. All fields are requiered (NOT-NULL). When I preview the data in VisualStudio, both queries return the correct result - no errors - but even thought the second query just asks for zipCode, all other fields selected in the first query are shown as empty fields as well.
Implementing the queries accoring to "DataAccesTutorial" shown on MSDN, only the first query works without errors. Executing the project and calling the second query, I get an exception, telling me:
"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."
So my question is:
Is it posible to have one TableAdapter with SQL-queries that select different fields or is there just one full set (SELECT - UPDATE - INSERT) per adapter
I hope my description is detailed enough.
Thanks,
Finch.
More than one query per tableAdapter
Charley Bogwill
If none of your DataColumns allow null values, then any SQL you use to populate the DataTable is going to have to provide values for all the columns; your second statement only provides the value for one column. You either need to remove the constraints, or add empty strings/values to the SQL:
SELECT '' as name, 0 as age, '' as city, '' as street, zipCode
FROM person
WHERE name = @name;
It's possible that you're trying to do something the wrong way, though. If all you want is a list of zipCodes, consider reading them into a List<string> using a SqlCommand and SqlDataReader.