Hey guys, something went really really wrong in the DataGridView with my final project :( it doesn't work the way I want it to be. I'm not sure what's going on please help me... I'm in really desperate need or else I might have to redo everything again. I'm currently using this as a reference: http://www.youtube.com/watch?v=bdpF6Dp4pFM
Customer name = txtstdid
Name = txtstdname
Gender/Sex = cbosex
Date of Birth = chodob1, chodob2, chodob3
Identification card number = txtic
Passport number = txtpassn
Phone number = txtpn
Date of depature = dod1, dod2, dod3
Address = txtaddress
Address at the point of arrival = txtaapoa
Databasegridview = dgvData
Public Class APAirPlane
Dim cnn As New OleDb.OleDbConnection
Private Sub btnclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclose.Click
Me.Close()
End Sub
Private Sub btnclear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclear.Click
Me.txtstdid.Text = ""
Me.txtstdname.Text = ""
Me.txtic.Text = ""
Me.txtpassn.Text = ""
Me.txtpn.Text = ""
Me.txtaddress.Text = ""
Me.txtaapoa.Text = ""
Me.txtstdid.Tag = ""
'enable button edit
Me.btnedit.Enabled = True
'set button add to add label
Me.btnadd.Text = "Add"
'
Me.txtstdid.Focus()
End Sub
Private Sub RefreshData()
If Not cnn.State = ConnectionState.Open Then
'open connection
cnn.Open()
End If
Dim da As New OleDb.OleDbDataAdapter("SELECT txtstdid as [ID], " & _
"txtstdname as [Name], cbosex as [Gender], chodob1 as [DD], chodob2 as [MM], chodob3 as [YYYY] " & _
"txtic as [Identification Number Card], txtpassn as [Passport Number], txtpn as [Phone Number] " & _
"dod1 as [DD1], dod2 as [MM2], dod3 as [YYYY3], txtaddress as [Address], txtaapoa [Address At Point Of Arrival] " & _
" FROM plane ORDER by txtstdid", cnn)
Dim dt As New DataTable
'fill data to datatable
da.Fill(dt)
'offer data in data table into datagridview
Me.dgvData.DataSource = dt
'close connection
cnn.Close()
End Sub
Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
Dim cmd As New OleDb.OleDbCommand
If Not cnn.State = ConnectionState.Open Then
'Open connection if it is not open yet
cnn.Open()
End If
cmd.Connection = cnn
'checkwhether add or update
If Me.txtstdid.Tag & "" = "" Then
'add new
'add data to table
cmd.CommandText = "INSERT INTO plane(txtstdid, txtstdname, cbosex, cbodob1, cbodob2, cbodob3, txtic, txtpassport, txtpassn, txtpn, dod1, dod2, dod3, txtaddress, txtaapoa, " & _
" VALUES(" & Me.txtstdid.Text & ",'" & Me.txtstdname.Text & "','" & Me.Combobox2.Text & "','" & Me.cbodob1.Text & "','" & Me.cbodob2.Text & "','" & Me.cbodob3.Text & "','" & Me.txtic.Text & "','" & Me.Label100.Text & "','" & Me.txtpassn.Text & "','" & Me.txtpn.Text & "','" & Me.dod1.Text & "','" & Me.dod2.Text & "','" & Me.dod3.Text & "','" & Me.txtaddress.Text & "','" & Me.txtaapoa.Text & "')"
cmd.ExecuteNonQuery()
Else
'update data in table
cmd.CommandText = "UPDATE Plane" & _
"SET stdid=" & Me.txtstdid.Text & _
", stdname='" & Me.txtstdname.Text & "'" & _
", ICNumber='" & Me.txtic.Text & "'" & _
", PassportNumber='" & Me.txtpassn.Text & "'" & _
", PhoneNumber='" & Me.txtpn.Text & "'" & _
", Address='" & Me.txtaddress.Text & "'" & _
", AddressAtPointOfArrival='" & Me.txtaapoa.Text & "'" & _
" WHERE stdid=" & Me.txtstdid.Tag
cmd.ExecuteNonQuery()
End If
'refresh data in list
RefreshData()
'clear form
Me.btnclear.PerformClick()
'close connection
cnn.Close()
End Sub
Private Sub FrontPage_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
cnn = New OleDb.OleDbConnection
cnn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & Application.StartupPath & "\data.mbd"
'
'get data into list
Me.RefreshData()
End Sub
Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
'check for the selected item in list
If Me.dgvData.Rows.Count > 0 Then
If Me.dgvData.SelectedRows.Count > 0 Then
Dim intstdid As Integer = Me.dgvData.SelectedRows(0).Cells("id").Value
'get data from database followed by id
'open connection
If Not cnn.State = ConnectionState.Open Then
cnn.Open()
End If
'Get data into database
Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM Plane " & _
" WHERE txtstdid=" & intstdid, cnn)
Dim dt As New DataTable
da.Fill(dt)
Me.txtstdid.Text = intstdid
Me.txtstdname.Text = dt.Rows(0).Item("stdname")
Me.Combobox2.Text = dt.Rows(0).Item("Gender")
Me.cbodob1.Text = dt.Rows(0).Item("DOB1")
Me.chodob2.Text = dt.Rows(0).Item("DOB2")
Me.chodob3.text = dt.Rows(0).Item("DOB3")
Me.txtic.Text = dt.Rows(0).Item("IC Number")
Me.txtpassn.Text = dt.Rows(0).Item("Passport Number")
Me.txtpn.Text = dt.Rows(0).Item("Phone Number")
Me.dod1.Text = dt.Rows(0).Item("DOP1")
Me.dod2.Text = dt.Rows(0).Item("DOP2")
Me.dod3.Text = dt.Rows(0).Item("DOD3")
Me.txtaddress.Text(dt.Rows(0).Item("Address")
Me.txtaapoa.Text = dt.Rows(0).Item("AAPOA")
'
'hide the id to be edited in TAG of txtstdid in case id is changed
Me.txtstdid.Tag = intstdid
'change button add to update
Me.btnadd.Text = "Update"
'disable button edit
Me.btnedit.Enabled = False
'close connection
cnn.Close()
End If
End If
End Sub
Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
'check for the selected item in list
If Me.dgvData.Rows.Count > 0 Then
If Me.dgvData.SelectedRows.Count > 0 Then
Dim intstdid As Integer = Me.dgvData.SelectedRows(0).Cells("id").Value
'open connection
If Not cnn.State = ConnectionState.Open Then
cnn.Open()
End If
'delete data
Dim cmd As New OleDb.OleDbCommand
cmd.Connection = cnn
cmd.CommandText = "DELETE FROM plane WHERE stdid=" & intstdid
cmd.ExecuteNonQuery()
'refresh data
Me.RefreshData()
'close connection
cnn.Close()
End If
End If
End Sub
End Class
