Gridviews together with Database with join

[Søren Reinke]

Hi there

I am having a little problem using a DataGridView together with a database
(access) where the query is containing a join.

The database has to tables:
1. Contacts, containing name, addresse, postalcode, age and more
2. CityNames, containing the PostalCode and the City name.

the SQL i use is:

SELECT Person.ID, Person.Navn, Person.Adresse, Person.PostNummer,
Person.CertificationsNiveau, Person.AntalDyk, Person.Ansat, PostNummer.[By]
FROM (Person INNER JOIN
PostNummer ON Person.PostNummer =
PostNummer.PostNummer)


I have used a dataset for the table adapters, and used the querybuilder to
create the queries.

Now on my aspx page i have added a DataGridView and an ObjectDataSource (the
aspx can be seen at the bottom)

It works fine when just showing the data, but because of the join the query
builder can not create an update, so when trying to edit a row i get an
error.

I have tried to add another 'Update' query, but it still complains.

How do you people normally solve this problem ?

Best regards
Søren Reinke


<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False" DataSourceID="ObjectDataSource1"
BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px"
CellPadding="3" GridLines="Horizontal" AutoGenerateSelectButton="True"
AutoGenerateEditButton="True" DataKeyNames="ID">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID"
InsertVisible="False" ReadOnly="True" SortExpression="ID" />
<asp:BoundField DataField="Navn" HeaderText="Navn"
SortExpression="Navn" />
<asp:BoundField DataField="Adresse" HeaderText="Adresse"
SortExpression="Adresse" />
<asp:BoundField DataField="PostNummer"
HeaderText="PostNummer" SortExpression="PostNummer" />
<asp:BoundField DataField="CertificationsNiveau"
HeaderText="CertificationsNiveau" SortExpression="CertificationsNiveau" />
<asp:BoundField DataField="AntalDyk" HeaderText="AntalDyk"
SortExpression="AntalDyk" />
<asp:CheckBoxField DataField="Ansat" HeaderText="Ansat"
SortExpression="Ansat" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
DeleteMethod="Delete" InsertMethod="Insert"
OldValuesParameterFormatString="original_{0}" SelectMethod="GetData"
TypeName="Aquasport1TableAdapters.PersonTableAdapter" UpdateMethod="Update">
<DeleteParameters>
<asparameter Name="Original_ID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asparameter Name="Navn" Type="String" />
<asparameter Name="Adresse" Type="String" />
<asparameter Name="PostNummer" Type="Int32" />
<asparameter Name="CertificationsNiveau" Type="String" />
<asparameter Name="AntalDyk" Type="Int32" />
<asparameter Name="Ansat" Type="Boolean" />
<asparameter Name="Original_ID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asparameter Name="Navn" Type="String" />
<asparameter Name="Adresse" Type="String" />
<asparameter Name="PostNummer" Type="Int32" />
<asparameter Name="CertificationsNiveau" Type="String" />
<asparameter Name="AntalDyk" Type="Int32" />
<asparameter Name="Ansat" Type="Boolean" />
</InsertParameters>
</asp:ObjectDataSource>