Database Discussion - Searching a Database (the easy way?)

Print Page | Close Window

Searching a Database (the easy way?)

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=7060
Printed Date: 28 March 2026 at 9:08am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Searching a Database (the easy way?)

Posted By: fark
Subject: Searching a Database (the easy way?)
Date Posted: 06 November 2003 at 4:13pm

Thanks to everyones incredible help I have used sql insert to create a database conversion program that actually works (fast!!) and is incredibly small as you can see. My question is I have a program that adds the records efficiently now how do I efficiently check to see if the record is already there so I don't make a duplicate. The field TrackingNumber should be unique in every case.

What would this do?

strsql = "SELECT * FROM UPS WHERE TrackingNumber = """ & BARCODE & """;"

Conn.execute(strsql)

Does this statement return anything to let me know what the result of the execute was (let me know if it found a match)?

Is there another easy way to search? Thanks Again!!!!!

<%

Dim xDb_Conn_Str
Dim Conn
Dim strsql
Dim ConFields
Dim dcSpeedee, rsSpeedee
Const DSN_NAME = "DSN=Speedee Track"
Dim I
Dim Fields, Values
Dim Counter

xDb_Conn_Str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("sd\shippingdatabase.mdb") & ";"
set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str1

Set dcSpeedee = Server.CreateObject("ADODB.Connection")
dcSpeedee.ConnectionTimeout = 20
dcSpeedee.CommandTimeout = 50
dcSpeedee.Open DSN_NAME
Set rsSpeedee = Server.CreateObject("ADODB.Recordset")
rsSpeedee.Open "SELECT * FROM Archive ;", dcSpeedee, adOpenStatic, adLockReadOnly

ConFields=LoadFile("convertspeedee.con")

Counter=0
Do While Not rsSpeedee.EOF
Fields=""
Values=""
strsql=""
strsql = "INSERT INTO UPS (ParcelCarrier "

I=0
Do While I<UBound(ConFields)
If rsSpeedee(ConFields(I))<>"" Then
  If Asc(rsSpeedee(ConFields(I)))<>0 Then
   'Response.Write rsSpeedee(ConFields(I)).Type
   Fields = Fields & ", " & ConFields(I+1)
   Values = Values & ", '" & Replace (CStr(rsSpeedee(ConFields(I))),"'","") & "'"
   'Response.Write "<BR>" & ConFields(I+1) & "<BR>" & rsSpeedee(ConFields(I)) & "<BR>"
  End If
End If
I=I+2
Loop

strsql = strsql & Fields & ") Values ('Speedee' " & Values & " );"

Conn.execute(strsql)
Counter=Counter+1
Response.Write Counter & " Total Records Added"
rsSpeedee.MoveNext
Loop

Response.Write "END"
%>

Replies:

Posted By: pmormr
Date Posted: 10 November 2003 at 4:12pm

<%

Dim xDb_Conn_Str
Dim Conn
Dim strsql
Dim ConFields
Dim dcSpeedee, rsSpeedee
Const DSN_NAME = "DSN=Speedee Track"
Dim I
Dim Fields, Values
Dim Counter

xDb_Conn_Str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("sd\shippingdatabase.mdb") & ";"
set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str1

Dim checkdoubles
Set checkdoubles = Server.CreateObject("ADODB.Recordset")
checkdoubles.open "SELECT * FROM UPS WHERE TrackingNumber = """ & BARCODE & """;", dcSpeedee

If checkdoubles.bof and checkdoubles.eof then
ConFields=LoadFile("convertspeedee.con")

Counter=0
Do While Not rsSpeedee.EOF
Fields=""
Values=""
strsql=""
strsql = "INSERT INTO UPS (ParcelCarrier "

strsql = strsql & Fields & ") Values ('Speedee' " & Values & " );"

Conn.execute(strsql)
Counter=Counter+1
Response.Write Counter & " Total Records Added"
rsSpeedee.MoveNext
Loop

Else
Response.Write "RECORD ALREADY ADDED"
End If

Response.Write "END"
%>

you may need to move some stuff around to get it to work properly, but you should get the main idea...

-------------
Paul A Morgan

http://www.pmorganphoto.com/" rel="nofollow - http://www.pmorganphoto.com/