Sonam Sonam -4 years ago 125 Question

Using combo-box as filter to show chart by time in using sql

I am trying to show total sales charts by (Day,week, month, year) using items in combo box as filter querying from sql in Please help. My codes are below, which throws the error saying 'cannot find table 0'.

cboxGroupBy.Items.Insert(0, "Day")
cboxGroupBy.Items.Insert(1, "Week")
cboxGroupBy.Items.Insert(2, "Month")
cboxGroupBy.Items.Insert(3, "Year")

SQL.ExecQuery("SELECT Date = DATEPART('" + Me.cboxGroupBy.Text + "', Date),
SUM (Amount) AS Amount
FROM Sales
GROUP BY DATEPART('" + Me.cboxGroupBy.Text + "', Date)")

Chart2.DataSource = SQL.SQLDS.Tables(0)
Chart2.Series.Add("Total Revenue")
Chart2.Series("Total Revenue").XValueMember = "Date"
Chart2.Series("Total Revenue").YValueMembers = "Amount"

Answer Source

Remove the single quote from the DatePart() function. No need to pass Day or Week etc. in single quote. Here is your modified query.

 SQL.ExecQuery("SELECT Date = DATEPART(" + Me.cboxGroupBy.Text + ", Date),                                                            
                 SUM (Amount) AS Amount 
                 FROM Sales                            
                 GROUP BY DATEPART(" + Me.cboxGroupBy.Text + ", Date)")
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download