Deleting whole table with Identity Field

If you delete the whole table data by using "delete from mytable" SQL command or manually deleting all records through SQL Server Manager, The Identity field sequential number will not reset.

If you want to reset the Identity field sequential number, you should use "truncate table mytable" instead.

PHP PDO Prepare with date field

I was sending a date like '12/31/2010' to the database using ODBC database extension without problem.

Recently, I just tried to be more secure by avoiding SQL Injection attack by using PDO::Prepare. It worked just fine except when I need to update a date field. It failed to update without warning.

Unlike the ODBC series, the PDO does not report database errors by default. It took me quite some time before I realize that the date has to be in "Y-m-d" format.

By the way, to get PHP to show the PDO error you can set

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); 

$sql = "SELECT * FROM booksa"; 

$q = $conn->query($sql) or die("ERROR: " . implode(":", $conn->errorInfo()));  

Getting element from DOM tree

If we want to get a cell element from a table, we would get the id of the table first. With the ID then we will navigate through the rows and cols to get to the cell.

A simple way to get this is

mycell = tblid.childNodes[x].childNodes[w]

Unfortunately, it is not that simple. Usually when people write html, they tends to spread out the tags in different lines for easier visibility. Often you would do this

<tr>

<td>text<td>

<tr>

In IE you would not have a problem as IE would ignore the whitespaces. However, other browsers are more specific. They will not ignore the whitespaces.

If you use the same simple code, you will not get the element that you wanted. You would have to find a way to ignore the whitespaces like IE.

The following is a simple code to do it.

function getcell(tbdid,params){

var z;

var zz;

addEval=tbdid

for (y=0;y<params.length;y++){

zz=-1

for (z=0;z<params[y];z++){

if (addEval.childNodes[z].nodeType != 1)

zz +=1

zz+=1

}

addEval=addEval.childNodes[zz]

}

return addEval

}

mycell=getcell(tblid,[x,w])

Note that x and w are numbers starting with 1 instead of 0.

Close top window automatically without prompt

In IE6 I used to do this

window.opener="x"

window.close()

The window will close nicely without prompting.

In IE7 it will not work. Instead use the following

window.open('','_self','');

window.close()

SetTimeout to temporarily disable buttons

Normally you would call the function and pass a callback function name to it so that at the time out event your function will be called.

Now what if I want to use the same call back function to act on different DOM elements? For example, I want to disable the button for a few seconds when I click on it. The disabling is to prevent user from clicking on it multiple times. The problem is that there are a few buttons of similar nature that acts differently on clicking. Instead of having to create different callback functions for each button, I want to use the same function for all the buttons.

It turned out that this could be done.

In your INPUT create a "onclick" event by calling a function and pass "this" as parameter.

In the called function do the following

myattribute=document.createAttribute("disabled")

myattribute.nodeValue=true

param.setAttributeNode(myattribute)

settimeout(function(){mycallback(param)},1000)

The first three lines will disable the button. The fourth line will call the timeout with mycallback as function. It is actually a dynamic function that calls mycallback function by passing the INPUT object as parameter. This means that the script still creates different functions for each button but it will not be created till user clicks on it. Coding wise this is easier to maintain than you create all the functions manually.

mycallback will then be able to act on the "param" (which is the "INPUT" object) by calling

param.removeAttribute("disabled")

Get date only from ODBC datetime in PHP

I always have problem with retrieving datetime field from ODBC. PHP will default to display it as "yyyy-mm-dd hh:mm:ss" format. The data is also converted to text. That means that you would not be able to do date calculation with it.

Normally I would use strtotime() to convert the date to timestamp first then perform the date manipulation. There are times when the date part of the datetime field is required. In which case I will have to include in the sql "convert(varchar,mydatefield,120)". Problem is MSSQL format 120 is "yyyy-mm-dd hh:mm:ss". There is no format that retrieves only "yyyy-mm-dd".

Fortunately the "convert" function allows truncation. So instead of using "convert(varchar,mydatefield,120)". I would use "convert(varchar(10),mydatefield,120)". The output will be a nice date only result.

If you do $date1-$date2, you will probably get a result like 1814400 because the returned result is in milliseconds. you need to divide the result by 60*60*24 to get days only result.

Excel connect to database

One of my project requires user to use Excel to fill in a form. Once it is filled, it will send the data to a database and returns a ID. Although it is not ideal for such a connection to be done in Excel, It is acceptable if the action is just to insert records. I set the access rights to just able to read and insert. The data is not sensitive and confidential.

Here is how I wrote the macro. Note that the getting and returning of ID part is not included here so as not to confuse reader.

sub updatedb()

Dim MyConn As ADODB.Connection

Dim MyRS As ADODB.Recordset

Set MyConn = New ADODB.Connection

MyConn.Open "DSN=odbcdsn;Uid=userid;Pwd=userpass"

SQL="insert int mytable (a,b) values ('" & ActiveSheet.Range("E1").Value & "','" & ActiveSheet.Range("F1").Value & "')"

Set MyRS = MyConn.Execute(Sql)

Set MyRS = Nothing

Set MyConn = Nothing

End Sub

Brio Working hour calculation

Brio query does not even have day of week not to mention networkingdays. The following is my own code to do the calculation. The calculation actually calculate the working hours between the two date excluding the off office hours and week ends. Unfortunately it is too much work to do a holiday exclusion.

Note that date5 is the earlier date and date6 is the latter date.

var date5 = CreateDate

var date6 = CreateDate2

var date1 = ToDate(ToChar(date5,"dd/mm/yyyy"));

var date2 = Nvl(date6,Sysdate());

var date3 = ToDate(ToChar(date2,"dd/mm/yyyy"));

var datecnt = date3-date1;

if (datecnt > 0){

var date4=date1

var offset=0

if (datecnt > 1 ){

for (x=1;x<datecnt;x++){

date4=date4+1

var nmonday=NextDay(date4,"Sunday");

var wkday = 7-(nmonday-date4);

if (wkday == 6) {

offset = offset + 0.25

} else {

if (wkday !=0) {offset = offset + 0.5}

}

}

}

var nmonday=NextDay(date1,"Sunday");

var wkday = 7-(nmonday-date1);

dateb=date5-date1

if (dateb < 9/24) {date5 = date1 +9/24}

if (dateb > 21/24) {date5 = date1 + 21/24}

if (dateb > 15/24 && wkday ==6) {date5 = date1 + 15/24}

if (wkday == 0){ datea = 0 } else {

if (wkday == 6) {datea=(date1+15/24)-date5} else {datea =(date1+21/24)-date5}

}

offset = offset+datea

var dateb = date6-date3

if (dateb < 9/24) {dateb = 9}

if (dateb > 21/24) {dateb = 21}

dateb= dateb-9/24;

(offset+dateb)*24*60

} else {(date6-date5)*24*60}