ADO - (ADD - EDIT - DELETE - SAVE)

[omarswan]

Hi,
Can someome give me an example of how to ADD, EDIT, DELETE & SAVE RECORDS - USING ADO


Thank in advance :)

[Elias]

There are a few things you need to know to do this.

1. Microsoft is pushing to support the ADO object model. What is that you ask? From my limited knowledge it is a set of precompiled class modules that you can use by setting a reference to it in VB. To do this, you click on Project->References->ActiveX Data Objects 2.0 Library. Don't use the 2.1 object library, I have been told there are known bugs. I haven't had any problems with 2.0.

2. Learn about ADO. Know that there are 3 types of objects that you can deal with when using ADO. Each object is powerful in its own way. The three objects are 1. The Connection object: allows you to connect to a database. 2. The command object: Powerful for using the Update and Insert Commands in SQL (among other things). 3. The Recordset Object: Used best for retreiving data from a database. You can have connected or dis-connected recordsets.

3. Learn about SQL syntax. SAMS makes the best books that I have learned everything I know about SQL. Very good index and relatively straight foward. SQL is the language that databases generally use.

4. Learn about connection strings. These strings are necessary for connecting to a database and allow for security to be implemented.

With all that in mind, I will give an example for each (ADD, EDIT, DELETE & SAVE RECORDS - USING ADO).

The following commands correspond to what you were asking:
What you want to do -> SQL Statement
ADD -> Insert
Edit -> Update
Delete -> Delete
Save -> .update method of a recordset

To be continued...

[Elias]

Here we go! I've used the Northwind Database in SQL Server. For Access, your connection string would be:

Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb

All you need to test is to create a database with 3 fields:
1. FirstName
2. LastName
3. ID ( this particular ID is an autonumber )

Where the datasource is the file path of the Access DB.

Private rstMain As ADODB.Recordset
Private Const strConnection = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;" & _
"User ID=sa;" & _
"Initial Catalog=Northwind;Data Source=(local)"
Private Sub Form_Load()
Set rstMain = New ADODB.Recordset
rstMain.Open "SELECT * " & _
"FROM employees", strConnection, adOpenDynamic, adLockOptimistic
If Not rstMain.EOF Then
rstMain.MoveLast
rstMain.MoveFirst
End If
Updatelist
End Sub

Private Sub Form_Unload(Cancel As Integer)
rstMain.Close
Set rstMain = Nothing
End Sub

Private Sub cmdAddEmployee_Click()
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;" & _
"User ID=sa;" & _
"Initial Catalog=Northwind;Data Source=(local)"

'Make sure you include your spaces!
cmd.CommandText = "INSERT INTO employees (FirstName,LastName,Title) " & _
"VALUES ('" & txtFirst.Text & "','" & txtLast.Text & "','" & txtTitle.Text & "')"

cmd.Execute
MsgBox "Employee has been Added!"
Set cmd = Nothing
End Sub

Private Sub cmdForward_Click()
With rstMain
.MoveNext
If .EOF Then
.MoveFirst
End If
End With
Updatelist
End Sub

Private Sub cmdBack_Click()
With rstMain
.MovePrevious
If .BOF Then
.MoveLast
End If
End With
Updatelist
End Sub

Private Sub cmdDeleteEmployee_Click()
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;" & _
"User ID=sa;" & _
"Initial Catalog=Northwind;Data Source=(local)"

'Make sure you include your spaces and single quotes!
'(Single Quotest not required for integers)

cmd.CommandText = "DELETE FROM employees " & _
"WHERE firstname='" & txtFirst.Text & "' " & _
"AND lastname='" & txtLast.Text & "'"

cmd.Execute
MsgBox "Employee " & txtFirst.Text & " " & txtLast.Text & " has been Deleted!"
Set cmd = Nothing
End Sub

Private Sub cmdUpdateTitle_Click()
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = strConnection

'Make sure you include your spaces and single quotes!
'(Single Quotest not required for integers)

cmd.CommandText = "UPDATE employees " & _
"SET title='" & txtTitle.Text & "' " & _
"WHERE firstname='" & txtFirst.Text & "' AND lastname='" & txtLast.Text & "'"

cmd.Execute
MsgBox "Employee " & txtFirst.Text & " " & txtLast.Text & "'s Title has been Changed!"
Set cmd = Nothing
End Sub

Private Sub Updatelist()
With rstMain
txtID.Text = !employeeid
txtFirst.Text = !firstname
txtLast.Text = !lastname
txtTitle.Text = !Title
End With
End Sub

Private Sub cmdUpdate2_Click()
'Save info Changed
'Note: See sub txtTitle_change
MsgBox "Employee " & txtFirst.Text & " " & txtLast.Text & "'s Title has been Changed!"
End Sub

Private Sub txtTitle_Validate(Cancel As Boolean)
rstMain!Title = txtTitle.Text
rstMain.Update
End Sub

Omarswan,
I think I've answered your question thouroughly. There are many other ways to do this, but I think I've touched on the basics. Have a great day! Feel free to email if you need further assistance.

-Elias
-elias@rtsassociates.net

[Clunietp]

Some ADO links for you, Omar:

ADO Tutorials & Info:
http://msdn.microsoft.com/library/psdk/dasdk/mdat3n3g.htm

http://support.microsoft.com/support/kb/articles/Q172/4/03.ASP

Migrating from DAO to ADO:
http://msdn.microsoft.com/library/techart/daotoadoupdate.htm

[Elias]

Omarswan,

There is one problem that can be fixed easily with my code. Remove the causes validations subroutine and change sub cmdUpdate2_Click() to the following:

Private Sub cmdUpdate2_Click()
'Save info Changed
'Note: See sub txtTitle_change
rstMain!Title = txtTitle.Text
rstMain.Update
MsgBox "Employee " & txtFirst.Text & " " & txtLast.Text & "'s Title has been Changed!"
End Sub

and change cmdDeleteEmployee_Click to the following:

Private Sub cmdDeleteEmployee_Click()
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;" & _
"User ID=sa;" & _
"Initial Catalog=Northwind;Data Source=(local)"

'Make sure you include your spaces and single quotes!
'(Single Quotest not required for integers)

cmd.CommandText = "DELETE FROM employees " & _
"WHERE employeeid='" & txtID.Text & "'"

cmd.Execute
MsgBox "Employee " & txtFirst.Text & " " & txtLast.Text & " has been Deleted!"
Set cmd = Nothing
If rstMain.RecordCount <> 0 Then
rstMain.MoveFirst
Updatelist
End If
End Sub

This will only delete the employee with the give id shown as opposed to all with the same name.

-Good luck,
-Elias

[omarswan]

:) Thanks alot for your help Elias :)

[Elias]

No problem, now if someone could just write me a line control class with a click event, I will be all set!

-Elias