hi there iam at the end of my project,but i have a tiny little problem here, i can be able SAVE,UPDATE and DELETE data in my database(ACCESS 2003)they work, but the results that is the UPDATED,SAVED OR DELETED data doesn't show until i close the form and load it again that's when the data that i saved or update shows or the data deleted disappears this is the last hurdle,i have to completing
I tried ds.acceptchanges() but it doesn't seem to do the trick
here are my codes
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.CONTACTSTableAdapter.Fill(Me.PEOPLEDataSet.CONTACTS)
connect()
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "PEOPLE")
Showitems()
ds.AcceptChanges()
Maxrows = ds.Tables("PEOPLE").Rows.Count
inc = -1
End Sub
Private Sub Showitems()
Dim ds As New DataSet
Dim dt As New DataTable
Dim cmd As New OleDb.OleDbCommand(sql, con)
Dim da As New OleDbDataAdapter
Dim ms As New System.IO.MemoryStream
ds.Tables.Add(dt)
da = New OleDbDataAdapter("SELECT*FROM CONTACTS", con)
da.Fill(dt)
txtAgentNumber.Text = dt.Rows(0).Item(1)
txtFirstName.Text = dt.Rows(0).Item(2)
txtMiddleName.Text = dt.Rows(0).Item(3)
txtSurName.Text = dt.Rows(0).Item(4)
cboGender.Text = dt.Rows(0).Item(5)
txtAddress.Text = dt.Rows(0).Item(6)
txtPhone.Text = dt.Rows(0).Item(7)
txtEmail.Text = dt.Rows(0).Item(8)
txtNotes.Text = dt.Rows(0).Item(9)
'picPhoto.Image = dt.Rows(0).Item(10)
ds.AcceptChanges()
con.Close()
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
If inc <> -1 Then
Dim cmd As New OleDb.OleDbCommand(sql, con)
Dim data As Byte() = File.ReadAllBytes(OpenFileDialog1.FileName)
con.Open()
sql = "INSERT INTO CONTACTS(AGENTNUMBER,FIRSTNAME,MIDDLENAME,SURNAME,GENDER,EMAIL,PHONE,ADDRESS,NOTES,PICTURE)"
sql = sql & "VALUES(@AGENTNUMBER,@FIRSTNAME,@MIDDLENAME,@SURNAME,@GENDER,@EMAIL,@PHONE,@ADDRESS,@NOTES,@PICTURE)"
cmd.CommandText = sql
cmd.Connection = con
cmd.Parameters.AddWithValue("@AGENTNUMBER", txtAgentNumber.Text)
cmd.Parameters.AddWithValue("@FIRSTNAME", txtFirstName.Text)
cmd.Parameters.AddWithValue("@MIDDLENAME", txtMiddleName.Text)
cmd.Parameters.AddWithValue("@SURNAME", txtSurName.Text)
cmd.Parameters.AddWithValue("@GENDER", cboGender.Text)
cmd.Parameters.AddWithValue("@EMAIL", txtEmail.Text)
cmd.Parameters.AddWithValue("@PHONE", txtPhone.Text)
cmd.Parameters.AddWithValue("@ADDRESS", txtAddress.Text)
cmd.Parameters.AddWithValue("@NOTES", txtNotes.Text)
cmd.Parameters.AddWithValue("@PICTURE", data)
cmd.ExecuteNonQuery()
cmd.Dispose()
MsgBox("NEW RECORD ADDED")
ds.AcceptChanges()
btnSave.Enabled = False
btnAdd.Enabled = True
btnEdit.Enabled = True
btnDelete.Enabled = True
End If
End Sub
Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
Dim cb As New OleDb.OleDbCommandBuilder(da)
If inc <> -1 Then
ds.Tables("PEOPLE").Rows(inc).Item(1) = txtAgentNumber.Text
ds.Tables("PEOPLE").Rows(inc).Item(2) = txtFirstName.Text
ds.Tables("PEOPLE").Rows(inc).Item(3) = txtMiddleName.Text
ds.Tables("PEOPLE").Rows(inc).Item(4) = txtSurName.Text
ds.Tables("PEOPLE").Rows(inc).Item(5) = cboGender.Text
ds.Tables("PEOPLE").Rows(inc).Item(6) = txtAddress.Text
ds.Tables("PEOPLE").Rows(inc).Item(7) = txtPhone.Text
ds.Tables("PEOPLE").Rows(inc).Item(8) = txtEmail.Text
ds.Tables("PEOPLE").Rows(inc).Item(9) = txtNotes.Text
Dim ms As New MemoryStream()
picPhoto.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp)
ds.Tables("PEOPLE").Rows(inc).Item(10) = ms.ToArray()
da.Update(ds, "PEOPLE")
MsgBox("DATA EDITED")
ds.AcceptChanges()
End If
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim con As New OleDbConnection
Dim cmd As New OleDbCommand
Dim ms As New MemoryStream
Dim bm As Bitmap = New Bitmap(picPhoto.Image)
bm.Save(ms, picPhoto.Image.RawFormat)
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\PEOPLE.mdb"
con.Open()
Using msS As MemoryStream = New MemoryStream()
Dim bmM As Bitmap = New Bitmap(picPhoto.Image)
bm.Save(ms, picPhoto.Image.RawFormat)
Dim arrPic() As Byte = ms.GetBuffer()
sql = "DELETE FROM CONTACTS WHERE AGENTNUMBER='" & txtAgentNumber.Text & "'"
cmd.CommandText = sql
cmd.Connection = con
cmd.ExecuteNonQuery()
If MessageBox.Show("Do you really want to Delete this Record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
MsgBox("Operation Cancelled")
Exit Sub
End If
con.Close()
End Using
End Sub
Private Sub NavigateRecords()
Dim da As New OleDb.OleDbDataAdapter("SELECT*FROM CONTACTS", con)
Dim dt As New DataTable
da.Fill(dt)
If ds.Tables("PEOPLE").Rows.Count > 0 Then
txtAgentNumber.Text = ds.Tables("PEOPLE").Rows(inc).Item(1)
txtFirstName.Text = ds.Tables("PEOPLE").Rows(inc).Item(2)
txtMiddleName.Text = ds.Tables("PEOPLE").Rows(inc).Item(3)
txtSurName.Text = ds.Tables("PEOPLE").Rows(inc).Item(4)
cboGender.Text = ds.Tables("PEOPLE").Rows(inc).Item(5)
txtAddress.Text = ds.Tables("PEOPLE").Rows(inc).Item(6)
txtPhone.Text = ds.Tables("PEOPLE").Rows(inc).Item(7)
txtEmail.Text = ds.Tables("PEOPLE").Rows(inc).Item(8)
txtNotes.Text = ds.Tables("PEOPLE").Rows(inc).Item(9)
Dim bytes As [Byte]() = ds.Tables("PEOPLE").Rows(inc).Item(10)
Dim ms As New MemoryStream(bytes)
picPhoto.Image = Image.FromStream(ms)
ds.AcceptChanges()
End If
End Sub
note all this code work