By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,915 Members | 1,349 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,915 IT Pros & Developers. It's quick & easy.

Weirdest thing I've ever seen... SQL can't find "49.0350"

100+
P: 101
The script I've made checks for identical fields in the database before it inserts a new row. However, there is one number that, when checked for, will not be found. It assumes there is no row, so it will insert it again....thus allowing for duplicate records. I've tried tonnes of data with no problems. But if I use this number, it skips it.Anyone have any clue? The number is "49.0350". Here's my code:
================================================== ======
file = "xlsupload\locations.xls"
sheet = "sheet1"


' Create a server connection object
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath(file) & ";" & _
"Extended Properties=""Excel 8.0;"""


' Create a server recordset object
Set RS = Server.CreateObject("ADODB.Recordset")


' Write the SQL Query
RS.open "select * from [" & sheet & "$]", cn


' List Results
do until RS.EOF

SQLQuery = "SELECT LocationId from TCHlocation where"

If Not IsNull(RS("Description")) Then SQLQuery = SQLQuery & " Description='" & RS("Description") & "'"
If Not IsNull(RS("long")) Then SQLQuery = SQLQuery & " AND long=" & RS("long") & ""
If Not IsNull(RS("lat")) Then SQLQuery = SQLQuery & " AND lat=" & RS("lat") & ""
If Not IsNull(RS("elevation")) Then SQLQuery = SQLQuery & " AND elevation=" & RS("elevation") & ""
If Not IsNull(RS("nearesttown")) Then SQLQuery = SQLQuery & " AND nearesttown='" & RS("nearesttown") & "'"
If Not IsNull(RS("eastofcityid")) Then SQLQuery = SQLQuery & " AND eastofcityid=" & RS("eastofcityid") & ""
If Not IsNull(RS("eastcitykm")) Then SQLQuery = SQLQuery & " AND eastcitykm=" & RS("eastcitykm") & ""
If Not IsNull(RS("westofcityid")) Then SQLQuery = SQLQuery & " AND westofcityid=" & RS("westofcityid") & ""
If Not IsNull(RS("westcitykm")) Then SQLQuery = SQLQuery & " AND westcitykm=" & RS("westcitykm") & ""
If Not IsNull(RS("segment")) Then SQLQuery = SQLQuery & " AND segment=" & RS("segment") & ""
If Not IsNull(RS("active")) Then SQLQuery = SQLQuery & " AND active=" & RS("active") & ""

'SQLQuery = "SELECT LocationId from TCHlocation where Description='" & RS("Description") & "' AND Long='" & RS("Long") & "' AND Lat='" & RS("Lat") & "'"
Response.Write SQLQuery & "<br>"
Set RSVerify = objConn.Execute(SQLQuery)

If RSVerify.EOF Then

Set RSInsert = Server.CreateObject("ADODB.Recordset")
RSInsert.Open "TCHlocation", objConn, 1, 3, 2
RSInsert.AddNew
RSInsert("long") = RS("long")
RSInsert("lat") = RS("lat")
RSInsert("elevation") = RS("elevation")
RSInsert("nearesttown") = RS("nearesttown")
RSInsert("eastofcityid") = RS("eastofcityid")
RSInsert("eastcitykm") = RS("eastcitykm")
RSInsert("westofcityid") = RS("westofcityid")
RSInsert("westcitykm") = RS("westcitykm")
RSInsert("segment") = RS("segment")
RSInsert("active") = RS("active")
RSInsert("description") = RS("description")
RSInsert.Update

If err<>0 Then
Response.Write "There was an error in putting " & RS("LocationID") & "<br><br>"
Else
Response.Write RS("Long") & " | " & RS("Lat") & " | " & RS("Elevation") & " | " & RS("NearestTown") & " | " & RS("EastOfCityId") & " | " & RS("EastCityKM") & " | " & RS("WestOfCityID") & " | " & RS("WestCityKM") & " | " & RS("Segment") & " | " & RS("Active") & " | " & RS("Description") & " has been uploaded.<br><br>"
End If
RSInsert.Close
Set RSInsert = Nothing
Else
Response.Write "A record containing these values has already been uploaded to database:<br>" & RS("Long") & " | " & RS("Lat") & " | " & RS("Elevation") & " | " & RS("NearestTown") & " | " & RS("EastOfCityId") & " | " & RS("EastCityKM") & " | " & RS("WestOfCityID") & " | " & RS("WestCityKM") & " | " & RS("Segment") & " | " & RS("Active") & "<br><br>"
End IF

RSVerify.Close
Set RSVerify = Nothing

RS.movenext
Loop

'Close the recordset/connection
RS.Close
cn.Close
Set RS = Nothing
================================================== ======

It's some pretty bad code, I know...
May 19 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.