updating fields in microsoft access 2000 thru visual basic

Question

i created a program that add and returns equipment, my problem is, its because my borrowing table and my return equipments table are in one table only..representing these fields

"productnumber"

"productname"

"dateborrowed"

"datereturned"

"borrowername"

"status"

what i want to do here is when a user returns an equipment, entering the same data on the fields would make errors on my database..especially on my productnumber because that is my primary key, so i decided to have a data grid in my return equipment form, so if a user return an equipment, all i will do is to update the datereturned field in my database..guys? can you help me with the codes?

Answer 1

As you have not indicated what it you specifically you need help with, I'll start by giving you the simple query to perform the update then I am going on to show you how to interact with Access.

The query to update datereturned:

  str = "UPDATE 'Your Table Name' SET datereturned = " & now ' This is your update query.

First thing to do is create a connection the the Access Database:

     Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=D:\Tecnical Stu"& _ 
"dy\Complete_Code\Ch08\data\NorthWind.mdb"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString )

Found at:

http://p2p.wrox.com/ado-net/28703-how-vbulletin-net-connect-access-database.html

Next you should perform the update:

dbConnection .Open()
str = "UPDATE 'Your Table Name' SET datereturned = " & now ' This is your update query.
'string stores the command and CInt is used to convert number to string
cmd = New OleDbCommand(str, cn)
icount = cmd.ExecuteNonQuery

Here is a class that will allow you to perform the interactions, easily and simply.

Imports System.Data.OleDb
Public Class Form1 Inherits System.Windows.Forms.Form
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e as _
System.EventArgs) Handles MyBase.Load
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles Button1.Click
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;_
Data Source=C:\emp.mdb;")
'provider to be used when working with access database
cn.Open()
cmd = New OleDbCommand("select * from table1", cn)
dr = cmd.ExecuteReader
While dr.Read()
TextBox1.Text = dr(0)
TextBox2.Text = dr(1)
TextBox3.Text = dr(2)
' loading data into TextBoxes by column index
End While
Catch
End Try
dr.Close()
cn.Close()
End Sub
End Class
When you run the code and click the Button, records from Table1 of the Emp database will be displayed in the TextBoxes.

Retrieving records with a Console Application

Imports System.Data.OleDb
Imports System.Console
Module Module1

Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader

Sub Main()
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\emp.mdb;_
Persist Security Info=False")
cn.Open()
cmd = New OleDbCommand("select * from table1", cn)
dr = cmd.ExecuteReader
While dr.Read()
WriteLine(dr(0))
WriteLine(dr(1))
WriteLine(dr(2))
'writing to console
End While
Catch
End Try
dr.Close()
cn.Close()
End Sub

End Module
Code for Inserting a Record

Imports System.Data.OleDb
Public Class Form2 Inherits System.Windows.Forms.Form
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim icount As Integer
Dim str As String

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

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles Button2.Click
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\emp.mdb;")
cn.Open()
str = "insert into table1 values(" & CInt(TextBox1.Text) & ",'" & TextBox2.Text & "','" &_
TextBox3.Text & "')"
'string stores the command and CInt is used to convert number to string
cmd = New OleDbCommand(str, cn)
icount = cmd.ExecuteNonQuery
MessageBox.Show(icount)
'displays number of records inserted
Catch
End Try
cn.Close()
End Sub
End Class

Found at:

http://www.startvbdotnet.com/ado/msaccess.aspx

Answer 2

You need to split the product and borrow/return data into two tables:

product:
"productnumber"
"productname"

borrowed:
borrowedID (use a simple autonumber)
productnumber (foreign key linked to productnumber in the product table)
"dateborrowed"
"datereturned"
"status"
borrowerID (foreign key linked to borrower table)

a third table for borrowers/customers would also be in order

borrowers:
borrowerID
"borrowername"