Performing date comparisons in VB6

Performing date comparisons in VB6: "Performing date comparisons in VB6

by Peter Aitken | More from Peter Aitken | Published: 8/1/05
The representation of dates in VB6 makes it really easy to compare dates, yet some programmers still go to unnecessary lengths to perform a comparison. Peter Aitken demonstrates that it's a breeze to perform date comparisons in VB6.

One of the ways that VB6 really shines is in how it handles dates using the Date data type. You can display a date in any one of several standard date formats, but internally, it's represented as a serial number specifying the number of days since December 31, 1899, with negative values used for dates prior to then. You can also use the decimal portion of the value to represent a time of day, but that's beyond the scope of this tip.

The representation of dates makes it really easy to compare dates, yet some VB programmers still go to unnecessary complexity to perform a comparison. There's no need to extract the year, month, and day individually for comparison-you can compare dates directly using the standard comparison operators.

For example, the date June 30, 2004, is represented by the serial number 38168. If you want to compare it with another date, you can just compare the serial numbers to see if one date is smaller, the same, or larger than the other.

Here's an example that executes one block of statements if the date stored in the type Date variable MyDate is earlier than January 1, 2000, and another block if it's the same or later than that date.

If MyDate < #1/1/2000# Then
' Statements here are executed if MyDate is earlier.
Else
' Statements here are executed if MyDate is the same or later.
End If

This is just one example of the flexibility of VB's Date data type."

Use Stars in SQL Server

Create a histograph in SQL Server: "Create a histograph in SQL Server

by Arthur Fuller | More from Arthur Fuller | Published: 8/15/05

Takeaway:
Arthur Fuller worked on a project recently that assigned star ratings to each problem. This system led him to create code that demonstrates how you would present this data via SQL Server. Check out his non database-dependent example code.

Star ratings are often used in film, hotel, and restaurant ratings. However, I worked on a project recently that used the star ratings scale to identify how critical a problem is; for instance, the more stars, the more urgent it was that the problem be addressed. This led me to wonder how you would present this data via SQL Server.

For starters, you can store the number of stars as a smallint, or even derive it using a calculation, as the statement below illustrates. Then comes the issue of presentation—how to turn a rating of 5 into five asterisks, as they might appear in a newspaper's film or restaurant review.

Now you must decide how you want to deliver the data (which in this case are star ratings). You have two choices:

You can leave it up to the client. This is easy for you, but hard for the developers of each client because the more clients, the more redundant code.
You can do it in the database. There's no downside to this option, and every client can simply extract the column without having to calculate.
In a case like this, I choose to deliver the result from the back-end and not the front-end. My reasoning is simple: For every additional front-end, new application-specific code is required. When you deliver the result from the back-end, new applications can reuse code.

A non database-dependent example
Let's suppose a table contains a column called NumberOfStars (varchar(5)), as well as all of the other required details. We can create a virtual column of type varchar that presents as many stars as the NumberOfStars column wants.

To begin, use this code (you may substitute any valid column reference for the second parameter):

SELECT REPLICATE( '*', 4)Assuming a column called Stars is in a table called Reviews, you could write the following:

SELECT REPLICATE( '*', Reviews.Stars)
FROM ReviewsTry this command against the Northwind database:

SELECT Quantity, REPLICATE( '*', Quantity/5)
FROM [Order Details]
WHERE Discount IS NOT NULLThis will result in a list like this (the list is truncated to save space):

12 **
10 **
5 *
9 *
40 ********
10 **
35 *******
15 ***
6 *
15 ***In the code above, I use the most generic alphabet. Depending on the fonts available, you could make it more interesting by substituting a graphic character for the asterisk. For example, take a look through the three Wingdings fonts. If you see something you like, substitute that character for the asterisk. You won't see the results in Query Analyzer, which is typographically agnostic, but you should see them in another client.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!"

www.washingtonpost.com - Two-Candidate Marriage

Baker Appears Undaunted By Fundraising Disparity: "Two-Candidate Marriage

