Building A Document Request Protocol Part 1/2 - Accepting new records from the client

(Page 5 of 7 )

The client can also pass SARP commands to add a new category or article to our Access database. As mentioned earlier, both the title and content of a new article must have underscores instead of spaces.

The AddCategory function is called when the ProcessCommands routines determines that the client is requesting to add a new records. Its signature looks like this:

Private Function AddCategory(strCat As String) As String

Firstly, it checks whether or not the specified category already exists:

Set objRS = GetRecordset("SELECT COUNT(*) FROM Categories WHERE name = '" & strCat & "'")

numRows = objRS.Fields(0)



If numRows = 0 Then

'Add new category

...

Else

'Category already exists

...

End If

If the category doesn't exist, then the ExecQuery function is called. ExecQuery is a custom function that accepts a valid SQL query and runs that query against our Access database:

'Add new category

ExecQuery ("INSERT INTO Categories(name) VALUES('" & strCat & "')")

AddCategory = "104 Category Added OK"

The AddCategory function returns "104 Category Added OK" if the category was added and didn't already exist.

If however the category already exists, then it won't be added to the categories table, and "105 Category Already Exists" will be returned to the client.

The AddArticle function works in much the same way as the AddCategory function, but accepts four arguments:

Private Function AddArticle(strTitle As String, strContent As String, curPrice As Currency, intCatId As Integer) As String

Obviously, these arguments are the values that will be inserted into the fields for the new article record in our Access database. For the title and content, the single quotes are replaced with double quotes, and the underscores with spaces:

'Make safe quotes

strTitle = Replace(strTitle, "'", "''")

strTitle = Replace(strTitle, "_", " ")



strContent = Replace(strContent, "'", "''")

strContent = Replace(strContent, "_", " ")

Next, we use the GetRecordSet function to return a count of records with the title strTitle:

Set objRS = GetRecordset("SELECT COUNT(*) FROM Articles WHERE title = '" & strTitle & "'")

numRows = objRS.Fields(0)

Lastly, the article will be added to the database if it doesn't already exist:

If numRows = 0 Then

'Add new category

ExecQuery ("INSERT INTO Articles(title, content, price, catId) VALUES('" & strTitle & "', '" & strContent & "', " & curPrice & ", " & intCatId & ")")

AddArticle = "106 Article Added OK"

Else

'Category already exists

AddArticle = "107 Article Already Exists"

End If

Next: Our VB App in action >>
 

More PHP Articles
More By Mitchell Harper

Comments On: Building A Document Request Protocol Part 1/2

>>> Be the FIRST to comment on this article!