P
Paul
I have an application that I am tryign to modify, I never wrote the
original so need help with my modifications.
Currently the app reads and writes to an Access database with one table
'pupils' I would like to keep this table but only ever update the
records in this table, but at the same time when I create a new record
it puts the current data into a different table 'history' and then
allows me to update the data in the 'pupils' table.
I have code for reading and writing to the 'pupils' table and creating a
new record in the 'pupils' table but when I edit is to look at the
'history' table it falls over. Please help, my code is below:
Public Overloads Function GetPupils() As DataSet
Return Me.GetPupils("lastname")
End Function
Public Overloads Function GetPupils(ByVal sortfield As String) As
DataSet
Dim conn As OleDb.OleDbConnection = GetConnection()
Try
Dim ds As New DataSet
Dim sql As String = "select
firstname,lastname,username,formgroup,yearofintake,idnum from pupils
order by " + sortfield
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Try
da.Fill(ds, "pupils")
Finally
da.Dispose()
End Try
Return ds
Finally
conn.Close()
conn.Dispose()
End Try
End Function
Public Function GetPupil(ByVal id As Integer) As DataSet
'Return a dataset representing a single pupil
Dim conn As OleDb.OleDbConnection = GetConnection()
Try
Dim sql As String = "Select * from pupils where idnum = " &
id.ToString
Dim sa As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Dim ds As New DataSet
Try
sa.Fill(ds, "pupils")
Finally
sa.Dispose()
End Try
Return ds
Finally
conn.Close()
conn.Dispose()
End Try
End Function
Public Function GetNewPupil() As DataSet
'Return a dataset representing a single pupil
Dim conn As OleDb.OleDbConnection = GetConnection()
Try
Dim sql As String = "Select * from pupils where idnum = -1"
Dim sa As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Dim ds As New DataSet
Try
sa.Fill(ds, "pupils") ' returns an empty dataset but
with the correct structure
Dim dr As DataRow = ds.Tables(0).NewRow ' creates a
new blank row
ds.Tables(0).Rows.Add(dr) ' add the blank row to the
dataset
Finally
sa.Dispose()
End Try
Return ds ' return the dataset containing one new, blank pupil
Finally
conn.Close()
conn.Dispose()
End Try
End Function
Public Sub SavePupils(ByVal ds As DataSet)
Dim conn As OleDb.OleDbConnection = GetConnection()
Try
Dim sql As String = "select firstname,lastname,idnum from
pupils"
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Try
Dim cb As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da)
If ds.HasChanges Then
da.Update(ds, "pupils")
ds.AcceptChanges()
End If
Finally
da.Dispose()
End Try
Finally
conn.Close()
conn.Dispose()
End Try
End Sub
Public Sub SavePupil(ByVal ds As DataSet)
'Update a dataset representing pupils
Dim conn As OleDb.OleDbConnection = GetConnection()
Try
Dim sql As String = "Select * from pupils"
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Try
Dim cb As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da)
If ds.HasChanges Then
da.Update(ds, "pupils")
ds.AcceptChanges()
End If
Finally
da.Dispose()
End Try
Finally
conn.Close()
conn.Dispose()
End Try
End Sub
Thanks in Advance.
Paul.
original so need help with my modifications.
Currently the app reads and writes to an Access database with one table
'pupils' I would like to keep this table but only ever update the
records in this table, but at the same time when I create a new record
it puts the current data into a different table 'history' and then
allows me to update the data in the 'pupils' table.
I have code for reading and writing to the 'pupils' table and creating a
new record in the 'pupils' table but when I edit is to look at the
'history' table it falls over. Please help, my code is below:
Public Overloads Function GetPupils() As DataSet
Return Me.GetPupils("lastname")
End Function
Public Overloads Function GetPupils(ByVal sortfield As String) As
DataSet
Dim conn As OleDb.OleDbConnection = GetConnection()
Try
Dim ds As New DataSet
Dim sql As String = "select
firstname,lastname,username,formgroup,yearofintake,idnum from pupils
order by " + sortfield
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Try
da.Fill(ds, "pupils")
Finally
da.Dispose()
End Try
Return ds
Finally
conn.Close()
conn.Dispose()
End Try
End Function
Public Function GetPupil(ByVal id As Integer) As DataSet
'Return a dataset representing a single pupil
Dim conn As OleDb.OleDbConnection = GetConnection()
Try
Dim sql As String = "Select * from pupils where idnum = " &
id.ToString
Dim sa As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Dim ds As New DataSet
Try
sa.Fill(ds, "pupils")
Finally
sa.Dispose()
End Try
Return ds
Finally
conn.Close()
conn.Dispose()
End Try
End Function
Public Function GetNewPupil() As DataSet
'Return a dataset representing a single pupil
Dim conn As OleDb.OleDbConnection = GetConnection()
Try
Dim sql As String = "Select * from pupils where idnum = -1"
Dim sa As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Dim ds As New DataSet
Try
sa.Fill(ds, "pupils") ' returns an empty dataset but
with the correct structure
Dim dr As DataRow = ds.Tables(0).NewRow ' creates a
new blank row
ds.Tables(0).Rows.Add(dr) ' add the blank row to the
dataset
Finally
sa.Dispose()
End Try
Return ds ' return the dataset containing one new, blank pupil
Finally
conn.Close()
conn.Dispose()
End Try
End Function
Public Sub SavePupils(ByVal ds As DataSet)
Dim conn As OleDb.OleDbConnection = GetConnection()
Try
Dim sql As String = "select firstname,lastname,idnum from
pupils"
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Try
Dim cb As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da)
If ds.HasChanges Then
da.Update(ds, "pupils")
ds.AcceptChanges()
End If
Finally
da.Dispose()
End Try
Finally
conn.Close()
conn.Dispose()
End Try
End Sub
Public Sub SavePupil(ByVal ds As DataSet)
'Update a dataset representing pupils
Dim conn As OleDb.OleDbConnection = GetConnection()
Try
Dim sql As String = "Select * from pupils"
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Try
Dim cb As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da)
If ds.HasChanges Then
da.Update(ds, "pupils")
ds.AcceptChanges()
End If
Finally
da.Dispose()
End Try
Finally
conn.Close()
conn.Dispose()
End Try
End Sub
Thanks in Advance.
Paul.