Save Changes in VB.NET 2005 with Access 2003 --- Not using any Binding Navigator

  • Thread starter Thread starter ankz
  • Start date Start date
A

ankz

Hi Guys

I have got 3 tables -[Personal Details, Course, Picture] in Access
Database with Student ID as primary Key in all and all tables are
linked with 1 to 1 relationship.

I have code the everying with manual program to access information of
tables on Windows Form. It is working properly. i can see all the
records properly.

I am not using Data Binding Navigator.

I have created SAVE Button on Form and i want that button to update
data/records in main database. [ If i change student Addess on form
then with SAVE Button click event, i want that to update in database]

Can you tell me how i can do it. i have been trying for so many weeks.

Please do help me.

here is the code of the form which is working properly at displaying
data from DB.

""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

Imports System.Data

Public Class ExistingStudentForm
Dim dt As New DataTable
Dim rowIndex As Integer = 0

Private Sub btnBack_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs)
Dim frmMenu As New Menu

frmMenu.Show()
Me.Hide()

End Sub

Private Sub PicUpload_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles PicUpload.Click
OpenFileDialog1.InitialDirectory = "F:\Ankit\Tameside College _
HND Year 1\Unit 7_Visual Basic.NET\SIRS_Use THIS ONE\Photos"
OpenFileDialog1.ShowDialog()
OpenFileDialog1.Filter =
"Bitmaps(*.bmp)|*.bmp|JPEG(*.jpg)|*.jpg|GIF(*.gif)|*.gif"

picperson.Image = Image.FromFile(OpenFileDialog1.FileName)
picperson.SizeMode = PictureBoxSizeMode.CenterImage

End Sub


Sub updateTextBoxes()
txtStudentID.Text =
CStr(dt.Rows(rowIndex)("PersonalDetail.StudentId"))
txtStudentName.Text = CStr(dt.Rows(rowIndex)("Name"))
txtAddress.Text = CStr(dt.Rows(rowIndex)("Address"))
txtTown.Text = CStr(dt.Rows(rowIndex)("Town"))

txtCourse.Text = CStr(dt.Rows(rowIndex)("CourseName"))
txtDoB.Text = CStr(dt.Rows(rowIndex)("DOB"))
txtSex.Text = CStr(dt.Rows(rowIndex)("Sex"))
txtTelephone.Text = CStr(dt.Rows(rowIndex)("Telephone"))
txtContactName.Text = CStr(dt.Rows(rowIndex)("ContactName"))
txtContactNo.Text = CStr(dt.Rows(rowIndex)("ContactNumber"))
picperson.Image =
Image.FromFile(CStr(dt.Rows(rowIndex)("FilePath")))

End Sub

Private Sub ExistingStudentForm_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load

Dim constr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Documents and Settings\Ankit\Desktop\Tameside College _ HND
Year 1\Unit 7_Visual Basic.NET\SIRS_Use THIS ONE\Student
Database\Students.mdb"

Dim SqlStr As String = "SELECT PersonalDetail.*, Course.*,
Picture.* FROM (PersonalDetail INNER JOIN Picture ON
PersonalDetail.StudentId = Picture.StudentId) INNER JOIN Course ON
PersonalDetail.StudentId = Course.StudentId"

Dim dataAdapter As New OleDb.OleDbDataAdapter(SqlStr, constr)

dataAdapter.Fill(dt)
dataAdapter.Dispose()
updateTextBoxes()


End Sub

Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles btnPrevious.Click
If (rowIndex > 0) Then
rowIndex = rowIndex - 1
updateTextBoxes()
End If
End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnNext.Click
If (rowIndex < dt.Rows.Count - 1) Then
rowIndex += 1
updateTextBoxes()
End If
End Sub


Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnSearch.Click
Dim studentid As String

Dim studentFound As Boolean = False

studentid = InputBox("Enter Student ID")
For i As Integer = 0 To (dt.Rows.Count - 1)
If CStr(dt.Rows(i)("personalDetail.StudentId") = studentid)
Then
studentFound = True
rowIndex = i
updateTextBoxes()
End If
Next

If (Not studentFound) Then
MsgBox("System Cannot find requested Student Information",
0, "Not in Table")
End If
End Sub


Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSave.Click












End Sub
End Class

"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""


Thanks.

Do reply either here or send me e mail me at
(e-mail address removed) / (e-mail address removed)

Cheers.
 
Ankz,

