|
|
 |
| 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 p2p Programmer to Programmer discussion community. This is a community of more than 40,000 computer programmers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining our free Wrox p2p community you can post your own programming questions and respond to other programmers’ questions. Registered users also don't have to see the ads that are displayed to guests. Registration is fast, simple and absolutely free so please, join today!
Join today and post to win prizes! Post more to increase your chances of being Wrox’s top poster of the month.
|
 |
September 9th, 2009, 04:14 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: , , USA.
Posts: 176
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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 |
September 9th, 2009, 04:33 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,000
Thanks: 5
Thanked 37 Times in 36 Posts
|
|
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.
================================================== =========
|
September 9th, 2009, 05:10 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: , , USA.
Posts: 176
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
?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
|
September 9th, 2009, 06:45 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,323
Thanks: 3
Thanked 70 Times in 69 Posts
|
|
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.
|
September 9th, 2009, 07:17 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: , , USA.
Posts: 176
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I had to test for null in all the fields and it worked.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
