Dim Service As New WebService1
Dim ConnStr As String = "server=(local);database=police;uid=police;password=police"
Dim Conn As New SqlConnection(ConnStr)
Dim strPeID As String = ""
Dim Sql As String
'Dim Sql As String = "SELECT * FROM TblPersonel"
Dim Cmd As SqlCommand
'Dim Idx As String = Request.QueryString("id")
Dim MyDa As New SqlDataAdapter(Sql, Conn)
Dim Da As New SqlDataAdapter
Dim Ds As New DataSet
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Dim strConnString As String
' strConnString = "SELECT * FROM TblPersonel"
Conn.Open()
If Not Page.IsPostBack Then
GridViewBindData()
End If
End Sub
Protected Sub GridViewBindData()
Sql = "SELECT * FROM TblPersonel"
Dim dtReader As SqlDataReader
Cmd = New SqlCommand(Sql, Conn)
dtReader = Cmd.ExecuteReader()
'*** BindData to GridView ***'
GridView1.DataSource = dtReader
GridView1.DataBind()
dtReader.Close()
dtReader = Nothing
End Sub
Protected Sub DetailsViewBindData()
Sql = "SELECT * FROM TblPersonel WHERE Pe_Id = '" & strPeID & "' "
Dim dtReader As SqlDataReader
Cmd = New SqlCommand(Sql, Conn)
dtReader = Cmd.ExecuteReader()
'*** BindData to DetailsView ***'
DetailsView1.DataSource = dtReader
DetailsView1.DataBind()
dtReader.Close()
dtReader = Nothing
End Sub
Protected Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload
Conn.Close()
Conn = Nothing
End Sub
Private Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView1.RowEditing
GridView1.Visible = False
strPeID = GridView1.DataKeys(e.NewEditIndex).Value.ToString
DetailsView1.Visible = True
DetailsViewBindData()
End Sub
Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles GridView1.SelectedIndexChanged
End Sub
Private Sub DetailsView1_ModeChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewModeEventArgs) Handles DetailsView1.ModeChanging
Select Case e.NewMode
Case DetailsViewMode.Edit
Dim lblPeID As Label = CType(DetailsView1.FindControl("lblPeID"), Label)
strPeID = lblPeID.Text
DetailsView1.ChangeMode(DetailsViewMode.Edit)
DetailsViewBindData()
Case DetailsViewMode.ReadOnly
DetailsView1.ChangeMode(DetailsViewMode.ReadOnly)
DetailsView1.Visible = False
GridView1.Visible = True
ShowData()
End Select
End Sub
Private Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DetailsView1.ItemUpdating
'*** Old PeID ***'
Dim lblPeID As Label = CType(DetailsView1.FindControl("lblPeID"), Label)
strPeID = lblPeID.Text
'*** PersonelID ***'
Dim txtPersonelID As TextBox = CType(DetailsView1.FindControl("txtEditPersonelID"), TextBox)
'*** Name ***'
Dim txtName As TextBox = CType(DetailsView1.FindControl("txtEditPeName"), TextBox)
'*** Position ***'
Dim txtPosition As TextBox = CType(DetailsView1.FindControl("txtEditPePosition"), TextBox)
Sql = "UPDATE TblPersonel SET Pe_Id = '" & txtPersonelID.Text & "' " & _
" ,Pe_name = '" & txtName.Text & "' " & _
" ,Pe_Position = '" & txtPosition.Text & "' " & _
" WHERE Pe_Id = '" & strPeID & "'"
Cmd = New SqlCommand(Sql, Conn)
Cmd.ExecuteNonQuery()
DetailsView1.ChangeMode(DetailsViewMode.ReadOnly)
DetailsView1.Visible = False
GridView1.Visible = True
GridViewBindData()
End Sub
Imports System.Data
Imports System.Data.SqlClient
Partial Public Class WebForm3
Inherits System.Web.UI.Page
Dim Service As New WebService1
Dim ConnStr As String = "server=(local);database=police;uid=police;password=police"
Dim Conn As New SqlConnection(ConnStr)
Dim strPeID As String = ""
Dim Sql As String
'Dim Sql As String = "SELECT * FROM TblPersonel"
Dim Cmd As SqlCommand
'Dim Idx As String = Request.QueryString("id")
Dim MyDa As New SqlDataAdapter(Sql, Conn)
Dim Da As New SqlDataAdapter
Dim Ds As New DataSet
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Dim strConnString As String
' strConnString = "SELECT * FROM TblPersonel"
Conn.Open()
If Not Page.IsPostBack Then
GridViewBindData()
End If
End Sub
Protected Sub GridViewBindData()
Sql = "SELECT * FROM TblPersonel"
Dim dtReader As SqlDataReader
Cmd = New SqlCommand(Sql, Conn)
dtReader = Cmd.ExecuteReader()
'*** BindData to GridView ***'
GridView1.DataSource = dtReader
GridView1.DataBind()
dtReader.Close()
dtReader = Nothing
End Sub
Protected Sub DetailsViewBindData()
Sql = "SELECT * FROM TblPersonel WHERE Pe_Id = '" & strPeID & "' "
Dim dtReader As SqlDataReader
Cmd = New SqlCommand(Sql, Conn)
dtReader = Cmd.ExecuteReader()
'*** BindData to DetailsView ***'
DetailsView1.DataSource = dtReader
DetailsView1.DataBind()
dtReader.Close()
dtReader = Nothing
End Sub
Protected Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload
Conn.Close()
Conn = Nothing
End Sub
Private Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView1.RowEditing
GridView1.Visible = False
strPeID = GridView1.DataKeys(e.NewEditIndex).Value.ToString
DetailsView1.Visible = True
DetailsViewBindData()
End Sub
Private Sub DetailsView1_ModeChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewModeEventArgs) Handles DetailsView1.ModeChanging
Select Case e.NewMode
Case DetailsViewMode.Edit
Dim lblPeID As Label = CType(DetailsView1.FindControl("lblPeID"), Label)
strPeID = lblPeID.Text
DetailsView1.ChangeMode(DetailsViewMode.Edit)
DetailsViewBindData()
Case DetailsViewMode.ReadOnly
DetailsView1.ChangeMode(DetailsViewMode.ReadOnly)
DetailsView1.Visible = False
GridView1.Visible = True
'ShowData()
End Select
End Sub
Private Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DetailsView1.ItemUpdating
'*** Old PeID ***'
Dim lblPeID As Label = CType(DetailsView1.FindControl("lblPeID"), Label)
strPeID = lblPeID.Text
'*** PersonelID ***'
Dim txtPersonelID As TextBox = CType(DetailsView1.FindControl("txtEditPersonelID"), TextBox)
'*** Name ***'
Dim txtName As TextBox = CType(DetailsView1.FindControl("txtEditPeName"), TextBox)
'*** Position ***'
Dim Position As DropDownList = CType(DetailsView1.FindControl("txtEditPosition"), DropDownList)
Sql = "UPDATE TblPersonel SET Pe_Id = '" & txtPersonelID.Text & "' " & _
" ,Pe_name = '" & txtName.Text & "' " & _
" ,Pe_Position = '" & Position.SelectedItem.Text & "' " & _
" WHERE Pe_Id = '" & strPeID & "'"
Cmd = New SqlCommand(Sql, Conn)
Cmd.ExecuteNonQuery()
DetailsView1.ChangeMode(DetailsViewMode.ReadOnly)
DetailsView1.Visible = False
GridView1.Visible = True
GridViewBindData()
End Sub
End Class
Private Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DetailsView1.ItemUpdating
....
....
....
....
....
....
Response.Redirect(.............)
End Sub
2. คลิกขวาที่ folder app_code (ถ้าไม่มีให้คลิกขวาที่ชื่อโปรเจ็คแล้ว add asp.net folder) เลือก add new item...
สร้าง class ตั้งชื่อว่า ExampleData.vb แล้วก็อปโค้ดด้านล่างไปใส่
ExampleData.vb
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
Public Class ExampleData
Private sqlConnection As SqlConnection
Public Sub New()
Dim sqlConnectionString As String = WebConfigurationManager.ConnectionStrings("SqlConnectionString").ConnectionString
sqlConnection = New SqlConnection(sqlConnectionString)
If Not TableExist("ExampleData") Then
CreateExampleData()
End If
If Not TableExist("DropDropListData") Then
CreateDropDropListData()
End If
End Sub
Public Function GetAllData() As DataTable
Dim sqlCommandString As String = "Select [ExampleData].[ID], [ExampleData].[Name], [ExampleData].[PositionID], [DropDropListData].[PositionName] From [ExampleData] Inner Join [DropDropListData] On ([ExampleData].[PositionID] = [DropDropListData].[PositionID])"
Dim sqlCommand As New SqlCommand(sqlCommandString, sqlConnection)
Dim Dt As New DataTable()
Dim dataAdapter As New SqlDataAdapter()
dataAdapter.SelectCommand = sqlCommand
dataAdapter.Fill(Dt)
Return Dt
End Function
Public Function GetData(ByVal ID As Integer) As DataTable
Dim sqlCommandString As String = "Select [ExampleData].[ID], [ExampleData].[Name], [ExampleData].[PositionID], [DropDropListData].[PositionName] From [ExampleData] Inner Join [DropDropListData] On ([ExampleData].[PositionID] = [DropDropListData].[PositionID]) Where [ExampleData].[ID]=@ID"
Dim sqlCommand As New SqlCommand(sqlCommandString, sqlConnection)
sqlCommand.Parameters.AddWithValue("@ID", ID)
Dim Dt As New DataTable()
Dim dataAdapter As New SqlDataAdapter()
dataAdapter.SelectCommand = sqlCommand
dataAdapter.Fill(Dt)
Return Dt
End Function
Public Function GetDropDownListData() As DataTable
Dim sqlCommandString As String = "Select [PositionID], [PositionName] From [DropDropListData]"
Dim sqlCommand As New SqlCommand(sqlCommandString, sqlConnection)
Dim Dt As New DataTable()
Dim dataAdapter As New SqlDataAdapter()
dataAdapter.SelectCommand = sqlCommand
dataAdapter.Fill(Dt)
Return Dt
End Function
Public Sub UpdateData(ByVal ID As Integer, ByVal Name As String, ByVal PositionID As Integer)
Dim sqlCommandString As String = "Update [ExampleData] Set [Name]=@Name, [PositionID]=@PositionID Where [ID]=@ID"
Dim sqlCommand As New SqlCommand(sqlCommandString, sqlConnection)
sqlCommand.Parameters.AddWithValue("@Name", Name)
sqlCommand.Parameters.AddWithValue("@PositionID", PositionID)
sqlCommand.Parameters.AddWithValue("@ID", ID)
sqlConnection.Open()
sqlCommand.ExecuteNonQuery()
sqlConnection.Close()
End Sub
Private Function TableExist(ByVal TableName As String) As Boolean
Dim sqlCommandString As String = String.Format("If Object_ID('{0}', 'U') Is Not Null Select 'true' Else Select 'false'", TableName)
Dim sqlCommand As New SqlCommand(sqlCommandString, sqlConnection)
sqlConnection.Open()
Dim haveTable As Boolean = Convert.ToBoolean(sqlCommand.ExecuteScalar())
sqlConnection.Close()
Return haveTable
End Function
Private Sub CreateExampleData()
Dim sqlCommandString As String = "Create Table [ExampleData] ([ID] int Identity(1,1) Primary Key Clustered, [Name] nvarchar(50), [PositionID] int)"
Dim sqlCommand As New SqlCommand(sqlCommandString, sqlConnection)
sqlConnection.Open()
sqlCommand.ExecuteNonQuery()
sqlConnection.Close()
FillExampleData()
End Sub
Private Sub FillExampleData()
Dim Dt As New DataTable()
Dt.Columns.Add(New DataColumn("Name", GetType(String)))
Dt.Columns.Add(New DataColumn("PositionID", GetType(Integer)))
For i As Integer = 0 To 9
Dim Dr As DataRow = Dt.NewRow()
Dr("Name") = String.Format("Tungman{0}", (i + 1).ToString())
Dr("PositionID") = 1
Dt.Rows.Add(Dr)
Next
Dim sqlCommandString As String = "Insert Into [ExampleData] ([Name], [PositionID]) Values (@Name, @PositionID)"
Dim sqlCommand As New SqlCommand(sqlCommandString, sqlConnection)
sqlCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name")
sqlCommand.Parameters.Add("@PositionID", SqlDbType.Int, 4, "PositionID")
Dim dataAdapter As New SqlDataAdapter()
dataAdapter.InsertCommand = sqlCommand
dataAdapter.Update(Dt)
End Sub
Private Sub CreateDropDropListData()
Dim sqlCommandString As String = "Create Table [DropDropListData] ([PositionID] int Identity(1,1) Primary Key Clustered, [PositionName] nvarchar(50))"
Dim sqlCommand As New SqlCommand(sqlCommandString, sqlConnection)
sqlConnection.Open()
sqlCommand.ExecuteNonQuery()
sqlConnection.Close()
FillDropDropListData()
End Sub
Private Sub FillDropDropListData()
Dim Dt As New DataTable()
Dt.Columns.Add(New DataColumn("PositionName", GetType(String)))
For i As Integer = 0 To 2
Dim Dr As DataRow = Dt.NewRow()
Dr("PositionName") = String.Format("Position{0}", (i + 1).ToString())
Dt.Rows.Add(Dr)
Next
Dim sqlCommandString As String = "Insert Into [DropDropListData] ([PositionName]) Values (@PositionName)"
Dim sqlCommand As New SqlCommand(sqlCommandString, sqlConnection)
sqlCommand.Parameters.Add("@PositionName", SqlDbType.NVarChar, 50, "PositionName")
Dim dataAdapter As New SqlDataAdapter()
dataAdapter.InsertCommand = sqlCommand
dataAdapter.Update(Dt)
End Sub
End Class
class นี้ใช้สำหรับติดต่อฐานข้อมูล (query, update) โดยมันจะสร้าง table ให้ใหม่สอง table
Imports System.Data
Partial Class GridViewDetailView
Inherits System.Web.UI.Page
Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load
Dim data As New ExampleData()
GridView1.DataSource = data.GetAllData()
GridView1.DataKeyNames = New String() {"ID"}
GridView1.DataBind()
DetailsView1.DataKeyNames = New String() {"ID"}
End Sub
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
e.Row.Attributes.Add("onclick", Page.ClientScript.GetPostBackEventReference(DirectCast(sender, GridView), String.Format("Select${0}", e.Row.RowIndex.ToString())))
e.Row.Attributes.Add("onmouseover", "javascript:this.style.backgroundColor='#EFF3FB'; this.style.cursor='pointer'")
e.Row.Attributes.Add("onmouseout", "javascript:this.style.backgroundColor='#FFFFFF';")
End If
End Sub
Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged
GridView1.SelectedRow.Attributes.Clear()
If GridView1.SelectedIndex >= 0 Then
Dim data As New ExampleData()
DetailsView1.DataSource = data.GetData(GridView1.DataKeys(GridView1.SelectedIndex).Value)
DetailsView1.DataBind()
End If
End Sub
Protected Sub DetailsView1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles DetailsView1.DataBound
Dim Drow As DataRowView = DirectCast(DetailsView1.DataItem, DataRowView)
Select Case DetailsView1.CurrentMode
Case DetailsViewMode.ReadOnly
DirectCast(DetailsView1.FindControl("LabelName"), Label).Text = Drow("Name")
DirectCast(DetailsView1.FindControl("LabelPosition"), Label).Text = Drow("PositionName")
Case (DetailsViewMode.Edit)
Dim txt1 As TextBox = DirectCast(DetailsView1.FindControl("TextBoxName"), TextBox)
txt1.Text = Drow("Name")
Dim ddlData As New ExampleData()
Dim ddl1 As DropDownList = DirectCast(DetailsView1.FindControl("DropDownListPosition"), DropDownList)
Dim dt As DataTable = ddlData.GetDropDownListData()
For Each dr As DataRow In dt.Rows
Dim lt As New ListItem()
lt.Text = dr("PositionName").ToString()
lt.Value = dr("PositionID").ToString()
If CInt(Drow("PositionID").ToString()) = CInt(dr("PositionID").ToString()) Then
lt.Selected = True
End If
ddl1.Items.Add(lt)
Next
End Select
End Sub
Protected Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DetailsView1.ItemUpdating
Dim id As Integer = CInt(DetailsView1.Rows(0).Cells(1).Text)
Dim txt1 As TextBox = DirectCast(DetailsView1.FindControl("TextBoxName"), TextBox)
Dim ddl1 As DropDownList = DirectCast(DetailsView1.FindControl("DropDownListPosition"), DropDownList)
Dim data As New ExampleData()
data.UpdateData(id, txt1.Text, CInt(ddl1.SelectedValue))
DetailsView1.ChangeMode(DetailsViewMode.ReadOnly)
DetailsView1.DataSource = data.GetData(GridView1.DataKeys(GridView1.SelectedIndex).Value)
DetailsView1.DataBind()
GridView1.DataSource = data.GetAllData()
GridView1.DataBind()
End Sub
Protected Sub DetailsView1_ModeChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewModeEventArgs) Handles DetailsView1.ModeChanging
Select Case e.NewMode
Case (DetailsViewMode.Edit)
DetailsView1.ChangeMode(DetailsViewMode.Edit)
Case (DetailsViewMode.ReadOnly)
DetailsView1.ChangeMode(DetailsViewMode.ReadOnly)
End Select
Dim data As New ExampleData()
DetailsView1.DataSource = data.GetData(GridView1.DataKeys(GridView1.SelectedIndex).Value)
DetailsView1.DataBind()
End Sub
End Class
Load balance : Server 03
Code (ASP)
wm กับ avatar ใหม่ สุดยอดดดดดดดดดดด 