Speaking of the Iveys, Glenn's wife, Jolene , is planning to run for the House of Delegates.

Jolene Ivey had been pondering for weeks whether to become a candidate for delegate or take on Britt, who has been mentioned as a possible running mate for Duncan. The legislators for the 47th District -- Britt and Democratic delegates Victor R. Ramirez , Rosetta C. Parker and Doyle L. Niemann -- are all serving their first terms.

"I figured I don't have enough to do," Ivey said with a chuckle, later rattling off a list of activities that keep the former stay-at-home mom's days jampacked. "But the bottom line is, when you want to get something done, you ask a busy person."

Ivey, the mother of five young boys who works part time doing public relations for Community Teachers Institute, a think tank in the District, said that the county needs to "get back on track" and that these are not the times for "wussy politicians" to be in office.

She has never run for elected office before -- unless you count student office in high school. "You would have thought I was running for president, though," she said."

Definition of UNC

Definition of UNC: "UNC

Stands for 'Universal Naming Convention,' not just the home of the North Carolina Tar Heels. UNC is a filename format that is used to specify the location of files, folders, and resources on a local-area network (LAN). The UNC address of a file may look something like this:

\\server-name\directory\filename

UNC can also be used to identify peripheral devices shared on the network, including scanners and printers. It provides each shared resource with a unique address. This allows operating systems that support UNC (such as Windows) to access specific resources quickly and efficiently."

Using Parameters with Stored Procedures

Using Parameters with Stored Procedures: "

Using Parameters with Stored Procedures
Regular Columnist : Kathi Kellenberger
Posted: 02/03/2006
(Original Publication Date: 8/8/2005)
More Articles From This Columnist
19,495 Reads

Join the discussion and read comments on this article.

Add to Your Virtual Briefcase
(What is this?)


Article Rating Total number of votes [49]

Summary:
It seems that SQL Server developers avoid stored procedures whenever possible, especially if they are new to the product. Kathi Kellenberger brings us a basic article that you can give to developers that explains the basics of how you use parameters with ADO.NET, especially output parameters.


--------------------------------------------------------------------------------
This Content Sponsored by:
Introduction
The prefered way for applications to access SQL Server data is by the use of stored procedures. The benefits include increased performance, security, and code reuse. Often, beginning SQL Server developers find the use of parameters, especially output parameters, with stored procedures confusing. This article will demonstrate how to use parameters with stored procedures, including some sample code using VB.Net. The examples shown are based on SQL 2000, but the concepts apply to SQL 2005 as well.

Creating a stored procedure
From Books Online, here is the syntax used to create a stored procedure:

CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]

[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]


You can also use the alter procedure statement to modify an existing stored procedure. A stored procedure can have up to 2,100 parameters. It is probably safe to assume that the typical stored procedure has no more than ten.

Here is a very simple create procedure statement that takes two input parameters and one output parameter. Normally, a stored procedure will perform some kind of data manipulation or retrieval, so this is not the average example. It will, however, serve as a simple way to illustrate the use of parameters.


CREATE PROC usp_AddTwoIntegers @FirstNumber int = 5,
@SecondNumber int, @Answer varchar(30) OUTPUT as

Declare @sum int
Set @sum = @FirstNumber + @SecondNumber
Set @Answer = 'The answer is ' + convert(varchar,@sum)
Return @sum

How to define a paramter
The first thing to remember is that the parameter name must start with an @ symbol. The name can’t have spaces, and there are some characters that are not allowed. If you just stick with alpha-numeric characters and underscores in your parameter names you won’t have any problems naming parameters.

Second, the data type of the parameter and possibly the size must be stated. For example, the size of a parameter of type VARCHAR must be specified. Third, if you wish to access a return value from the parameter, the OUTPUT keyword must be used. In the example create proc statement, three parameters are defined, two are integers. One of the parameters, @Answer, is an OUTPUT parameter and will be used to pass a value back to the caller.

