Searching Doubles

Bart1123
12-15-2003, 03:59 PM
I have an Access2K db that is connecting to an AutoCAD drawing. The X/Y coordinate values of objects in the drawing have been extracted to a DB.
I would like to assign a unique ID in the db based on other information found in the drawing. So I have created a SELECT statement to match the X/Y coords with an object in the drawing then place the new ID I created with that recordset.

There are 3 records that continually elude the SQL statement and a new duplicate record is created. If I trap the SELECT statement, copy it and go to the db directly, and create a query in the SQL statement builder it wont find it either. However when I run a "find duplicates" query on the table after the new duplicate record has been created they both come up.


The Coordinates are stored in the db as a DOUBLE with the decimal places set to AUTO. I have looked at other recordsets that were successful and can find no difference in the format.

I have pasted the code that is performing the search below:

Public Sub GetAtt(NewBlock As AcadBlockReference)
'On Error GoTo errhandler

Set rsAtt = New ADODB.Recordset

'loops through all the attributes of the block to do an initial match
Attributes = NewBlock.GetAttributes
dXcoord = NewBlock.InsertionPoint(0)
dYcoord = NewBlock.InsertionPoint(1)

For i = LBound(Attributes) To UBound(Attributes)
sAtt = Attributes(i).TagString

Select Case sAtt
Case "RTE"
vRoute = Attributes(i).TextString
Case "QUAN"
vCable = Attributes(i).TextString
Case "PED#"
vNumber = Attributes(i).TextString
End Select
sAtt = ""
Next i

sSQL = "select * from Blocks where [XCoord]=" & dXcoord & " and [YCoord]=" & dYcoord & ""

'Opens the DB for editing, and querries based on criteria above
rsAtt.Open sSQL, sConn, adOpenKeyset, adLockOptimistic, -1

If Not rsAtt.EOF Then
'If a record is found then DB is updated ID, Handle and Blockname are added to record
If IsNull(rsAtt!id) Then rsAtt!id = sDwgName & vObjId
If IsNull(rsAtt!Handle) Then rsAtt!Handle = vObjId
If IsNull(rsAtt!BLOCKNAME) Then rsAtt!BLOCKNAME = bObj.Name
Debug.Print rsAtt!id

'loops through the attributes and writes the value to the DB
For i = LBound(Attributes) To UBound(Attributes)
sAtt = Attributes(i).TagString
Select Case sAtt
Case "PED#"
If IsNull(rsAtt![PED#]) Then rsAtt![PED#] = Attributes(i).TextString
Case "UNIT"
If IsNull(rsAtt!UNIT) Then rsAtt!UNIT = Attributes(i).TextString
Case "YR"
If IsNull(rsAtt!YR) And Attributes(i).TextString = "" Then
rsAtt!YR = Null
Else
rsAtt!YR = Attributes(i).TextString
End If

Case "TD"
If IsNull(rsAtt!TD) Then rsAtt!TD = Attributes(i).TextString
Case "QUAN"
If IsNull(rsAtt!QUAN) And Attributes(i).TextString = "" Then
rsAtt!QUAN = Null
Else
rsAtt!QUAN = Attributes(i).TextString
End If
Case "RTE"
If IsNull(rsAtt!RTE) And Attributes(i).TextString = "" Then
rsAtt!RTE = Null
Else
rsAtt!RTE = Attributes(i).TextString
End If
Case "MCST"
If IsNull(rsAtt!MCST) And Attributes(i).TextString = "" Then
rsAtt!MCST = Null
Else
rsAtt!MCST = Attributes(i).TextString
End If
Case "TCST"
If IsNull(rsAtt!TCST) And Attributes(i).TextString = "" Then
rsAtt!TCST = Null
Else
rsAtt!TCST = Attributes(i).TextString
End If
End Select
rsAtt.Update

Next i

rsAtt.Close
Set rsAtt = Nothing
Else

'If a current record does not exist a new record is created and the data inserted
rsAtt.AddNew

If IsNull(rsAtt!id) Then rsAtt!id = sDwgName & vObjId
If IsNull(rsAtt!Handle) Then rsAtt!Handle = vObjId
If IsNull(rsAtt!BLOCKNAME) Then rsAtt!BLOCKNAME = bObj.Name
If IsNull(rsAtt!XCoord) Then rsAtt!XCoord = dXcoord
If IsNull(rsAtt!YCoord) Then rsAtt!YCoord = dYcoord

For i = LBound(Attributes) To UBound(Attributes)
sAtt = Attributes(i).TagString
Select Case sAtt
Case "PED#"
rsAtt![PED#] = Attributes(i).TextString
Case "UNIT"
rsAtt!UNIT = Attributes(i).TextString
Case "YR"
If IsNull(rsAtt!YR) And Attributes(i).TextString = "" Then
rsAtt!YR = Null
Else
rsAtt!YR = Attributes(i).TextString
End If
Case "TD"
rsAtt!TD = Attributes(i).TextString
Case "QUAN"
If IsNull(rsAtt!QUAN) And Attributes(i).TextString = "" Then
rsAtt!QUAN = Null
Else
rsAtt!QUAN = Attributes(i).TextString
End If
Case "RTE"
If IsNull(rsAtt!RTE) And Attributes(i).TextString = "" Then
rsAtt!RTE = Null
Else
rsAtt!RTE = Attributes(i).TextString
End If
Case "MCST"
If IsNull(rsAtt!MCST) And Attributes(i).TextString = "" Then
rsAtt!MCST = Null
Else
rsAtt!MCST = Attributes(i).TextString
End If
Case "TCST"
If IsNull(rsAtt!TCST) And Attributes(i).TextString = "" Then
rsAtt!TCST = Null
Else
rsAtt!TCST = Attributes(i).TextString
End If
End Select
Next i
rsAtt.Update
rsAtt.Close
Set rsAtt = Nothing
End If

Exit Sub
errhandler:

End Sub


Is there anything special I should know about searching Double Data Types?
Thanks in advance for your assistance

Bart

bvmantos
12-15-2003, 06:46 PM
WHat is the data type of ur primary key?

Bart1123
12-16-2003, 07:14 AM
WHat is the data type of ur primary key?

Thanks for the response, I have not set up the db with a Primary Key. The concatinated ID that is being inserted will be the primary key.

Bart

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum