| |
|
|
|
Code Archive
If you're a programmer or just starting out, we can help you get the job done with a confidential support agreement. If you're
stuck on some code and need a hand, contact us and end the suffering. In the meantime
check out these code bytes, they may get your brain in motion.
Coders, if you find these snippets and refreshers useful, we would love if you would link to us at www.divprogram.com.
CATEGORIES
T-SQL (MS SQL)
Cold Fusion
Javascript
T-SQL
Get the ID of the record added in a stored procedure
CREATE PROCEDURE dbo.CreateGallery
@galleryname varchar(80),
@galleryid int OUTPUT
AS
insert into photogallery(galleryname)
VALUES (@galleryname)
SET @GalleryID = @@IDENTITY
Retreive everything in a string based column up to the first comma.
SUBSTRING(dbo.mytable.keywords, 1, CAST(PATINDEX('%,%', replace(dbo.mytable.keywords, ' ', ',')) -1 as integer))
Remove duplicate records from a table
DELETE FROM Items
WHERE EXISTS (
SELECT Id
FROM Items ItemsInner
WHERE ItemsInner.Itemnumber = Items.Itemnumber
AND ItemsInner.Id < Items.Id )
Replace a string of data in a TEXT field.
UPDATE TableName
SET myFieldName = REPLACE(SUBSTRING(myFieldName, 1, DATALENGTH(myFieldName)), 'My Bad Text', 'My New Text')
Update one table with information from another
UPDATE dbo.archive
SET dbo.archive.name = dbo.clients.name
FROM clients
WHERE clients.clientID = archive.clientID
Update one table (accessoryitems) with information from another (products)
INSERT INTO AccessoryItems(productid, accessoryid)
SELECT products.productID, products.accessories as Accessoryid
FROM Products
Pad a varchar field with some character (zeros in this case)
UPDATE Tracking
SET trackingnumber = Right('0000000000'+CAST(trackingnumber AS varchar(10)),10)
Very simple case statement
CASE WHEN left(Name, 3) = 'old' THEN (dbo.clientinfo.Last_Name) ELSE (dbo.clientarchive.LastName) END
Pick a random record from a table (the NEWID() function is the key here)
SELECT TOP 1 dbo.products.ProductName, dbo.products.ProductImage, dbo.products.ProductID
FROM dbo.products
ORDER BY NEWID()
Combine multiple columns or rows into one string variable or comma delimited value
(The output here would be CardNumbers = "02/2007, 02/2007, 02/2007")
DECLARE @AList varchar(25)
SELECT @AList = COALESCE(@AList + ', ', '') + cast(cardexpmonth as varchar(2)) + '/' + cast(cardexpyear as varchar(4))
FROM dbo.clientinfo
SELECT @AList as CardNumbers
Coalesce means 'Take this column and if its not null or the first record, add the comma and then add whatever other columns I want and then keep looping.
I use this to dynamically populate keywords on a data generated page.
Generate a result set compatible with Froogle
SELECT DISTINCT TOP 100 PERCENT
(dbo.products.ProductName) AS title,
cast(cast(dbo.products.ProductPrice AS Decimal(10,2)) as varchar) AS price,
'http://www.yoursite.com/images/large/' + dbo.products.ProductImageLg AS image_link,
cast('http://www.yoursite.com/productdetails.cfm?ID=' + cast(dbo.Products.productid AS Varchar) AS Varchar) AS link,
cast(dbo.products.ProductID as varchar) AS id,
ltrim(rtrim(left(dbo.products.ProductDescriptionShort, 255))) AS description,
replace(ltrim(rtrim(dbo.products.ProductName)), ' ', ',') AS label
FROM dbo.products
WHERE ProductPrice > 0 AND len(ProductName) > 3
COLD FUSION
Loop over all fields posted by a form
<cfloop list="#form.fieldnames#" index="i">
<cfset string="form.#i#">
#i#: #evaluate(string)#
</cfloop>
Simple (free) CF/JS CAPTCHA style form validation.
This is a session based CAPTCHA code to help reduce spam submitted by web forms. It writes a
javascript ascii code which is difficult for robots to read and outputs it on the page then
writes a CF session variable which gets passed to the form validator.
Form Page:
<Table>
<tr><Td class="bodycopy" align="right">To reduce spam, please type the following number:
<script type="text/javascript">
<cfset session.valid="">
<cfloop from="1" to="5" index="loopin">
<cfset thisdigit = randrange(0,9)>
<cfset thisascii = thisdigit + 48>
<cfset session.valid = session.valid & thisdigit>
<cfoutput>document.write(String.fromCharCode(#thisascii#));</cfoutput>
</cfloop>
</script>
</td><td>
<input class="bodycopy" type="text" name="SpamValidationCode" size="26">
</td></tr>
</Table>
Validation Page:
<cfif not form.SpamValidationCode EQ session.valid>
<script language="JavaScript">alert('Invalid spam validation. Please re-enter code.'); history.back(-1);</script><cfabort>
</cfif>
Add a space before all uppercase letters
#REReplace("DiversifiedProgrammingEnterprises", "([A-Z])", " \1", "ALL")#
Grab RSS news feeds from Yahoo News. This gets the top three.
<cfhttp url="http://news.search.yahoo.com/news/rss?ei=UTF-8&p=new+hampshire&c=&eo=UTF-8" method="get">
<cfset objRSS = xmlParse(cfhttp.filecontent)>
<cfset newslength = arrayLen(objRSS.rss.channel.item)>
<table width="800" align="center">
<cfloop index=i from=1 to=3>
<tr>
<cfoutput>
<td><a href="#objRSS.rss.channel.item[i].link.xmltext#">
#objRSS.rss.channel.item[i].title.xmltext#</a></td>
<td><a href="#objRSS.rss.channel.item[i].link.xmltext#">
#objRSS.rss.channel.item[i].description.xmltext#</a></td>
</cfoutput>
</tr>
</cfloop>
</table>
Docking loose tabs in Homesite 5
In the View menu, open Files 1 Primary. At the bottom of the tab (where the files appear, NOT where directories appear) right click and choose Move Files 1 To - Left side.
Do the same thing for File 2 Secondary. For the snippets tab (and maybe others), redocking requires the following registry alteration. Close Homesite, click Start - Run. Type regedit.
In the registry editor, navigate to HKEY_CURRENT_USER\Software\Macromedia\HomeSite5\Docking\frmDockSnippets. Make your registry look like the default reg settings, which are below:
"Visible"="1"
"AlwaysOnTop"="1"
"Position"="0,0,197,694"
"Floating"="0"
"DockHost"="AllaireTabDockForm0"
"PageIndex"="3"
"DockTab"="0"
Get dynamically generated form input boxes on the processing page
(Example: Input boxes in a loop called name1, job1, name2, job2, etc.)
#evaluate("form.name" & id)#
#evaluate("form.job" & id)#
Basic stored procedure code
<cfstoredproc procedure="yourprocname" datasource="mydbase" username="username" password="password">
<cfprocparam cfsqltype="CF_SQL_INTEGER" variable="memberid" value="#session.memberid#" type="In">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" variable="vote" value="#numberformat(form.vote, "99")#" type="In">
<cfprocresult name="someresults">
</cfstoredproc>
Delete all session variables
<cfset tempvariable = StructClear(session)>
Delete one particular session variables
<cfset tempvariable = StructDelete(session, "clientid")>
Use CFFile to upload an attachment from a form posting to a different directory.
(remember to use enctype="multipart/form-data" instead of the default enctype="application/x-www-form-urlencoded" in your form!
<cfset curdirectory = #getdirectoryfrompath(expandpath("*.*"))#>
<!--- Move up out of the admin directory and back down into an Images directory --->
<cfset curdirectory = #replacenocase(curdirectory, "\admin\", "\images\", "All")#>
<CFIF form.theformfield eq "">
<script language="JavaScript">alert('You must upload a menu!'); history.back(-1);</script><cfabort>
<cfelse>
<CFFILE action="upload" filefield="theformfield" destination="#curdirectory#" nameconflict="MAKEUNIQUE">
<CFSET ServerFile1 = FILE.SERVERFILE>
</cfif>
Write a form posting to a text file in your current directory
<cfset thisdirectory = #getdirectoryfrompath(getcurrenttemplatepath())#>
<cfset textfile = #thisdirectory# & "specials.htm"> <!--- Name of the page --->
<cfset finaltext = #form.thetext#> <!--- The text to write --->
<cffile action="WRITE" file="#textfile#" output="#finaltext#">
A handy, short date loop. Remove the - before prevyears or leave to show years past
M <select name="birth_month">
<cfloop from="1" to="12" index="themonth">
<option value="#themonth#">#themonth#</option>
</cfloop>
</select>
D <select name="birth_day">
<cfloop from="1" to="31" index="theday">
<option value="#theday#">#theday#</option>
</cfloop>
</select>
Y <select name="birth_year">
<cfloop from="0" to="80" index="prevyears">
<cfset theyear = dateadd('yyyy', -prevyears, now())>
<option value="#dateformat(theyear, "yyyy")#">#dateformat(theyear, "yyyy")#</option>
</cfloop>
</select>
JAVASCRIPT
Disable the submit button to prevent double processing
onclick="this.disabled=1; this.form.submit(); this.value='Processing Order. Please wait...';"
Open the print dialog box
<a href="javascript:void(0);" onclick="window.print();">CLICK HERE TO PRINT THIS PAGE</a>
Open a confirm alert box. If they click No, it aborts the submission.
onclick="if (confirm ('Are you sure?')) {return true} else {return false}"
|
|
|
|
|
