[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Problem getting access to retrieve data from vb.net

Posted on 2016-10-19
6
Medium Priority
?
50 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 36

Expert Comment

by:ste5an
ID: 41850841
Check the content of myCriteria.
0
 

Author Comment

by:mgmhicks
ID: 41850913
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 52

Expert Comment

by:Gustav Brock
ID: 41851078
Try using the AS syntax:

    from Occupants as a

/gustav
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:mgmhicks
ID: 41851155
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
ID: 41851271
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
ID: 41856729
I came up with the solution
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 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