469,323 Members | 1,493 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

Best Practice for comparing SQL tables

12
Greetings All,

I am trying to parse a directory of images and reference a table in SQL to check if that image filename exists in the table. If not I'd like to INSERT it into a table (tbl_images) and if it does exist just ignore and move on.

Ideally I'd like to take it one step further in that if it finds a record that doesn't match a filename, that row is deleted or flagged for later review. For now I'd just like to get the INSERT working.

Because my DB is on a different server than the files I am using ASP to query the folder and pull contents. My thoughts are that I would take what ASP is pulling and insert it into a temporary table and then compare the two tables. Is this the best approach? I'm fairly new at database design/practices. Any suggestions or guidance is much appreciated.
Jun 6 '08 #1
7 1315
jeffstl
432 Expert 256MB
I'm not sure what you mean by the database is on a different server, and copying the table over etc. But anyway this would be the code for your solution
assuming you have a connection string called DataConn and recordsets set up
Expand|Select|Wrap|Line Numbers
  1.  
  2. SearchSQL = "SELECT TableFileName FROM ImageTable WHERE TableFileName = '" & PassedFileName & "'"
  3. MyRS.Open SearchSQL,DataConn,3,3
  4.  
  5. If MyRS.Recordcount <> 0 then 'file exists
  6.      'do nothing
  7. else
  8.     'insert new file name
  9.     InsertSQL = "INSERT into ImageTable (TableFileName) VALUES ('" & PassedFileName & "')"
  10.     DataConn.execute(InsertSQL)
  11. end if
  12.  
  13.  
  14.  
Is this what you mean?
Jun 6 '08 #2
sbryguy
12
wow it took me a while longer to get back to this than expected. Basically what I'm trying to do is read a driectory with ASP; then looping through that directory I'd like to compare the filenames with the filepath column in a SQL table. Here's the code I'm playing with :

Expand|Select|Wrap|Line Numbers
  1. for each item in folder.Files 
  2.  
  3.  url = MapURL(item.path)
  4.  
  5.         If url = rs.Fields.Item("filepath").Value Then
  6.         tFlag = true
  7.         Response.Write("<li>" _
  8.         & "<a href=""" & url & """>"_
  9.         & item.Name & "</a> - " _
  10.         & item.Size & " bytes, " _
  11.         & "last modified on " & item.DateLastModified & ".  "&tFlag)
  12.         Response.Write("<b>")        
  13.         Response.Write("**</b></li>")
  14.         rs.MoveNext()
  15.         Else
  16.         Response.Write("<li>" _
  17.         & "<a href=""" & url & """>"_
  18.         & item.Name & "</a> - " _
  19.         & item.Size & " bytes, " _
  20.         & "last modified on " & item.DateLastModified & ".  "&tFlag)
  21.         Response.Write("</li>")
  22.         End If
  23. Next
  24.  
  25.      Response.Write("</ul>" & vbCrLf)
  26.      Response.Write("</li>" & vbCrLf)
This compares the files but if the last record is reached the script stops listing the files; it only lists the files that match the RS. Anyone know how I can compare filenames to a DB table and list the rest of the files?
Jun 24 '08 #3
jeffstl
432 Expert 256MB
Off hand I don't see anything wrong with your logic or code.

Is your question reversed?

In other words are you saying that the links all print out for all the files, but are missing links for records in the database?

Another way of looking at it: Do you consider your "master list" of files to be those in the directory, or those on the database?

Your code as it is now seems to operate based on the assumption that the directory of files is your master list, so if there are file names in the database that don't exist in the directory, they would be missed.
Jun 24 '08 #4
sbryguy
12
Yes, my master list would be the directory; I'm trying to set it up that any item that doesn't match a record in the database will be linked to an addImage page that would add that image information to the db after clicked.

What happens when I have the rs.MoveNext() is that it reaches the end of the file and stops processing the list of files in the directory.

I can get it to display files that match the DB, (in this case that means the image has already been entered into the table). However, how can I either continue to process the files in the directory, listing them and flagging them as new, or create a new list that has only non-processed links.

I'm having the reverse problem, files not in DB are not being listed. Does that help clear things up? This is uncharted territory for me so I'm trying to make sense of what I'm asking as well as solving the problem. Thanks for your quick response.
Jun 24 '08 #5
jeffstl
432 Expert 256MB
Oh ok. I think I see whats happening. I didn't realize you were actually getting an error and it stops? Is this whats happening? It seems like you would because the RS is going to hit its end before the directory loop is done....

If that is the case this should help

Expand|Select|Wrap|Line Numbers
  1. for each item in folder.Files 
  2.  
  3.  url = MapURL(item.path)
  4.         if not rs.EOF then
  5.         If url = rs.Fields.Item("filepath").Value Then
  6.         tFlag = true
  7.         Response.Write("<li>" _
  8.         & "<a href=""" & url & """>"_
  9.         & item.Name & "</a> - " _
  10.         & item.Size & " bytes, " _
  11.         & "last modified on " & item.DateLastModified & ".  "&tFlag)
  12.         Response.Write("<b>")      
  13.         Response.Write("**</b></li>")
  14.         rs.MoveNext()
  15.         else
  16.                 'code to handle link to add page, recordset is at end
  17.         end if
  18.         Else
  19.         Response.Write("<li>" _
  20.         & "<a href=""" & url & """>"_
  21.         & item.Name & "</a> - " _
  22.         & item.Size & " bytes, " _
  23.         & "last modified on " & item.DateLastModified & ".  "&tFlag)
  24.         Response.Write("</li>")
  25.         End If
  26. Next
  27.  
  28.  
Jun 24 '08 #6
sbryguy
12
That worked perfectly! Thanks a million for your help and guidance.
Jun 24 '08 #7
sbryguy
12
Spoke too soon,

The script works great the first time; however after an image is added; the only images listed are now in the DB and not the unlisted.

I don't understand why but the conditional statement that compares the filename with the DB entry is trumping the rest of the script after I execute an insert call.
Jun 24 '08 #8

Post your reply

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

Similar topics

4 posts views Thread by Will Hartung | last post: by
2 posts views Thread by digitalQ | last post: by
3 posts views Thread by BPDudeMan | last post: by
1 post views Thread by Diffident | last post: by
9 posts views Thread by Bryan Hepworth | last post: by
7 posts views Thread by Steve | last post: by
5 posts views Thread by Frank Millman | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.