Parameters can be given a default value. The @FirstNumber parameter has a default value of 5.

How to Call a Stored Procedure from Query Analyzer
A stored procedure can be called from query analyzer, another stored procedure or any other client that can talk to SQL Server. Here is a sample script that can be pasted into Query Analyzer to demonstrate the use of our proc:

Declare @a int, @b int, @c varchar(30)
Select @a = 1, @b = 2
Exec usp_AddTwoIntegers @a, @b, @c OUTPUT
Select @c


---------------
The answer is 3

Notice that the keyword OUTPUT is used in the calling statement as well as the proc's definition. All of the arguments were defined as variables but given names that don’t match the original parameter names. This works because SQL resolves the parameters based on the position.

Only the output parameter must be defined up front. The next example passes literal values for the first two arguments.

Declare @Answer varchar(30)
Exec usp_AddTwoIntegers 1,2,@Answer OUTPUT
Select @Answer


---------------
The answer is 3

It is also possible to mix up the order of the arguments if the parameters are named in the calling statement.

The @FirstNumber parameter has a default that the proc will use since the argument is not specified.

Declare @Answer varchar(30)
Exec usp_AddTwoIntegers @Answer = @Answer OUTPUT, @SecondNumber = 5
Select @Answer


----------------
The answer is 10

Hopefully many of you have noticed that the actual numeric sum is also returned from the procedure. This example shows how to access the return value.

Declare @sum int, @Answer varchar(30)
Exec @sum = usp_AddTwoIntegers 5,5,@Answer
Select @sum


-----------
10

Normally return values are used to report the success or failure of the procedure. So, even though you can return any integer value from the proc, usually a 0 for success or 1 for failure is returned.

Stored procedures are usually used to retrieve or manipulate data. Here is a typical stored procedure using the Northwind sample database that ships with SQL 2000. The parameter is used in the where clause to filter the results.

Create proc usp_GetCustomerName @CustomerID varchar(5) as
Select CompanyName from Customers where CustomerID = @CustomerID

go

exec usp_GetCustomerName 'ANTON'

CompanyName
-----------------------
Antonio Moreno Taquería

Calling a stored procedure from VB.Net
As promised, here is a code snippet showing how to call a stored procedure using VB.Net. Again, no one would ever call a stored procedure to add two numbers together. This example is given to illustrate the use of parameters.

Create a new VB.Net Windows Application project. Add a button to the form. Add this statement to top of the form’s code window:

Imports System.Data.SqlClient

Paste this code into the form’s code window right above the End Class statement. I added the sample stored procedure to the Northwind database. Modify the connection string so it will work in your environment.

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click

'You may need to modify the connection string
Dim conn As New SqlConnection("Integrated Security=SSPI;" _
& "Persist Security Info=False;Initial Catalog=Northwind;" _
& "Data Source=(local);Packet Size=4096;")
conn.Open()

'set up the command object
Dim myCommand As New SqlCommand("usp_AddTwoIntegers", conn)
myCommand.CommandType = CommandType.StoredProcedure

'add the first two parameters
myCommand.Parameters.Add("@FirstNumber", 5) 'this one is optional
myCommand.Parameters.Add("@SecondNumber", 5)

'The output and return parameters must be created as objects
Dim myAnswer As New SqlParameter("@Answer", SqlDbType.VarChar, 30)
myAnswer.Direction = ParameterDirection.Output

Dim mySum As New SqlParameter()
mySum.Direction = ParameterDirection.ReturnValue

'add them to the parameter collection
myCommand.Parameters.Add(myAnswer)
myCommand.Parameters.Add(mySum)

'execute the query
myCommand.ExecuteNonQuery()

'display the values
MsgBox(myAnswer.Value)
MsgBox(mySum.Value)
End Sub

Run the form and click the button to see the output parameter and return values.

Conclusion
Using stored procedures is the best way for applications to access SQL data. This article explained, using a very simple example, how to use parameters with stored procedures."