Best Practice for comparing SQL tables

[sbryguy]

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.

[jeffstl]

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

SearchSQL = "SELECT TableFileName FROM ImageTable WHERE TableFileName = '" & PassedFileName & "'"
MyRS.Open SearchSQL,DataConn,3,3

If MyRS.Recordcount <> 0 then 'file exists
     'do nothing
else
    'insert new file name
	InsertSQL = "INSERT into ImageTable (TableFileName) VALUES ('" & PassedFileName & "')"
	DataConn.execute(InsertSQL)
end if

Is this what you mean?

[sbryguy]

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 :

for each item in folder.Files 

 url = MapURL(item.path)
		
		If url = rs.Fields.Item("filepath").Value Then
		tFlag = true
		Response.Write("<li>" _
		& "<a href=""" & url & """>"_
		& item.Name & "</a> - " _
		& item.Size & " bytes, " _
		& "last modified on " & item.DateLastModified & ".  "&tFlag)
		Response.Write("<b>")		
		Response.Write("**</b></li>")
		rs.MoveNext()
		Else
		Response.Write("<li>" _
		& "<a href=""" & url & """>"_
		& item.Name & "</a> - " _
		& item.Size & " bytes, " _
		& "last modified on " & item.DateLastModified & ".  "&tFlag)
		Response.Write("</li>")
		End If
Next
   
     Response.Write("</ul>" & vbCrLf)
     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?

[jeffstl]

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.

[sbryguy]

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.

[jeffstl]

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

for each item in folder.Files 
 
 url = MapURL(item.path)
        if not rs.EOF then
        If url = rs.Fields.Item("filepath").Value Then
        tFlag = true
        Response.Write("<li>" _
        & "<a href=""" & url & """>"_
        & item.Name & "</a> - " _
        & item.Size & " bytes, " _
        & "last modified on " & item.DateLastModified & ".  "&tFlag)
        Response.Write("<b>")      
        Response.Write("**</b></li>")
        rs.MoveNext()
        else
                'code to handle link to add page, recordset is at end
        end if
        Else
        Response.Write("<li>" _
        & "<a href=""" & url & """>"_
        & item.Name & "</a> - " _
        & item.Size & " bytes, " _
        & "last modified on " & item.DateLastModified & ".  "&tFlag)
        Response.Write("</li>")
        End If
Next

[sbryguy]

That worked perfectly! Thanks a million for your help and guidance.

[sbryguy]

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.