Stone Coast Web Design's Weblog

Navigation

Search

Blogroll

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

| |

E-mail

Total Posts: 112
This Year: 50
This Month: 0
This Week: 0
Comments: 0

Monday, June 18, 2007

Preventing Duplicate Logins

Monday, June 18, 2007 11:21:48 AM (Eastern Standard Time, UTC-05:00) ( ASP.NET | SQL )

I recently had to put into place a method that prevented more than one user from logging into our company web site under the same login ID. With the help of a friend, this is what we came up with.

Basically, each time a user logs in, the user's UserID and sessionID are stored in a database table at login. Each time the user visits a page in the site, the user's session ID is matched against what is stored in the database. If the sessionIDs do not match, then the user is logged out and redirected to a page that says something like "duplicate login detected. you're a cheating bum. etc."

A typical scenario would work like this:

User A logs into the site and the sessionID is recorded into the dbase. She browses from page and page within the site and on each page her sessionID is verified against what's stored in the database.

User B then logs into the site using User A's ID and password. The sessionID information stored in the database for User A is overwritten by User B. When user A tries to browse to another page, she receives a message stating that someone has logged into the site using her ID. User A can log back in, but then User B will receive the duplicate login message and round and round they go.

Here's the code for it.

This stored procedure is called on the login page. If the user's userID is not already in the datbase, then it is inserted as a new record. Otherwise, the existing record is updated with the new session ID.

CREATE Procedure sp_LoginStatusCheck
@UserID as Int,
@SessionID as nvarchar(100)

AS
    IF (SELECT COUNT(UserID)
FROM TLoginStatus
WHERE UserID = @UserID) = 0

BEGIN
INSERT INTO TLoginStatus(UserID, SessionID) Values (@UserID, @SessionID)

END
ELSE
         UPDATE TLoginStatus
         SET SessionID = @SessionID
         WHERE UserID = @UserID
GO

Call sp_LoginStatusCheck in the login page.

Sub AddSessionCheck(ByVal UserID As Integer)
'Due to some bizarre requirement that there must be some value assigned to a Session variable not to lose
'the value for SessionID, I created a random session variable and value
Session("BA") = "BA"


Dim myConnection As SqlConnection
Dim cmd As SqlCommand

myConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
cmd = New SqlCommand("sp_LoginStatusCheck", myConnection)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = UserID
cmd.Parameters.Add("@SessionID", SqlDbType.NVarChar, 100).Value = Session.SessionID

Try
myConnection.Open()
cmd.ExecuteNonQuery()
Finally
myConnection.Close()
End Try

End Sub

On each page that I Want to check for a duplicate login, I would first get the userID value which is stored in a cookie value in the Page_Load function.

<%@ Page Language="VB" Inherits="MyBaseClass" src="~/classes/MyBaseClass.vb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
Sub Page_Load()
If Not IsPostBack Then
Dim strUserID As String
If Not IsNothing(Request.Cookies("BA")) Then
strUserID = Request.Cookies("BA")("UserID").ToString()
lblUserID.Value = strUserID
End If

'Call CheckLoginStatus
CheckLogin(strUserID)

End If
End Sub
</script>

I created a new class called MyBaseClass with a sub called CheckLogin that accepts UserID as a parameter. This gets called on every page that I check.

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
Imports System.Web.HttpContext

Public Class MyBaseClass
Inherits System.Web.UI.Page

Public Shared Sub CheckLogin(ByVal UserID As Integer)

'This entire section is the duplicate login prevention section
System.Web.HttpContext.Current.Session("Spice") = "Ginger"
Dim strSessionID = System.Web.HttpContext.Current.Session.SessionID
Dim strLoginStatus As String

Dim MyConnection As SqlConnection
Dim Cmd As SqlCommand

MyConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Cmd = New SqlCommand("sp_LoginStatusCheck2", MyConnection)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = UserID
Cmd.Parameters.Add("@SessionID", SqlDbType.NVarChar, 100).Value = strSessionID
Cmd.Parameters.Add("@LoginStatus", SqlDbType.Bit).Direction = ParameterDirection.Output

Try
MyConnection.Open()
Cmd.ExecuteNonQuery()
strLoginStatus = Cmd.Parameters(2).Value
'System.Web.HttpContext.Current.Response.Write(strLoginStatus)
Finally
MyConnection.Close()
End Try

If strLoginStatus = "False" Then
System.Web.HttpContext.Current.Response.Redirect("/duplicate.login.aspx")
End If

