Problem getting access to retrieve data from vb.net
I have the following code in vb.net. I do not get an error and if I copy mstr to access query it does return results, but from vb.net it returns 0 records. Any Ideas.
thanks
Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click Dim mycriteria As String = BuildSearchCriteria() Dim dsSearch As New DataSet Dim daSearch As New OleDbDataAdapter Dim cmdSearch As New OleDbCommand Dim myConn As New OleDbConnection(mConn) Dim mStr As String = String.Empty mStr = "select a.propertyid ,a.bldgid,a.unitid, a.resiid,a.occufirstname, a.occulastname,b.FullID as ResidentFullID, " mStr = mStr & "b.Status,b.id,a.ResponsibleFlag, b.ListedDate,rtrim(a.OccuLastName)+', '+ RTRIM(a.OccuFirstName) as resiName, " mStr = mStr & "b.TotalAmt,b.OriginalAmt,b.CFeeAmt,b.CBY_AcctNo, '' as resistatus from Occupants a left outer join Master b on " mStr = mStr & "a.PropertyId=b.PropertyID and a.BldgId=b.BldgID and a.UnitId=b.UnitID and a.ResiId=b.ResiID " mStr = mStr & mycriteria With cmdSearch .Connection = myConn .CommandText = mStr .CommandType = CommandType.Text End With daSearch.SelectCommand = cmdSearch daSearch.Fill(dsSearch) dgSearch.DataSource = dsSearch.Tables(0) End Sub
below is the strings. Again if I copy mstr to access it works.
myCriteria
Where a.occufirstname like 'jo*' and a.occulastname like 'davis*' "
mystr with mycriteria
"select a.propertyid ,a.bldgid,a.unitid, a.resiid,a.occufirstname, a.occulastname,b.FullID as ResidentFullID, b.Status,b.id,a.ResponsibleFlag, b.ListedDate,rtrim(a.OccuLastName)+', '+ RTRIM(a.OccuFirstName) as resiName, b.TotalAmt,b.OriginalAmt,b.CFeeAmt,b.CBY_AcctNo, '' as resistatus from Occupants a left outer join Master b on a.PropertyId=b.PropertyID and a.BldgId=b.BldgID and a.UnitId=b.UnitID and a.ResiId=b.ResiID Where a.occufirstname like 'jo*' and a.occulastname like 'davis*' "
Sorry that didn't do the trick Gustav. Here is what it looks like now. Again it works in access query.
select a.propertyid ,a.bldgid,a.unitid, a.resiid,a.occufirstname, a.occulastname,b.FullID as ResidentFullID, b.Status,b.id,a.ResponsibleFlag, b.ListedDate,rtrim(a.OccuLastName)+', '+ RTRIM(a.OccuFirstName) as resiName, b.TotalAmt,b.OriginalAmt,b.CFeeAmt,b.CBY_AcctNo,'' as resistatus from Occupants as a left outer join Master b on a.PropertyId=b.PropertyID and a.BldgId=b.BldgID and a.UnitId=b.UnitID and a.ResiId=b.ResiID Where a.occufirstname like 'jo*' and a.occulastname like 'davis*'