In my Sql table, when a value is entered in, the date is recorded with it. The problem is, I want to show all the values(dynamically created buttons in my case) that do not have a date entered for today's date (DateTime.Today). How would I do this if that entry has not even been added in for the day?
EDIT: The date is within the SQL table, but also in a listview filled with data from the sql table for easy access. The buttons should only be shown for those that do not have a date entered for today.
public void load()
{
foreach (ListViewItem item in ListView.Items)
{
//item.SubItems[5].Text is row's ID
SqlConnection conn = new SqlConnection(connstring);
string strsql = "SELECT ID from Table1 WHERE ID = '" item.SubItems[5].Text "'";
SqlCommand cmd = new SqlCommand(strsql, conn);
SqlDataReader reader = null;
cmd.Connection.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
System.Windows.Forms.Button test1Button = new System.Windows.Forms.Button();
test1Button.Click = new EventHandler(button1ButtonClick);
test1Button .Text = reader["ID"].ToString();
test1Button .Size = new System.Drawing.Size(120, 38);
this.Controls.Add(test1Button );
flowLayoutPanel.Controls.Add(test1Button );
System.Windows.Forms.Button test2Button = new System.Windows.Forms.Button();
test2Button Button.Click = new EventHandler(LabelBtn_Click);
test2Button Button.Text = reader["ID"].ToString();
test2Button Button.BackColor = Color.DarkRed;
test2Button Button.ForeColor = Color.White;
test2Button Button.Size = new System.Drawing.Size(120, 38);
this.Controls.Add( test2Button );
flowLayoutPanel2.Controls.Add( test2Button );
}
}
}
UPDATE: I have updated the code, I realized I needed to join tables, and with the joining of these tables I have better access to the dates. The dates are not null, they simply are not entered at all yet for the day. The date does not exist in the database at all until the user enters in a result.
public void load()
{
foreach (ListViewItem item in ListView.Items)
{
SqlConnection conn = new SqlConnection(connstring);
string strsql = "SELECT * from Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.[Table1 _ID] = t2.[Table2 _ID] WHERE Convert(Date, yourDateColumn) > Convert(Date, CURRENT_TIMESTAMP)";
SqlCommand cmd = new SqlCommand(strsql, conn);
SqlDataReader reader = null;
cmd.Connection.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
System.Windows.Forms.Button test1Button = new System.Windows.Forms.Button();
test1Button .Click = new EventHandler(button1ButtonClick);
test1Button .Text = reader["ID"].ToString();
test1Button .Size = new System.Drawing.Size(120, 38);
this.Controls.Add(test1Button );
flowLayoutPanel.Controls.Add(test1Button );
System.Windows.Forms.Button test2Button = new System.Windows.Forms.Button();
test2Button .Click = new EventHandler(LabelBtn_Click);
test2Button .Text = reader["ID"].ToString();
test2Button .BackColor = Color.DarkRed;
test2Button .ForeColor = Color.White;
test2Button .Size = new System.Drawing.Size(120, 38);
this.Controls.Add(test2Button );
flowLayoutPanel2.Controls.Add(test2Button );
}
}
}
CodePudding user response:
First of all, you should really avoid constructing your queries like that, you're opening your application to security risks. Look into using the AddWithValue method here: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparametercollection.addwithvalue?view=dotnet-plat-ext-7.0
Also, if you say the date is in the database just extract it in your query, which I can't seem to understand because it seems to me you are currently extracting only the ID, but based on the ID... Better do something like:
SELECT * FROM Table1 WHERE Convert(Date,yourDateColumn) != Convert(Date, CURRENT_TIMESTAMP)
This way you only select the rows from your table where the date IS NOT today's date.
Also, you seem to be adding the buttons twice, once to the form and once to a flowLayoutPanel, which I think is unnecessary, just add it to the panel.