End Sub

End Class

Here's the code for the stored procedure sp_LoginStatusCheck2

CREATE Procedure sp_LoginStatusCheck2
@UserID as Int,
@SessionID as nvarchar(100),
@LoginStatus as Bit OUTPUT

--We don't want to check the LoginStatus of enterprise level accounts UserID 884, UserID 7996
AS
IF @UserID=884
    SET @LoginStatus = 1
ELSE IF @UserID=7996
    SET @LoginStatus = 1
ELSE

    IF (SELECT COUNT(*) FROM TLoginStatus WHERE UserID = @UserID AND SessionID = @SessionID) > 0
        SET @LoginStatus = 1
    ELSE
        SET @LoginStatus = 0
GO

Comments [0] | Trackback |

Friday, June 15, 2007

Date and time functions

Friday, June 15, 2007 3:49:55 PM (Eastern Standard Time, UTC-05:00) ( ASP.NET )

Date and Time Properties

When working with dates and times it is often necessary to know the current date and/or time. These values can be extracted from the server's system clock through the properties shown in the table below. These properties are used in several of the date and time functions described below.

Property	Value
DateString	Returns a String value representing the current date. DateString = 06-15-2007
Now	Returns a Date value containing the current date and time. Now = 6/15/2007 3:34:34 PM
TimeOfDay	Returns a Date value containing the current time of day (the date is set to 1/1/0001). TimeOfDay = 1/1/0001 3:34:34 PM
Timer	Returns a Double value representing the number of seconds elapsed since midnight. Timer = 56074.6734594
TimeString	Returns a String value representing the current time of day. TimeString = 15:34:34
Today	Returns or sets a Date value containing the current date. Today = 6/15/2007 12:00:00 AM

Date Functions

Date functions are summarized in the following table with functions described more fully below.

Function	Use
DateAdd()	Returns a Date value containing a date and time value to which a specified time interval has been added.
DateDiff()	Returns a Long value specifying the number of time intervals between two Date values.
DatePart()	Returns an Integer value containing the specified component of a given Date value.
DateSerial()	Returns a Date value representing a specified year, month, and day, with the time information set to midnight (00:00:00).
DateValue()	Returns a Date value containing the date information represented by a string, with the time information set to midnight (00:00:00).
Day()	Returns an Integer value from 1 through 31 representing the day of the month.
IsDate()	Returns a Boolean value indicating whether an expression can be converted to a date.
Month()	Returns an Integer value from 1 through 12 representing the month of the year.
MonthName()	Returns a String value containing the name of the specified month.
WeekDay()	Returns an Integer value containing a number representing the day of the week.

DateAdd() adds a given date interval to a date to produce a calculated date. Its general format is

DateAdd(interval, number, date)

String	Unit of time interval
d	Day of month (1 through 31)
y	Day of year (1 through 366)
m	Month
q	Quarter
w	Day of week (1 through 7)
ww	Week of year (1 through 53)
yyyy	Year

where interval is one of the string values shown in the accompanying table, number gives the number of intervals to add, and date is the date and time to which the interval is to be added. For example, the following function returns the (formatted) date six months from today.

FormatDateTime(DateAdd("m", 6, Today),
DateFormat.LongDate)

Six months from today is Saturday, December 15, 2007.

DateDiff() returns a value giving the number of identified intervals between two dates. Its general format is

DateDiff(interval, date1, date2)

where interval is a string value representing an interval type using the date values shown in the table under the DateAdd() function, and date1 and date2 are the two dates between which the interval is calculated (date1 is subtracted from date2). The following example calculates the number of shopping days until Christmas.

DateDiff( "d", Today, "12/24/" & DatePart("yyyy", Today) )

There are 192 shopping days until Christmas.

Notice that the date2 argument passes "12/24" & DatePart("yyyy", Today). The DatePart() function (see below) extracts the current year from the current date and appends it to the string so that the calculation is always based on the current year.

DatePart() extracts a specified component of a given Date. Its general format is

DatePart(part, date)

where part is a string value representing a date component using the values shown in the table under the DateAdd() function, and date is any Date value. For example, the following function determines the day of the week on January 1, 2010.

WeekdayName(DatePart("w", "01/01/2010"))

January 1, 2010 is on a Friday

DatePart() returns an integer value (Sunday = 1) representing the day of the week. This value is converted into a weekday name with the WeekDayName() function (see below).

DateSerial() returns a Date value based upon integer values representing a year, month, and day. Its general format is

