Datagrid wont display data when sql contains where clause
I am working on a windows forms application vb.net / Access . I have a form which contains a set of text boxes for search criteria, a command button which contains the code for displaying the data and a datagrid.
The datagrid displays just fine when the sql does not contain where clause.
Button_click contains the following:
The sql is as follows:
searchstr = "SELECT TransNo, [Authorization], RDM, ItemDesc, TransDate, Vendor, HoldersName, UnitCost, TotalAmt, Credit, Memo, PKey From PCardLog where " & UCase(searchstr) & " and ((RDM Is Null) Or (RDM In ('M','D'))) order by Pkey DESC"
'
Dim strSQL As String = searchstr
Dim dsUnRec As New DataSet()
dsUnRec = GetDataSet(strSQL, "UnRec")
DataGrid1.DataSource = dsUnRec.Tables("UnRec")
Private Function GetDataSet(ByVal sql As String, ByVal dstab As String) As DataSet
Try
Cmd.Connection = Conn 'Conn declared in public module
Cmd.CommandText = sql
Conn.Open()
Dim adapter As New OleDbDataAdapter()
adapter.SelectCommand = Cmd
Dim ds As New DataSet(dstab)
adapter.Fill(ds, dstab)
Return ds
Catch err As Exception
lblstatus.Text = err.Message
Finally
Conn.Close()
End Try
End Function
I dont get an error while running this. By the way, I tried debug.writeline to extract the sql and try on access database It returns the results just fine.
Can anyone please help?