VB icon

Universal Database Updater

Email
Submitted on: 1/6/2015 1:09:00 AM
By: Lewis Mandrake (from psc cd)  
Level: Intermediate
User Rating: By 3 Users
Compatibility: ASP (Active Server Pages)
Views: 740
 
     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.
 
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
'**************************************
' for :Universal Database Updater
'**************************************
mention flying monkeys.
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
				
'**************************************
' Name: Universal Database Updater
' Description: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.
' By: Lewis Mandrake (from psc cd)
'
' 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.
'
' Assumes:I'm guessing most intermediate programmers will be able to read this. It could be a big time saver if used properly.
'
' Side Effects:'Jumping for joy.
'**************************************

<%
DataConnection = "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\database\data.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 <b>"&request.querystring("table")&" </b> has had the following values updated<BR>"
Response.Write "<B>"&request.querystring("field1")&" </b>has been updated to use <b>"&request.querystring("field1_value")&"</b> as it's value <BR>"
Response.Write "<B>"&request.querystring("field2")&" </b> has been updated to use <b>"&request.querystring("field2_value")&" </b>as it's value <BR>"
Response.Write "Your primary key is <B>"&request.querystring("where_value")&" </B> and it has updated where the value of that key is set to <b>"&request.querystring("primarykey")&"</b><p>"
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 "</p><p><b>Universal Querystring Updater</b><br>By <a href='http://sammoses.com'>Sam Moses</a> (c) 2002</p>"
end if
if request.querystring("diagnostic")="" then response.redirect "" &request.querystring("reset")&""
end if
%>


Other 6 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Intermediate category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.