Sunday, November 11, 2012

VBScript for URL link creation


We had a need to create URL link of our project on users's desktop using a script. First stage the users had enough rights on their PC's so we included the required icon file also with the script and passed the following VBScript to users to click on it to create the desktop icon link.

' Definie shell object
Set objWSHShell = WScript.CreateObject("Wscript.Shell")
strDesktop = objWSHShell.SpecialFolders("Desktop")
' Copy the icon file to windows
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFileCopy = objFSO.GetFile( objWSHShell.CurrentDirectory&"\icon.ico")
objFileCopy.Copy ("C:\WINDOWS\system32\")
' Define link properties and create link
strShortcutName = "Log In" ' Shortcut Name - Edit for the wanted name
strShortcutPath = "http://thesystem.com/Login.aspx" ' Link URL - Edit to the wanted URL
strIconPath = "C:\WINDOWS\system32\icon.ico"
Set objShortcut = objWSHShell.CreateShortcut(strDesktop & "\" & strShortcutName & ".lnk")
objShortcut.TargetPath=strShortcutPath
objShortcut.IconLocation=strIconPath
objShortcut.Save

' Quit Script
WScript.Quit

But later the user access rights has been limited and were blocked accessing system folder, the link creator didn't created the link with supplied icon file. As the icon file is copied to the system folder. So the updated script would not use supplied icon file, but make use of existing system icon.

' Definie shell object
Set objWSHShell = WScript.CreateObject("Wscript.Shell")
strDesktop = objWSHShell.SpecialFolders("Desktop")

' Define link properties and create link
strShortcutName = "LogIn" ' Shortcut Name - Edit for the wanted name
strShortcutPath = "http://thesystem.com/Login.aspx" ' Link URL - Edit to the wanted URL
strIconPath = "c:\windows\system32\shell32.dll,44"
Set objShortcut = objWSHShell.CreateShortcut(strDesktop & "\" & strShortcutName & ".lnk")
objShortcut.TargetPath=strShortcutPath
objShortcut.IconLocation=strIconPath
objShortcut.Save

' Quit Script
WScript.Quit

Now the icon shown from system.

Friday, October 5, 2012

ASP.NET default Login page and controller modification for using with Web Service or local method

By default the ASP.NET login page and controls are bound with default methods and its bit uneasy to work with ( at least for some ppl like me). We could make use of same controller and pages with our own mechanism of logging in.

1. Changes on web.config

<location path="Login.aspx">
    <system.web>
      <authorization>
        <allow users="*" />
      </authorization>
    </system.web>
  </location>
  <location path="Styles">
    <system.web>
      <authorization>
        <allow users="*" />
      </authorization>
    </system.web>
  </location>
  <location path="Images">
    <system.web>
      <authorization>
        <allow users="*" />
      </authorization>
    </system.web>
  </location>
  <system.web>
    <authorization>
      <deny users="?" />
    </authorization>
  </system.web>

2. Moving files
    Move the Login.aspx and related files outside the default folder and delete the folder
   

3. Update on Login frontend, add the onclick method

<p class="submitButton">
                    <asp:Button ID="LoginButton" runat="server" onclick="LoginButton_Click" Text="Log In" ValidationGroup="LoginUserValidationGroup"/>
                </p>

4. Update on Login backend

protected void LoginButton_Click(object sender, EventArgs e)
        {
            bool authSuccess = false;
            sc.AppCode = WebConfigurationManager.AppSettings["appCode"];
            sc.AppPwd = WebConfigurationManager.AppSettings["appPwd"];
            sc.ClientIP = "aa";
            sc.EndUserId = "aa";

            try
            {
                if (LoginUser.UserName == WebConfigurationManager.AppSettings["userId"]) // web.config user id
                {
                    if (LoginUser.Password == WebConfigurationManager.AppSettings["userPwd"])
                    {
                        authSuccess = true;
                    }
                    else 
                    {
                        authSuccess = false;
                    }
                }
                else // other user id
                {
                    req.ServiceContext = sc;
                    req.UserId = LoginUser.UserName;
                    req.UserPwd = LoginUser.Password;

                    try
                    {
                        response = port.Authenticate(req);
                        if(response.Status.ToUpper() == "OK")
                        {
                            authSuccess = true;
                        }
                        else
                        {
                            authSuccess = false;
                            wsStatus.Text = response.Message;
                        }

                    }
                    catch (Exception lex)
                    {
                        wsStatus.Text = lex.Message;
                        authSuccess = false;
                    }
                }

                //
                if (authSuccess)
                {
                    FormsAuthentication.SetAuthCookie(LoginUser.UserName, false /* createPersistentCookie */);
                    string continueUrl = Request.QueryString["ReturnUrl"];
                    if (String.IsNullOrEmpty(continueUrl))
                    {
                        continueUrl = "~/";
                    }
                    Response.Redirect(continueUrl);
                }
                else
                {
                    LoginUser.FailureText = "Login Failure!";
                }
            }
            catch (Exception lex)
            {
                LoginUser.FailureText = lex.Message;
            }
        }

ASP.NET - Almost complete use of Datagrid

I was on a need of creating datagrid object on my page with sorting, filtering, updating needs. You could make use of below example for some complex cases (Dropdown, Date selector update columns for relational data table updates, filters, multiple update queries)

    <table border="0" cellspacing="0">
        <tbody>
<tr>
        <th>Owner_Name</th>
        <th>OS</th>
        <th>Type</th>
        <th>Status</th>
        <th>Action Type</th>
        </tr>
<tr>
        <td><asp:dropdownlist appenddatabounditems="true" autopostback="true" datasourceid="dsPopulateOwner_Name" datatextfield="UNAME" datavaluefield="UID" font-size="11px" id="ddlOwner_Name" runat="server" width="130px">
            <asp:listitem text="*" value="%"></asp:listitem>
        </asp:dropdownlist>
        <asp:sqldatasource connectionstring="&lt;%$ ConnectionStrings:Connection %&gt;" id="dsPopulateOwner_Name" runat="server" selectcommand="SELECT
            Id as UID, Name as UNAME FROM tblUser"></asp:sqldatasource>
        </td>
        <td><asp:dropdownlist appenddatabounditems="true" autopostback="true" datasourceid="dsPopulateOS" datavaluefield="OS" font-size="11px" id="ddlOS" runat="server" width="130px">
            <asp:listitem text="All" value="%"></asp:listitem>
        </asp:dropdownlist>
        <asp:sqldatasource connectionstring="&lt;%$ ConnectionStrings:Connection %&gt;" id="dsPopulateOS" runat="server" selectcommand="SELECT
            DISTINCT OS FROM tblServer"></asp:sqldatasource></td>
        <td><asp:dropdownlist appenddatabounditems="true" autopostback="true" datasourceid="dsPopulateType" datavaluefield="Type" font-size="11px" id="ddlType" runat="server" width="130px">
            <asp:listitem text="All" value="%"></asp:listitem>
        </asp:dropdownlist>
        <asp:sqldatasource connectionstring="&lt;%$ ConnectionStrings:Connection %&gt;" id="dsPopulateType" runat="server" selectcommand="SELECT
            DISTINCT Type FROM tblServer"></asp:sqldatasource></td>
        <td><asp:dropdownlist appenddatabounditems="true" autopostback="true" datasourceid="dsPopulateStatus" datavaluefield="Status" font-size="11px" id="ddlStatus" runat="server" width="130px">
            <asp:listitem text="All" value="%"></asp:listitem>
        </asp:dropdownlist>
        <asp:sqldatasource connectionstring="&lt;%$ ConnectionStrings:Connection %&gt;" id="dsPopulateStatus" runat="server" selectcommand="SELECT
            DISTINCT Status FROM tblServer"></asp:sqldatasource></td>
        <td><asp:dropdownlist appenddatabounditems="true" autopostback="true" datasourceid="dsPopulateActionType" datavaluefield="ActionType" font-size="11px" id="ddlActionType" runat="server" width="130px">
            <asp:listitem text="All" value="%"></asp:listitem>
        </asp:dropdownlist>
        <asp:sqldatasource connectionstring="&lt;%$ ConnectionStrings:Connection %&gt;" id="dsPopulateActionType" runat="server" selectcommand="SELECT
            DISTINCT ActionType FROM tblServer"></asp:sqldatasource></td>
        </tr>
</tbody></table>
<asp:gridview allowsorting="True" autogeneratecolumns="False" cellpadding="3" datakeynames="Id" datasourceid="SqlDataSource1" id="GridView1" pageindex="1" pagesize="100" runat="server" width="95%">
        <alternatingrowstyle backcolor="#B6CFA5">
        <columns>
        <asp:templatefield headertext="Select">
        <itemtemplate>
        <asp:checkbox id="chkSelect" runat="server">
        </asp:checkbox></itemtemplate>
        </asp:templatefield>
            <asp:boundfield datafield="Id" headertext="ID" readonly="true" sortexpression="Id" visible="true">
            <asp:boundfield controlstyle-cssclass="cssWdth" datafield="Hostname" headertext="Hostname" sortexpression="Hostname">
            </asp:boundfield>
            <asp:boundfield controlstyle-cssclass="cssWdth" datafield="IP_Address" headertext="IP_Address" sortexpression="IP_Address">
            </asp:boundfield>
            <asp:templatefield headerstyle-horizontalalign="Left" headertext="Owner" sortexpression="Owner"> 
               <edititemtemplate> 
                <asp:dropdownlist appenddatabounditems="true" autopostback="false" cssclass="cssWdth" datasourceid="dsPopulateOwner_Name" datatextfield="UNAME" datavaluefield="UID" id="ddlEditOwner_Name" runat="server" selectedvalue="&lt;%# Bind(&quot;Owner&quot;) %&gt;">
                </asp:dropdownlist>
               </edititemtemplate>  
               <itemtemplate> 
                <asp:label id="lblOwner_Name" runat="server" text="&lt;%# Bind(&quot;Owner_Name&quot;) %&gt;"></asp:label> 
               </itemtemplate>
            </asp:templatefield>
            <asp:templatefield headerstyle-horizontalalign="Left" headertext="OS" sortexpression="OS"> 
               <edititemtemplate> 
                <asp:dropdownlist cssclass="cssWdth" datasourceid="dsPopulateOS" datatextfield="OS" datavaluefield="OS" id="ddlEditOS" runat="server" selectedvalue="&lt;%# Bind(&quot;OS&quot;) %&gt;">
                </asp:dropdownlist>
               </edititemtemplate>  
               <itemtemplate> 
                <asp:label id="lblOS" runat="server" text="&lt;%# Bind(&quot;OS&quot;) %&gt;"></asp:label> 
               </itemtemplate>
            </asp:templatefield>
            <asp:templatefield headerstyle-horizontalalign="Left" headertext="Type" sortexpression="Type"> 
               <edititemtemplate> 
                <asp:dropdownlist cssclass="cssWdth" datasourceid="dsPopulateType" datatextfield="Type" datavaluefield="Type" id="ddlEditType" runat="server" selectedvalue="&lt;%# Bind(&quot;Type&quot;) %&gt;">
                </asp:dropdownlist>
               </edititemtemplate>  
               <itemtemplate> 
                <asp:label id="lblType" runat="server" text="&lt;%# Bind(&quot;Type&quot;) %&gt;"></asp:label> 
               </itemtemplate>
            </asp:templatefield>
            <asp:templatefield headerstyle-horizontalalign="Left" headertext="Status" sortexpression="Status"> 
               <edititemtemplate> 
                <asp:dropdownlist cssclass="cssWdth" datasourceid="dsPopulateStatus" datatextfield="Status" datavaluefield="Status" id="ddlEditStatus" runat="server" selectedvalue="&lt;%# Bind(&quot;Status&quot;) %&gt;">
                </asp:dropdownlist>
               </edititemtemplate>  
               <itemtemplate> 
                <asp:label id="lblStatus" runat="server" text="&lt;%# Bind(&quot;Status&quot;) %&gt;"></asp:label> 
               </itemtemplate>
            </asp:templatefield>
            <asp:templatefield headerstyle-horizontalalign="Left" headertext="ActionType" sortexpression="ActionType"> 
               <edititemtemplate> 
                <asp:dropdownlist appenddatabounditems="true" autopostback="false" cssclass="cssWdth" datasourceid="dsPopulateActionType" datatextfield="ActionType" datavaluefield="ActionType" id="ddlEditActionType" runat="server" selectedvalue="&lt;%# Bind(&quot;ActionType&quot;) %&gt;">
                </asp:dropdownlist>
               </edititemtemplate>  
               <itemtemplate> 
                <asp:label id="lblActionType" runat="server" text="&lt;%# Bind(&quot;ActionType&quot;) %&gt;"></asp:label> 
               </itemtemplate>
            </asp:templatefield>
            <asp:templatefield headerstyle-horizontalalign="Left" headertext="ActionDate" sortexpression="ActionDate"> 
               <edititemtemplate> 
                <asp:textbox cssclass="cssWdth" id="TextBox2" runat="server" text="&lt;%# Bind(&quot;ActionDate&quot;) %&gt;"></asp:textbox>
                <ajaxtoolkit:calendarextender format="dd-MM-yyyy" id="CalendarExtender2" runat="server" targetcontrolid="TextBox2">
               </ajaxtoolkit:calendarextender></edititemtemplate>  
               <itemtemplate> 
                <asp:label id="lblActionDate" runat="server" text="&lt;%# Bind(&quot;ActionDate&quot;) %&gt;"></asp:label> 
               </itemtemplate>
            </asp:templatefield>
            <asp:commandfield showeditbutton="True">
        </asp:commandfield></asp:boundfield></columns>
        <editrowstyle backcolor="#FFCC99" width="95%">
        </editrowstyle></alternatingrowstyle></asp:gridview>
        </code><br />
<code><asp:button id="btnSelct" onclick="GetSelected" runat="server" text="Edit selected records">
        <asp:button id="btnCancel" postbackurl="~/Default.aspx" runat="server" text="Return">
        </asp:button></asp:button></code><br />
<code>
        <asp:sqldatasource connectionstring="&lt;%$ ConnectionStrings:Connection %&gt;" filterexpression="Owner_Name like '{0}%' and OS like '{1}%' and Type like '{2}%' and Status like '{3}%' and ActionType like '{4}%'" id="SqlDataSource1" runat="server" selectcommand="SELECT S.ID, S.HostName AS Hostname, S.IP AS IP_Address, U.Name AS Owner_Name, S.Owner, S.OS, S.Type, S.Status, S.ActionType, S.ActionDate 
            FROM tblServer AS S INNER JOIN tblUser AS U ON S.Owner = U.Id" updatecommand="UPDATE [tblServer] 
                           SET [HostName] = @HostName
                            ,[IP] = @IP_Address
                            ,[Owner] = @Owner
                            ,[OS] = @OS
                            ,[Type] = @Type
                            ,[Status] = @Status
                            ,[ActionType] = @ActionType
                            ,[ActionDate] = @ActionDate
                            ,[ModifiedDate] = CURRENT_TIMESTAMP
                            ,[ModifiedBy] = @ModifiedBy 
                           WHERE [Id] = @Id; 
                           INSERT INTO [tblActivityLog]
                           ([ActionTimelog]
                            ,[ActionCode]
                            ,[ActionUserName]
                            ,[ServerHostname]
                            ,[ServerLocation]
                            ,[ServerOS]
                            ,[ServerType]
                            ,[ServerOwner]
                            ,[ServerStatus]
                            ,[ServerFollowUpDate]
                            ,[ServerActionType]
                            ,[ServerActionDate]
                            ,[UserLogin]
                            ,[UserName]
                            ,[UserType]
                            ,[UserStatus]
                            ,[Notes]) 
                            values(
                            CURRENT_TIMESTAMP
                            ,@ActionCode
                            ,@ModifiedBy
                            ,@HostName
                            ,''
                            ,@OS
                            ,@Type
                            ,@Owner
                            ,@Status
                            ,''
                            ,@ActionType
                            ,@ActionDate
                            ,@ModifiedBy
                            ,@ModifiedBy
                            ,''
                            ,''
                            ,''
                            );">
            <filterparameters>
                     <asp:controlparameter controlid="ddlOwner_Name" name="Name" propertyname="SelectedItem.Text">
                     <asp:controlparameter controlid="ddlOS" name="OS" propertyname="SelectedValue">
                     <asp:controlparameter controlid="ddlType" name="Type" propertyname="SelectedValue"> 
                    <asp:controlparameter controlid="ddlStatus" name="Status" propertyname="SelectedValue">
                    <asp:controlparameter controlid="ddlActionType" name="ActionType" propertyname="SelectedValue">                
            </asp:controlparameter></asp:controlparameter></asp:controlparameter></asp:controlparameter></asp:controlparameter></filterparameters>
            <updateparameters>
                <asp:parameter name="Id" type="Int32">
                <asp:parameter name="HostName" type="String">
                <asp:parameter name="IP_Address" type="String">
                <asp:parameter name="Owner" type="Int32">
                <asp:parameter name="OS" type="String">
                <asp:parameter name="Type" type="String">
                <asp:parameter name="Status" type="String">
                <asp:parameter name="ActionType" type="String">
                <asp:parameter name="ActionDate" type="DateTime">
                <asp:parameter defaultvalue="UnKnown" name="ModifiedBy" type="string">
                <asp:parameter defaultvalue="UPD" name="ActionCode" type="string">
            </asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></updateparameters>
        </asp:sqldatasource>


Friday, August 10, 2012

MSSQL Split, Pad Zeroes, Get Alphabets, and Substring

Got a request to create a data table get from some existing data to some required data formatting.
The Source data is like 'C10 L1_B21773197D.xls', 'EV1 L1_B21773602F.xls', 'EV116 L1_B21773659G.xls', etc.
The following were the requirements.

CategoryCode
No
NVARCHAR(10)
C
Contained in legacy data file name
Sample legacy data file name : “C10 L1_B21773197D
1.       Alphabets prior to Numeric characters)
FolderNo
No
VARCHAR(10)
C001
Contained in legacy data file name
Sample legacy data file name : “C10 L1_B21773197D
1.       convert numeric characters prior to “white space” to a 3 digit number (e.g. “1” --> “001”)
2.       Append 3 digit number to Category Code
VolumeNo
No
VARCHAR(10)
C001L01
Contained in legacy data file name
Sample legacy data file name : “C10 L1_B21773197D
1.       Extract the 2nd occurrence of an Alphabets in File Name after “white Space” (It will be value L or V)
2.       convert the numeric characters prior to “_”to a 2 digit number (e.g. “1” --> “01”)
3.       Append the 2 digit number (step2) to Alphabet (step1) to get volume code like L01
4.       Append volume code to Folder No to get Volume No like C001L01
VolumeBarcode
No
VARCHAR(12)
B21773203C
Contained in legacy data file name
Sample legacy data file name : “C10 L1_B21773197D
·         The 10 Alphanumeric characters after “_”

I created a temporary table with required values and get the desired data on another SELECT.
Follows the SQL statements.

SELECT SUBSTRING([Filename],1,(len([Filename]) - 4)) as TFilename  --This line will remove the file extension 
 ,dbo.f_get_alphabets(PARSENAME(REPLACE(SUBSTRING([Filename],1,(len([Filename]) - 4)), ' ', '.'), 2)) as Code -- This is to get only the alphabets from first portion
,dbo.f_get_numbers(PARSENAME(REPLACE(SUBSTRING([Filename],1,(len([Filename]) - 4)), ' ', '.'), 2)) AS TCat --This is to get only the numbers from first portion
,PARSENAME(REPLACE(SUBSTRING([Filename],1,(len([Filename]) - 4)), ' ', '.'), 1) as Vol -- This part is to get the second portion
into #TempLD -- Store data into temp table
FROM [dbo].[SourceTable]
  
  SELECT Code as CategoryCode,
  Code + RIGHT('000'+ TCat, 3) as FolderNo, -- Padding Zeroes infront to make it 3 digit number with zeroes in front
  Code + RIGHT('000'+ TCat, 3) + LEFT(PARSENAME(REPLACE(Vol, '_', '.'), 2),1)+RIGHT('00'+ RIGHT(PARSENAME(REPLACE(Vol, '_', '.'), 2),1),2) as VolumeNo, -- Padded numbers and Alphabets concatenated to make the desired string
  PARSENAME(REPLACE(Vol, '_', '.'), 1) as VolumeBarcode, -- Split the portion behind _ character
  from #TempLD

Follow is the function used on the above query. For numbers, the IF condition would be changed as @code between 48 and 57.

CREATE FUNCTION [dbo].[f_get_alphabets](
        @string                varchar(255)
) RETURNS varchar(255)
BEGIN
    
    DECLARE @newstring varchar(255)
    DECLARE @num int
    DECLARE @code int
    SELECT @newstring = ''
    SELECT @num = 1

    WHILE @num < LEN(@string)+1
    BEGIN
        SET @code = ASCII(SUBSTRING(@string, @num, 1))
        --- ascii: 48-57 (numbers 0-9)
        --- ascii: 65-90 (uppercase letters A-Z)
        --- ascii: 97-122 (lowercase letters a-z)
        IF (@code between 65 and 90) or (@code between 97 and 122)
        BEGIN
            SET @newstring = @newstring + SUBSTRING(@string, @num, 1)
        END
        SET @num = @num + 1
    END
    RETURN @newstring
END