SQL Query

[Syafiqah Zahirah]

Hye, I having a problem with this sql query.. string sql = "SELECT * FROM Environmnets INNER JOIN TDevice ON Environmnets.Deviceid = TDevice.id INNER JOIN TMushroomHouse ON TDevice.MushroomHouseId = TMushroomHouse.id WHERE (Environmnets.DateT =(SELECT MAX(DateT) AS Expr1 FROM Environmnets AS Environmnets_1)) AND TMushroomHouse.Name ='"+ DropDownList1.Text + "'"; SqlConnection con = new SqlConnection(strConnString); SqlCommand cmd = new SqlCommand(sql, con); // SqlDataReader dr = cmd.ExecuteReader(); try { con.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { HouseTextBox.Text = dr["House"].ToString(); DateTTextBox.Text = dr["DateT"].ToString(); TempInTextBox.Text = dr["TempIn"].ToString(); TempOutTextBox.Text = dr["tempOut"].ToString(); HumidityTextBox.Text = dr["Humidity"].ToString(); } } There is no error occur when I run this code, but when I select home from dropdownlist, the data from database didn't display in the textbox. Anybody can help me?

[Richard Deeming]

The first problem is that your code is susceptible to SQL Injection[^]. You should be using a parameterized query instead. While we're fixing that, the SqlConnection, SqlCommand and SqlDataReader objects all implement IDisposable, so they should be wrapped in using blocks to make sure their resources are freed. We'll also get rid of the SELECT *, since you only need five columns.

string sql = "SELECT House, DateT, TempIn, tempOut, Humidity FROM Environmnets INNER JOIN TDevice ON Environmnets.Deviceid = TDevice.id INNER JOIN TMushroomHouse ON TDevice.MushroomHouseId = TMushroomHouse.id WHERE (Environmnets.DateT = (SELECT MAX(DateT) AS Expr1 FROM Environmnets AS Environmnets_1)) AND TMushroomHouse.Name = @MushroomHouseName";

using (SqlConnection con = new SqlConnection(strConnString))
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@MushroomHouseName", DropDownList1.Text);

con.Open();

using (SqlDataReader dr = cmd.ExecuteReader())
{
    while (dr.Read())
    {
        HouseTextBox.Text = dr\["House"\].ToString();
        DateTTextBox.Text = dr\["DateT"\].ToString();
        TempInTextBox.Text = dr\["TempIn"\].ToString();
        TempOutTextBox.Text = dr\["tempOut"\].ToString();
        HumidityTextBox.Text = dr\["Humidity"\].ToString();
    }
}

}

Now, if the code is definitely executing, and there's no error, but you're not seeing anything in the textboxes, then that means there were no records returned by your query. If I had to guess, I'd say it's probably connected to the DateT filter:

WHERE (Environmnets.DateT = (SELECT MAX(DateT) AS Expr1 FROM Environmnets AS Environmnets_1))

I suspect you want the last record for a specific TMushroomHouse, but you're actually only looking for the last record overall, and only returning it if it matches the selected house. If that's the case, try changing your query to:

SELECT TOP 1 House, DateT, TempIn, tempOut, Humidity FROM Environmnets INNER JOIN TDevice ON Environmnets.Deviceid = TDevice.id INNER JOIN TMushroomHouse ON TDevice.MushroomHouseId = TMushroomHouse.id WHERE TMushroomHouse.Nam

[Syafiqah Zahirah]

Tq..it working right now :) And I have one more question, mushroomhousename is not the same table with environmnets..how i want to call mushroomhousename and put it in a textbox?

[Richard Deeming]

Try something like:

SELECT TOP 1 House, DateT, TempIn, tempOut, Humidity, TMushroomHouse.Name As MushroomHouseName FROM Environmnets INNER JOIN TDevice ON Environmnets.Deviceid = TDevice.id INNER JOIN TMushroomHouse ON TDevice.MushroomHouseId = TMushroomHouse.id WHERE TMushroomHouse.Name = @MushroomHouseName ORDER BY Environmnets.DateT DESC

This adds the Name column from the TMushroomHouse table as a new result column called MushroomHouseName. You can then read it from your code and put it in a textbox:

while (dr.Read())
{
...
MushroomHouseTextBox.Text = dr["MushroomHouseName"].ToString();
}

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer