Click here to Skip to main content
15,031,687 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I use Visual Studio 2010 with VB.Net and Microsoft Access Database.I have form Loan and form Equipment. In form Equipment i have item Equipment ID, Equipment Item,Model,Description,Quantity,Serial No and Status. In form Loan i have Equipment ID, Name,Staff ID,Telephone No,Department Equipment Item,Date Loan,Date Return and Period. When user key in Equipment ID in form Loan the Equipment Item will be set automatic after key in all detail in form Equipment. Tq for helping.
Posted
Updated 7-Apr-13 21:55pm
v2
Comments
Anurag Sinha V 8-Apr-13 3:48am
   
What platform do you want to use?
via Backend? via Frontend?
Edit your question and please be more clear...Then people over here will be able to help you in the right direction..

-Anurag
tarhex 11-Apr-13 17:34pm
   
You want equipment details based on the ID entered?
hanigbl 12-Apr-13 1:22am
   
Yes. Can u help me? Tq
tarhex 12-Apr-13 8:15am
   
I can help you but I need to know where you want the details to be displayed. Is it on Textboxes or DataGridView
hanigbl 15-Apr-13 21:30pm
   
I want the details to be display on textboxes. Actually i use datagridview to display all item in form. Tq very much.

1 solution

Private Sub getDetails()



Dim ID As String = txtID.Text

Try


Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath & "\database\sample.accdb" + " ;Jet OLEDB:Database Password=sample;")


conn.Open()
Dim cmd As OleDbCommand = New OleDbCommand("SELECT ID,amt,last_pay_date FROM details WHERE ID= " & ID & " ", conn)

Dim reader As OleDbDataReader = cmd.ExecuteReader
reader.Read()



'display amount details
txtAmount.Text = reader(1)

'close the connection
conn.Close()

End If


Catch ex As Exception

Dim mybox As New myMsgbox

With mybox
.txtError.Text = "An error has occured, cancel and try again!"
.ShowDialog()
End With

End Try

End Sub
   
Comments
hanigbl 16-Apr-13 23:26pm
   
This code should place at my Equipment Item text box right? Sorry because im a new in this field. Tq.
hanigbl 17-Apr-13 1:46am
   
Dim cmd As OleDbCommand = New OleDbCommand("SELECT ID,amt,last_pay_date FROM details WHERE ID= " & ID & " ", conn)= This SELECT ID i must get all detail from form Equipemtn right?
tarhex 18-Apr-13 14:41pm
   
Dim cmd As OleDbCommand = New OleDbCommand("SELECT ID,amt,last_pay_date FROM details WHERE ID= " & ID & " ", conn)

In the code above, the "ID,amt,last_pay_date" are the columns (details) that can be displayed (in your case, equipment details). The "Details" is the name of the Table in the database.
hanigbl 21-Apr-13 21:23pm
   
I only want the Equipment Item in form Equipment display in form Loan when i key in the Equipment ID. The code u give me i have put in form Loan under Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click ? Tq for helping me.
tarhex 22-Apr-13 3:05am
   
Are you adding data to database or retrieving from database?

The sample code I posted is to retrieve from database. post your code below let me see so that I can really help.
hanigbl 22-Apr-13 21:35pm
   
Actually I have 3 form such as form Loan, form Equipment and form Return. I want link each form use Equipment ID. In form Loan i have Equipment ID,Name,Staff ID,Telephone No,Department,Equipment Item,Date Loan, Date Return and Period. In form Equipment i have Equipment ID, Equipment Item (with combo box list),model(combo box list),Description,Quantity,Serial No and Status. When user key in the form Loan i want Equipment Item will be automatic display when user key in Equipment ID. In form Return i have same details with form Loan but i have no idea how to change the status available back after user key in form Return.
hanigbl 22-Apr-13 21:36pm
   
This is my form Loan code.

Public Class frmLoan
Dim cnn As New OleDb.OleDbConnection

Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click

Dim intResponse As Integer

intResponse = MsgBox("Are you sure want to quit?", _
vbYesNo + vbQuestion, _
"Quit")

frmMenu.Show()
Me.Close()


End Sub

Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
Me.txtEquipmentID.Text = ""
Me.txtName.Text = ""
Me.txtStaffID.Text = ""
Me.txtTelNo.Text = ""
'Me.cboDepartment.Items.Clear() 'item clear when edit after that clear button
' Me.cboEquipmentItem.Items.Clear() 'item clear when edit after that clear button
'dtpDateLoan.Text = ""
Me.txtPeriod.Text = ""
Me.txtEquipmentID.Tag = ""

'enable button edit
Me.btnEdit.Enabled = True

'set button add to add label
Me.btnAdd.Text = "Add"

Me.txtEquipmentID.Focus()
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

Dim DateLoan As DateTime = dtpDateLoan.Value
Dim DateReturn As DateTime = dtpDateReturn.Value
Dim result As TimeSpan = DateLoan.Subtract(DateReturn)
Dim Period As Integer = result.TotalDays
txtPeriod.Text = Period

If Not cnn.State = ConnectionState.Open Then
'open connection if it is not open yet
cnn.Open()

End If

cmd.Connection = cnn
'check whether add new or update
If Me.txtEquipmentID.Tag & "" = "" Then
'add new to data table

cmd.CommandText = "INSERT INTO Loan(EquipmentID, Name, StaffID, TelephoneNo, Department, EquipmentItem, DateLoan, DateReturn, Period)" & _
" VALUES (" & Me.txtEquipmentID.Text & ", '" & Me.txtName.Text & "', '" & Me.txtStaffID.Text & "', '" & Me.txtTelNo.Text & "','" & _
Me.cboDepartment.Text & "','" & Me.txtEquipItem.Text & "','" & dtpDateLoan.Value & "', '" & dtpDateReturn.Value & "', '" & Me.txtPeriod.Text & "')"

cmd.ExecuteNonQuery()
'display msge box
MsgBox("Successfull added")
Else
'update data in datatable
cmd.CommandText = "UPDATE Loan " & _
"SET EquipmentID= " & Me.txtEquipmentID.Text & _
", Name='" & Me.txtName.Text & "'" & _
", StaffID='" & Me.txtStaffID.Text & "'" & _
", TelephoneNo='" & Me.txtTelNo.Text & "'" & _
", Department='" & Me.cboDepartment.Text & "'" & _
", EquipmentItem='" & Me.txtEquipItem.Text & "'" & _
", Period='" & Me.txtPeriod.Text & "'" & _
"WHERE EquipmentID=" & Me.txtEquipmentID.Tag
cmd.ExecuteNonQuery()

End If
Me.btnClear.PerformClick()

'close conneciton
cnn.Close()



End Sub

Private Sub frmLoan_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cnn = New OleDb.OleDbConnection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\HaniAll\EquipmentTracking\Equipment.mdb;Persist Security Info=True"

Me.Refresh()


End Sub

hanigbl 22-Apr-13 22:26pm
   
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.dgvLoan.Rows.Count > 0 Then
If Me.dgvLoan.SelectedRows.Count Then
Dim intEquipmentID As Integer = Me.dgvLoan.SelectedRows(0).Cells("id").Value
'get data form database followed by equipid
'open conneciton
If Not cnn.State = ConnectionState.Open Then
cnn.Open()
End If

Dim DateLoan As DateTime = dtpDateLoan.Value
Dim DateReturn As DateTime = dtpDateReturn.Value
Dim result As TimeSpan = DateLoan.Subtract(DateReturn)
Dim Period As Integer = result.TotalDays
txtPeriod.Text = Period

'get data into datatable
Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM Loan " & _
"WHERE EquipmentID=" & intEquipmentID, cnn)

Dim dtLoan As New DataTable
da.Fill(dtLoan)


Me.txtEquipmentID.Text = intEquipmentID
Me.txtName.Text = dtLoan.Rows(0).Item("Name")
Me.txtStaffID.Text = dtLoan.Rows(0).Item("StaffID")
Me.txtTelNo.Text = dtLoan.Rows(0).Item("TelephoneNo")
Me.cboDepartment.Text = dtLoan.Rows(0).Item("Department")
Me.txtEquipItem.Text = dtLoan.Rows(0).Item("EquipmentItem")
Me.txtPeriod.Text = dtLoan.Rows(0).Item("Period")


'hide the id to be edited in TAG of txtEquipId in case id is changed
Me.txtEquipmentID.Tag = intEquipmentID
'change button add to update
Me.btnAdd.Text = "Update"
'disable button edit
Me.btnEdit.Enabled = False



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.dgvLoan.Rows.Count > 0 Then
If Me.dgvLoan.SelectedRows.Count > 0 Then
Dim intEquipmentID As Integer = Me.dgvLoan.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 Loan WHERE EquipmentID=" & intEquipmentID



'msgbox
Dim intResponse As Integer

intResponse = MsgBox("Are you sure want to delete " _
& " this item? ", _
vbYesNo + vbDefaultButton2, _
"Delete")
'If intResponse = vbYes Then
' 'code to delete the rows would go here
'End If


cmd.ExecuteNonQuery()

'refresh data
Me.Refresh()

'close connection
cnn.Close()



End If


End If
End Sub

hanigbl 22-Apr-13 22:29pm
   
Private Sub btnList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnList.Click
If Not cnn.State = ConnectionState.Open Then
'open connection
cnn.Open()
End If

Dim da As New OleDb.OleDbDataAdapter("SELECT EquipmentID as [ID], " & _
"Name as [Name], StaffID, TelephoneNo, Department, EquipmentItem, DateLoan,DateReturn,Period " & _
"FROM Loan ORDER BY EquipmentID", cnn)

Dim dtLoan As New DataTable
'fill data to datatable
da.Fill(dtLoan)

'offer data in datatable into datagridview
Me.dgvLoan.DataSource = dtLoan

'close connection
cnn.Close()



End Sub


End Class
tarhex 26-Apr-13 18:29pm
   
OK, first things first. Have you being able to retrieved the details of the equipment?

What you want now is how to update the database to show equipment returned to reflect that it has being returned?
hanigbl 23-May-13 2:21am
   
Yes. I already successful set the equipment first available and change to unavailable if equipment borrowed. But now i have no idea to change back after the equipment return back to available in Form Return. Tq for helping

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900