Dim objConn As New OleDbConnection
Dim objCmd As New OleDbCommand
Dim dtAdapter As New OleDbDataAdapter
Dim ds As New DataSet
Dim strConnString,strSQL As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& _
Server.MapPath("database/mydatabase.mdb")&";"
strSQL = "SELECT * FROM customer"
objConn.ConnectionString = strConnString
With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
End With
dtAdapter.SelectCommand = objCmd
dtAdapter.Fill(ds)
'*** BindData to GridView ***'
myGridView.DataSource = ds
myGridView.DataBind()
dtAdapter = Nothing
objConn.Close()
objConn = Nothing
Imports System.Data
Imports MySql.Data.MySqlClient
Imports System.DateTime
Imports eWorld.UI
Imports System.Data.SqlClient
Partial Class DataGrid1
Inherits System.Web.UI.Page
Dim objConn As MySqlConnection
Dim objCmd As MySqlCommand
Dim strSQL As String
Dim Maxid As Integer
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim strConnString As String
strConnString = "Server=localhost;User Id=root; Password=; Database=reportstate; Pooling=false; Convert Zero Datetime=True;"
objConn = New MySqlConnection(strConnString)
objConn.Open()
If Not Page.IsPostBack() Then
BindData()
End If
End Sub
Protected Sub BindData()
Dim Data As New DataSet
Dim dtReader As MySqlDataReader
strSQL = "SELECT * FROM tbl_infomation "
objCmd = New MySqlCommand(strSQL, objConn)
dtReader = objCmd.ExecuteReader()
'*** BindData to DataGrid ***'
myDataGrid.DataSource = dtReader
myDataGrid.DataBind()
dtReader.Close()
dtReader = Nothing
End Sub
Sub ShowPageCommand(ByVal sender As Object, ByVal e As DataGridPageChangedEventArgs)
myDataGrid.CurrentPageIndex = e.NewPageIndex
BindData()
End Sub
Protected Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload
objConn.Close()
objConn = Nothing
End Sub
Protected Sub myDataGrid_CancelCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs) Handles myDataGrid.CancelCommand
myDataGrid.EditItemIndex = -1
myDataGrid.ShowFooter = True
BindData()
End Sub
Protected Sub myDataGrid_DeleteCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs) Handles myDataGrid.DeleteCommand
strSQL = "DELETE FROM tbl_infomation WHERE ID = '" & myDataGrid.DataKeys.Item(e.Item.ItemIndex) & "'"
objCmd = New MySqlCommand(strSQL, objConn)
objCmd.ExecuteNonQuery()
myDataGrid.EditItemIndex = -1
BindData()
End Sub
Protected Sub myDataGrid_EditCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs) Handles myDataGrid.EditCommand
myDataGrid.EditItemIndex = e.Item.ItemIndex
myDataGrid.ShowFooter = False
BindData()
End Sub
Protected Sub myDataGrid_ItemCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs) Handles myDataGrid.ItemCommand
If e.CommandName = "Add" Then
'*** ID ***'
Dim txtID As TextBox = CType(e.Item.FindControl("txtAddID"), TextBox)
Dim Lid As Label = CType(e.Item.FindControl("LableID"), Label)
Dim objCmd As MySqlCommand
Dim strSQL As String
Dim da As New MySqlDataAdapter
Dim ds As New DataSet
Dim dt As New DataTable
strSQL = "select Max(ID) As Maxid from tbl_infomation"
objCmd = New MySqlCommand(strSQL, objConn)
da.SelectCommand = objCmd
da.Fill(ds, "tbl_infomation") 'Keep data to Dataset
Maxid = CInt(ds.Tables("tbl_infomation").Rows(0)("Maxid").ToString()) + 1
Lid.Text = Maxid
'*** Date ***'
Dim txtDate As CalendarPopup = DirectCast(e.Item.FindControl("Cal_Pop"), CalendarPopup)
Dim day1 As Date = txtDate.SelectedDate
Dim Day_1 As Date = day1.Year & "-" & day1.ToString("MM-dd")
'*** Active ***'
Dim CheckActive As RadioButton = CType(e.Item.FindControl("RadioBtn1"), RadioButton)
Dim ChAc As String = CheckActive.Checked
'*** Remove ***'
Dim CheckRemove As RadioButton = CType(e.Item.FindControl("RadioBtn2"), RadioButton)
Dim ChRe As String = CheckRemove.Checked
'*** Province ***'
Dim txtProvince As DropDownList = CType(e.Item.FindControl("txtAddProvince"), DropDownList)
Dim txt_Province As String = txtProvince.SelectedValue
'*** Num ***'
Dim txtNum As TextBox = CType(e.Item.FindControl("txtAddNum"), TextBox)
If ChAc = True Then
strSQL = "INSERT INTO tbl_infomation (ID,Date,Active,Province,Num)VALUES('" & txtID.Text & "','" & day1.Year & "-" & day1.ToString("MM-dd") & "','Yes' ,'" & txt_Province & "','" & txtNum.Text & "')"
ElseIf ChRe = True Then
strSQL = "INSERT INTO tbl_infomation (ID,Date,Remove,Province,Num)VALUES('" & txtID.Text & "','" & day1.Year & "-" & day1.ToString("MM-dd") & "','Yes' ,'" & txt_Province & "','" & txtNum.Text & "')"
End If
objCmd = New MySqlCommand(strSQL, objConn)
objCmd.ExecuteNonQuery()
BindData()
End If
End Sub
Protected Sub myDataGrid_UpdateCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs) Handles myDataGrid.UpdateCommand
'*** ID ***'
Dim txtID As TextBox = CType(e.Item.FindControl("txtEditID"), TextBox)
'*** Date ***'
Dim txt_Date As CalendarPopup = DirectCast(e.Item.FindControl("CalendarPopup1"), CalendarPopup)
Dim day1 As Date = txt_Date.SelectedDate
'*** Active ***'
Dim txtActive As TextBox = CType(e.Item.FindControl("txtEditActive"), TextBox)
'*** Remove ***'
Dim txtRemove As TextBox = CType(e.Item.FindControl("txtEditRemove"), TextBox)
'*** Province ***'
Dim txtProvince As DropDownList = CType(e.Item.FindControl("txtEditProvince"), DropDownList)
Dim txt_Province As String = txtProvince.SelectedValue
'*** Num ***'
Dim txtNum As TextBox = CType(e.Item.FindControl("txtEditNum"), TextBox)
strSQL = "UPDATE tbl_infomation SET ID = '" & txtID.Text & "' " & _
" ,Date = '" & day1.Year & "-" & day1.ToString("MM-dd") & "' " & _
" ,Active = '" & txtActive.Text & "' " & _
" ,Remove = '" & txtRemove.Text & "' " & _
" ,Province = '" & txt_Province & "' " & _
" ,Num = '" & txtNum.Text & "' " & _
" WHERE ID = '" & myDataGrid.DataKeys.Item(e.Item.ItemIndex) & "'"
objCmd = New MySqlCommand(strSQL, objConn)
objCmd.ExecuteNonQuery()
myDataGrid.EditItemIndex = -1
myDataGrid.ShowFooter = True
BindData()
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim MStr As String = MDropdown.SelectedValue
Dim YStr As String = YDropDown.SelectedValue
Dim PStr As String = ProDropDown.SelectedValue
Dim Rbtn As String = RadioList.SelectedValue
If (Rbtn = "Active") And (PStr <> "null") Or (YStr <> "null") Or (MStr <> "null") Then
If (PStr = "null") And (YStr = "null") Then
strSQL = "SELECT * FROM tbl_infomation where Month(Date) = '" & MStr & "' and Active = 'Yes' "
ElseIf (PStr = "null") And (MStr = "null") Then
strSQL = "SELECT * FROM tbl_infomation where Year(Date) = '" & YStr & "' and Active = 'Yes' "
ElseIf (YStr = "null") And (MStr = "null") Then
strSQL = "SELECT * FROM tbl_infomation where Province = '" & PStr & "' and Active = 'Yes' "
ElseIf MStr = "null" Then
strSQL = "SELECT * FROM tbl_infomation where Province = '" & PStr & "' and Year(Date) = '" & YStr & "' and Active = 'Yes'"
ElseIf YStr = "null" Then
strSQL = "SELECT * FROM tbl_infomation where Province = '" & PStr & "' and Month(Date) = '" & MStr & "' and Active = 'Yes' "
ElseIf PStr = "null" Then
strSQL = "SELECT * FROM tbl_infomation where Year(Date) = '" & YStr & "' and Month(Date) = '" & MStr & "' and Active = 'Yes' "
Else
strSQL = "SELECT * FROM tbl_infomation where Year(Date) = '" & YStr & "' and Month(Date) = '" & MStr & "' and Province = '" & PStr & "' and Active = 'Yes'"
End If
ElseIf (Rbtn = "Remove") And (PStr <> "null") Or (YStr <> "null") Or (MStr <> "null") Then
If (PStr = "null") And (YStr = "null") Then
strSQL = "SELECT * FROM tbl_infomation where Month(Date) = '" & MStr & "' and Remove = 'Yes'"
ElseIf (PStr = "null") And (MStr = "null") Then
strSQL = "SELECT * FROM tbl_infomation where Year(Date) = '" & YStr & "' and Remove = 'Yes' "
ElseIf (YStr = "null") And (MStr = "null") Then
strSQL = "SELECT * FROM tbl_infomation where Province = '" & PStr & "' and Remove = 'Yes' "
ElseIf MStr = "null" Then
strSQL = "SELECT * FROM tbl_infomation where Province = '" & PStr & "' and Year(Date) = '" & YStr & "' and Remove = 'Yes' "
ElseIf YStr = "null" Then
strSQL = "SELECT * FROM tbl_infomation where Province = '" & PStr & "' and Month(Date) = '" & MStr & "' and Remove = 'Yes'"
ElseIf PStr = "null" Then
strSQL = "SELECT * FROM tbl_infomation where Year(Date) = '" & YStr & "' and Month(Date) = '" & MStr & "' and Remove = 'Yes' "
Else
strSQL = "SELECT * FROM tbl_infomation where Year(Date) = '" & YStr & "' and Month(Date) = '" & MStr & "' and Province = '" & PStr & "' and Remove = 'Yes' "
End If
ElseIf (Rbtn = "Active") And (PStr = "null") And (YStr = "null") And (MStr = "null") Then
strSQL = "SELECT * FROM tbl_infomation where Active = 'Yes' "
ElseIf (Rbtn = "Remove") And (PStr = "null") And (YStr = "null") And (MStr = "null") Then
strSQL = "SELECT * FROM tbl_infomation where Remove = 'Yes' "
Else
If (PStr = "null") And (YStr = "null") Then
strSQL = "SELECT * FROM tbl_infomation where Month(Date) = '" & MStr & "' "
ElseIf (PStr = "null") And (MStr = "null") Then
strSQL = "SELECT * FROM tbl_infomation where Year(Date) = '" & YStr & "' "
ElseIf (YStr = "null") And (MStr = "null") Then
strSQL = "SELECT * FROM tbl_infomation where Province = '" & PStr & "' "
ElseIf MStr = "null" Then
strSQL = "SELECT * FROM tbl_infomation where Province = '" & PStr & "' and Year(Date) = '" & YStr & "' "
ElseIf YStr = "null" Then
strSQL = "SELECT * FROM tbl_infomation where Province = '" & PStr & "' and Month(Date) = '" & MStr & "' "
ElseIf PStr = "null" Then
strSQL = "SELECT * FROM tbl_infomation where Year(Date) = '" & YStr & "' and Month(Date) = '" & MStr & "' "
Else
strSQL = "SELECT * FROM tbl_infomation where Year(Date) = '" & YStr & "' and Month(Date) = '" & MStr & "' and Province = '" & PStr & "' "
End If
End If
Dim dtReader As MySqlDataReader
objCmd = New MySqlCommand(strSQL, objConn)
dtReader = objCmd.ExecuteReader()
'*** BindData to DataGrid ***'
myDataGrid.DataSource = dtReader
myDataGrid.DataBind()
dtReader.Close()
dtReader = Nothing
End Sub
Protected Sub BtnAll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnAll.Click
strSQL = "SELECT * FROM tbl_infomation "
Dim dtReader As MySqlDataReader
objCmd = New MySqlCommand(strSQL, objConn)
dtReader = objCmd.ExecuteReader()
'*** BindData to DataGrid ***'
myDataGrid.DataSource = dtReader
myDataGrid.DataBind()
dtReader.Close()
dtReader = Nothing
End Sub
Protected Sub myDataGrid_pageIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles myDataGrid.PageIndexChanged
End Sub
End Class
Protected Sub ShowPageCommand_PageIndexChanging(sender As Object, e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles ShowPageCommand_PageIndexChanging
myDataGrid.PageIndex = e.NewPageIndex
xxxx
strSQL = "SELECT * FROM tbl_infomation "
objCmd = New MySqlCommand(strSQL, objConn)
dtReader = objCmd.ExecuteReader()
myDataGrid.DataSource = dtReader
myDataGrid.DataBind()
xxx
End Sub