A Generic GetRows VBScript Class - Part II: Adding Update and AddNew functions

Expanding on a previous article, we will see how to add 2 new methods to our class: one that inserts a new record in our table and another that edits an existing one. Use them against any table with just a few lines of code.

Published: Apr 29, 2002
Tested with: ASP 3.0
Category: ASP
23,716 views

Introduction

In a previous article, I showed how to create a class which returns your recordset as an array using the GetRows function. In this article, I will expand on the class, adding two more functions: one that adds a new record to your table, and one that edits a row in your table.

Expanded Class

We will be adding these new properties/methods to our class:

Database
Properties	+Output : String
Methods	+AddNew(in table : String, in fields : Array, in values : Array) : Boolean +Update(in table : String, in id : Double, in fields : Array, in values : Array) : Boolean

1	Class Database
2	...
3	Dim i_output
4
5	Public Property Get Output
6	Output = i_output
7	End Property
8
9	Public Sub AddNew(table,fields,values)
10	Dim arrFields
11	Dim arrValues
12	Dim i
13	Const adOpenKeyset = 1
14	Const adLockOptimistic = 3
15	Const adCmdTable = 2
16	i_objRS.Open table, i_objConn, adOpenKeySet, adLockOptimistic, adCmdTable
17	i_objRS.AddNew
18	For i = 0 To UBound(fields)
19	i_objRS(fields(i)) = values(i)
20	Next
21	i_objRS.Update
22	i_output = i_objRS("ID")
23	End Sub
24
25	Public Sub Update(table,ID,fields,values)
26	Dim arrFields
27	Dim arrValues
28	Dim i
29	Dim strQuery
30	Dim strFields
31	Const adOpenDynamic = 2
32	Const adLockOptimistic = 3
33	Const adCmdText = 1
34	strQuery = ""
35	For i = LBound(fields) to UBound(fields)
36	strQuery = strQuery & fields(i) & ", "
37	Next
38	strQuery = Left(strQuery, Len(strQuery) - 2)
39	strQuery = "select " & strQuery & " from " & table & " where ID=" & ID
40	i_objRS.Open strQuery, i_objConn, adOpenDynamic, adLockOptimistic, adCmdText
41	For i = 0 To UBound(fields)
42	i_objRS(fields(i)) = values(i)
43	Next
44	i_objRS.Update
45	End Sub
46	...

The AddNew method loops through the array of the fields passed to it, and for each one assigns it the value in the matching array of the values. The table to add a new row is passed as a parameter, but if you are only making changes to one table you can hard code that in the method. A nice feature with this method, is that after the addition of the row, you may wish to return any fields that are created automatically, like the creation time or the ID. For each value you want returned, save it locally within the class after the update, and then create a Get Property for it. You can see an example in the class of returning the ID field.

The Update method also loops through the fields and updates them with their corresponding values. I select what row to update based on a table and an ID that is passed in. Then I create the query.

Using AddNew and Output

1	<%
2	Dim arrFields
3	Dim arrValues
4	Dim clsDatabase
5	Dim numNewID
6	Dim numCategoryID,strQuestion,strAnswer,numPosterID
7	numCategoryID = "5"
8	strQuestion = "What is this?"
9	strAnswer = "This is the answer."
10	numPosterID = "10"
11	'-- include the fields you need to add in the array
12	arrFields = Array("category_ID","question","answer","user_ID")
13	'-- and their corresponding values
14	arrValues = Array(numCategoryID,strQuestion,strAnswer,numPosterID)
15	'-- create instance of the class
16	Set clsDatabase = New Database
17	'-- call the AddNew method passing the necessary parameters
18	clsDatabase.AddNew "tablename",arrFields,arrValues
19	'-- return the primary key of the new record created
20	numNewID = clsDatabase.Output
21	Set clsDatabase = Nothing
22	%>

The code above shows a very common scenario in a database INSERT: create a new record and get back the newly created primary key (ID). Let's go through the code. First, we create two local arrays: one holds the field names of the row in the table we are inserting, and the other the actual values we are inserting. These could come for example from a form post. Then we create an instance of the class, and we call the AddNew method, passing the necessary parameters to it. One of those parameters is the table name we are inserting to; change this to your needs. The Output property returns the newly created primary field (ID) of the row.

Using Update

1	<%
2	Dim arrFields
3	Dim arrValues
4	Dim clsDatabase
5	Dim numCategoryID,strQuestion,strAnswer
6	numCategoryID = "7"
7	strQuestion = "What is this?"
8	strAnswer = "This is the new answer."
9	arrFields = Array("category_ID","question","answer")
10	arrValues = Array(numCategoryID,strQuestion,strAnswer)
11	Set clsDatabase = New Database
12	clsDatabase.Update "tablename",arrFields,arrValues
13	Set clsDatabase = Nothing
14	%>

Again, we create two arrays, one containing the fields to be updated and the other containing the values to be updated to.

Conclusion: complete Class code

Database
Properties	+Output : String
Methods	-Class_Initialize() +GetArray(in strQuery : String) : Array +AddNew(in table : String, in fields : Array, in values : Array) : Boolean +Update(in table : String, in id : Double, in fields : Array, in values : Array) : Boolean -Class_Terminate()

1	Class Database
2
3	Dim i_dbConnection
4	Dim i_objConn
5	Dim i_objRS
6	Dim i_output
7
8	Private Sub Class_Initialize()
9	Const MAX_TRIES = 10
10	Dim intTries
11	On Error Resume Next
12	Do
13	Err.Clear
14	'edit the next line to point to your database
15	i_dbConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/faqs.mdb")
16	Set i_objConn = Server.CreateObject("ADODB.Connection")
17	i_objConn.Open i_dbConnection
18	Set i_objRS = Server.CreateObject("ADODB.Recordset")
19	intTries = intTries + 1
20	Loop While (Err.Number <> 0) And (intTries < MAX_TRIES)
21	End Sub
22
23	Public Function GetArray(strQuery)
24	'-- Cursor Type, Lock Type
25	' ForwardOnly 0 - ReadOnly 1
26	' KeySet 1 - Pessimistic 2
27	' Dynamic 2 - Optimistic 3
28	' Static 3 - BatchOptimistic 4
29	i_objRS.Open strQuery, i_objConn, 0, 1
30	If Err.Number <> 0 Then
31	Response.Write("There was an error processing your request.<br>Please try again.")
32	Exit Function
33	Else
34	If i_objRS.EOF and i_objRS.BOF Then
35	Response.Write("There are currently no records returned.")
36	Exit Function
37	Else
38	GetArray = i_objRS.GetRows()
39	End If
40	End If
41	End Function
42
43	Public Property Get Output
44	Output = i_output
45	End Property
46
47	Public Sub AddNew(table,fields,values)
48	Dim arrFields
49	Dim arrValues
50	Dim i
51	Const adOpenKeyset = 1
52	Const adLockOptimistic = 3
53	Const adCmdTable = 2
54	i_objRS.Open table, i_objConn, adOpenKeySet, adLockOptimistic, adCmdTable
55	i_objRS.AddNew
56	For i = 0 To UBound(fields)
57	i_objRS(fields(i)) = values(i)
58	Next
59	i_objRS.Update
60	i_output = i_objRS("ID")
61	End Sub
62
63	Public Sub Update(table,ID,fields,values)
64	Dim arrFields
65	Dim arrValues
66	Dim i
67	Dim strQuery
68	Dim strFields
69	Const adOpenDynamic = 2
70	Const adLockOptimistic = 3
71	Const adCmdText = 1
72	strQuery = ""
73	For i = LBound(fields) to UBound(fields)
74	strQuery = strQuery & fields(i) & ", "
75	Next
76	strQuery = Left(strQuery, Len(strQuery) - 2)
77	strQuery = "select " & strQuery & " from " & table & " where ID=" & ID
78	i_objRS.Open strQuery, i_objConn, adOpenDynamic, adLockOptimistic, adCmdText
79	For i = 0 To UBound(fields)
80	i_objRS(fields(i)) = values(i)
81	Next
82	i_objRS.Update
83	End Sub
84
85	Private Sub Class_Terminate()
86	Const adOpenState = 1 'indicates that the object is open
87	If Not i_objRS Is Nothing Then
88	If i_objRS.State = adOpenState Then
89	i_objRS.Close
90	End If
91	Set i_objRS = Nothing
92	End If
93	If Not i_objConn Is Nothing Then
94	If i_objConn.State = adOpenState Then
95	i_objConn.Close
96	End If
97	Set i_objConn = Nothing
98	End If
99	End Sub
100
101	End Class