Spooky's Databases > Update Records

The good thing about database records, is they aren't static.
You have the ability to update or change them.

Databases need to have information passed to them in a language they understand.
Again we will concentrate on Access as our database

Update Queries
A very basic update query form looks something like this:

This form would submit to a page that contains a custom DRW.
The custom SQL (Step 2would be :
UPDATE TABLE 
SET Field='::Field::'
WHERE ID=::ID::

The record number '3' would have its 'Field' value changed to 'Spooky'.

As you can see, passing text values for update is quite simple.

Now imagine you had the same form that also had a DOB (Date of Birth) field that you could update. (I'm a spring chicken, I know ;c)

This form would also submit to a page that contains a custom DRW.
The custom SQL would be :
UPDATE TABLE 
SET Field='::Field::', DOB='::DOB::'
WHERE ID=::ID:: 

Now right about here I was going to say "We'll hang on, Access needs dates in the format of #date# doesn't it?" Well this is where it gets weird.

Access, for the purpose of the FP form, prefers either the SQL version of formatting which is Date='Date' or the Access version which is Date=#Date#

Formatting your date without the ' or # characters will give VERY unpredictable results :)

Data Conversion

Your normal formats are these :

Access

• Date = #xx/xx/xx# 
• Text = 'Text'
• Numeric = Numeric

SQL

• Date = 'xx/xx/xx' 
• Text = 'Text'
• Numeric = Numeric

Checkboxes / Radio buttons

• Radio=::RadioName::
• checkbox=::CheckboxName::
  
  Using checkboxes, you'll need to put a default value of null in step 3, more options.
  This assumes values of "true/false" and database columns of the same type

FrontPage does most of the work for you within the fpdbform.inc page where it performs the data conversion like so:

Select Case rs(strDBField).Type
Case adInteger Or adBigInt Or adUnsignedTinyInt Or adUnsignedSmallInt Or adUnsignedInt Or adUnsignedBigInt
   rs(strDBField) = CInt(strField)
Case adSingle Or adDecimal Or adNumeric 
   rs(strDBField) = CSng(strField)
Case adDouble
   rs(strDBField) = CDbl(strField)
Case adCurrency
   rs(strDBField) = CCur(strField)
Case adBoolean
   rs(strDBField) = cBool(strField)
Case adDate Or adDBDate Or adDBTime or adDBTimeStamp
   rs(strDBField) = CDate(strField)
Case Else
   rs(strDBField) = CStr(strField)
End Select

For additional information, a published Microsoft document can be found here

Well, that's all for now!