check if row exists in table -- vb.net data

vb.net data : check if row exists in table

Beth
5/25/2006 3:39:01 PM

Hello.
I need to import files from our network to an image server, but only if they
haven't been imported already. I have a table storing records for each file
that's imported, and I want to check that table to see if there's a row that
matches the file's name and size. If it's there, I don't import.

I have a tableAdapter with two queries- the default one that fills a
datatable with all rows, and one that fills by the file name and size.

I also have two datatables- one for the results with all records, and one
for the single result matching the search criteria.

I can't get the fillby method to work, and I'm not sure what I'm doing
wrong.

I can get records added to the table, but the next time I run the
application and do the fill, it says the data table has zero records, when
the database has the records from the last run.

I don't have any UI- just a 'go' and 'exit' button. All the data comes from
files on the network.

I have one dataset control on my main form named ctlDSFNImport.
Here's some of the code:
Dim dirInfo As DirectoryInfo
Dim targetFile As FileInfo
Dim taFilesValid As dsFnimportTableAdapters.taTblFilesValid
Dim dtFilesValid As dsFnimport.dtTblFilesValidDataTable
Dim dtFilesValidExists As dsFnimport.dtTblFilesValidDataTable
Dim arrFiles() As FileInfo
Dim drFilesValid As dsFnimport.dtTblFilesValidRow
Dim sFile As String
Dim sSourceDir As String
Dim lSize As Long
Dim eFileState As m_enumFileState

taFilesValid = New dsFnimportTableAdapters.taTblFilesValid()
dtFilesValid = ctlDsFnimport.dtTblFilesValid
taFilesValid.ClearBeforeFill = False
taFilesValid.Fill(dtFilesValid)
....
m_status("Searching for " & sSourceDir & "\*.jpg")
dirInfo = New DirectoryInfo(sSourceDir)
arrFiles = dirInfo.GetFiles("*.jpg", SearchOption.AllDirectories)
lFilesFound = arrFiles.Length
m_status("Sorting files...")
Array.Sort(arrFiles, compareByName)
For Each targetFile In arrFiles
sFile = LCase(targetFile.Name)
lSize = targetFile.Length
dtFilesValidExists = ctlDsFnimport.dtTblFilesValid
taFilesValid.FillByKey(dtFilesValidExists, sFile, lSize)
If dtFilesValidExists.Rows.Count > 0 Then
eFileState = m_enumFileState.m_efsUnchanged
Else
drFilesValid = dtFilesValid.NewdtTblFilesValidRow
drFilesValid.f_file = sFile
drFilesValid.f_size = lSize
...

dtFilesValid.AdddtTblFilesValidRow(drFilesValid)
taFilesValid.Update(dtFilesValid)
dtFilesValid.AcceptChanges()
End If

all help appreciated...

William (Bill) Vaughn
5/31/2006 10:29:59 AM

I hate to use a client application for this kind of work. Bulk copy
operations should be done on the server. I would build a DTS script or write
a BCP-enabled application to move the rows to a temporary table. Once on the
server, it's a relatively easy task to do an INSERT where the PK is not in
the target table.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

[quoted text, click to view]

Beth
5/31/2006 11:34:01 AM

Thanks for the response.
I figured out what I needed to do.
I added a query with an invalid condition in the WHERE clause to use for
adding new records (WHERE ID = null)
and added another query to find if the record exists given the key fields.
Both queries are in the same tableadapter, but I use two different data
tables for the results.

So it's more like:
Dim dtFilesValid As dsFnimport.dtTblFilesValidDataTable
Dim dtFilesValidExists As dsFnimport.dtTblFilesValidDataTable
....
taFilesValid = New dsFnimportTableAdapters.taTblFilesValid()
dtFilesValid = ctlDsFnimport.dtTblFilesValid
taFilesValid.ClearBeforeFill = False
taFilesValid.FillForInsert(dtFilesValid) ' this returns no rows
....
dtFilesValidExists = New
dsFnimport.dtTblFilesValidDataTable
' this call is slow
taFilesValid.FillByKey(dtFilesValidExists, sTypeCode,
sMunic, sFile) ' these are the key fields
If dtFilesValidExists.Rows.Count = 0 Then
drFilesValid = dtFilesValid.NewdtTblFilesValidRow
....
dtFilesValid.AdddtTblFilesValidRow(drFilesValid)
taFilesValid.Update(dtFilesValid)
dtFilesValid.AcceptChanges()

Also, I have my .mdb added to my project, but I needed to change its 'Copy
to output directory' property to 'Copy if newer' so it didn't keep
overwriting the .mdb in the bin folder with the .mdb in the project folder.

We can't use a server-side application, as we're importing to a Filenet
server, which is proprietary.
My application will call their components to do the import.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about vb.net data

home · blog · groups

Questions? Comments? Contact the dn