Humprey Evangelista Cogay

My Programming Adventures
SQL versions

Its been months since i have'nt posted anything haha. I was addicted to photography indeed. Http://HumpreyCogay.BlogSpot.Com

Any way i want to share one of my problems then. I've been coding SQL Querries for SQL Server for several years now, but working for the bank we use AS400 DB2 as a main DBMS. The problem here is there are several differences between the 2. SQL Server and DB2.

 Luckily i hava a book titled SQL Cook Book published by O'Reilly.

Here's a sample problem that i have(from the book)

   update e
      set e.sal  = ns.sal,
          e.comm = ns.sal/2
     from emp e,
          new_sal ns
    where ns.deptno = e.deptno

And you cannot use it in DB2, Here's the DB2 Version instead

 update emp e set (e.sal,e.comm) = (select ns.sal, ns.sal/2
                                      from new_sal ns
                                     where ns.deptno=e.deptno)
  where exists ( select null
                   from new_sal ns
                  where ns.deptno = e.deptno )

Now I'm back in the community... Hehehhe

Posted: 04-23-2008 2:44 PM by Comgen | with 1 comment(s)
Filed under: ,
New Hobby

 Canon 40D

 

 Canon 40D

I always admired photography. And as a gift to my self i bought a Canon EOS 40D

        http://www.dpreview.com/reviews/canoneos40d/ 

 Now i can use my own images on my Flash Animations.....

 Any Amateur photographers there? maybe jam tayo hehehe.....

Posted: 12-13-2007 3:49 PM by Comgen | with 6 comment(s)
Filed under: , ,
Use Membership API in Winforms

I usually code my own authentication module in my Windows Application but since i have been playing with Web Applications lately, I thought, maybe there's a way  to use the Membership API inside System.Web on my Windows Applications. So after playing and Googling around i have made it worked.

Below are the procedures  

  1. Use aspnet_regsql.exe (can be found inside C:\WINNT\Microsoft.NET\Framework\v2.0.50727 for VB 2005 Express ) to create your Membership related tables and Stored Procedures, Just follow the on screen procedures, I have tested this with SQL Server 2000.
  2. Create a New Windows Application
  3. Open your App.Config and add the following entries
    <connectionStrings>
        <add name="MySqlConnection"
             connectionString="Server=YOURSERVERNAME; Database=YOURDATABASE; User Id=YOURUSERID; password=YOURPASSWORD"
             providerName="System.Data.SqlClient" />
    </connectionStrings
<system.web>
        <membership defaultProvider="SqlProvider">

            <providers>

                <clear />

                <add name="SqlProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="MySqlConnection"

                   applicationName="MYAPPLICATIONNAME"

                   enablePasswordRetrieval="false"

                   enablePasswordReset="false"

                   requiresQuestionAndAnswer="false"

                   requiresUniqueEmail="false"
                         
               passwordFormat="Hashed" />

            </providers>

        </membership>
    </system.web>
 

                4. Add a reference to System.Web

                5. add an Imports System.Web.Security statement on top of your main form code

                6. To create a new user just use the sample code below

                           Try
                                        Membership.CreateUser("Administrator", "p@ssword1")
                            Catch ex As MembershipCreateUserException
                                        MsgBox(ex.Message)
                            End Try

               7. To authenticate a user use the sample code below

 

                            If Membership.ValidateUser(txtUserId.Text, txtPassword.Text) = True Then
                                    frmMain.Show()                                  
                            Else
                                    ShowError("Invalid Password or UserName")
                            End If  
 

 And thats all....

 Hope this one helps.

 

Sorry if the Code is a little Bit Messy still trying to figure out the best way of pasting Codes....
 

Unraveling the Mysteries of .NET 2.0 Configuration

I attended one of the PHINUG tech talks and listen to one of sir punzies talk last year, I saw his XENO project and have played with the configuration class after. then last week i have stumbled upon this page http://www.codeproject.com/dotnet/mysteriesofconfiguration.asp and found out we can do more.....

 Hope this one helps

 

