2

I have an Event Table with ContactID as foreign key. When I delete a contact from Contact Table, I set ContactID in Event Table to NULL.

In Event GridView I am displaying Event Information along with ContactName and Number from Contact Table.

GridView:

<asp:GridView ID="GridView4" runat="server" ShowHeaderWhenEmpty="true" CssClass="table table-striped" style="font-family:sans-serif; font-size:medium" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" />
<Columns>
    <asp:BoundField DataField="StartDate" HeaderText="Date" />
    <asp:HyperLinkField DataTextField="Title" DataNavigateUrlFields="ID" DataNavigateUrlFormatString="~/Views/Portal/ViewEvent.aspx?evtID={0}" HeaderText="Event" ItemStyle-Width="150px" ></asp:HyperLinkField>
    <asp:BoundField DataField="Name" HeaderText="Contact Name" />
    <asp:BoundField DataField="Cell" HeaderText="Contact #" />
    <asp:BoundField DataField="EventType" HeaderText="Type" />
    <asp:BoundField DataField="Confirmed" HeaderText="Status" />
    <asp:BoundField DataField="Comments" HeaderText="Comments" />
</Columns>

Query:

SELECT Event.ID, Event.StartDate, Event.Title,Contact.Name,Contact.Cell,Event.EventType, Event.Confirmed,Event.Comments 
FROM Event 
LEFT JOIN Contact ON Event.ContactID=Contact.ID

aspx.cs:

string myConnection = dbController.connectionString;
SqlConnection conn = new SqlConnection(myConnection);
string query = query = "SELECT Event.ID, Event.StartDate, Event.Title, Contact.Name, Contact.Cell, Event.EventType, Event.Confirmed, Event.Comments FROM Event LEFT JOIN Contact ON Event.ContactID=Contact.ID";

GridView4.DataSource = null;
GridView4.DataBind();

DataTable dt = new DataTable();
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(query, conn);
da.Fill(dt);
conn.Close();

if (dt.Rows.Count > 0)
{
     GridView4.DataSource = dt;
     GridView4.DataBind();
}

The rows show up just fine in Sql Server Management Studio. But on web interface the rows where ContactID is NULL don't show.

Kindly guide me in the right direction.

Stephan Bauer
  • 9,120
  • 5
  • 36
  • 58
el323
  • 2,760
  • 10
  • 45
  • 80
  • can you verify that the query you are running manually in SSMS is identical to the one being run by the application? e.g. using SQL Profiler or a similar tool? – ADyson Dec 02 '16 at 10:57
  • Did u checked in the C# Code Behind Part, in databound method is there any logic existed....... – User Dec 02 '16 at 10:59
  • I have updated the question. Added the code behind as well. Its the same query as of Sql Server. – el323 Dec 02 '16 at 11:03
  • ok. I realise this is unlikely, but it's best to rule it out since this is a bit weird: are they both definitely pointing at the same database? (e.g. it could be that the app is pointing at Dev instance and SSMS is pointing at the Test instance by mistake)? – ADyson Dec 02 '16 at 11:05
  • No. Both are pointing to the same database. I have tested that. – el323 Dec 02 '16 at 11:06
  • 2
    Next thing: set a breakpoint on the line _after_ the `da.Fill(dt);` command. Then inspect `dt` to see if it has all the expected rows in it. That way hopefully we can determine if the rows are being dropped by the query, or by the GridView. – ADyson Dec 02 '16 at 11:09
  • Thanks. Debugging helped. – el323 Dec 02 '16 at 11:14
  • ok good. what was the result? – ADyson Dec 02 '16 at 11:14
  • I was missing a LEFT join in a query. – el323 Dec 02 '16 at 11:15

0 Answers0