DateSerial(year, month, day)

For example, the function DateSerial(5, 7, 15) returns 7/15/2005 12:00:00 AM. You might wonder why the need to return a date if you already know the date! The point is that the passed values are integers, perhaps collected from a form in which a year, month, and day are selected from drop-down lists. These integer values are combined and converted into a Date type with the DateSerial() function.

DateValue() returns a Date type from a String value representing a date. This function works as a converter to a Date data type. For example, the function call DateValue("January 1, 2005") returns 1/1/2005 12:00:00 AM.

Day() returns an Integer representing the day of the month from a passed date. For instance, function Day(Today) returns 15. This is the same value as returned from the function DatePart("d", Today).

IsDate() returns a Boolean value indicating whether the passed value can be converted into a Date type. This function is handy when testing user input to verify that an actual date has been entered. A function call in the format IsDate("02/31/2005") returns False.

Month() returns an Integer value from 1 through 12 representing the month of the year. It works similar to the Day() function. The function call Month(Today) returns 6.

MonthName() accepts an Integer representing a month of the year and returns a String value containing the name of the month. Used in conjunction with the Month() function, the function call MonthName(Month(Today)) produces June.

WeekDay() returns an Integer value between 1 and 7 (Sunday = 1) representing the day of the week. The function WeekDay(Today) returns 6, which is identical to the value returned by DatePart("w", Today).

WeekDayName() accepts an Integer representing a day of the week and returns a String value containing the name of the day of the week. Used in conjunction with the Weekday() function, the function call WeekDayName(WeekDay(Today)) produces Friday.

Time Functions

Time functions are summarized in the following table with functions described more fully below. Several of the date functions can be applied to time measurements.

Function	Use
DateAdd()	Returns a Date value containing a date and time value to which a specified time interval has been added.
DateDiff()	Returns a Long value specifying the number of time intervals between two Date values.
DatePart()	Returns an Integer value containing the specified component of a given Date value.
Hour()	Returns an Integer value from 0 through 23 representing the hour of the day.
Minute()	Returns an Integer value from 0 through 59 representing the minute of the hour.
Second()	Returns an Integer value from 0 through 59 representing the second of the minute.
TimeSerial()	Returns a Date value representing a specified hour, minute, and second, with the date information set relative to January 1 of the year 1.
TimeValue()	Returns a Date value containing the time information represented by a string, with the date information set to January 1 of the year 1.

DateAdd() adds a given time interval to a time to produce a calculated time. Its general format is

DateAdd(interval, number, time)

String	Unit of time interval
h	Hour
n	Minute
s	Second

where interval is one of the string values shown in the accompanying table, number gives the number of intervals to add, and time is the date and time to which the interval is to be added. For example, the following function returns the (formatted) time twelve hours from now.

FormatDateTime(DateAdd("h", 12, Now), DateFormat.LongDate)

Twelve hours from now is 3:34:34 AM.

DateDiff() can be used to return a value giving the number of identified intervals between two times. Its general format is

DateDiff(interval, time1, time2)

where interval is a string value representing an interval type using the time values shown in the table under the DateAdd() function, and time1 and time2 are the two times between which the interval is calculated (time1 is subtracted from time2). The following example calculates the number of minutes until midnight.

DateDiff("n", TimeString, "11:59:59 PM") + 1

There are 506 minutes until midnight.

It can be a bit tricky working with times because of the roll-over that takes place at midnight. If the value "00:00:00 AM" were to be used in the above example, it would produce -933 minutes, which are the number of minutes from the beginning of the current day. To get around this problem, one minute is added to the time difference until "11:59:59 PM" of the current day.

DatePart() can be used to extract a specified component of a given time. Its general format is

DatePart(part, time)

where part is a string value representing a time component using the time values shown in the table under the DateAdd() function, and time is any Date value. For example, the following function determines the current hour (24-hour clock) of the current day.

DatePart("h", Now)

The current hour of the day is 15

Hour() returns an Integer representing the hour of the day (24-hour clock). For instance, function Hour(Now) returns 15. This is the same value as returned from the function DatePart("h", Now).

Minute() returns an Integer representing the minute of the hour. For instance, function Minute(Now) returns 34. This is the same value as returned from the function DatePart("n", Now).

Second() returns an Integer representing the second of the minute. For instance, function Second(Now) returns 34. This is the same value as returned from the function DatePart("s", Now).

