Time range in SQL Server

Sometimes we encounter situations wherein we need to include in our query, a time range given that the only fields in the SQL server database is the start time and the duration:

StartTime Duration
09:30:00 AM 60
07:30:00 AM 90
*The table name in this example is tbl_UserSchedule


We sometimes want to show it in a form of a time range just like looking at a schedule:
Schedule
09:30:00 AM - 10:30:00 AM
07:30:00 AM - 09:00:00 AM

The following SQL statement does the job (although not quite optimized):
SELECT
CONVERT(char(9), StartTime, 108) +
CASE WHEN StartTime > '11:59:59 PM'
THEN 'PM' ELSE 'AM' END
+ ' - ' +
CONVERT(char(9), DATEADD(minute,
Duration, StartTime), 108) +
CASE WHEN DateAdd(minute, Duration, StartTime) > '11:59:59 PM'
THEN 'PM' ELSE 'AM' END

FROM tbl_UserSchedule

Create a CNAME record at godaddy

These instructions are from blogger's site. However, you have to have both domain and hosting account with Godaddy. Total DNS will be empty otherwise.





GoDaddy.com

  1. Log in to your account at www.godaddy.com.
  2. Open the Domains tab and select My Domain Names. You'll be directed to the Domain Manager page.
  3. Click the domain that you'd like to use with your blog.
  4. Click the Total DNS Control And MX Records link at the bottom of the section entitled Total DNS.
  5. Click Add New CNAME Record in the box labelled CNAMES (aliases). If you've already created a CNAME record for your blog's address, click the pencil icon next to the existing CNAME record.
  6. For the Name, enter only the subdomain of the address you want to use for your blog. For example, if you picked www.mydomain.com as your address, enter www here.
  7. Enter ghs.google.com as the Host Name. Specify a TTL or use the default setting of 1 hour.
  8. Click OK, and then click OK again.

Keep up with the latest technology of online gambling when you learn how to play roulette and internet slots, and also play video poker at the best online casino around, CasinoBonus.org!

Using events/delegates in C#

This example demonstrates the use of delegate in C#. The example consists of a class named Class1.cs and a form named Form1. The class has an attribute named Property1 and implements the use of delegate by having an event fired when the property changes.
The Form contains a textbox and a button. When the user clicks the button, it initializes the class and sets its attribute (Property1) to "testProperty". The value set will automatically be displayed at the textbox once the event fires.


//Class1.cs
//***********************************************************************************
using System;
using System.Collections.Generic;
using System.Text;

namespace WindowsApplication1
{
public class Class1
{
public delegate void ValueSet();
public event ValueSet OnValueSet;

private string _property1;

public string Property1
{
get { return _property1; }
set {
_property1 = value;
OnValueSet();
}
}

}
}
//Form1.cs
//***********************************************************************************
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace WindowsApplication1
{
public partial class Form1 : Form
{
Class1 c;
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
c = new Class1();
c.OnValueSet += new Class1.ValueSet(c_OnValueSet);
c.Property1 = "testProperty";
}

void c_OnValueSet()
{
textBox1.Text = c.Property1;
}

}
}

More codes at codelinquent forum

Next Trade Expo

I wonder when the next trade expo here in Davao will be. I wasn't able to attend last time as I was too preoccupied with everything else. The trade expo last year was a success. Many companies participated and there were exhibits. The expo was held at the Davao Convention Center last October 23-25, 2008. It catered the needs of and provided opportunities for the Small and Medium Enterprises. A few factors entice me to attending trade expos. The first would be the wide range of opportunities for business. I have been given a chance to become an entrepreneur for a few years and it wasn't easy. My partners and I had the enthusiasm but still didn't quite succeed the way we wanted to. Marketing is one way to have it. Through trade expos, a business can build linkages to other businesses and potential customers. It is a great way for businesses to "socialize". The second factor that attract my attention to these expos are the exhibits. We can get great ideas for our own businesses. If you're a startup, it is easy to find ways to jumpstart a business from these exhibits. Lastly, the physical appearance of those exhibits, and the ambiance of the venue usually is pleasing. This is because the organizers will do their best to provide a formal and elegant venue for the event. Afterall, the participants are businessmen and professionals.

A few notes for those participating in trade expos:
  1. Use trade show booths. It is best to have it done by professionals.
  2. Display banner stands at your booth positioning it almost perpendicular to the entrance. This will allow clients to see your business while they are sight-seeing from other booths.
  3. Use the easy to dissassemble Pipe and Drape. This makes it easy for you to cleanup after the expo.
  4. Most importantly, know your business. It is always easy to start a business and participate in these trade shows. The question is, do you really know what you are offering to your customers and can you really give it to them?

Number to Text in Excel that can handle more than 3 decimal places

follow the instructions from my previous post.
paste the following code instead of the new one.

Option Explicit
'****************
' Main Function *
'****************

Function SpellNumber(ByVal MyNumber)
Dim Temp, WholeNumberText, DecimalText
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

' String representation of amount.
MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
DecimalText = Left(Mid(MyNumber, DecimalPlace + 1), DecimalPlace - 1) '& Format(0, String(DecimalPlace - 1, "0"))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then
WholeNumberText = Temp & Place(Count) & WholeNumberText
End If
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

Select Case DecimalText
Case ""
DecimalText = " and 0/100"
Case Else
DecimalText = " and " & DecimalText & "/" & (10 ^ (Len(DecimalText)))


End Select
SpellNumber = WholeNumberText & DecimalText
End Function


'*******************************************
' Converts a number from 100-999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If

GetHundreds = Result
End Function

'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************

Function GetTens(TensText)
Dim Result As String

Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function


'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function

Using MySQL with Visual Basic .Net

Before you use this code, here are my assumptions:
  1. You have background on database management system
  2. You know how to create a database in mysql
  3. You know how to add tables to the database
  4. You are familiar with Visual Basic .Net
Things needed/installed:
  1. MySQL Connector
  2. Microsoft Visual Basic Express 2008 (Since this is what I'm using right now. You can use Visual Basic Express 2005 or the full version included in the Microsoft Visual Studio Package)
  3. MySQL Editor (I'm using WAMP's phpMyAdmin)

Things to do:
  1. Create a database in MySQL with at least 1 table. I'm using the following in this sample:
Database Name: Contacts
Table Name: ContactInformation
Fields: Id (int, autoIncrement), Name (varchar(64)), ContactNumber (varchar(20))

Sample Contents:
Full Texts Id Name ContactNumber
Edit Delete 1 John Doe (yyy) yyy-yyyy
Edit Delete 2 Jane Doe (xxx) xxx-xxxx


Visual Basic .Net Code
  1. Create a new Visual Basic Windows Forms Application.
  2. Add a DataGridView to the form
  3. On the code view, overwrite the code with the following:
Imports System.Data.Odbc

Public Class Form1
Private cn As OdbcConnection
Private adp As OdbcDataAdapter
Private dt As DataTable

Private Sub Form1_Load(ByVal sender As System.Object, B
yVal e As System.EventArgs) Handles MyBase.Load
cn = New OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=Contacts; User=root;Password=;Option=3;")
Call cn.Open()
adp = New OdbcDataAdapter("select * from ContactInformation", cn)
dt = New DataTable()
Call adp.Fill(dt)
DataGridView1.DataSource = dt
End Sub
End Class


HAPPY NEW YEAR!!!

HAPPY NEW YEAR!!!