unable to join tables

Question

User-305388250 posted

Hey,

I was wondering if you could help me. You have given some excellent reponses to peoples problems so i think you may be able to help..

Basically i am trying to join 2 tables and output some information only if tbl_customer_bookings.county = tbl_companies.county_name.

However i am having problems doing this. The output should give me 3 returns "andrew", "john", and "vgv". It DOES do this, BUT repeats it four times for each person. Below is the result i get:-

Customer Name	Mobile No	County
John	ouhn	Cheshire
vgv	hv	Cheshire
andrew	123455679	Cheshire
John	ouhn	Cheshire
vgv	hv	Cheshire
andrew	123455679	Cheshire
John	ouhn	Cheshire
vgv	hv	Cheshire
andrew	123455679	Cheshire
John	ouhn	Cheshire
vgv	hv	Cheshire
andrew	123455679	Cheshire

I have checked for duplicate data in the database but i only have 3 fields so why is this repeating so many times..?

The code i have used for my gridview is shown below:-

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px"

CellPadding="4" DataSourceID="AccessDataSource1" Width="505px">

<FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />

<RowStyle BackColor="White" ForeColor="#330099" />

<Columns>

<asp:BoundField DataField="county_name" HeaderText="county_name"

SortExpression="county_name" Visible="False" />

<asp:BoundField DataField="cust_id" HeaderText="cust_id" InsertVisible="False"

SortExpression="cust_id" Visible="False" />

<asp:BoundField DataField="cust_name" HeaderText="Customer Name"

SortExpression="cust_name" />

<asp:BoundField DataField="mobile_no" HeaderText="Mobile No"

SortExpression="mobile_no" />

<asp:BoundField DataField="county" HeaderText="County"

SortExpression="county" />

</Columns>

<PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />

<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />

<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />

</asp:GridView>

<asp:AccessDataSource ID="AccessDataSource1" runat="server"

DataFile="~/App_Data/test.mdb"

SelectCommand="SELECT tbl_companies.county_name, tbl_customer_bookings.cust_id, tbl_customer_bookings.cust_name, tbl_customer_bookings.mobile_no, tbl_customer_bookings.county FROM (tbl_customer_bookings INNER JOIN tbl_companies ON tbl_companies.county_name = tbl_customer_bookings.county)">

</asp:AccessDataSource>

Please Help

Thanks

Answer 1

User77042963 posted

Try to modify your SELECT statement to this:

SelectCommand="SELECT DISTINCT  tbl_companies.county_name, tbl_customer_bookings.cust_id, tbl_customer_bookings.cust_name, tbl_customer_bookings.mobile_no, tbl_customer_bookings.county FROM  tbl_customer_bookings INNER JOIN tbl_companies ON tbl_companies.county_name = tbl_customer_bookings.county"

Answer 2

User-305388250 posted

Thank you so much, that works now.

Would it be ok to ask you for help if i get stuck again?

Thanks again.

Regards