Click here to Skip to main content
13,863,239 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
Using sqlconn As New SqlConnection("sqlstring")
                Using da As New SqlDataAdapter(String.Empty, sqlconn)
                    Dim death As New StringBuilder("")
                    Dim Cemetery As New StringBuilder("")
                    Dim sql As New StringBuilder("Select profile.Individual_ID, First_name, Middle_name, Last_name, Sex, Race, Place_of_birth, County_of_birth, State_of_birth, Date_of_birth") 'inner join death on death.individual_ID = profile.individual_ID
                    Dim wherestatement As New StringBuilder(" WHERE 1 = 1 ")

 If Not String.IsNullOrEmpty(Birthyearstart.Text) AndAlso Not String.IsNullOrEmpty(Birthyearend.Text) Then
                        wherestatement.Append(" And Year(Date_of_birth) Between Year(@StartYear) And Year(@EndYear)")
                        da.SelectCommand.Parameters.AddWithValue("@StartYear", Birthyearstart.Text)
                        da.SelectCommand.Parameters.AddWithValue("@EndYear", Birthyearend.Text)
                    End If

 sql.Append(" from profile ")
                    sql.Append(death)
                    sql.Append(Cemetery)
                    sql.Append(wherestatement)
                    '   da.SelectCommand.CommandText = sql.ToString() + " Order by Last_name ASC;"
                    da.SelectCommand.CommandText = sql.ToStr


I am storing the dates as such 00/00/1901 when I do not know the month or day or both. However, when I run my code I am getting no rows returned for the date structure. If I put an actual date such as 01/01/1901 it will return all records pertaining to that date.

Is there a way to use placeholders and still filter the year.

What I have tried:

I tried to do another placement holder and still got the same result.
Posted
Updated 12-Feb-19 2:20am
Comments
MadMyche 11-Feb-19 22:50pm
   
I don't see a FROM in any of that SQL mess
Dave Kreskowiak 12-Feb-19 0:46am
   
That code is a mess, won't compile as the third StringBuilder isn't complete. There's no closing ") on it.

As was already pointed out, there's no FROM clause in your SQL statement. Which table is this query supposed to be starting with to pull data from?

It's also a terrible way to build an SQL statement. A better way to do this would be to create a stored procedure in the database and just pass the parameters to it. It cleans up the code nicely and make it far more flexible and maintainable.
Richard MacCutchan 12-Feb-19 4:14am
   
Why are you using text strings when referring to date values? Use proper Date/DateTime types.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Apart from the problems with your query as mentioned in the comments, the only way I can see your concept working is to store the day, month and year in separate nullable columns. Then just use the year_of_birth column in your where clause
Year_of_birth Between @StartYear And @EndYear
Or make a valid Date_of_Birth a compulsory field
   
Comments
Maciej Los 12-Feb-19 12:09pm
   
5ed!
BTW: it was already discussed here: Should I separate a date into day, month, and year columns?[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190214.1 | Last Updated 12 Feb 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100