SqlDataAdapter 範例(ADO.NET)

引用來源
--
Imports System.Data.SqlClient
Public Class User_Edit
Inherits System.Windows.Forms.Form
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim ra As Integer

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

myConnection = New SqlConnection("server=.\SQLExpress;Initial Catalog=BillMasterDB;Integrated Security=True")
myCommand = New SqlCommand("update Tbl_UserDetails set Usr_Fname='" + txtFname.Text + "',Usr_Lname='" + txtLname.Text + "',Usr_Gender='" + txtGender.Text + "',Usr_DOB='" + txtDOB.Text + "',Usr_Address1='" + txtAddr1.Text + "',Usr_Address2='" + txtAddr2.Text + "',Usr_City='" + txtCity.Text + "',Usr_State='" + txtState.Text + "',Usr_Country='" + txtCountry.Text + "',Usr_Zip='" + txtZip.Text + "',Usr_Email='" + txtEmail.Text + "',Usr_Phone='" + txtPhone.Text + "',Usr_Mobile='" + txtMobile.Text + "' where Usr_Fname='" + txtFname.Text + "' ", myConnection)
Try
myConnection.Open()
ra = myCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
myConnection = New SqlConnection("server=.\SQLExpress;Initial Catalog=BillMasterDB;Integrated Security=True")
myCommand = New SqlCommand("update Tbl_User set Usr_Type='" + txtUsertype.Text + "',Usr_Pwd='" + txtPass.Text + "' where Usr_Name= '" + txtUsername.Text + "' ", myConnection)
Try
myConnection.Open()
ra = myCommand.ExecuteNonQuery()
MessageBox.Show("Updated")
myConnection.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub

Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
Me.Close()
End Sub

Private Sub User_Edit_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim conn As SqlConnection
Dim da As New SqlDataAdapter
Dim ds As New DataSet
conn = New SqlConnection("server=.\SQLExpress;Initial Catalog=BillMasterDB;Integrated Security=True")
conn.Open()
da = New SqlDataAdapter("select Tbl_User.Usr_Name,Tbl_User.Usr_Type,Tbl_User.Usr_Pwd,Tbl_UserDetails.Usr_Fname,Tbl_UserDetails.Usr_Lname,Tbl_UserDetails.Usr_Gender,Tbl_UserDetails.Usr_DOB,Tbl_UserDetails.Usr_Address1,Tbl_UserDetails.Usr_Address2,Tbl_UserDetails.Usr_City,Tbl_UserDetails.Usr_State,Tbl_UserDetails.Usr_Country,Tbl_UserDetails.Usr_Zip,Tbl_UserDetails.Usr_Email,Tbl_UserDetails.Usr_Phone,Tbl_UserDetails.Usr_Mobile from Tbl_User inner join Tbl_UserDetails on Tbl_User.Usr_Name=Tbl_UserDetails.Usr_Fname", conn)

ds = New DataSet("UserEdit")
da.Fill(ds, "UserEdit")
DataGridView1.DataSource = ds.Tables("UserEdit")
conn.Close()


txtUsername.Text = User_Management.DataGridView1.CurrentRow.Cells(0).Value.ToString()
txtUsertype.Text = User_Management.DataGridView1.CurrentRow.Cells(1).Value.ToString()
txtPass.Text = User_Management.DataGridView1.CurrentRow.Cells(2).Value.ToString()
txtFname.Text = User_Management.DataGridView1.CurrentRow.Cells(3).Value.ToString()
txtLname.Text = User_Management.DataGridView1.CurrentRow.Cells(4).Value.ToString()
txtGender.Text = User_Management.DataGridView1.CurrentRow.Cells(5).Value.ToString()
txtDOB.Text = User_Management.DataGridView1.CurrentRow.Cells(6).Value.ToString()
txtAddr1.Text = User_Management.DataGridView1.CurrentRow.Cells(7).Value.ToString()
txtAddr2.Text = User_Management.DataGridView1.CurrentRow.Cells(8).Value.ToString()
txtCity.Text = User_Management.DataGridView1.CurrentRow.Cells(9).Value.ToString()
txtState.Text = User_Management.DataGridView1.CurrentRow.Cells(10).Value.ToString()
txtCountry.Text = User_Management.DataGridView1.CurrentRow.Cells(11).Value.ToString()
txtZip.Text = User_Management.DataGridView1.CurrentRow.Cells(12).Value.ToString()
txtEmail.Text = User_Management.DataGridView1.CurrentRow.Cells(13).Value.ToString()
txtPhone.Text = User_Management.DataGridView1.CurrentRow.Cells(14).Value.ToString()
txtMobile.Text = User_Management.DataGridView1.CurrentRow.Cells(15).Value.ToString()


End Sub
End Class