Programming tips for dummies (like me...)
* Title inspired by this
Catching Elephant is a theme by Andy Taylor
Insert Values into an Identity Column
Some days ago I had to insert a record with an specific value to the primary key. But it happened to be in a table that had an identity column which means this column automatically assigns a value for each new row inserted. When you try you get the error “An explicit value for the identity column in table ‘Tablename’ can only be specified when a column list is used and IDENTITY_INSERT is ON”.
But there’s a trick:
SET IDENTITY_INSERT tablename ON
INSERT INTO tablename
(cd_identity, vl_column)
VALUES
(32,’test’)
SET IDENTITY_INSERT tablename OFF
Export data to Excel using ASP
The code below show an easy way to export the data you present in a html page to Excel using asp. I usually have an “Export” button and put the “reponse.contentType, Response.addHeader” code inside an if-command (if Request(“button”) <> “” then):
<html><head></head>
<body>
<form name=”form1” method=”post”>
<% ‘Export to Excel
Response.Clear
Response.ContentType = “application/vnd.ms-excel”
Response.addHeader “content-disposition”,”attachment;filename=myfile.xls”
%>
<table>
<tr><td>col1</td><td>col2</td></tr>
<tr><td>col1</td><td>col2</td></tr>
<tr><td>col1</td><td>col2</td></tr>
</table>
</form>
</body>
</html>
PS: pay attention to close correctly every <tr>,<th><td> tag otherwise your .xls file will export the tags as part of your content.
ASP Active Directory Authentication
In my opinion, the main benefit of authenticating the login in your system against Active Directory (AD) is the fact that you won’t have to worry about maintaining passwords and it’s much easier for users to memorize only one. With the same username and password users can access the network domain and the system.
It’s simple to implement. The form contains two fields, username and password. AuthenticateUser is the core function:
<html><head></head>
<body>
<form method=”post”>
<tr>
<th>username:</th>
<td><input type=’text’ name=’UserName’ value=’<%=request.form(“UserName”)%>’ size=’30’></td>
</tr>
<tr>
<th>Password:</th>
<td><input type=’password’ name=’Password’ value=’<%=request.form(“Password”)%>’ size=’30’></td>
</tr>
<tr>
<td colspan=”2”><input name=”submit” type=”submit” value=” OK “><br /><br /></td>
</tr>
</tbody>
</table>
</form>
</body>
dim result
Domain = “servername” ‘Server name or IP
submit = request.form(“submit”)
if request.form(“submit”) = “ OK ” then
result = AuthenticateUser(request.form(“UserName”), request.form(“Password”), Domain)
if result then
response.write “Welcome!”
else
response.write “Username/Password incorrect”
end if
end if
function AuthenticateUser(UserName, Password, Domain)
dim strUser
AuthenticateUser = false
strUser = UserName
strPassword = Password
strQuery = “SELECT * FROM ‘LDAP://” & Domain & “’ WHERE objectClass=’*’ “
set oConn = server.CreateObject(“ADODB.Connection”)
oConn.Provider = “ADsDSOOBJECT”
oConn.Properties(“User ID”) = strUser
oConn.Properties(“Password”) = strPassword
oConn.Properties(“Encrypt Password”) = true
oConn.open “DS Query”, strUser, strPassword
set cmd = server.CreateObject(“ADODB.Command”)
set cmd.ActiveConnection = oConn
cmd.CommandText = strQuery
on error resume next
set oRS = cmd.Execute
if oRS.bof or oRS.eof then
AuthenticateUser = false
else
AuthenticateUser = true
end if
set oRS = nothing
set oConn = nothing
end function
Reading a XML file using Progress/OpenEdge
Progress has some specific methods that make it easier to read XML files. This example shows, in a simple way, how to use them. Considering a XML file which has the tags:
<employee number=”1”>
<name>John</name>
</employee>
This program will read the file and display the attribute number:
/* Declarations */
DEFINE VARIABLE hDoc AS HANDLE.
DEFINE VARIABLE hRoot AS HANDLE.
DEFINE VARIABLE hTable AS HANDLE.
DEFINE VARIABLE hField AS HANDLE.
DEFINE VARIABLE i AS INTEGER.
DEFINE VARIABLE j AS INTEGER.
DEFINE VARIABLE c-link AS CHARACTER NO-UNDO.
DEFINE VARIABLE c-attribute AS CHARACTER NO-UNDO VALUE “aaa”.
/* Create the objects we will need. */
CREATE X-DOCUMENT hDoc.
CREATE X-NODEREF hRoot.
CREATE X-NODEREF hTable.
CREATE X-NODEREF hField.
ASSIGN c-link = “http://server/application/myfile.xml?attrib=” + c-attribute).
/* Read in the file */
hDoc:LOAD (“file”, c-link, FALSE).
/* Get the root of the structure */
hDoc:GET-DOCUMENT-ELEMENT (hRoot).
/* Read each child from the root. */
REPEAT i = 1 TO hRoot:NUM-CHILDREN:
hRoot:GET-CHILD (hTable,i).
/* The remaining fields are elements with text values. */
REPEAT j = 1 TO hTable:NUM-CHILDREN:
hTable:GET-CHILD (hField,j).
disp hTable:GET-ATTRIBUTE(“number”).
END.
END.
/* Delete the objects we created.*/
DELETE OBJECT hDoc.
DELETE OBJECT hRoot.
DELETE OBJECT hTable.
DELETE OBJECT hField.
Javascript - Open maximized window
The javascript function below enables a link to open a maximized window:
<script language=”Javascript”>
function OpenPageMax(link,description)
{
var win = window.open(link, description, ‘toolbar=no,resizable=yes,scrollbars=yes’);
win.moveTo(-4, -4)
win.resizeTo(screen.availWidth + 8, screen.availHeight + 8)
}
</script>
You can call the function in the “onclick” property of a <a> tag, for example:
<a href=”#” onclick=”javascript:OpenPageMax(‘mypage.asp’,’Title’)”>Maximized Window</a>
Uploading/Reading textfile with Webspeed
Webspeed is a web-based script language for progress database. The code below is useful when you have to manipulate information provided by users in a text-file. It simply reads the file uploaded and store the data in a temp-table. For this you will need a html file with a form and a file field called w_file. Since it has a file field, the form must be define as ‘enctype=”multipart/form-data”’ otherwise the file won’t be uploaded. The code below should be in a ‘request_method = “post”’ condition.
DEFINE VARIABLE mfile AS MEMPTR NO-UNDO.
DEFINE VARIABLE cFile AS CHAR NO-UNDO.
DEFINE VARIABLE c-line AS CHAR NO-UNDO.
DEF STREAM s-imp.
ASSIGN mFile = get-binary-data(“w_file”).
IF mFile <> ? THEN DO:
ASSIGN cFile = SESSION:TEMP-DIRECTORY + “~/” + “myfile” + STRING(TIME,”HH:MM:SS”) + “.txt”.
COPY-LOB FROM mFile TO FILE cFile NO-CONVERT.
/*import file*/
INPUT STREAM s-imp FROM value(cfile) NO-ECHO.
REPEAT:
IMPORT STREAM s-imp UNFORMATTED c-line.
IF TRIM(c-line) <> “” THEN DO:
CREATE tt-temp.
ASSIGN tt-temp.content = c-line.
END.
END.
END.
PS: the binaryUploadMaxSize property in the ubroker.properties specifies a maximum size for uploaded files, so it must have a value bigger than 0.
Fluid 960 Grid System
Not every team can count with a interface designer or someone who was hired specifically to be in charge of the visual interface. Sometimes you just need and easy and quick way (yet powerful!) to present your project, that’s when a CSS framework can save your day! The Fluid 960 Grid System is definitely worth a try. You can easily adapt it to what you need and improve the features since it uses Mootools.
ASP Timeout expired
Sometimes you’re developing a report which uses a complex sql query leading to a timeout error. And then you have to face the “Microsoft OLE DB Provider for SQL Server error ‘80040e31’ Timeout expired” error. In these cases, try using “CommandTimeout”. For example:
Set sql_cmd = Server.CreateObject (“ADODB.Command”)
sql_cmd.CommandTimeout = 600 ‘10 minutes
sql_cmd.ActiveConnection = MM_objConn_STRING
sql_cmd.CommandText = “select * from mytable”
sql_cmd.Prepared = true
Set sqlptr = sql_cmd.Execute
While (NOT sqlptr.EOF)
%>
<tr>
<td><%=sqlptr.Fields.Item(“table_field”).Value %></td>
</tr>
<%
sqlptr.MoveNext()
Wend
If (sqlptr.CursorType > 0) Then
sqlptr.MoveFirst
Else
sqlptr.Requery
End If
sqlptr.Close()
Set sqlptr = Nothing
