There's an answer to your question.

Jerry Dausman's profileThere's an answer to you...BlogLists

MCT Blogs


Microsoft Certified Trainer blogs

Blogs I Like


Tomas Havetta If you like Czechs ...

Ralph Shillington's 'Things I Like' blog Looks into the things I like, too.

Mikhail Arkhipov's Weblog Visual web developer stuff.

Karla's Coding and Stuff Blog A .NET coding-themed blog for the whole world, if it wants

Leadership By Numbers General management issues and Lotus Notes tech stuff, by my cousin Jack.

Books I Like


HTML Programmer's Reference by Thomas A. Powell and Dan Whitworth

Visual Basic.NET: A Beginner's Guide by Jeff Kent

So we had a problem today. We were trying to create, from an .asp page, an Excel spreadsheet that users could download. One of the columns carried zip codes, but the leading zeros of the zip codes were dropped, since Excel thought of these as numbers. Well, there's a lot of complicated things you can do, such as <meta> tags and such, but what's the easiest way to get this data into Excel, and have it display correctly? Just write the data in an HTML style table and use a style tag that Excel recognizes.

The example below is a simple spreadsheet, saved with the .xls extension for Excel. It has only two cells, both with the zip code value "02139" included. Notice that the second cell has a class attribute of "txt" however. That makes all the difference.

<html>
<head>
<style>
.txt {mso-number-format:\@;}
</style>
</head>
<body> <table>
    <tr>
      <td>02139</td>
      <td class='txt'>02139</td>
    </tr>
</table></body>
</html>

January 24

Searching for Answers

I can't believe I haven't added anything to this blog in 11 months! Somebody wake me up!

On my new job I wrote a script to help me find specific text in files. I know what you're going to say: there's a built in feature in Windows that will do the same thing. True, but it only gives me a list of files. I want more! And I got more. The script below does the following:

uses an specific list of files to search (editable),
lists the line number where the text was found in the file,
puts the output in .csv format so I can play with it in another program, and
can be adapted to display any information about the file.

The script uses a list (Reports.txt) as an input list of files to scan. You can create this list by using the directory command at the "C:>" prompt as follows:

dir *.* /B /S >Reports.txt

Here's the code for the script that does the work:

' ************************************
' **                                **
' ** Scan for Particular Commands **
' **                                **
' **        Scan4Commands.vbs       **
' **                                **
' ************************************
'
' Copyright (C) 2007 Jerome F. Dausman
'
' Purpose:
'
'    This program takes a list of reports
' and scans the list for particular text.
' The user inputs the path and filename
' of the text file listing the reports.
' The user also inputs the text that is
' being searched for.
' An output file is created with the
' cross-reference information.
'
'=== DECLARATIONS =========================================

Option Explicit

'--- Objects
Dim objFSO        'file system object
Dim objRepts      'the list of report files
Dim objRpt        'the report file being scanned
Dim objOut        'the output report generated

'--- string variables
Dim str           'useful string variable
Dim strScript     'this script
Dim strLocation   'this script's location
Dim strRepts      'the name of the listing file
Dim strRpt        'the name of individual report files
Dim strOut        'the name of the output file
Dim strSearch     'the search string

'--- numeric variables
Dim intLine 'line count
Dim intX 'useful integer

'--- constants
Const ForReading = 1
Const ForAppending = 8

