I have a database with a date of birth. From this, I need to select records that include a large number of variables including records where an individual is older than a selected age and/or younger than a selected age, and even worse, of a given astrological sign if one is selected.
I'm currently using a strongly typed dataset. Is this something that's do-able with a dataset, or should I build the select string on the form programmatically? I have an ASP site that I'm re-writing in ASP.NET 2.0. In the ASP site the select string includes the code below. I just don't see how I can do this in a dataset.
if (strLowerAge <> 18) or (strUpperAge <> 99) then
If (strLowerAge <> "") Then
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') >= " & strLowerAge & ")"
End If
If (strUpperAge <> "") Then
If (strSQL <> "") Then
strSQL = strSQL & " AND (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")"
End If
end if
End If
If (strAstSign <> "") Then
Select Case strAstSign
Case "Aries 3/21 - 4/19"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 3 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 4 AND DATEPART(d,[DATE OF BIRTH]) < 20 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m, [DATE OF BIRTH]) = 3 AND DATEPART(d, [DATE OF BIRTH]) > 20 ) OR (DATEPART(m, [DATE OF BIRTH]) = 4 AND DATEPART(d, [DATE OF BIRTH]) < 20 )"
End If
Case "Taurus 4/20 - 5/20"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 4 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) < 21 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 4 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) < 21 )"
End If
Case "Gemini 5/21 - 6/20"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) < 21 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) < 21 )"
End If
Case "Cancer 6/21 - 7/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Leo 7/23 - 8/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Virgo 8/23 - 9/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Libra 9/23 - 10/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Scorpio 10/23 - 11/21"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) < 22 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) < 22 )"
End If
Case "Sagittarius 11/22 - 12/21"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) < 22 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) < 22 )"
End If
Case "Capricorn 12/22 - 1/19"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) < 20 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) < 20 )"
End If
Case "Aquarius 1/20 - 2/18"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) < 19 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) < 19 )"
End If
Case "Pisces 2/19 - 3/20"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) > 18 ) OR (DATEPART(m,[DATE OF BIRTH]) = 3 AND DATEPART(d,[DATE OF BIRTH]) < 21 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) > 18 ) OR (DATEPART(m,[DATE OF BIRTH]) = 3 AND DATEPART(d,[DATE OF BIRTH]) < 21 )"
End If
End Select
END IF
Diane
I have made sql statements like this, and they are a pain in the butt. I cannot comment on how to do this in a dataset in ASP.NET 2.0, but this is how I would do it:
Firstly, I would use a stringBuilder to create this string. It is more effiecient that a string.
Secondly, I see that you have a lot of If (strSQL <> "") Then ... Else ... End If statements. To combat this, I make sure that the strSQL is not empty in the first place. This way you do not need to check if it is empty or not. In your case, I would put the following at the top:
Dim strSQL as new StringBuilder("SELECT .... FROM members WHERE NOT [FIRST NAME] IN NULL ")
Then all you have to do is append to this every time e.g:
Dim strSQL as new StringBuilder("SELECT " & _" [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE]" & _" FROM members " & _" WHERE NOT [FIRST NAME] IS NULL" )If (strLowerAge <> 18) or (strUpperAge <> 99) then If (strLowerAge <> "") Then strSQL.append(" AND (DateDiff(yyyy, [DATE OF BIRTH],'" & date & "') >= " & strLowerAge & ")")End If If (strUpperAge <>"")Then strSQL.append(" AND (DateDiff(yyyy, [DATE OF BIRTH],'" & date & "') <= " & strUpperAge & ")")End ifEnd If That should make you code a little shorter and easier to handle. To get the finall string, just use Dim mySQL as String = strSQL.toString().
If you really want to make it effiecient, the I would do all of this in the database as a stored procedure. If you are using SQL Server, you can build SQL statements as string and then execute them within a stored procedure. e.g.
CREATE PROCEDURE mySQLBuilder (@.sdtDOBsmalldatetime@.otherVariableint@.vSortColumn@.vSortDirection@.vWhereStr)ASDECLARE @.sqlvarchar(1000)SET @.sql ='SELECT ... FROM ...' + @.vWhereStr +' ORDER BY ' + @.vSortColumn +' ' + @.vSortDirectionEXEC (@.sql)GO
Just treat the stored procedure as you would a select statement.
HTH
Jag
|||Thank you Jag, you gave me a lot of useful information.
I have never used a stored procedure. It sounds like I would have the same problem though that I would have with the dataset - the astrology sign which is not in the database and needs to be calculated. Unless I have 12 different procedures, 1 per sign, I need to some how include an 'if then' in the SELECT statement. Is that doable?
It gets even better. Currently, in order to give the members better exposure, I have the order clause dependent on the day of the week. One day O oder by last name, another by first name, another by date of birth, another by state, etc. So I would need 84 different procedures. Right?
Diane
|||As you haven't used stored procedures before, I would leave that for now and go the way you are going. You can do 'if then' statements in stored procedures and use them to build a select statement (like you did in VB), but you should start off with easier things than this.
To learn about stored procedures, just do a google search for 'tutorial stored procedures'. You should get some good ones. They are mostly used for database input, but you can use them for data retrieval as well. They are more efficient that adhoc SQL statements as they are compiled in the SQL database.
Jag