TimeSerial() returns a Date value representing a specified hour, minute, and second, with the date information set relative to January 1 of the year 1. Its general format is

TimeSerial(hour, minute, second)

For example, the function TimeSerial(5, 30, 45) returns 1/1/0001 5:30:45 AM. As in the case for the DateSerial() function, integer values are combined and converted into a Date type.

TimeValue() returns a Date type from a String value representing a time. This function works as a converter to a Date data type. For example, the function call TimeValue("4:35:17 PM") returns 1/1/0001 4:35:17 PM.

Some of the date and time functions appear trivial in isolation. However, you will find them crucial when you begin combining them in applications that relay on accurate reporting of and calculations involving dates and times.

Comments [0] | Trackback |

Common mathematical functions

Friday, June 15, 2007 9:22:58 AM (Eastern Standard Time, UTC-05:00) ( ASP.NET )

Mathematical Functions

Popular mathematical functions are summarized in the following table. Note that certain functions do not require the Math. prefix.

Function	Use
Math.Abs()	Returns the absolute value. Math.Abs(-10) returns 10.
Math.Ceiling()	Returns an integer that is greater than or equal to a number. Math.Ceiling(5.333) returns 6.
Fix()	Returns the integer portion of a number. Fix(5.3333) returns 5.
Math.Floor()	Returns an integer that is less than or equal to a number. Fix(5.3333) returns 5.
Int()	Returns the integer portion of a number. Int(5.3333) returns 5.
Math.Max()	Returns the larger of two numbers. Math.Max(5,7) returns 7.
Math.Min()	Returns the smaller of two numbers. Math.Min(5,7) returns 5.
Math.Pow()	Returns a number raised to a power. Math.Pow(12,2) returns 144.
Rnd()	Returns a random number between 0 and 1. Used in conjunction with Randomizestatement to initialize the random number generator.
Math.Round()	Rounds a number to a specified number of decimal places. Rounds up on .5. Math.Round(1.1234567,5) returns 1.12346.
Math.Sign()	Returns the sign of a number. Returns -1 if negative and 1 if positive. Math.Sign(-5) returns -1.
Math.Sqrt()	Returns the square root of a positive number. Math.Sqrt(144) returns 12.

Random Numbers

The Rnd() function returns a random number between 0 and 1. More likely, the need is to generate a number within a particular range, between a given low and high number. This is accomplished with the following formula.

Math.floor((high - low + 1) * Rnd() + low)

For instance, to generate a random number between 0 and 10 the formula becomes

Math.floor((10 - 0 + 1) * Rnd() + 0)

Comments [0] | Trackback |

Common string functions

Friday, June 15, 2007 9:20:04 AM (Eastern Standard Time, UTC-05:00) ( ASP.NET )

I find I use these all of the time

String Functions

Several built-in string functions perform string manipulations to augment simple concatenation with the "&" operator. These functions are summarized in the following table.

Function	Use
Asc()	Returns the character code of the first character of a string. Asc("A") returns 65.
Chr()	Returns the display character of a character code. Chr(65) returns "A".
GetChar()	Returns the character at a specified position in a string, counting from 1. GetChar("This is a string", 7) returns "s".
InStr()	Returns the starting position in a string of a substring, counting from 1. InStr("This is a string", "string") returns 11.
InStrRev()	Returns the starting position in a string of a substring, searching from the end of the string. InStr("This is a string", "string") returns 11.
LCase()	Returns the lower-case conversion of a string. LCase("THIS IS A STRING") returns "this is a string".
Left()	Returns the left-most specified number of characters of a string. Left("This is a string", 4) returns "This".
Len()	Returns the length of a string. Len("This is a string") returns 16.
LTrim()	Removes any leading spaces from a string. LTrim(" This is a string") returns "This is a string".
Mid()	Returns a substring from a string, specified as the starting position (counting from 1) and the number of characters. Mid("This is a string", 6, 4) returns "is a".
Replace()	Replaces all occurences of a substring in a string. Replace("This is a string", " s", " longer s") returns "This are a longer string" (replaces an "s" preceded by a blank space).
Right()	Returns the right-most specified number of characters of a string. Right("This is a string", 6) returns "string".
RTrim()	Removes any trailing spaces from a string. RTrim("This is a string ") returns "This is a string".
Str()	Returns the string equivalent of a number. Str(100) returns "100".
Space()	Fills a string with a given number of spaces. "This" & Space(5) & "string" returns "This string".
StrComp()	Compares two strings. Return values are 0 (strings are equal), 1 (first string has the greater value), or -1 (second string has the greater value) based on sorting sequence. StrComp("This is a string", "This string") returns -1.
StrReverse()	Reverses the characters in a string. StrReverse("This is a string") returns "gnirts a si sihT".
Trim()	Removes any leading and trailing spaces from a string. Trim(" This is a string ") returns "This is a string".
UCase()	Returns the upper-case conversion of a string. UCase("This is a string") returns "THIS IS A STRING".
Val()	Converts a numeric expression to a number. Val( (1 + 2 + 3)^2 ) returns 36.

