Count Data from Database using stored Procedure w/ inner join
<p>I'm having a problem counting data w/ inner join.</p>

<p>I want to count how many cottages are available, here are my tables:</p>

<p><img src="http://i.stack.imgur.com/OUo4e.jpg" alt="enter image description here"></p>

<p><img src="http://i.stack.imgur.com/vBV4x.jpg" alt="enter image description here"></p>

<p>here's my code in class getting the cottage number.</p>

<pre><code>public void CheckCottages()
 {
     con.Close();
     SqlCommand comUmbrella = new SqlCommand("CountCottages", con);
     comUmbrella.CommandType = CommandType.StoredProcedure;
     comUmbrella.Parameters.Add("@CottageType", SqlDbType.NVarChar).Value = "Umbrella";
     comUmbrella.Parameters.Add("@ReservedDate", SqlDbType.DateTime).Value = this.ARRIVAL;
     con.Open();
     comUmbrella.ExecuteNonQuery();
     drUmbrella = comUmbrella.ExecuteReader();
     if (drUmbrella.Read())
     {
         this.UMBRELLA = drUmbrella.GetInt32(drUmbrella.GetOrdinal("Rows"));
     }
     con.Close();
     SqlCommand comNativeKubo = new SqlCommand("CountCottages", con);
     comNativeKubo.CommandType = CommandType.StoredProcedure;
     comNativeKubo.Parameters.Add("@CottageType", SqlDbType.NVarChar).Value = "Native Kubo";
     comNativeKubo.Parameters.Add("@ReservedDate", SqlDbType.DateTime).Value = this.ARRIVAL;
     con.Open();
     comNativeKubo.ExecuteNonQuery();
     drKubo = comNativeKubo.ExecuteReader();
     if (drKubo.Read())
     {
         this.NATIVEKUBO = drKubo.GetInt32(drKubo.GetOrdinal("Rows"));
     }
     con.Close();
     SqlCommand comTreeHouse = new SqlCommand("CountCottages", con);
     comTreeHouse.CommandType = CommandType.StoredProcedure;
     comTreeHouse.Parameters.Add("@CottageType", SqlDbType.NVarChar).Value = "Tree house";
     comTreeHouse.Parameters.Add("@ReservedDate", SqlDbType.DateTime).Value = this.ARRIVAL;
     con.Open();
     comTreeHouse.ExecuteNonQuery();
     drTree = comTreeHouse.ExecuteReader();
     if (drTree.Read())
     {
         this.TREEHOUSE = drTree.GetInt32(drTree.GetOrdinal("Rows"));
     }
     con.Close();
     SqlCommand comPavillion = new SqlCommand("CountCottages", con);
     comPavillion.CommandType = CommandType.StoredProcedure;
     comPavillion.Parameters.Add("@CottageType", SqlDbType.NVarChar).Value = "Pavillion";
     comPavillion.Parameters.Add("@ReservedDate", SqlDbType.DateTime).Value = this.ARRIVAL;
     con.Open();
     comPavillion.ExecuteNonQuery();
     drPavillion = comPavillion.ExecuteReader();
     if (drPavillion.Read())
     {
         this.PAVILLION = drPavillion.GetInt32(drPavillion.GetOrdinal("Rows"));
     }
 }
</code></pre>

<p>Here's my stored Procedure:</p>

<pre><code>ALTER PROCEDURE dbo.CountCottages
(
    @CottageType nvarchar(50),
    @ReservedDate datetime
)

AS
SELECT count(dbo.Cottages.CottageName)
FROM dbo.Cottages INNER JOIN
dbo.ResortTransactions ON dbo.Cottages.CottageID = dbo.ResortTransactions.CottageID
where dbo.Cottages.CottageType=@CottageType and dbo.ResortTransactions.Status != 'Cancelled' and dbo.ResortTransactions.ReservedDate != @ReservedDate

RETURN
</code></pre>

<p>What's wrong to my code? I hope someone can help me :)</p>

<p>Thanks in advance! </p>
