Function SqlScalar(sql As String) As Integer
Try
Using cmd As New OleDbCommand(sql, Con)
cmd.Parameters.AddWithValue("@d1", Me.DateTimePicker1.Value.Date)
cmd.Parameters.AddWithValue("@d2", Me.DateTimePicker2.Value.Date)
If Con.State <> ConnectionState.Open Then Con.Open()
Dim c As Integer = cmd.ExecuteScalar
Con.Close()
Return c
End Using
Catch ex As Exception
MsgBox(ex.Message)
Return 0
End Try
End Function
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim rep As New count
rep.SetDataSource(dt)
Dim q As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([sex] ='ذكر') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("m1", q)
Dim w As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([sex] ='انثى') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("g1", w)
Dim t As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([sex] ='ذكر') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("m2", t)
Dim r As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([sex] ='انثى') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("g2", r)
Dim u As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([sex] ='ذكر') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("m3", u)
Dim n As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([sex] ='انثى') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("g3", n)
Dim i As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([sex] ='ذكر') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("m4", i)
Dim a As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([sex] ='انثى') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("g4", a)
Dim s As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([sex] ='ذكر') AND ([age] BETWEEN 15 AND 19) AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("m5", s)
Dim d As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([sex] ='انثى') AND ([age] BETWEEN 15 AND 19) AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("g5", d)
Dim b As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([sex] ='ذكر') AND ([age] >20) AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("m6", b)
Dim f As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([sex] ='انثى') AND ([age] >20) AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("g6", f)
Dim z As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([Type] ='كلب') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("dog", z)
Dim x As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([Type] ='جرذ') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("rat", x)
Dim c As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([Type] ='حمار') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("donky", c)
Dim m As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([Type] ='ماعز') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("goat", m)
Dim y As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([Type] ='قط') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("cat", y)
Dim ee As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([Type] ='اخرى') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("ather", ee)
Dim eqe As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([state] ='معروف') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("know", eqe)
Dim yw As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([place] ='الراس') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("head", yw)
Dim yew As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([place] ='الجذع') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("j", yew)
Dim yuw As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([place] ='ساق') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("leg", yuw)
Dim yyw As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([place] ='القدم') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("foot", yyw)
Dim ysw As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([place] ='اليد') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("hand", ysw)
Dim ynw As String = SqlScalar("SELECT COUNT(*) FROM [Table1] WHERE ([place] ='اصبع اليد') AND ([age] <1) AND ([Birthday] BETWEEN @d1 AND @d2) ")
rep.SetParameterValue("finger", ynw)
rep.SetParameterValue("start_date", Me.DateTimePicker1.Value.Date)
rep.SetParameterValue("end_date", Me.DateTimePicker2.Value.Date)
CrystalReportViewer1.ReportSource = rep
CrystalReportViewer1.Refresh()
End Sub