|
Já data v Accesu aktualizuji takhle
Dim strQuery As String = "SELECT * FROM tbl_Activity WHERE ID=" & ID
Dim ColumnName as String = "status"
Dim newValue as String = "OK"
UpdateDB(strQuery, ColumnName, newNalue)
Public Sub UpdateDB(ByRef Query As String, ByRef CN1 As String, ByRef Value1 As Object)
Try
Using Connection As New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = Data.mdb")
Dim dt As DataTable = New DataTable
Dim da As New OleDbDataAdapter(Query, Connection)
Dim R As Integer = 0
da.Fill(dt)
For Each DR As DataRow In dt.Rows
dt.Rows(R).Item(CN1) = Value1
R += 1
Next
Dim cmd As New OleDbCommandBuilder(da)
da.Update(dt)
dt.AcceptChanges()
End Using
Catch ex As Exception
cError.Message(ex.GetBaseException.ToString)
End Try
End Sub
V novém projektu jsem si na to udělal třídu, abych to mohl volat jednodušeji a universálně. Já mám v každé tabulce sloupec "ID" který mi identifikuje řádek, přesto snad není problém si to upravit
Public Class c_Data
#Region "Delete"
Public ReadOnly Property DeleteMulti(ByVal Where As String, ByVal Tablename As String) As Boolean
Get
Dim SQLstring As String = "DELETE * FROM " & Tablename & " WHERE " & Where
Return cmdTable(SQLstring)
End Get
End Property
#End Region
#Region "Update"
Public ReadOnly Property Update(ByVal ID As String, ByVal NewValue As String, ByVal Tablename As String, ByVal ColumnName As String) As Boolean
Get
Dim SQLstring As String = "UPDATE " & Tablename & " SET " & ColumnName & "='" & NewValue.Trim & "' WHERE ID='" & ID & "'"
Return cmdTable(SQLstring)
End Get
End Property
Public ReadOnly Property Update(ByVal ID As String, ByVal NewValue As Long, ByVal Tablename As String, ByVal ColumnName As String) As Boolean
Get
Dim SQLstring As String = "UPDATE " & Tablename & " SET " & ColumnName & "='" & NewValue & "' WHERE ID='" & ID & "'"
Return cmdTable(SQLstring)
End Get
End Property
Public ReadOnly Property Update(ByVal ID As String, ByVal NewValue As Single, ByVal Tablename As String, ByVal ColumnName As String) As Boolean
Get
Dim SQLstring As String = "UPDATE " & Tablename & " SET " & ColumnName & "='" & NewValue & "' WHERE ID='" & ID & "'"
Return cmdTable(SQLstring)
End Get
End Property
Public ReadOnly Property Update(ByVal ID As String, ByVal NewValue As Boolean, ByVal Tablename As String, ByVal ColumnName As String) As Boolean
Get
Dim SQLstring As String = "UPDATE " & Tablename & " SET " & ColumnName & "='" & NewValue & "' WHERE ID='" & ID & "'"
Return cmdTable(SQLstring)
End Get
End Property
Public ReadOnly Property Update(ByVal ID As String, ByVal NewValue As Date, ByVal Tablename As String, ByVal ColumnName As String) As Boolean
Get
Dim SQLstring As String = "UPDATE " & Tablename & " SET " & ColumnName & "='" & NewValue & "' WHERE ID='" & ID & "'"
Return cmdTable(SQLstring)
End Get
End Property
Public ReadOnly Property Update(ByVal ID As Long, ByVal NewValue As String, ByVal Tablename As String, ByVal ColumnName As String) As Boolean
Get
Dim SQLstring As String = "UPDATE " & Tablename & " SET " & ColumnName & "='" & NewValue.Trim & "' WHERE ID=" & ID
Return cmdTable(SQLstring)
End Get
End Property
Public ReadOnly Property Update(ByVal ID As Long, ByVal NewValue As Long, ByVal Tablename As String, ByVal ColumnName As String) As Boolean
Get
Dim SQLstring As String = "UPDATE " & Tablename & " SET " & ColumnName & "='" & NewValue & "' WHERE ID='" & ID & "'"
Return cmdTable(SQLstring)
End Get
End Property
Public ReadOnly Property Update(ByVal ID As Long, ByVal NewValue As Single, ByVal Tablename As String, ByVal ColumnName As String) As Boolean
Get
Dim SQLstring As String = "UPDATE " & Tablename & " SET " & ColumnName & "='" & NewValue & "' WHERE ID='" & ID & "'"
Return cmdTable(SQLstring)
End Get
End Property
Public ReadOnly Property Update(ByVal ID As Long, ByVal NewValue As Boolean, ByVal Tablename As String, ByVal ColumnName As String) As Boolean
Get
Dim SQLstring As String = "UPDATE " & Tablename & " SET " & ColumnName & "='" & NewValue & "' WHERE ID='" & ID & "'"
Return cmdTable(SQLstring)
End Get
End Property
Public ReadOnly Property Update(ByVal ID As Long, ByVal NewValue As Date, ByVal Tablename As String, ByVal ColumnName As String) As Boolean
Get
Dim SQLstring As String = "UPDATE " & Tablename & " SET " & ColumnName & "='" & NewValue & "' WHERE ID='" & ID & "'"
Return cmdTable(SQLstring)
End Get
End Property
Public ReadOnly Property UpdateMulti(ByVal Where As String, ByVal NewValue As Long, ByVal Tablename As String, ByVal ColumnName As String) As Boolean
Get
Dim SQLstring As String = "UPDATE " & Tablename & " SET " & ColumnName & "=" & NewValue & " WHERE " & Where
Return cmdTable(SQLstring)
End Get
End Property
Public ReadOnly Property UpdateMulti(ByVal Where As String, ByVal NewValue As Boolean, ByVal Tablename As String, ByVal ColumnName As String) As Boolean
Get
Dim SQLstring As String = "UPDATE " & Tablename & " SET " & ColumnName & "=" & NewValue & " WHERE " & Where
Return cmdTable(SQLstring)
End Get
End Property
#End Region
#Region "Action"
Private ReadOnly Property cmdTable(ByVal SQLstring As String) As Boolean
Get
'Debug.print("cmdTable" & vbTab & SQLstring)
Dim Connection As New System.Data.OleDb.OleDbConnection With {.ConnectionString = My.Settings.MSConnectionString}
Dim cmd As New System.Data.OleDb.OleDbCommand With
{.CommandType = System.Data.CommandType.Text,
.Connection = Connection,
.CommandText = SQLstring}
Try
Connection.Open()
cmd.ExecuteNonQuery()
Connection.Close()
cmd.Cancel()
Return True
Catch ex As Exception
cMessage.ErrorMsg(Err.Description, "Chyba při update:" & SQLstring)
Return False
End Try
End Get
End Property
#End Region
End Class
|