Solved

Problem getting access to retrieve data from vb.net

Posted on 2016-10-19
6
23 Views
Last Modified: 2016-10-24
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

Open in new window

0
Comment
Question by:mgmhicks
  • 4
6 Comments
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
Check the content of myCriteria.
0
 

Author Comment

by:mgmhicks
Comment Utility
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*' "
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Try using the AS syntax:

    from Occupants as a

/gustav
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:mgmhicks
Comment Utility
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*' 

Open in new window

0
 

Accepted Solution

by:
mgmhicks earned 0 total points
Comment Utility
As it turns out they want you to use the % when you're using it from Vbnet so if I change the Asterick to a % then it works. thanks for your help
0
 

Author Closing Comment

by:mgmhicks
Comment Utility
I came up with the solution
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now