Posted: 06-29-2007 1:16 PM by Comgen | with no comments
Filed under: ,
Wow JavaScript is Cool

I've been coding Javascript when i was in college and whenever i try to play with Web Apps since my primary role hear at our company is related with Windows Apps.

I've been browsing for something new this afternoon when i stumble upon this

http://msdn.microsoft.com/msdnmag/issues/07/05/javascript/default.aspx

WOW. Javascript is much more cooler than i first thought.

Posted: 05-11-2007 3:59 PM by Comgen | with 2 comment(s)
Filed under:
Notify Users Using PC Sound and Flashing Taskbar

I have projects that our Data Center Use to upload,download and process data for our Systems(AS400,Windows, Unix). Most of the time the Data Center operator will have to do things simultaneously (Physically). So sometimes , a finished processing is not noticed for several minutes (sometimes for more than 30 minutes), Which causes a short delay for the next phase.

So i remembered my college days machine problems. I usually add pc beeps on my apps Startup.

So using the Console.Beep() , Background Worker, and the FlashWindowEx API from PInvoke.Net.  I came out with this.  

Note :

      I added a timer just to simulate an ongoing process.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
namespace PC_Beep
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }
   private Boolean StopBeeping;
   [
DllImport("user32.dll")]
   static extern Int32 FlashWindowEx(ref FLASHWINFO pwfi);
   [
StructLayout(LayoutKind.Sequential)]
   public struct FLASHWINFO
   {
      public UInt32 cbSize;
      public IntPtr hwnd;
      public UInt32 dwFlags;
      public UInt32 uCount;
      public UInt32 dwTimeout;
   }


   //Stop flashing. The system restores the window to its original state. 
   public const UInt32 FLASHW_STOP = 0;
   //Flash the window caption. 
   public const UInt32 FLASHW_CAPTION = 1;
   //Flash the taskbar button. 
   public const UInt32 FLASHW_TRAY = 2;
   //Flash both the window caption and taskbar button.
   //This is equivalent to setting the FLASHW_CAPTION | FLASHW_TRAY flags. 
   public const UInt32 FLASHW_ALL = 3;
   //Flash continuously, until the FLASHW_STOP flag is set. 
   public const UInt32 FLASHW_TIMER = 4;
   //Flash continuously until the window comes to the foreground. 
   public const UInt32 FLASHW_TIMERNOFG = 12; 


   public static bool FlashWindowEx(Form frm)
   {
      //Get Window Handle
      IntPtr hWnd = frm.Handle;
      FLASHWINFO fInfo = new FLASHWINFO();
      fInfo.cbSize =
Convert.ToUInt32(Marshal.SizeOf(fInfo));
      fInfo.hwnd = hWnd;
      fInfo.dwFlags = FLASHW_ALL | FLASHW_TIMERNOFG;
      fInfo.uCount =
UInt32.MaxValue;
      fInfo.dwTimeout = 0;
      return (FlashWindowEx(ref fInfo) == 0);
   }

   private void button1_Click(object sender, EventArgs e)
   {
      
timer1.Interval = 5000;
      timer1.Enabled =
true;
   }

   private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
   {
      
while (!StopBeeping)
      {
         
Console.Beep(2000, 500);
         
Console.Beep(1500, 500);
      }
   }

   private void timer1_Tick(object sender, EventArgs e)
   { 
      FlashWindowEx(
this);
      timer1.Enabled =
false
      StopBeeping =
false;
      backgroundWorker1.RunWorkerAsync();
      
MessageBox.Show("Process Done", "Comgen Systems Ltd.", MessageBoxButtons.OK);
      
StopBeeping = true;
   }
  }
}

What happens here is when the timer ellapsed it calls the RunWorkerAsync() method of the Background Worker which will call its DoWork method which contains our code for the PC Beep which will run until the StopBeeping boolean variable is set to true.

