使用数据集通过oledbdataadapter更新数据库. [英] Update database through oledbdataadapter using dataset.

问题描述

大家好，我正在做一个大学项目.
我期待执行数据删除和删除等操作.更新.
我正在使用DataSet & OleDbDataAdapter .
如果我执行删除或更新，则更改不会立即反映在数据库中.
只有关闭应用程序&才能看到更改.再次重新启动.

请帮助我如何使用带有数据集的oledbdataadapter更新数据源(数据库).

预先谢谢你

在form_load事件上:

Hi guys I am working on a college project.
I am looking forward to perform data manipulation operations such as delete & update.
I am using DataSet & OleDbDataAdapter .
If I perform the delete or update the changes are not immediately reflected in the database.
I can see the changes only if I close the application & again restart it again.

Please help me how to update datasource(database) using oledbdataadapter with dataset.

Thank you in advance

on form_load event:

connection = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;data source= D:\Project(Pritam)\Networking Banking System\ActualDatabase.mdb")
        sql_first = "Select * from ClientInfo "
        sql_second = "Select * from Client_Account_Status"
        connection.Open()
        adapter = New OleDbDataAdapter(sql_first, connection)
        adapter.Fill(dataset, "ClientInfo")
        adapter.SelectCommand.CommandText = sql_second
        adapter.Fill(dataset, "Client_Account_Status")
        adapter.Dispose()
        connection.Close()
        i = 0
        last_record = ((dataset.Tables("ClientInfo").Rows.Count) - 1)
        final()
end sub
Public Sub final()
        Try
            Dim title As String = dataset.Tables("ClientInfo").Rows(i).Item("Title")
            Dim first_name As String = dataset.Tables("ClientInfo").Rows(i).Item("First_Name")
            Dim last_name As String = dataset.Tables("ClientInfo").Rows(i).Item("Last_Name")
            txtAccountHolderName.Text = title & " " & first_name & " " & last_name
            txtAccountNumber.Text = dataset.Tables("ClientInfo").Rows(i).Item("Account_No")
            txtAccountType.Text = dataset.Tables("ClientInfo").Rows(i).Item("Account_Type")
            txtCurrencyType.Text = dataset.Tables("ClientInfo").Rows(i).Item("Currency_Type")
            txtAccountBalance.Text = dataset.Tables("Client_Account_Status").Rows(i).Item("Balance")
            txtSearch.Text = txtAccountNumber.Text
        Catch ex As Exception
            MsgBox("No records to Delete", MsgBoxStyle.Critical, "Notice!")
            frmServerMainMenu.Show()
            Me.Close()
        End Try
    End Sub

---编辑kschuler:将您的代码从您的评论复制到问题中.当人们看到问题中格式化的代码而不是无法格式化的注释时，人们可以更轻松地提供帮助.

---EDIT kschuler: Copied your code from your comment to the question. It''s much easier for people to help when they can see the code formatted inside the question instead of the comments where it can''t format.

推荐答案

如何您正在更新数据库吗?因为您提供的代码没有显示您如何将更改推回数据库.使用OleDbDataAdapter创建数据集时，它将创建静态副本.它绝不连接到实际数据库.因此，除非您要在其他地方更新Db，否则我不知道您重新启动应用程序时所做的更改.

但是，仅更新DataAdapter并不是您唯一需要做的事情.设置表映射非常重要.没有它，DataAdapter将不知道如何更新数据库.

这是我过去使用过的一些示例代码:

How are you updating the database? Because the code you provided doesn''t show how you are pushing the changes back to the database. When you create a dataset using the OleDbDataAdapter, it makes a static copy. It is in no way connected to the actual database. So, unless you''re updating the Db somewhere else, I don''t know how you''re changes would even be there when you restart the application.

But, just updating the DataAdapter isn''t the only thing you need to do. It''s very important to set up the table mapping. Without it, the DataAdapter doesn''t know how to update the database.

Here''s some example code that I have used in the past:

Dim openedConnectionString As Boolean = False
If Not connectionString.State = ConnectionState.Open Then
    connectionString.Open()
    openedConnectionString = True
End If

Dim insertionDS As New DataSet
Dim newDataAdapter As OleDbDataAdapter
Dim sql As String = "SELECT * FROM [" & ActiveProject.Identifier & " Populations] ORDER BY ID;"

newDataAdapter = New OleDb.OleDbDataAdapter(sql, connectionString)

'Create mapping
newDataAdapter.TableMappings.Add("Table", "Populations")
With newDataAdapter.TableMappings(0).ColumnMappings
    .Add("ID", "ID")
    .Add("Name", "Name")
    .Add("Char ID", "Char ID")
    .Add("Stratum", "Stratum")
    .Add("PopLevel", "PopLevel")
End With

'Make sure field names are bracketed
Dim cb As New OleDbCommandBuilder(newDataAdapter)
cb.QuotePrefix = " ["
cb.QuoteSuffix = "] "

'Add Table
newDataAdapter.Fill(insertionDS)

'Get last ID number to increment
Dim lastIndex As Integer = 0
With insertionDS.Tables("Populations")
    If .Rows.Count <> 0 Then
        lastIndex = .Rows(.Rows.Count - 1).Item("ID")
    End If

    'Insert record
    Dim newRow As DataRow = .Rows.Add
    newRow.BeginEdit()
    newRow.Item("ID") = lastIndex + 1
    newRow.Item("Name") = Population
    newRow.Item("Char ID") = ""
    newRow.Item("Stratum") = Stratum
    newRow.Item("PopLevel") = Level
    newRow.EndEdit()
End With

'Save changes
'Update and Delete aren't actually necessary for this example...but you may need to create them
newDataAdapter.UpdateCommand = cb.GetUpdateCommand
newDataAdapter.InsertCommand = cb.GetInsertCommand
newDataAdapter.DeleteCommand = cb.GetDeleteCommand
newDataAdapter.Update(insertionDS)

newDataAdapter.Dispose()

'If we opened the connectionstring, then close it
If openedConnectionString Then
    connectionString.Close()
End If

这篇关于使用数据集通过oledbdataadapter更新数据库.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！