Comments [0] | Trackback |

Removing part of a string

Friday, June 15, 2007 9:17:35 AM (Eastern Standard Time, UTC-05:00) ( ASP.NET )

I've done this a few times at work and I always seem to forgot how I did it previously. In this case, I needed to remove all of the text from a SQL statement from the ORDER BY clause forward.

So a typical SQL statement like this:

SELECT * FROM tableA ORDER BY ID

needed to look like this:

SELECT * FROM tableA

strSqlText = "SELECT * FROM tableA ORDER BY ID"

'This returns the ordinal position (first character of ORDER BY
intOrderByCount = InStr(strSqlText, "ORDER BY")

'This removes the O in ORDER BY
intOrderByCount = intOrderByCount - 1

'This takes only the Left part of the SQL statement and removes everything after intOrderByCount
strSqlText = Left(strSqlText, intOrderByCount)

I'm sure there are better ways of doing this but this way worked for me

Also see: Common String Functions

Comments [0] | Trackback |

Thursday, June 14, 2007

Assign Dataset Value to a Label Control

Thursday, June 14, 2007 11:31:55 AM (Eastern Standard Time, UTC-05:00) ( ASP.NET )

A coworker asked me how to do this so I thought I would add it here

mylabel.Text = myDataSet.Tables[0].Rows[0].item["column"];

C#

mylabel.Text = myDataSet.Tables[0].Rows[0]["column"].ToString();

Comments [0] | Trackback |

Monday, June 11, 2007

Document Type Declaration Error

Monday, June 11, 2007 12:15:45 PM (Eastern Standard Time, UTC-05:00) ( ASP.NET )

I had a problem recently where my CSS in my pages was out of whack and couldn't figure out why. It turned out that my document type declaration was in the wrong place and was causing the problem. This document type declaration is the VS 2005 default for new .aspx pages.

I was doing this:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>

<script runat="server">

.....

</script>

and structuring it this way corrected the problem

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

.....

</script>

Comments [0] | Trackback |

Thursday, June 07, 2007

Saving an Array to a Database

Thursday, June 07, 2007 11:54:16 AM (Eastern Standard Time, UTC-05:00) ( ASP.NET | Javascript/AJAX )

I really like this example because it illustrates just how powerful client-side and server-side technology used in conjunction can be. My javascript function, saveSelectedtoDatabase, passes a few parameters to my .aspx page saveSelected.aspx. The string passed is in the form saveSelected.aspx?UserID=2447&BenchmarkName=Benchmark1&CompanyName=IBM*Ford Motor*AMD&CompID=14575*17411*41522

function savedSelectedtoDatabase(intUserID,benchmarkName,thistext,thisvalue)
{
xmlHttp=GetXmlHttpObject()
if (xmlHttp==null)
{
alert ("Your browser does not support the XMLHttpRequest object.")
return
}
var url="saveSelected.aspx?UserID="+intUserID+"&BenchmarkName="+benchmarkName+"&CompanyName="+thistext+"&CompID="+thisvalue;
xmlHttp.onreadystatechange=stateChanged5
xmlHttp.open("GET",url,true)
xmlHttp.send(null)
}

saveSelected.aspx takes the querystring value passed to it and splits the CompanyName and CompID values by * and then saves each record to a SQL Server database.

Comments [0] | Trackback |

Creating an Array or String from Database Records

Thursday, June 07, 2007 10:23:55 AM (Eastern Standard Time, UTC-05:00) ( ASP.NET )

I've been working on a project which inolved a lot of javascript and one requirement was to grab a list of industry values and their primary keys, place them into an array or string and then return those back to my javascript function. In this first example I use the StringBuilder() method which I guess from user feedback is much more efficient than creating a ArrayList object.

In this second example, I do the identical thing except that I use an ArrayList

Mostly Web Development

Navigation

Search

Categories

On this page

Archive

Blogroll