Next a message box is shown. And when the user presses OK the StopBeeping variable will be set to True which then will be trapped by our background worker and stop the pc beep.

The FlashWindowsEx Api is used to flash our application in the taskbar.

You can also use the this.Activate to activate your Form...

 

Monitor WinForms Controls Events

A few months ago i have a new project and i've stumbled upon a bug in my app which is related to events on one of my instantiated ListBox Control.

Here's a tool that helped me a lot in understanding the series of events that happen when you do something to a control (Click, Drag etc).

http://www.codeproject.com/csharp/controlinspector.asp

 

Apache Log4Net

During my last post Logging Application Block Intro (Tracing Primer) sir Cruizer ask about the advantage/disadvantage of Apache Log4Net over the MS Enterprise library's Logging Application Block. So i try to read some information and found out that Log4Net seems to be a perfect candidate for logging since Log4Net is based on log4j logging library which is in development since 1996 so i guest that Log4Net is developed with the advantage/disadvantage of Log4J in mind.

For those who want to know more about Log4Net you can visit its Site at http://logging.apache.org/log4net/

Thanks to sir Cruizer.

Posted: 03-19-2007 9:00 AM by Comgen | with 5 comment(s)
Filed under: , ,
Logging Application Block Intro (Tracing Primer)

We have an applciation (Bought from another company) that has a very good logging system, and during the Integration and Parallel Test that we've did, We saw the importance and benefits of having such. I've been doing a research about the Logging Application Block of the Enterprise Library 2.0 so that we can at least try to implement a logging system to our own applications and i stumbled into the System.Diagnostics Trace classes, and i found these articles by Mike Rousos.

   A Tracing Primer - Part 1
   A Tracing Primer - Part 2 A
   A Tracing Primer - Part 2 B
   A Tracing Primer - Part 2 C

Hope this gives a good intro for you guys about tracing.

Stored Procedure Generator

Here's a tool that im using for some of my projects. What it does is it Automate the Generation of Stored Procedures for Your Database.

You just have to execute a querry like this

             EXEC pr__SYS_MakeUpdateRecordProc 'Order_Details', 1

and it will automatically generate a stored procedure for the table (Order_Details) you specified. The link below shows the original article and also includes the generation of SPs for the CRUD Operations. One requirement is, you got to have a primary key for the table you wish to use. You can also use this article as a stepping stone to code a more flexible application that generates SPs with more options.

Click the link below

StoredProcGenerator

 

 

Truncating an SQL Server Log File

During my last post try to query the actual size of the logfile, and today i'm going to share a script i found months ago at the microsoft site. I cant find the original link....

Notes:

      Be sure that you have a backup of your database so that if something goes wrong you have something to get back into. 
      Do this only if you have no other choice

-----------------------------------------------------------------------------------

CREATE PROCEDURE TRUNCATELOG AS
 SET NOCOUNT ON
   DECLARE @LogicalFileName sysname,
           @MaxMinutes INT,
           @NewSize INT

   -- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
   SELECT  @LogicalFileName = 'CCISDB_Log',  -- Use sp_helpfile to
      -- identify the logical file
      -- name that you want to shrink.
           @MaxMinutes = 10,      -- Limit on time allowed to wrap log.
           @NewSize    = 10       -- in MB

   -- Setup / initialize
   DECLARE @OriginalSize int
   SELECT @OriginalSize = size -- in 8K pages
     FROM sysfiles
     WHERE name = @LogicalFileName
   SELECT 'Original Size of ' + db_name() + ' LOG is ' +
           CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
           CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
     FROM sysfiles
     WHERE name = @LogicalFileName

   CREATE TABLE DummyTrans
     (DummyColumn char (8000) not null)

   -- Wrap log and truncate it.
   DECLARE @Counter   INT,
           @StartTime DATETIME,
           @TruncLog  VARCHAR(255)
   SELECT  @StartTime = GETDATE(),
           @TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
   -- Try an initial shrink.
   DBCC SHRINKFILE (@LogicalFileName, @NewSize)

   EXEC (@TruncLog)

   -- Wrap the log if necessary.
   WHILE     @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
         AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  -- the log has not shrunk
         AND (@OriginalSize * 8 /1024) > @NewSize  -- The value passed in for new size is smaller than the current size.
     BEGIN -- Outer loop.
       SELECT @Counter = 0
       WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
         BEGIN -- update
           INSERT DummyTrans VALUES ('Fill Log')  -- Because it is a char field it inserts 8000 bytes.
           DELETE DummyTrans
           SELECT @Counter = @Counter + 1
         END   -- update
       EXEC (@TruncLog)  -- See if a trunc of the log shrinks it.
     END   -- outer loop
   SELECT 'Final Size of ' + db_name() + ' LOG is ' +
           CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
           CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
     FROM sysfiles
     WHERE name = @LogicalFileName
   DROP TABLE DummyTrans
   PRINT '*** Perform a full database backup ***'
   SET NOCOUNT OFF
