100分 求通过DataSet 更新数据库中数据的标准代码
100分 求通过DataSet 更新数据库中数据的标准代码
[解决办法]
Imports System
Imports System.Data
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim sConnectionString As String
' Modify the following code to correctly connect to your SQL Server.
sConnectionString = "Password=StrongPassword;User ID=UserName; " & _
"Initial Catalog=pubs; " & _
"Data Source=(local) "
Dim objConn As New SqlConnection(sConnectionString)
objConn.Open()
' Create an instance of a DataAdapter.
Dim daAuthors As _
New SqlDataAdapter( "Select * From Authors ", objConn)
' Create an instance of a DataSet, and retrieve data from the Authors table.
Dim dsPubs As New DataSet( "Pubs ")
daAuthors.FillSchema(dsPubs, SchemaType.Source, "Authors ")
daAuthors.Fill(dsPubs, "Authors ")
'*****************
'BEGIN ADD CODE
' Create a new instance of a DataTable
Dim tblAuthors As DataTable
tblAuthors = dsPubs.Tables( "Authors ")
Dim drCurrent As DataRow
' Obtain a new DataRow object from the DataTable.
drCurrent = tblAuthors.NewRow()
' Set the DataRow field values as necessary.
drCurrent( "au_id ") = "993-21-3427 "
drCurrent( "au_fname ") = "George "
drCurrent( "au_lname ") = "Johnson "
drCurrent( "phone ") = "800 226-0752 "
drCurrent( "address ") = "1956 Arlington Pl. "
drCurrent( "city ") = "Winnipeg "
drCurrent( "state ") = "MB "
drCurrent( "contract ") = 1
'Pass that new object into the Add method of the DataTable.Rows collection.
tblAuthors.Rows.Add(drCurrent)
MsgBox( "Add was successful. ")
'END ADD CODE
'*****************
'BEGIN EDIT CODE
drCurrent = tblAuthors.Rows.Find( "213-46-8915 ")
drCurrent.BeginEdit()
drCurrent( "phone ") = "342 " & drCurrent( "phone ").ToString.Substring(3)
drCurrent.EndEdit()
MsgBox( "Record edited successfully ")
'END EDIT CODE
'*****************
'BEGIN SEND CHANGES TO SQL SERVER
Dim objCommandBuilder As New SqlCommandBuilder(daAuthors)
daAuthors.Update(dsPubs, "Authors ")
MsgBox( "SQL Server updated successfully " & chr(13) & "Check Server explorer to see changes ")
' END SEND CHANGES TO SQL SERVER
'*****************
'BEGIN DELETE CODE
drCurrent = tblAuthors.Rows.Find( "993-21-3427 ")
drCurrent.Delete()
MsgBox( "Record deleted successfully ")
'END DELETE CODE
'*****************
' CLEAN UP SQL SERVER
daAuthors.Update(dsPubs, "Authors ")
MsgBox( "SQL Server updated successfully " & Chr(13) & Chr(13) & "Check Server Explorer to see changes ")
End Sub
End Module
[解决办法]
http://blog.programfan.com/article.asp?id=19282
