i have chart and i want fill it by searching between 2 dates ( my database is MS Access 2003 and i am using Vb.net Forms )

Question

i have chart and i want fill it by searching between 2 dates ( my database is MS Access 2003 and i am using Vb.net Forms ) the error in SQL statment and the image link down shows the error my code is

Try
        Dim MyStringDate1 As Date = CDate(DateTimePicker1.Value.Date)
        Dim MyStringDate2 As Date = CDate(DateTimePicker2.Value.Date)

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & Application.StartupPath & "\LIBRARY.mdb"
        con.Open()
        sql = "select CATEGORY , COUNT(CATEGORY)as CatStac from tblRentals where  dateIssued between   #" & MyStringDate1 & "# AND #" & MyStringDate2 & "#   from  tblRentals   GROUP BY CATEGORY"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "tblRentals")
        con.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
        MsgBox(" SQL OR CONNECTION ERROR ")
    End Try

    Try
        Dim T As Title = Chart1.Titles.Add("CATEGORY STACS FOR LIBRARY")
        '~~> Formatting the Title
        With T
            .ForeColor = Color.Black            '~~> Changing the Fore Color of the Title 
            .BackColor = Color.Coral            '~~> Changing the Back Color of the Title 

            '~~> Setting Font, Font Size and Bold/Italicizing
            .Font = New System.Drawing.Font("Times New Roman", 11.0F, System.Drawing.FontStyle.Bold)
            .Font = New System.Drawing.Font("Times New Roman", 11.0F, System.Drawing.FontStyle.Underline)
            .BorderColor = Color.Black          '~~> Changing the Border Color of the Title 
            .BorderDashStyle = ChartDashStyle.DashDotDot '~~> Changing the Border Dash Style of the Title 
        End With
        Chart1.DataSource = ds.Tables("tblRentals")
        Dim Series1 As Series = Chart1.Series("Series1")
        Series1.Name = "CATEGORY STACS"
        Chart1.Series(Series1.Name).ChartType = SeriesChartType.Pie

        ' Chart1.Series(Series1.Name).LabelFormat = "0"
        Chart1.Series(Series1.Name).IsValueShownAsLabel = True
        Chart1.Series(Series1.Name).XValueMember = "CATEGORY"
        Chart1.Series(Series1.Name).YValueMembers = "CatStac"
        Dim LG As Legend = Chart1.Legends(0)
        '~~> Changing the Back Color of the Legend 
        LG.BackColor = Color.Wheat
        '~~> Changing the Fore Color of the Legend
        LG.ForeColor = Color.DarkSlateBlue
        '~~> Setting Font, Font Size and Bold
        LG.Font = New System.Drawing.Font("Times New Roman", 11.0F, System.Drawing.FontStyle.Bold)
        '~~> Assigning a title for the legend
        LG.Title = "Legend"
        Chart1.Size = New System.Drawing.Size(500, 500)
    Catch ex As Exception
        MsgBox(ex.Message)
        MsgBox("CHART ERROR ")
    End Try

http://s17.postimg.org/wqkde2fjj/image.png

I UPDATE MY SQL STATEMENT BUT NOW RETURN ALL COLUMNS NOT COLUMNS BETWEEN 2 DATES IT SHOULD RETURN 10 VALUES BUT NOW RETURN ALL VALUES — Mak Haj, Jan 12 '14 at 15:16
this will work i try it : sql = "select CATEGORY , COUNT(CATEGORY)as CatStac from tblRentals where dateIssued between #" & MyStringDate1 & "# AND #" & MyStringDate2 & "# GROUP BY CATEGORY" — Al-3sli, Jan 12 '14 at 15:25
Also you repeat `from tblRentals` twice before `Where` and after it — Al-3sli, Jan 12 '14 at 15:28
I APPRECIATE YOUR HELP SIR AND I FIX IT BUT STILL RETURN ALL COLUMNS NOT COLUMNS WHAT I NEED BETWEEN THE 2 DATES ! EVEN I PUT BETWEEN DATES NOT IN DATABASE STILL RETURN ALL COLUMNS — Mak Haj, Jan 12 '14 at 15:32
THANK YOU BRO , WITH YOUR ANSWER I NEED TO CHANGE MY REGION FORMAT ! AND NOW IT IS WORKING THANK YOU — Mak Haj, Jan 12 '14 at 16:49

Al-3sli · Accepted Answer · 2014-01-12T15:24:45.120

0

try change the select like this :

sql = "select CATEGORY , COUNT(CATEGORY)as CatStac from tblRentals where  dateIssued  >  #" & MyStringDate1 & "# AND dateIssued  < #" & MyStringDate2 & "#   GROUP BY CATEGORY"

Add this to your code to bind your dataSet to chart :

'Set chart data source  
chart1.DataSource = ds  

'Set series members names for the X and Y values  
chart1.Series("Series 1").XValueMember = "col1"
chart1.Series("Series 1").YValueMembers = "col2"  

'Data bind to the selected data source  
chart1.DataBind()

edited Jan 12 '14 at 15:24

answered Jan 12 '14 at 14:42

Al-3sli

2,161
2
15
19

Sir the problem in SQL statment see the link http://s17.postimg.org/wqkde2fjj/image.png – Mak Haj Jan 12 '14 at 14:49

i have chart and i want fill it by searching between 2 dates ( my database is MS Access 2003 and i am using Vb.net Forms )

1 Answers1