What is the advantage in your idea from a 1 to 1 relation instead of just
set it in the datarow

Now you need at least 9 datatable accesses to do an update.

Cor

ankz said:
Hi Guys

I have got 3 tables -[Personal Details, Course, Picture] in Access
Database with Student ID as primary Key in all and all tables are
linked with 1 to 1 relationship.

I have code the everying with manual program to access information of
tables on Windows Form. It is working properly. i can see all the
records properly.

I am not using Data Binding Navigator.

I have created SAVE Button on Form and i want that button to update
data/records in main database. [ If i change student Addess on form
then with SAVE Button click event, i want that to update in database]

Can you tell me how i can do it. i have been trying for so many weeks.

Please do help me.

here is the code of the form which is working properly at displaying
data from DB.

""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

Imports System.Data

Public Class ExistingStudentForm
Dim dt As New DataTable
Dim rowIndex As Integer = 0

Private Sub btnBack_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs)
Dim frmMenu As New Menu

frmMenu.Show()
Me.Hide()

End Sub

Private Sub PicUpload_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles PicUpload.Click
OpenFileDialog1.InitialDirectory = "F:\Ankit\Tameside College _
HND Year 1\Unit 7_Visual Basic.NET\SIRS_Use THIS ONE\Photos"
OpenFileDialog1.ShowDialog()
OpenFileDialog1.Filter =
"Bitmaps(*.bmp)|*.bmp|JPEG(*.jpg)|*.jpg|GIF(*.gif)|*.gif"

picperson.Image = Image.FromFile(OpenFileDialog1.FileName)
picperson.SizeMode = PictureBoxSizeMode.CenterImage

End Sub


Sub updateTextBoxes()
txtStudentID.Text =
CStr(dt.Rows(rowIndex)("PersonalDetail.StudentId"))
txtStudentName.Text = CStr(dt.Rows(rowIndex)("Name"))
txtAddress.Text = CStr(dt.Rows(rowIndex)("Address"))
txtTown.Text = CStr(dt.Rows(rowIndex)("Town"))

txtCourse.Text = CStr(dt.Rows(rowIndex)("CourseName"))
txtDoB.Text = CStr(dt.Rows(rowIndex)("DOB"))
txtSex.Text = CStr(dt.Rows(rowIndex)("Sex"))
txtTelephone.Text = CStr(dt.Rows(rowIndex)("Telephone"))
txtContactName.Text = CStr(dt.Rows(rowIndex)("ContactName"))
txtContactNo.Text = CStr(dt.Rows(rowIndex)("ContactNumber"))
picperson.Image =
Image.FromFile(CStr(dt.Rows(rowIndex)("FilePath")))

End Sub

Private Sub ExistingStudentForm_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load

Dim constr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Documents and Settings\Ankit\Desktop\Tameside College _ HND
Year 1\Unit 7_Visual Basic.NET\SIRS_Use THIS ONE\Student
Database\Students.mdb"

Dim SqlStr As String = "SELECT PersonalDetail.*, Course.*,
Picture.* FROM (PersonalDetail INNER JOIN Picture ON
PersonalDetail.StudentId = Picture.StudentId) INNER JOIN Course ON
PersonalDetail.StudentId = Course.StudentId"

Dim dataAdapter As New OleDb.OleDbDataAdapter(SqlStr, constr)

dataAdapter.Fill(dt)
dataAdapter.Dispose()
updateTextBoxes()


End Sub

Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles btnPrevious.Click
If (rowIndex > 0) Then
rowIndex = rowIndex - 1
updateTextBoxes()
End If
End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnNext.Click
If (rowIndex < dt.Rows.Count - 1) Then
rowIndex += 1
updateTextBoxes()
End If
End Sub


Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnSearch.Click
Dim studentid As String

Dim studentFound As Boolean = False

studentid = InputBox("Enter Student ID")
For i As Integer = 0 To (dt.Rows.Count - 1)
If CStr(dt.Rows(i)("personalDetail.StudentId") = studentid)
Then
studentFound = True
rowIndex = i
updateTextBoxes()
End If
Next

If (Not studentFound) Then
MsgBox("System Cannot find requested Student Information",
0, "Not in Table")
End If
End Sub


Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSave.Click












End Sub
End Class

"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""


Thanks.

Do reply either here or send me e mail me at
(e-mail address removed) / (e-mail address removed)

Cheers.
Click to expand...
 
Back
Top