我有图表，我想通过搜索2个日期（我的数据库是Access 2003和我使用Vb.Net表单）填写它 [英] 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 )

问题描述

我有图表，我希望通过在2个日期之间搜索来填充它（我的数据库是MS Access 2003，我使用的是Vb.net表单）

你可以看到SQL语句中的错误看到链接

我的代码是

尝试
 Dim MyStringDate1 As Date = CDate（DateTimePicker1.Value。日期）
 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 =选择CATEGORY，COUNT（CATEGORY）作为来自tblRentals的CatStac，其中dateIssued介于＃& MyStringDate1& ＃AND＃& MyStringDate2& #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）
结束尝试
 
尝试
 Dim T作为标题= Chart1.Titles.Add（CLEGARY STACS FOR LIBRARY）
'~~>使用T 
格式化标题
 .ForeColor = Color.Black'~~>更改标题的前景颜色
 .BackColor = Color.Coral'~~>更改标题的背面颜色
 
'~~>设置字体，字体大小和粗体/斜体
 .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'~~>更改标题的边框颜色
 .BorderDashStyle = ChartDashStyle.DashDotDot'~~>更改标题的边框短划线样式
结束
 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）
'~~>更改图例的背面颜色
 LG.BackColor = Color.Wheat 
'~~>更改图例的前景颜色
 LG.ForeColor = Color.DarkSlateBlue 
'~~>设置字体，字体大小和粗体
 LG.Font = New System.Drawing.Font（Times New Roman，11.0F，System.Drawing.FontStyle.Bold）
'~~>为图例分配标题
 LG.Title =图例
 Chart1.Size = New System.Drawing.Size（500,500）
 Catch ex As Exception 
 MsgBox（ ex.Message）
 MsgBox（CHART ERROR）
结束尝试
 

http://s17.postimg.org/wqkde2fjj/image.png [ ^ ]

解决方案

 sql =  选择CATEGORY，COUNT（CATEGORY）为来自tblRentals的CatStac，其中dateIssued介于＃& MyStringDate1&  ＃AND＃& MyStringDate2&  来自tblRentals的  GROUP BY CATEGORY 

您已经提到了 FROM 子句，在 GROUP BY 之前无需再次提及子句。

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 )
you can see the error in SQL statment see the link
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[^]

解决方案

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

You have already mentioned the FROM clause, no need to mention again before GROUP BY Clause.

这篇关于我有图表，我想通过搜索2个日期（我的数据库是Access 2003和我使用Vb.Net表单）填写它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！