Searching Databases

[tk.unlimited]

Hey All!!
Having troubles with searching for data within a DB. I have to RESTRICT the HTML report output by using an IF statement and selecting only the records where the student Birth Month matches a month entered by the user (i.e user defined month). For Example, if the user enters 1, the report should only show the students born in January. I am uncertain whether it lies in the SQL Query which currently is

sSql = "select * from student

" or if it lies within the code itself.. Im sure you have to put it in the Do Loop though
I have the following code:

iTotal = 0 
Do While oDataReader.Read() And iTotal <= 10
 
' --- Place here If Statement for Search Report to be completed in :
 
' --- Page 6 - Point 3 - Bullet Point No 8 Restricting HTML to a Month
 
sRecordLine = "<TR>"
 
For iColCounter = 0 To oDataReader.FieldCount - 1
 
' --- Below is an If Statement to format Dates .. Advanced 
 
If dtTempDate.GetType.Equals(oDataReader.Item(iColCounter).GetType) Then
 
sColOutput = CDate(oDataReader.Item(iColCounter)).ToShortDateString
 
Else
 
sColOutput = oDataReader.Item(iColCounter).ToString
 
End If
 
sRecordLine = sRecordLine & "<td>" & sColOutput & "</td>"
 
Next
 
sRecordLine = sRecordLine & "</TR>"
 
 
 
iTotal = iTotal + 1
 
sPageText = sPageText & sRecordLine & vbCrLf
 
'End If ' --- end if of Search Report If Statement
 
Loop

 

[kulrom]

Is your DB field set up to hold only months or it holds entire date i.e. 10/10/1985

If it's only months holder you can simply say:

sSql = "select * from student where birthMonth = '" & txtMonth.text & "'"

Regards ​

 

[tk.unlimited]

It holds the entire date

 

[kulrom]

Well, i guess this is a good start for you:

Dim myMonth As Integer

Dim myDate As Date = "11.09.2004"

myMonth = myDate.Month

MessageBox.Show(myMonth) 'will return "9"


Regards

 

[kulrom]

Also why you don't ask a user/s to select a birthdate from datetimepicker instead ... ???

 

[jmcilhinney]

"SELECT * FROM student WHERE MONTH(dateofbirth) = " & iMonth

 

[kulrom]

How could i forget a MONTH function

Actually as John gave you an example i can only add that this func returns a number from 1 to 12 corresponding to the month of the given date. Its syntax is MONTH (date-expression)

Also there are similar time functions like DAY, MDY, MONTHNAME (returns the name of the month from a date), MONTHS (returns the integer number of months between two dates), WEEKDAY, YEAR etc.

Regards

 

[tk.unlimited]

IS there something like MONTHNAME AND YEAR?? That way if the user inputs January 2005 the SQL query will search for 01/01/2005 - 31/01/2005

 

[jmcilhinney]

Of course. You're just ANDing together two conditions like any other.

SELECT * FROM student WHERE MONTHNAME(dob) = 'January' AND YEAR(dob) = 2005

Be aware that not all databases support the MONTHNAME function, most notably SQL Server (although Access does). The MONTH function is more widely supported.

 

[tk.unlimited]

IM having troubles with it as the month and year are coming from the ONE textbox

 

[jmcilhinney]

Then you need to extract the month value and year value seperately from that TextBox before placing them into the SQL statement. I'd actually suggest using two seperate controls. I'd use a ComboBox or DomainUpDown for the month and a NumericUpDown for the year. That will guarantee that the selections are valid and make it easy to get the values. So many people seem to use TextBoxes for everything, but they should generally only be used when you want to allow free-form data entry. If you want to limit what data is entered then use the controls that provide the appropriate validation. It's also easier for the user to select from a list of months than to type a full month name, and they get grumpy if they get error messages because they entered invalid data.

 

[kulrom]

back to the post No. 4 and carefuly read about how to get only month or year


Regards