Solved

Problem getting access to retrieve data from vb.net

Posted on 2016-10-19
6
40 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 34

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 51

Expert Comment

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

    from Occupants as a

/gustav
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

631 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