mgmhicks
asked on
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
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
Check the content of myCriteria.
ASKER
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.Responsibl eFlag, b.ListedDate,rtrim(a.OccuL astName)+' , '+ RTRIM(a.OccuFirstName) as resiName, b.TotalAmt,b.OriginalAmt,b .CFeeAmt,b .CBY_AcctN o, '' 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*' "
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.Responsibl
Try using the AS syntax:
from Occupants as a
/gustav
from Occupants as a
/gustav
ASKER
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*'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I came up with the solution