CODEWITHSUNDEEP
c#sqldatagridview
user3560189
user3560189

Reputation: 73

The schema returned by the new query differs from the base query (C#/SQL - VS 2012)

For a homework task I have to develop a C# application to interface with a SQL Server database file (.mdf), providing a dataGridView to browse the contents, and several buttons to execute queries.

I have been going well, found how to add queries to my table adapter, how to call them etc.

Now I am having problems making a query that returns the maximum pay in hourlyPayRate.

I have a database employee that contains the following attributes: employeeID, name, position, hourlyPayRate.

My query is 

SELECT MAX(hourlyPayRate) 
FROM employee

I right click employeeTableAdapter, click "Add Query...", name it Max and put in the query. When I click okay I get the following error message:

The schema returned by the new query differs from the base query.

The query executes correctly in the query builder, it is only when I click "OK" to save it that I receive the error.

Looking around SE there are no definitive answers to this question.

Thanks, Michael.

Upvotes: 3

Views: 13737

Answers (3)

Robert Peter Bronstein
Robert Peter Bronstein

Reputation: 166

Everyone is wrong here, you don't edit the sql in the table adapter but edit the sql in the dataset.

Upvotes: 0

Ken
Ken

Reputation: 2838

The actual reason for this is that the base QUERY returns more or less columns than the QUERY you are adding . Visual Studio cares about this ; and should not after all it is a new query.

NOTE: We are talking query columns not Database Table Columns. The Error regards Base Query - for example a fill, and perhaps you want to have a fill by and hide the field of a foreignID - because your base query outputs that column, and your added query does not - the new query differs from the base in that the columns are not the same. (I think this is done to ensure bound objects are properly bound; but I really do not know (think datagridview.)

So for example 

Fill() Query
SELECT        Id, Name, FKtblThing_ID
FROM            ITEMS

Adding this query works..

FillByID() Query
SELECT        Id, Name, FKtblThing_ID
FROM            ITEMS
WHERE        (FKtblThing_ID = @FKtbl_ThingID)

If instead you tried this - it would not work.

FillByID() Query
SELECT        Id, Name
FROM            ITEMS
WHERE        (FKtblThing_ID = @FKtbl_ThingID)

This is the error you would receive:

The schema returned by the new query differs from the base query.

Upvotes: 0

user3560189
user3560189

Reputation: 73

The solution has been found, for all those wondering.

The problem is that the query returns a table which has a different number of columns that the database.

Usually in most DBMS's this is not an issue, but for some reason Visual Studio was having none of that.

The solution was this query:

SELECT employeeID, name, position, hourlyPayRate FROM employee WHERE (hourlyPayRate = (SELECT MAX(hourlyPayRate) AS MaxRate FROM employee AS TempTable))

And then trimming the unneeded from the result as you like. For me this was as simple as having a label that derived it's data only from the hourlyPayRate attribute.

Upvotes: 4

Related Questions