GO

Monitor SQL Server Logfile

My App Crashed a few days ago. And the culprit, SQL Server LogFile. So i searched for ways to monitor my Transaction Log File and i found this

use MYDB;
SELECT
     sysfiles.size * 8/1024
FROM
    sysfiles
WHERE
    NAME = 'MYDB_log'

It will return the exact value which is shown on enterprise manager whenever you right click and peep on your databases' properties.

So what i did is, I created a monitor which prompts the DataCenter whenever the Transaction LogFile grows Booom. I'll Share the Stored Proc i found on microsoft on how to force Truncate the log file. As in 10Gb to 10 mb on my next post. God Bless

Executing a batch file that accepts an argument in .NET 2.0

One of my project is to produce text file versions of our report and to upload it to the company's AS400 Server machine, I also coded a CLLE program that will send this reports to our Printer that looks like a washing machine (high speed printers).

My company have several Upload Batch files.  So for the sake of productivity I decided to alter these scripts to suite my needs since my estimate tells that it would take a little bit longer to code the upload module in .Net 2.0.

So he'res how I called the batch file which requires an argument.

       Dim p1 As Process = New Process
       Dim businessDate as DateTime= new DateTime(1,1,2007)

       p1.StartInfo.FileName = "Up2EQ"
       p1.StartInfo.Arguments = businessDate.ToString("MMdd")
       p1.Start()

hope this one helps

 

 

ascii 160 caused a SQL Query problem =)

I have been doing a project which is running perfectly on my WinXp Development PC.

After a week we decided to go parallel, so i have deployed the project on the test workstations then we found out that a module is not working. I have tried several methods to fix the problem but no luck came. I suddenly thought of going back to the querry that i recently checked, what i did was a coded a parser that will check each character in my querry. I found out that there are parts of my querry that has some extra blank spaces which is actually ASCII character 160 and not the real space which is ASCII Char 32. Then i try to retype then Wow it worked.

I guest this happened when i have copied the querry from my excel file (Reason Long Story....) then when i tried to export it to my SQL DB some characters  Mutated hehehe....

WOW ASCII CHARACTER 160 KILLED MY DAY..... 

Posted: 01-31-2007 10:59 AM by Comgen | with no comments
Filed under: ,
Where VB.Net My.Settings is Stored

Its been weeks since my last post since I've been very busy here with some projects.

I tried using VB.Net on one of my project since my Project Manager told me so because there are a few C# guys in here. I used the My.Settings feature and found out that its not getting the values from the Config file inside the application folder. And what I did is I tried to hack around the registry and my Users Folder and there I found It. VB Stores the data inside the  \Local Settings\Application Data of the Current Users Folder

   Sample:
         C:\Documents and Settings\Administrator\Local Settings\Applcation Data\<Name of Application>

Hope this one cuts some time on those starting to use the My.Settings Feauture of VB.Net

 

More Posts « Previous page - Next page »