Const strQ = """"
Const strC = ","

'CONST vbOKOnly = 0
'CONST vbOKCancel = 1 'Display OK and Cancel buttons.
'CONST vbAbortRetryIgnore = 2 'Display Abort, Retry, and Ignore buttons.
'CONST vbYesNoCancel = 3 'Display Yes, No, and Cancel buttons.
'CONST vbYesNo = 4 'Display Yes and No buttons.
'CONST vbRetryCancel = 5 'Display Retry and Cancel buttons.
'CONST vbCritical = 16 'Display Critical Message icon.
'CONST vbQuestion = 32 'Display Warning Query icon.
'CONST vbExclamation = 48 'Display Warning Message icon.
'CONST vbInformation = 64 'Display Information Message icon.
'CONST vbDefaultButton1 = 0 'First button is the default.
'CONST vbDefaultButton2 = 256 'Second button is the default.
'CONST vbDefaultButton3 = 512 'Third button is the default.
'CONST vbDefaultButton4 = 768 'Fourth button is the default.
'CONST vbApplicationModal = 0 'Application modal.
'CONST vbSystemModal = 4096 'System modal.

'=== MAIN PROGRAM =========================================

'--- get the script name and location --------
strScript = WScript.ScriptName
strLocation = WScript.ScriptFullName
intX = Instr(strLocation, strScript) - 1
strLocation = Left(strLocation, intX)
MsgBox strLocation

'-- get the name of the textfile list of reports to scan
str = "What is the name of the list of report files to scan?"
strRepts = InputBox(str,"List of Reports", "Reports.txt")

'--- check file validity ---------------------
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Not (objFSO.FileExists(strRepts)) Then
MsgBox "The file '" & strRepts & "' does not exist.", vbCritical, "No Scan"
WScript.Quit
End If

'--- create an output file -------------------
str = "What is the name of the report file you will create?"
strOut = InputBox(str, "Output File", "Search.csv")
On Error Resume Next
Set objOut = objFSO.OpenTextFile(strOut, ForAppending, True)
If (Err.Number <> 0) Then
str = strOut & vbCrLf
str = str & "is not a valid file for reporting."
Msgbox str, vbCritical, "No Output"
WScript.Quit
End If
On Error Goto 0

'--- get input search string -----------------
str = "What is the string you are looking for?"
strSearch = InputBox(str, "Search String", ".CommandText")

'--- write output header ---------------------
objOut.WriteLine(" ")
str = strQ & "===============================================" & strQ
objOut.WriteLine(str)
str = strQ & "Scan4Command Output Report" & strQ & strC
str = str & strQ & Date() & strQ
objOut.WriteLine(str)
str = strQ & "-----------------------------------------------" & strQ
objOut.WriteLine(str)
str = "Search Text: " & strSearch
objOut.WriteLine(str)
str = strQ & "===============================================" & strQ
objOut.WriteLine(str)
objOut.WriteLine(" ")
str = strQ & "Report" & strQ & strC
str = str & strQ & "Line" & strQ & strC
str = str & strQ & "Text" & strQ
objOut.WriteLine(str)
str = strQ & "-------" & strQ & strC
str = str & strQ & "-----" & strQ & strC
str = str & strQ & "-----------------------" & strQ
objOut.WriteLine(str)

'--- read from list and loop -----------------
Set objRepts = objFSO.OpenTextFile(strRepts, ForReading)
Do While objRepts.AtEndOfStream <> True

    '--- test a file name --------------------
    strRpt = objRepts.ReadLine
    If (objFSO.FileExists(strRpt)) Then

        '--- open the file -------------------
        intLine = 0
on error resume next
        Set objRpt = objFSO.OpenTextFile(strRpt, ForReading)
If Err.Number <> 0 Then
    msgbox "bad file = " & str
End If
on error goto 0

        '--- look for the search string ------
        Do While objRpt.AtEndOfStream <> True
            str = objRpt.ReadLine
            intLine = intLine + 1
            intX = Instr(str, strSearch)
            If (intX > 0) Then
               str = mid(str, intX)

               '--- output info --------------
               objOut.Write(strQ & strRpt & strQ)
               objOut.Write(strC & CStr(intLine) & strC)
               objOut.WriteLine(strQ & str & strQ)
            End If
        Loop
    End If
Loop

'--- write end of output ---------------------
str = strQ & "===============================================" & strQ
objOut.WriteLine(str)

'--- down and out ----------------------------
objRepts.Close
objOut.Close
str = "Done!"
MsgBox str, vbInformation, "Scan4Commands"
WScript.Quit

Hope this is helpful for you!

February 26

Forgotten Dependancies

Correct me if I'm wrong, but I believe past versions of SQL Server used to show all dependancies for all types of objects. Today I had to find out in which stored procedures a certain view was used. I ran sp_depends on the name of the view and all I got was the tables that the view depended upon. Knowing that the view was used in at least one stored procedure I proceeded to investigate the system table sysdepends. Lo and behold, there was no information for that stored procedures relationship to the view in sysdepends! What to do? Write my own procedure that I'll call spDependsAlso:

CREATE PROCEDURE spDependsAlso
@bblock varchar(50)
AS
SELECT
@bblock AS 'Building_Block'
, so.name AS 'Dependant'
FROM sysobjects so JOIN syscomments sc ON so.id = sc.id
WHERE sc.text like '%' + @bblock + '%'
AND @bblock <> so.name

Of course, you may want to create this as a function instead. And it would certainly work faster if we used the 'CONTAINS' function in the WHERE clause ... but then we'd have to use Search Service and regularly update the search indexes. I find this version works well enough for me.

January 25

New Job

What would you say to to the same hours, but more pay?

A good friend and neighbor dropped an offer in my lap last November. So not wanting to turn down a big raise and a challenging position I accepted. I am now working for Dynamics Research Corporation on a contract with the Department of Homeland Security, Citizenship and Immigration Services. My official title is Senior Staff IT Analyst.

But wait! There's more!

Two and a half weeks into the job my new boss gets an offer he can't refuse! So who gets to take his old job? It's offered to me ... and I took a second raise and promotion (effective February 1).

Call now! Operators are Standing By!

I will continue with this blog ... for several reasons: 1) my job still involves teaching, albeit a lot less; 2) I intend to continue on as retain my MCT certification; and 3) I seem to like sharing what I learn!

1:12 PM | Add a comment | Permalink | Blog it | miscellaneous

September 28

Sharing Session between ASP and ASP.NET

One of my students in my recent Upgrading to ASP.NET class wanted to know how to share Application and Session variables between .asp and .aspx pages. The manual says you can't do it directly. Two minutes after the last student left I found good answers from Microsoft MVP Peter A. Bromberg's site.

There are two ways you can share the information. We discussed one of the ways in class: create a COM object that you use in place of a session object. When you save a session variable you would use both the session id (which you can retrieve from ServerVariables) and a key to store the value. Since you can use COM objects in both .asp and .aspx pages, each would have access to the information. The COM object would typically use a database to store the data on the back end. The second way the MVP suggested was to store the data in both Session variables and hidden <input> tags, posting them across from .asp to .aspx, and vice versa.

A detailed discussion and accompanying code can be found at http://www.eggheadcafe.com/articles/20021207.asp and

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/ConvertToASPNET.asp

[ms2310, module 14, page 7
and ms2640, module 5, page 8]

View more entries

Profile

Jerry Dausman

Categories

Archives