empty string pull

[newbeee]

I have the following:

SelectPage: simply prompt user for a year string

 Public ReadOnly Property FindValue() As String
        Get
            FindValue = TextBox1.Text & " "
        End Get
    End Property

Protected Sub btnFind_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFind.Click
        Dim rpData As New clsACData
         rpData.GetDeceasedReport(FindValue())
End Sub

Here's my func in clsACData: this func suppose to generate result pulling off from a sql statement where the year is input from the select page.

Public Function GetDeceasedReport(ByVal dDate As String) As DataTable
        
        Dim strRegNames As String = "SELECT Folder.FolderID, Folder.FirstName, Folder.LastName, Address.Address1, Address.City, Address.State, 
Address.Zip, Address.PrimaryPhone, Folder.DeathDate, Folder.BirthDate FROM Folder 
INNER JOIN Address ON Folder.FolderID = Address.FolderID 
WHERE year(Folder.DeathDate)= " & dDate
        Dim vContinue As Boolean = False
        Dim temprp As New Metafile.ResultsPlus.Results
        Dim strResult As String()
        Dim dt As New DataTable()
        If temprp.Initialize(rpUID, rpPWD, rpServer, rpDatabase, Metafile.ResultsPlus.Results.RpDBType.ESqlServer, sqlUID, sqlPWD, 30, rpPort) Then
            vContinue = True
        End If
        'Try
        If vContinue Then
            dt.Columns.Add("ID", GetType(Integer))
            dt.Columns.Add("FirstName", GetType(String))
            dt.Columns.Add("LastName", GetType(String))
            dt.Columns.Add("RetreantAddress", GetType(String))
            dt.Columns.Add("City", GetType(String))
            dt.Columns.Add("State", GetType(String))
            dt.Columns.Add("ZipCode", GetType(String))
            dt.Columns.Add("PhoneNumber", GetType(String))
            dt.Columns.Add("DeathDate", GetType(String))
            dt.Columns.Add("BirthDate", GetType(String))

            strResult = temprp.ListOpenSQL(strRegNames, -1)
            For i As Integer = 0 To strResult.Length - 1
                Dim dr As Data.DataRow
                Dim resultFields() As String = Split(strResult(i), vbTab)
                dr = dt.Rows.Add(resultFields)
            Next
        End If
        GetDeceasedReport = dt
        ' Catch ex As Exception
        'Throw
        'End Try
    End Function

Everything seems to work just fine if I hard code the year in my sql statement (my page is generated with the result I'm looking for), but doesn't work when prompting user for input. It fails on:

Incorrect syntax near '='.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '='.

Source Error:


Line 807: dt.Columns.Add("BirthDate", GetType(String))
Line 808:
Line 809: strResult = temprp.ListOpenSQL(strRegNames, -1)
Line 810: For i As Integer = 0 To strResult.Length - 1
Line 811: Dim dr As Data.DataRow

It seems to stop at the sql statement where the year input is blank...even though I did put in the value.

Any thoughts or input is appreciated.

 

[ashishnaicker]

You'd probably want to re-write the SQL statement as:

"SELECT Folder.FolderID, Folder.FirstName, Folder.LastName, Address.Address1, Address.City, Address.State,
Address.Zip, Address.PrimaryPhone, Folder.DeathDate, Folder.BirthDate FROM Folder
INNER JOIN Address ON Folder.FolderID = Address.FolderID
WHERE year(Folder.DeathDate)= '" & dDate & "'"

 

[newbeee]

Thanks ashishnaicker. I got it now. You're awesome!
However, I'm getting another error msg. It happens when I try to split the array of string tab delimited into seperate string so that it could display in each column. Can you help? I'm very new with vb.net and not quite familiar with the syntax yet.

Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:


Line 809: Dim dr As Data.DataRow
Line 810: Dim resultFields() As String = Split(strResult(i), vbTab)
Line 811: dr = dt.Rows.Add(resultFields)
Line 812: Next
Line 813: End If

 

[newbeee]

Never mind. I take that bad. The problem is not the splitting part, but it fails because the resultFields is empty. Thus, there is no data for it to split. The select statement couldn't take in dDate as a string input from client. It's just blank.
SELECT Folder.FolderID, Folder.FirstName, Folder.LastName, Address.Address1, Address.City, Address.State,Address.Zip, Address.PrimaryPhone, Folder.DeathDate, Folder.BirthDate FROM Folder INNER JOIN Address ON Folder.FolderID = Address.FolderID WHERE year(Folder.DeathDate)= ''

Any thoughts?

Thanks.

 

[ashishnaicker]

Why do you want to enter the date in string format? It's not necessary.

Instead you could do something like - try to cast the string date input "dDate" to a date type. If successful, then execute the query otherwise don't. The "dDate" parameter is best used in the SQL query as a date type. Using strings for dates sometimes comes up with funny errors (maybe something to do with regional settings???)

 

[newbeee]

The users don't enter a date, they only enter a year (eg. 2001)
In the SQL statement, I used the year function to compare. In theory, when user enter 2001 in a textbox, which takes in a string, and my getDeceased function takes in a string...so that should work...right?

My regional setting is set as English(United States), short date. Any other thoughts? Thanks in advance for everything...

 

[cjard]

Read the PQ link in my signature, and you will know why writing SQLs like this is a very bad idea.. Follow PQ's advice so you can have a SQL that has a parameter that you declare to be a date.. DOnt try to shove everything into a string.. jsut because string can often represent something that looks like a date, number, boolean, DOESNT MEAN YOU SHOULD DO IT