Sonam Sonam -4 years ago 125
Vb.net Question

Using combo-box as filter to show chart by time in vb.net 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 vb.net. 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.Clear()
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