Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Closed Thread
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 9th, 2009, 03:14 PM
Friend of Wrox
Points: 1,035, Level: 12
Points: 1,035, Level: 12 Points: 1,035, Level: 12 Points: 1,035, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default Select Statement

I have two tables. The first table(table1) has addresses (duplicates too) which I am reading to write into the second table(table2). What I am doing is after reading the next record for table1 I check table2 and see if the address already exists. I am doing this by reading table2 and checking if it is there and if it is then I go to the next record on table1.

My problem is in my select statement. Seems like it is not seeing the duplicates so it goes on and writes the record to table2.

Can someone please help me figure out what I am doing wrong. Here is a snipet of my code.

Code:
         Set rsTempRecord = New ADODB.Recordset
         mySQL = "SELECT * FROM ADDRESS_MULTI WHERE " & strIdentifierSSN & " = " & intPersonSSN & " AND ad_address1 = '" & rsAddress!Address_1 & "' AND ad_address2 = '" & rsAddress!Address_2 & "' AND ad_city = '" & rsAddress!City & "' AND ad_zip = " & Str(rsAddress!Zip)
         rsTempRecord.CursorLocation = adUseClient
         rsTempRecord.CursorType = adOpenDynamic
         rsTempRecord.LockType = adLockOptimistic
         rsTempRecord.Open mySQL, adoSQLConnection
 
         If Not rsTempRecord.EOF Then
            blnDuplicate = True
         End If
 
        if not blnduplicate then
          'writing record to table2
         .....
        end if
Is there a better way to handle this logic?

Thanks
  #2 (permalink)  
Old September 9th, 2009, 03:33 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Can you post and output of the specified query..
this:
strIdentifierSSN & " = " & intPersonSSN,
sounds very strange for a query...
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
  #3 (permalink)  
Old September 9th, 2009, 04:10 PM
Friend of Wrox
Points: 1,035, Level: 12
Points: 1,035, Level: 12 Points: 1,035, Level: 12 Points: 1,035, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default

?mysql
SELECT * FROM CT_ADDRESS_MULTI WHERE SSN1 = 466923781 AND ad_address1 = '20011 Doherty Circle' AND ad_address2 = '' AND ad_city = 'Katy' AND ad_zip = 774490000
  #4 (permalink)  
Old September 9th, 2009, 05:45 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

And you really used NUMERIC fields for SSN1 and ad_zip??

Not VARCHAR?

Perhaps ad_address2 is NULL instead of a blank string. NULL and a blank string are *NOT* the same thing, to any SQL query.
  #5 (permalink)  
Old September 9th, 2009, 06:17 PM
Friend of Wrox
Points: 1,035, Level: 12
Points: 1,035, Level: 12 Points: 1,035, Level: 12 Points: 1,035, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default

I had to test for null in all the fields and it worked.
Closed Thread


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Again select statement... RinoDM SQL Language 6 January 30th, 2009 06:51 AM
select statement RinoDM SQL Language 10 January 15th, 2009 12:19 PM
select statement ??? RinoDM SQL Server 2000 7 June 19th, 2008 08:40 AM
Select statement.. help please sarah lee SQL Server 2000 4 May 16th, 2007 09:13 AM
Select from another select statement to a repeater simsen ASP.NET 2.0 Professional 0 May 2nd, 2007 04:34 PM



All times are GMT -4. The time now is 05:59 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.