System Date and Batch Files

Batch File that will create a dynamic file name based on System Date

I've been working on batch files recently. My client  would want to copy the  files to network access storage (NAS). The scenario would be that we daily the system will copy certain file to NAS to avoid overriding the file we want a dynamic file name. One of the option is to use vb script invoking file system object. Another is to use batch file. Batch file would be the first choice for ease of use. The question would then be how are we going to do this such that we  file name will be using the system date? The answer is.

set strname="%date:~10,4%-%date:~4,2%-%date:~7,2%"

You may also refer to this blog post.

Macro in Action

I've been using macro for quite sometime now. One of the most commonly used is to retrieved data from certain database server. In this post I will discuss on how to retrieved data from database. So lets get started. We will be using import code to retrieved our data. But before using the import code we need to have a connection string. Below is the typical connection string. It has 3 Parameters which we will explain later.

Public Function ConnectSQL(bConnectOption As Boolean, datasource As String, catalog As String) As Boolean
  On Error GoTo ErrorHandler
  Set conADO = New ADODB.Connection
  If bConnectOption = True Then
    If conADO.State = adStateClosed Then
      With conADO          
           .ConnectionString = "Provider=SQLOLEDB.1; Integrated Security=SSPI;Persist Security Info =false; Data Source=" & datasource & ";" & "Initial Catalog=" & catalog & ";"
         .CursorLocation = adUseClient
        .Open
        ConnectSQL = True
      End With
    Else
      ConnectSQL = True
    End If
  Else
    If conADO.State = adStateClosed Then
      With conADO       
       .ConnectionString = "Provider=SQLOLEDB.1; Integrated Security=SSPI;Persist Security Info =false; Data Source=" & datasource & ";" & "Initial Catalog=" & catalog & ";"
       
        .CursorLocation = adUseClient
        .Open
        ConnectSQL = True
      End With
    Else
      ConnectSQL = True
    End If
  End If
  Exit Function
ErrorHandler:
  MsgBox "SQL server login failed. Pls contact your system administrator", vbInformation
End Function 

 Code below will get the data and put it in the sheet.

Private Sub GetDataFromDatabase(targetCell As Range, catalog As String, datasource As String)
    On Error GoTo err
       
        Dim strsql As String
        Dim yrcdlr As Integer
        yrcldr1 = Year(CDate(Sheet1.Range("O4").Text))
        yrcldr2 = Year(CDate(Sheet1.Range("R4").Text))
       
        Set MyRs = New ADODB.Recordset
        If ConnectSQL(True, datasource, catalog) Then
       
        strsql = "SELECT * FROM TABLEName"
           
            If MyRs.State = 1 Then MyRs.Close
               MyRs.Open strsql, conADO, adOpenForwardOnly, adLockReadOnly
              
               If MyRs.RecordCount > 0 Then
                    RS2WS MyRs, targetCell
               End If
              
               MyRs.Close
               
        End If
   
    Set MyRs = Nothing
   
err:
    Exit Sub
    MsgBox err.Description, vbInformation
End Sub

Server Error in '/Application Name' Application

Today, I encountered error Server Error in '/Application Name' Application.

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Ideally in web application when using stored procedure in collating data the processing
time for stored should be not greater than 1 second only. However  in my case the processing
time was 34 sec that's 34 times slower than the ideal time. In displaying details to the users I used the N-tier approach and used stored procedure in querying data. But my stored procedure is too slow, as mentioned in generating 128923 rows  it took 34seconds to processed. Increasing the timeout period can temporarily cure the problem but it is not a proactive approach. Instead I make use of temporary tables during run time and scaling my database - proper indexing.

Hacker Way

I'm a big fan of MARK ZUCKERBERG, when his company goes Public he Published this article. I can't really say that I'm a hacker, but I love innovate - As they say innovation is the father of invention. I can say that I have learned from experts. As Mr Zuckerberg say and I quote  "Hacking is also an inherently hands-on and active discipline. Instead of debating for days whether a new idea is possible or what the best way to build something is, hackers would rather just prototype something and see what works. There’s a hacker mantra that you’ll hear a lot around Facebook offices: “Code wins arguments.” Read More

Google to start scanning andriod apps

http://googlemobile.blogspot.com/2012/02/android-and-security.html with this move android apps will now be free of malware - better than perfect.