Imports System.Data
Imports System.Data.SqlClient
Partial Class DataGrid1
Inherits System.Web.UI.Page
Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim strSQL As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim strConnString As String
strConnString = "Server=AQUILA;UID=TESTUSR;PASSWORD=TEST9999;database=TESTDTA;Max Pool Size=400;Connect Timeout=600;"
objConn = New SqlConnection(strConnString)
objConn.Open()
If Not Page.IsPostBack() Then
BindData()
End If
End Sub
Protected Sub BindData()
strSQL = "SELECT * FROM HR.Employee"
Dim dtReader As SqlDataReader
objCmd = New SqlCommand(strSQL, objConn)
dtReader = objCmd.ExecuteReader()
'*** BindData to DataGrid ***'
myDataGrid.DataSource = dtReader
myDataGrid.DataBind()
dtReader.Close()
dtReader = Nothing
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 HR.Employee WHERE EmployeeID = '" & myDataGrid.DataKeys.Item(e.Item.ItemIndex) & "'"
objCmd = New SqlCommand(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
'*** CustomerID ***'
Dim txtCustomerID As TextBox = CType(e.Item.FindControl("txtEmployeeID"), TextBox)
'*** Name ***'
Dim txtName As TextBox = CType(e.Item.FindControl("txtFirstName"), TextBox)
'*** Email ***'
Dim txtEmail As TextBox = CType(e.Item.FindControl("txtLastName"), TextBox)
'*** CountryCode ***'
Dim txtCountryCode As TextBox = CType(e.Item.FindControl("txtBirthdate"), TextBox)
'*** Budget ***'
Dim txtBudget As TextBox = CType(e.Item.FindControl("txtADepartmentID"), TextBox)
'*** Used ***'
Dim txtUsed As TextBox = CType(e.Item.FindControl("txtBossID"), TextBox)
' Dim txtUsed As TextBox = CType(e.Item.FindControl("txtBossID"), TextBox)
Dim txtAddModifiedDate As TextBox = CType(e.Item.FindControl("txtBossID"), TextBox)
' Dim txtUsed As TextBox = CType(e.Item.FindControl("txtBossID"), TextBox)
strSQL = "INSERT INTO HR.Employee (EmployeeID,FirstName,txtLastName,CountryCode,Budget,Used) " & _
" VALUES ('" & txtCustomerID.Text & "','" & txtName.Text & "','" & txtEmail.Text & "' " & _
" ,'" & txtCountryCode.Text & "','" & txtBudget.Text & "','" & txtUsed.Text & "') "
objCmd = New SqlCommand(strSQL, objConn)
objCmd.ExecuteNonQuery()
BindData()
End If
End Sub
Protected Sub myDataGrid_UpdateCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs) Handles myDataGrid.UpdateCommand
'*** CustomerID ***'
Dim txtCustomerID As TextBox = CType(e.Item.FindControl("txtEditCustomerID"), TextBox)
'*** Email ***'
Dim txtName As TextBox = CType(e.Item.FindControl("txtEditName"), TextBox)
'*** Name ***'
Dim txtEmail As TextBox = CType(e.Item.FindControl("txtEditEmail"), TextBox)
'*** CountryCode ***'
Dim txtCountryCode As TextBox = CType(e.Item.FindControl("txtEditCountryCode"), TextBox)
'*** Budget ***'
Dim txtBudget As TextBox = CType(e.Item.FindControl("txtEditBudget"), TextBox)
'*** Used ***'
Dim txtUsed As TextBox = CType(e.Item.FindControl("txtEditUsed"), TextBox)
strSQL = "UPDATE HR.Employee SET CustomerID = '" & txtCustomerID.Text & "' " & _
" ,Name = '" & txtName.Text & "' " & _
" ,Email = '" & txtEmail.Text & "' " & _
" ,CountryCode = '" & txtCountryCode.Text & "' " & _
" ,Budget = '" & txtBudget.Text & "' " & _
" ,Used = '" & txtUsed.Text & "' " & _
" WHERE CustomerID = '" & myDataGrid.DataKeys.Item(e.Item.ItemIndex) & "'"
objCmd = New SqlCommand(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
strSQL = "SELECT * FROM HR.Employee WHERE (Name like '%" & Me.TextBox1.Text & "%') "
objCmd = New SqlCommand(strSQL, objConn)
'Dim dtReader As SqlDataReader
Dim dtAdapter As SqlDataAdapter
Dim ds As New DataSet
dtAdapter = New SqlDataAdapter(strSQL, objConn)
dtAdapter.Fill(ds)
myDataGrid.DataSource = ds
myDataGrid.DataBind()
End Sub
Protected Sub myDataGrid_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles myDataGrid.SelectedIndexChanged
End Sub
End Class
Imports System.Data
Imports System.Data.SqlClient
Partial Class DataGrid1
Inherits System.Web.UI.Page
Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim strSQL As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim strConnString As String
strConnString = "Server=AQUILA;UID=TESTUSR;PASSWORD=TEST9999;database=TESTDTA;Max Pool Size=400;Connect Timeout=600;"
objConn = New SqlConnection(strConnString)
objConn.Open()
If Not Page.IsPostBack() Then
BindData()
DropDownListDataTable()
End If
End Sub
Sub DropDownListDataTable()
Dim objConn As SqlConnection
Dim dtAdapter As SqlDataAdapter
Dim dt As New DataTable
Dim strConnString As String
strConnString = "Server=AQUILA;UID=TESTUSR;PASSWORD=TEST9999;database=TESTDTA;Max Pool Size=400;Connect Timeout=600;"
objConn = New SqlConnection(strConnString)
objConn.Open()
Dim strSQL As String
strSQL = "SELECT * FROM [HR.Department]"
dtAdapter = New SqlDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
dtAdapter = Nothing
objConn.Close()
objConn = Nothing
'*** DropDownlist ***'
With Me.myDDL1
.DataSource = dt
.DataTextField = "Name"
.DataValueField = "Name"
.DataBind()
End With
'*** Default Value ***'
' myDDL1.SelectedIndex = myDDL1.Items.IndexOf(myDDL1.Items.FindByValue("TH"))
End Sub
Protected Sub BindData()
strSQL = "SELECT * FROM [HR.Employee]"
Dim dtReader As SqlDataReader
objCmd = New SqlCommand(strSQL, objConn)
dtReader = objCmd.ExecuteReader()
'Dim dtAdapter As SqlDataAdapter
'Dim ds As New DataSet
'dtAdapter = New SqlDataAdapter(strSQL, objConn)
'dtAdapter.Fill(ds)
'myDataGrid.DataSource = ds
'myDataGrid.DataBind()
'*** BindData to DataGrid ***'
myDataGrid.DataSource = dtReader
myDataGrid.DataBind()
dtReader.Close()
dtReader = Nothing
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 [HR.Employee] WHERE EmployeeID = '" & myDataGrid.DataKeys.Item(e.Item.ItemIndex) & "'"
objCmd = New SqlCommand(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
'*** CustomerID ***'
Dim txtCustomerID As TextBox = CType(e.Item.FindControl("txtEmployeeID"), TextBox)
'*** Name ***'
Dim txtName As TextBox = CType(e.Item.FindControl("txtFirstName"), TextBox)
'*** Email ***'
Dim txtEmail As TextBox = CType(e.Item.FindControl("txtLastName"), TextBox)
'*** CountryCode ***'
Dim txtCountryCode As TextBox = CType(e.Item.FindControl("txtBirthdate"), TextBox)
'*** Budget ***'
Dim txtBudget As TextBox = CType(e.Item.FindControl("txtADepartmentID"), TextBox)
'*** Used ***'
Dim txtUsed As TextBox = CType(e.Item.FindControl("txtBossID"), TextBox)
' Dim txtUsed As TextBox = CType(e.Item.FindControl("txtBossID"), TextBox)
Dim txtAddModifiedDate As TextBox = CType(e.Item.FindControl("txtModifiedDate"), TextBox)
' Dim txtUsed As TextBox = CType(e.Item.FindControl("txtBossID"), TextBox)
strSQL = "INSERT INTO [HR.Employee] (EmployeeID,FirstName,LastName,Birthdate,DepartmentID,BossID,ModifiedDate) " & _
" VALUES ('" & txtCustomerID.Text & "','" & txtName.Text & "','" & txtEmail.Text & "' ,'" & txtCountryCode.Text & "','" & txtBudget.Text & "','" & txtUsed.Text & "','" & txtAddModifiedDate.Text & "')"
objCmd = New SqlCommand(strSQL, objConn)
objCmd.ExecuteNonQuery()
BindData()
End If
End Sub
Protected Sub myDataGrid_UpdateCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs) Handles myDataGrid.UpdateCommand
'*** CustomerID ***'
Dim txtCustomerID As TextBox = CType(e.Item.FindControl("txtEmployeeID"), TextBox)
'*** Name ***'
Dim txtName As TextBox = CType(e.Item.FindControl("txtFirstName"), TextBox)
'*** Email ***'
Dim txtEmail As TextBox = CType(e.Item.FindControl("txtLastName"), TextBox)
'*** CountryCode ***'
Dim txtCountryCode As TextBox = CType(e.Item.FindControl("txtBirthdate"), TextBox)
'*** Budget ***'
Dim txtBudget As TextBox = CType(e.Item.FindControl("txtADepartmentID"), TextBox)
'*** Used ***'
Dim txtUsed As TextBox = CType(e.Item.FindControl("txtBossID"), TextBox)
' Dim txtUsed As TextBox = CType(e.Item.FindControl("txtBossID"), TextBox)
Dim txtAddModifiedDate As TextBox = CType(e.Item.FindControl("txtModifiedDate"), TextBox)
' Dim txtUsed As TextBox = CType(e.Item.FindControl("txtBossID"), TextBox)
strSQL = "UPDATE [HR.Employee] SET EmployeeID = '" & txtCustomerID.Text & "' " & _
" ,FirstName = '" & txtName.Text & "' " & _
" ,LastName = '" & txtEmail.Text & "' " & _
" ,Birthdate = '" & txtCountryCode.Text & "' " & _
" ,DepartmentID = '" & txtBudget.Text & "' " & _
" ,BossID = '" & txtUsed.Text & "' " & _
" ,ModifiedDate = '" & txtAddModifiedDate.Text & "' " & _
" WHERE CustomerID = '" & myDataGrid.DataKeys.Item(e.Item.ItemIndex) & "'"
objCmd = New SqlCommand(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
strSQL = "SELECT * FROM [HR.Employee] WHERE (FirstName like '%" & Me.TextBox1.Text & "%')or(LastName like '%" & Me.TextBox1.Text & "%')or(EmployeeID = '%" & Me.TextBox2.Text & "%')"
' or(Employee like '%" & Me.TextBox1.Text & "%') "
objCmd = New SqlCommand(strSQL, objConn)
'Dim dtReader As SqlDataReader
Dim dtAdapter As SqlDataAdapter
Dim ds As New DataSet
dtAdapter = New SqlDataAdapter(strSQL, objConn)
dtAdapter.Fill(ds)
myDataGrid.DataSource = ds
myDataGrid.DataBind()
End Sub
Protected Sub myDataGrid_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles myDataGrid.SelectedIndexChanged
End Sub
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles myDDL1.SelectedIndexChanged
End Sub
End Class