Universal Database Updater

Posted: 2002-06-01
By: ArchiveBot
Viewed: 60

Filed Under:

ASP/ HTML

No attachments for this post

Why I wrote thise code:
While working on a project, I realized that I was writing too many update statements. Not that it's hard, but hand coding update statements can feel like pulling teeth if you are working with large applications where you do a lot of updating. So why not create a dynamic update statement that saves time, and effort, and only needs to be written once? Okay, I admit it, I can be lazy sometimes. But this one is actually useful.
This code takes values from a querystring and uses them to update two fields within a database record. It can be used for any table in any access database.

Original Author: Lewis Mandrake

Inputs

'The only hard coded value is the database path.
'This can be changed.
'Query String Values:
'table: Your table name
'field1: Your first field name
'field1_value: the value you want to put into your field
'field2: Your second field name
'field2_value: The value you want to put into your second field.
'where_value: the name of your primary key field
'primarykey: the value in your primary key field
'diagnostic: if diagnostic=test then it will tell
'you what has been updated
'reset: If you are not using it in diagnostic
'mode, your reset should be the URL you want to
'redirect to.

Assumptions

I'm guessing most intermediate programmers will be able to read this. It could be a big time saver if used properly.

Side Affects

'Jumping for joy.

API Declarations

mention flying monkeys.

Code

<%
DataConnection = "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:databasedata.mdb;"
If request.querystring("state")="update" then
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = DataConnection
Command1.CommandText = "UPDATE "&request.querystring("table")&" SET "&request.querystring("field1")&"='"&request.querystring("field1_value")&"',"&request.querystring("field2")&"='"&request.querystring("field2_value")&"' WHERE "&request.querystring("where_value")&"="&request.querystring("primarykey")&""
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
if request.querystring("diagnostic") = "test" then
Response.Write "the table "&request.querystring("table")&" has had the following values updated
"
Response.Write ""&request.querystring("field1")&" has been updated to use "&request.querystring("field1_value")&" as it's value
"
Response.Write ""&request.querystring("field2")&" has been updated to use "&request.querystring("field2_value")&" as it's value
"
Response.Write "Your primary key is "&request.querystring("where_value")&" and it has updated where the value of that key is set to "&request.querystring("primarykey")&"

"
Response.Write "This update is based on the values in the querystring. To change these values, or update a different set of fields or tables, then just change the values in the address bar above."
Response.Write "

Universal Querystring Updater
By Sam Moses (c) 2002

Comments on this post

No comments have been added for this post.

You must be logged in to make a comment.