January 2, 2009 at 9:14 am
When I used the below code on SQL2000 it worked fine. When I upgraded my DB to 2005 it started listing everything twice! I changed the provider= in the connection string from SQLOLEDB.1 to SQLNCLI with no love either way. The code is in a form used to create a list of records, the user selects the record, clicks the submit button and opens the record. The page still works, but everything is listed twice. I am using hand-jammed ASP with VB.
Code snippet:
Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open sDBName, sDBUser, sDBpwd
Set objRS = Server.CreateObject("ADODB.Recordset")
set objrs = objdb.execute("select * from dbo.MyTable order by Item")
do while not objrs.eof
response.write("<option value='" & objrs("Item") & "'")
response.write(">" & objrs("Item") & " " & vbCrLf)
objrs.movenext
loop
objrs.close
objdb.close
set objrs = nothing
set objdb = nothing
Any help or insight is greatly appreciated!
January 2, 2009 at 9:32 am
Nevermind, I don't know why SQL 2000 didnt care about "SELECT DISTINCT" but 2005 does
set objrs = objdb.execute("select DISTINCT * from dbo.MyTable order by Item")
do while not objrs.eof
response.write("<option value='" & objrs("Item") & "'")
response.write(">" & objrs("Item") & " " & vbCrLf)
objrs.movenext
January 2, 2009 at 9:38 am
jamie.loy (1/2/2009)
Nevermind, I don't know why SQL 2000 didnt care about "SELECT DISTINCT" but 2005 doesset objrs = objdb.execute("select DISTINCT * from dbo.MyTable order by Item")
do while not objrs.eof
response.write("<option value='" & objrs("Item") & "'")
response.write(">" & objrs("Item") & " " & vbCrLf)
objrs.movenext
It's a long shot Jamie, but the data sets returned by 2k and 2k5 would be the same...have you checked to see if data was accidentally migrated into this table twice?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 2, 2009 at 9:53 am
Wow, that was a quick reply Chris! 🙂
Ok, I am an idiot...I never would have caught that! You are the bomb Chris. Now I will need to delete the duplicates 🙁
That sucks...I wish the was a "Delete Duplicate records" button somewhere.
I use the
SELECT DISTINCT *
INTO NewTable
FROM OldTable
Unless there is a better way?
January 2, 2009 at 10:05 am
jamie.loy (1/2/2009)
Wow, that was a quick reply Chris! 🙂Ok, I am an idiot...I never would have caught that! You are the bomb Chris. Now I will need to delete the duplicates 🙁
That sucks...I wish the was a "Delete Duplicate records" button somewhere.
I use the
SELECT DISTINCT *
INTO NewTable
FROM OldTable
Unless there is a better way?
Hi Jamie
SELECT DISTINCT *
INTO NewTable
FROM OldTable
is probably the simplest and quickest way to dedupe, the problem is, it rarely works because the scenario isn't that simple. You've already shown that the statement eliminates the dupes you were getting so you're probably in luck. Compare rowcounts with and without the DISTINCT keyword, and if the rowcount with DISTINCT is exactly half that without, then it's probably safe to go ahead so long as no other users are accessing the table.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply