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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

Duplicate names and addresses

I have two sql tables tblPatient and tblHousehold which have duplicate records in them.  The tables are linked by a field lngHouseid.

I need to write a program to query the databases and display on the form the duplicates retuned for that person.

I am new to vb.net so I'm having a problem getting started.  I wrote a query in sql which returns the records but when I try to put that sql in a sql adapter it gives me an error.

So far I have a sql connection which is working, but I cannot get my sql adapter to work.  Also If I am successful if pull the records I'm not sure how to group them to display on a page by name.

Thanks
0
running32
Asked:
running32
  • 7
  • 7
2 Solutions
 
Bob LearnedCommented:
How did you go about accomplishing this task?  What is the error that you get?  Do you have any code to highlight this problem?

Bob
0
 
running32Author Commented:
Below is the sql script I ran in query analyzer.   When I try and add this in the data adapter I get

Error in select clause: experssion near 'Count' missing from clasue.  Unbale to parse text.

SELECT P.strPatientid, P.strLname, P.strmname, P.strFname,
    convert(char(10),P.dtmDOB, 101) As DOB,
    P.strSSNum, P.strPrgEntry,NameCount.Count, H.strAddress, H.StrCity, H.strState
FROM tblPatient as P
INNER JOIN tblHousehold As H ON p.lngHouseID = h.lngHouseID
INNER JOIN (SELECT strLname, strFname,dtmdob, Count(*) as Count
          FROM tblPatient
          GROUP BY strLname, strFname, dtmdob
          HAVING count(*) > 1) AS NameCount
     --If lname and fname are duplicated
     on p.strLname = NameCount.strLname
     AND p.strFname = NameCount.strFname
    where p.dtmdob = NameCount.dtmdob
    or p.dtmdob is null
    or right(p.dtmdob,4) = right(NameCount.dtmdob,4)
    or p.dtmdob is null
     --Check for Alias    
ORDER BY p.strlname, p.strfname
0
 
Bob LearnedCommented:
My first guess would be to change this:

Count(*) as Count

to this:

Count(*) as CountNames

Bob
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Bob LearnedCommented:
SELECT P.strPatientid, P.strLname, P.strmname, P.strFname,
    convert(char(10),P.dtmDOB, 101) As DOB,
    P.strSSNum, P.strPrgEntry,NameCount.Count, H.strAddress, H.StrCity, H.strState
FROM tblPatient as P
INNER JOIN tblHousehold As H ON p.lngHouseID = h.lngHouseID
INNER JOIN (SELECT strLname, strFname,dtmdob, Count(*) as CountNames
          FROM tblPatient
          GROUP BY strLname, strFname, dtmdob
          HAVING count(*) > 1) AS NameCount
     on p.strLname = NameCount.strLname
     AND p.strFname = NameCount.strFname
    where p.dtmdob = NameCount.dtmdob
    or p.dtmdob is null
    or right(p.dtmdob,4) = right(NameCount.dtmdob,4)
    or p.dtmdob is null
ORDER BY p.strlname, p.strfname

Bob
0
 
running32Author Commented:
If I run the script below it will return back the results but then I get the warning there where errors configuring the data adapter.

SELECT     P.strPatientid, P.strLname, P.strmname, P.strFname, CONVERT(char(10), P.dtmDOB, 101) AS DOB, P.strSSNum, P.strPrgEntry, NameCount. COUNT,
                      H.strAddress, H.StrCity, H.strState
FROM         tblPatient AS P INNER JOIN
                      tblHousehold AS H ON p.lngHouseID = h.lngHouseID INNER JOIN
                          (SELECT     strLname, strFname, dtmdob, COUNT(*) AS COUNT
                            FROM          tblPatient
                            GROUP BY strLname, strFname, dtmdob
                            HAVING      COUNT(*) > 1) AS NameCount ON p.strLname = NameCount.strLname AND p.strFname = NameCount.strFname
WHERE     p.dtmdob = NameCount.dtmdob OR
                      p.dtmdob IS NULL OR
                      RIGHT(p.dtmdob, 4) = RIGHT(NameCount.dtmdob, 4) OR
                      p.dtmdob IS NULL
ORDER BY p.strlname, p.strfname
0
 
Bob LearnedCommented:
When you are in the SQL designer, you can right click in the empty spaces, and select the Run option, and it should return rows if everything is valid.

Bob
0
 
running32Author Commented:
yes it did thanks.  Then I added a list box and text box to the form and it is displaying all the records.  So I guess it does not matter if there is an error or not?  Strange.

Now I have all the records how do I display all the information for just one set of duplicates.  An example of this is that I have 3 duplicates of the name Abbey and 3 duplicates of the name Able.  I want to be able to display the 3 occurances of Abbey on a form, clean then up by deleteing etc. and then click next to get the 3 occurances of Able.

I read something about working with rows in dataview but I'm a little lost.

Thanks.
0
 
Bob LearnedCommented:
Did it have errors creating the SelectCommand, DeleteCommand, UpdateCommand or InsertCommand?  If it didn't have any errors with the SelectCommand, then you can view records.  But, if it had errors in the DeleteCommand, then any operation that would delete records would get an error later on.

Bob
0
 
running32Author Commented:
I had errors on the select command and the other commands did not show up.
0
 
running32Author Commented:
When I view I see that the screen comes up read only..  Ouch now I'm hosed....
0
 
Bob LearnedCommented:
This is one of those that would be helpful if I could look over your shoulder.  What do you mean by "read-only"?

Bob
0
 
running32Author Commented:
Scrap that I've lost my mind.  The project is ready only.  Duhhhhhh  Man am I stupid.
0
 
RobertRFreemanCommented:
Some tips on data access in .net
http://vsnetdatabinding.blogspot.com/
0
 
Bob LearnedCommented:
Good blog, Robert :)

Bob
0
 
running32Author Commented:
Thanks for the artical it helped alot.  Thanks TheLearnedOne for trying to help you got me started.

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now