[RESOLVED] Is this database update/insert code correct..it runs really slow for me

[M@dH@tter]

It just runs really slow..course i'm basing my idea of slow on a comparison to loading the same data into a listview.
does loading a database have a beginupdate/endupdate like a listview? if so maybe that's what i'm missing???
However on good note all the bugs seem to be gone now..so that's a plus :-)

    Dim LocDir As String = "C:\"
    Dim dsNewRefLibRow = New OleDb.OleDbCommand
    Dim dt As DataTable
    Dim dv As DataView
    Public WithEvents Import As BackgroundWorker
    Public ItemList As New List(Of Manual)

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'ds.Clear()
        Import = New BackgroundWorker
        With Import
            .WorkerReportsProgress = True
            .WorkerSupportsCancellation = True
        End With
 
               CurrentDBFiles = "RefLib.accdb"
                TempDB = "NewRefLib"
                dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"
                Dim fldr As String = LocDir & CurrentDBFiles
                dbSource = "Data Source = " & fldr
                conRefLib.ConnectionString = dbProvider & dbSource
                conRefLib.Open()

                sqlRefLib = "SELECT * FROM AssistDB"
                daRefLib = New OleDb.OleDbDataAdapter(sqlRefLib, conRefLib)
                daRefLib.Fill(dsRefLib, TempDB)
                conRefLib.Close()

    End Sub

    Private Sub Import_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles Import.DoWork
 
        Dim Import As BackgroundWorker = CType(sender, BackgroundWorker)
        Dim lines() As String = File.ReadAllLines(strFileName)
        ItemList.AddRange((From l In lines Let NLine = l.Split("|"c) Select New Manual With { _
                       .StrModel = Trim(NLine(0)), _
                       .StrDocument = Trim(NLine(1)), _
                       .StrDescription = Trim(NLine(2)), _
                       .StrByteSize = Trim(NLine(3)), _
                       .StrFileSize = Trim(NLine(4)), _
                       .StrStatus = Trim(NLine(5)), _
                       .StrLocation = Trim(NLine(6)), _
                       .StrCRC32 = Trim(NLine(7)), _
                       .StrFileName = Trim(NLine(8)), _
                       .StrSite = Trim(NLine(9))}).ToArray)


        For Each udtmanual In ItemList ' Starting at here everything seems really slow to me

                CurrentDBFiles = "RefLib.accdb"
                TempDB = "NewRefLib"
                dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"
                RefLibFldr = LocDir & CurrentDBFiles
                dbSource = "Data Source=" & RefLibFldr
                sqlRefLib = "SELECT * FROM AssistDB"
                Check_For_RefLib_Dups(udtmanual)

        Next
        conRefLib.Close()
    End Sub

    Private Sub Check_For_RefLib_Dups(ByVal udtmanual)
        dt = dsRefLib.Tables("NewRefLib")
        dv = dt.DefaultView
        With udtmanual

            dv.RowFilter = "FileName = '" & .strfilename & "'"
            If dv.Count > 0 Then
                'MessageBox.Show("A duplicate was found")
                RefLib_Update(.StrModel, .StrDocument, .StrDescription, .StrByteSize, .StrFileSize, .StrStatus, .StrLocation, .StrCRC32, _
                              .StrFileName, .StrSite, conRefLib)
            Else
                'MessageBox.Show("A duplicate was Not found")
                RefLib_AddItem(.StrModel, .StrDocument, .StrDescription, .StrByteSize, .StrFileSize, .StrStatus, .StrLocation, .StrCRC32, _
                               .StrFileName, .StrSite, daRefLib, dsRefLib, conRefLib)
            End If

        End With
    End Sub

    Private Sub RefLib_Update(ByVal Model, ByVal Document, ByVal description, ByVal ByteSize, ByVal FileSize, ByVal Status, _
                             ByVal Location, ByVal Crc32, ByVal FileName, ByVal StrSite, ByRef conRefLib)

        'Create the command.
        Dim UpdateSQL As String = "UPDATE(AssistDB) " & _
                                 "SET Model = @Model, " & _
                                 "Document = @Document, " & _
                                 "Description = @Description, " & _
                                 "ByteSize = @ByteSize, " & _
                                 "FileSize = @FileSize, " & _
                                 "Status = @Status, " & _
                                 "Location = @Location, " & _
                                 "Crc32 = @Crc32, " & _
                                 "FileName = @FileName, " & _
                                 "Site = @Site " & _
                                 "WHERE  FileName = @FileName"

        Dim dsNewRefLibRow = New OleDb.OleDbCommand(UpdateSQL, conRefLib)

        dsNewRefLibRow.Parameters.AddWithValue("Model", Model)
        dsNewRefLibRow.Parameters.AddWithValue("Document", Document)
        dsNewRefLibRow.Parameters.AddWithValue("Description", description)
        dsNewRefLibRow.Parameters.AddWithValue("ByteSize", ByteSize)
        dsNewRefLibRow.Parameters.AddWithValue("FileSize", FileSize)
        dsNewRefLibRow.Parameters.AddWithValue("Status", Status)
        dsNewRefLibRow.Parameters.AddWithValue("Location", Location)
        dsNewRefLibRow.Parameters.AddWithValue("Crc32", Crc32)
        dsNewRefLibRow.Parameters.AddWithValue("FileName", FileName)
        dsNewRefLibRow.Parameters.AddWithValue("Site", StrSite)

        Try
            If conRefLib.State = ConnectionState.Closed Then conRefLib.Open()
            dsNewRefLibRow.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            ' connection.Close()
        End Try
    End Sub

    Private Sub RefLib_AddItem(ByVal Model, ByVal Document, ByVal description, ByVal ByteSize, ByVal FileSize, ByVal Status, _
                              ByVal Location, ByVal Crc32, ByVal FileName, ByVal StrSite, ByRef daRefLib, ByVal dsRefLib, ByVal ConRefLib)

        Dim insertSQL As String = "INSERT INTO AssistDB "
        insertSQL &= "(Model, Document, Description, ByteSize, FileSize, Status, Location, Crc32, FileName, Site)"
        insertSQL &= "VALUES (Model, Document, Description, ByteSize, FileSize, Status, Location, Crc, FileName, StrSite)"

        dsNewRefLibRow = New OleDb.OleDbCommand(insertSQL, ConRefLib)

        dsNewRefLibRow.Parameters.AddWithValue("Model", Model)
        dsNewRefLibRow.Parameters.AddWithValue("Document", Document)
        dsNewRefLibRow.Parameters.AddWithValue("Description", description)
        dsNewRefLibRow.Parameters.AddWithValue("ByteSize", ByteSize)
        dsNewRefLibRow.Parameters.AddWithValue("FileSize", FileSize)
        dsNewRefLibRow.Parameters.AddWithValue("Status", Status)
        dsNewRefLibRow.Parameters.AddWithValue("Location", Location)
        dsNewRefLibRow.Parameters.AddWithValue("Crc32", Crc32)
        dsNewRefLibRow.Parameters.AddWithValue("FileName", FileName)
        dsNewRefLibRow.Parameters.AddWithValue("Site", StrSite)


        Try
            If ConRefLib.State = ConnectionState.Closed Then ConRefLib.Open()
            dsNewRefLibRow.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            ' connection.Close()
        End Try

    End Sub

[dunfiddlin]

If it works, it's correct in that sense. But I have to admit that trying to read it made my head hurt and my eyes sore. There just seems to be way too much code there for what it achieves, and you rely far too heavily on accessing the database for just about everything. The whole point of modern database methods is to minimise reads and writes to the database proper and take advantage of the faster processing offered in memory. I suspect that there is a good deal of duplication of effort in there too but it's always difficult to tell when you just don't have the actual files and database etc. to play with so I may be wrong.

One thing I would definitely cut out though is the message boxes. I've never understood the love affair which people seem to have with them. Do I really need to know what it is they are telling me and especially do I really need to know so much that it warrants interrupting the program and waiting for me to push a button to say that I've read it? Almost never in my experience.

[formlesstree4]

This also looks to be Access, which isn't the snappiest platform. I could be wrong about that, though.

[jmcilhinney]

That code's rather terrible I'm afraid. As dunfiddlin says, there's loads of wasted effort there. I think the most important thing is that you should be saving all the data as a batch though. Exactly how you proceed depends heavily on one thing. In your code you use 'dsRefLib.Tables("NewRefLib")' as the source to check for duplicates. Does that means that that DataTable already contains all the data for the table that you're updating?

[M@dH@tter]

Originally i tried this routine but it ran even slower than the above by at least 4 min or so.. course that taking into account that no data was written back to the database until the loop had finished and I called the update line "daRefLib.Update(dsRefLib, "NewRefLib")"

        'Dim cb As New OleDb.OleDbCommandBuilder(daRefLib)
        'Dim dsNewRefLibRow As DataRow

        'dsNewRefLibRow = dsRefLib.Tables("NewRefLib").NewRow()

        'dsNewRefLibRow.Item("Model") = Model
        'dsNewRefLibRow.Item("Document") = Document
        'dsNewRefLibRow.Item("Description") = description
        ' dsNewRefLibRow.Item("ByteSize") = ByteSize
        'dsNewRefLibRow.Item("FileSize") = FileSize
        'dsNewRefLibRow.Item("Status") = Status
        ' dsNewRefLibRow.Item("Location") = Location
        ' dsNewRefLibRow.Item("Crc32") = Crc32
        ' dsNewRefLibRow.Item("FileName") = FileName
        ' dsNewRefLibRow.Item("Site") = StrSite

        'dsRefLib.Tables("NewRefLib").Rows.Add(dsNewRefLibRow)

i take the existing data from the database and store it in memory and then do any checks for duplicates on that table..if no dup add it to the table in memory..or at least that's what i thought..after looking at the code again and stepping thru it i realized that the data was being compared right..but then it was being written to the db that was being stored on the HD..this may be the bottle neck..i don't know..
anywho

Yes this is MS access database. i'm open to any other db that may be faster..i only used Access cause i already had MS office 2007 installed
If the code above is doing to much..can someone offer a new take on it..
I think the for loop should at the very minimum give some idea of how i was trying to make this work?

oh the messagebox.show stuff is only there until i can make sure that nothing is or was wrong.. i always comment those out when i run a full run test..i leave them there tho in case i need to see exactly what was going on etc...

[jmcilhinney]

If you already have a DataTable then you should use it. You can simply loop through the data and call LoadDataRow on your DataTable for each record. That will take care of updating an existing row or adding a new one, so you don't have to check manually for duplicates. That will require that you set the PrimaryKey of your DataTable to the column(s) that you're using for comparison, which may or may not be the same as your actual PK. You can also fiddle with the UpdateBatchSize of your data adapter to see if you can speed up the actual save step.

[M@dH@tter]

ok so i looked at this

http://msdn.microsoft.com/en-us/library/kcy03ww2%28v=vs.110%29.aspx

and i also looked at this

http://msdn.microsoft.com/en-us/library/system.data.datatable.rowchanging.aspx

in both cases it looked to me like the dups would be added ..maybe i'm wrong..but that's what it looked like to me

can ya give me an example bud? of how your piecing what your talking about together..or maybe give me better links than the ones i found..cause i don't think they show it.

[jmcilhinney]

How could LoadDataRow possibly add duplicates? Its description specifically states:

Finds and updates a specific row. If no matching row is found, a new row is created using the given values.

A new row is only added if the PK of the row doesn't already exist. As long as you specify the column that you want to compare on as the PK then no duplicates can be added in that column. Of course, that means that your existing data must not have any duplicates in that column either. So, in your case, you're comparing on the FileName column. Make that column the PrimaryKey of your DataTable, call LoadDataRow and the rest is done for you. You'll want to specify False for acceptChanges because you want to RowState to remain Added or Modified so that those changes are saved when you call Update.

[M@dH@tter]

Is this even remotely Close?

Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System
Imports System.ComponentModel

Public Class Form1
    Dim dt As New DataTable()
    Dim myConnection As OleDbConnection = New OleDbConnection
    Dim da As OleDbDataAdapter
    Dim ItemList As New List(Of Manual)
    Dim StrFileName As String = "C:\RefLib.txt"
    Public WithEvents Import As BackgroundWorker
    Dim RowIndex As Integer

    Structure Manual
        Dim StrModel As String
        Dim StrDocument As String
        Dim StrDescription As String
        Dim StrByteSize As String
        Dim StrFileSize As String
        Dim StrStatus As String
        Dim StrLocation As String
        Dim StrCrc32 As String
        Dim StrFileName As String
        Dim StrSite As String
        Dim udtmanual As String
    End Structure
 
   Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\RefLib.accdb"

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        If Not File.Exists("C:\RefLib.accdb") Then
            File.Copy("C:\Database1.accdb", "C:\RefLib.accdb")
        End If
        Import.RunWorkerAsync()

    End Sub

    Private Sub Import_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles Import.DoWork

        Dim Import As BackgroundWorker = CType(sender, BackgroundWorker)

        myConnection.ConnectionString = connString
        da = New OleDbDataAdapter("Select * from AssistDB", myConnection)
        da.Fill(ds, "NewAssistDB")
        myConnection.Close()

        Dim lines() As String = File.ReadAllLines(StrFileName)
        ItemList.AddRange((From l In lines Let NLine = l.Split("|"c) Select New Manual With { _
                       .StrModel = Trim(NLine(0)), _
                       .StrDocument = Trim(NLine(1)), _
                       .StrDescription = Trim(NLine(2)), _
                       .StrByteSize = Trim(NLine(3)), _
                       .StrFileSize = Trim(NLine(4)), _
                       .StrStatus = Trim(NLine(5)), _
                       .StrLocation = Trim(NLine(6)), _
                       .StrCRC32 = Trim(NLine(7)), _
                       .StrFileName = Trim(NLine(8)), _
                       .StrSite = Trim(NLine(9))}).ToArray)


        Dim dc1 As DataColumn = New DataColumn("Model")
        Dim dc2 As DataColumn = New DataColumn("Document")
        Dim dc3 As DataColumn = New DataColumn("Description")
        Dim dc4 As DataColumn = New DataColumn("ByteSize")
        Dim dc5 As DataColumn = New DataColumn("FileSize")
        Dim dc6 As DataColumn = New DataColumn("Status")
        Dim dc7 As DataColumn = New DataColumn("Location")
        Dim dc8 As DataColumn = New DataColumn("Crc32")
        Dim dc9 As DataColumn = New DataColumn("FileName")
        Dim dc10 As DataColumn = New DataColumn("Site")



        For Each udtmanual In ItemList
            Dim Row As DataRow
            With udtmanual

                Dim NewRow As Object() = New Object(9) {}

                RowIndex = ItemList.IndexOf(udtmanual)
                Import.ReportProgress(RowIndex * 100 / ItemList.Count)

                NewRow(0) = IIf(.StrModel = "", System.DBNull.Value, .StrModel)
                NewRow(1) = IIf(.StrDocument = "", System.DBNull.Value, .StrDocument)
                NewRow(2) = IIf(.StrDescription = "", System.DBNull.Value, .StrDescription)
                NewRow(3) = IIf(.StrByteSize = "", System.DBNull.Value, .StrByteSize)
                NewRow(4) = IIf(.StrFileSize = "", System.DBNull.Value, .StrFileSize)
                NewRow(5) = IIf(.StrStatus = "", System.DBNull.Value, .StrStatus)
                NewRow(6) = IIf(.StrLocation = "", System.DBNull.Value, .StrLocation)
                NewRow(7) = IIf(.StrCrc32 = "", System.DBNull.Value, .StrCrc32)
                NewRow(8) = IIf(.StrFileName = "", System.DBNull.Value, .StrFileName)
                NewRow(9) = IIf(.StrSite = "", System.DBNull.Value, .StrSite)

                dt.BeginLoadData() ' Add the new row to the rows collection.
                Row = dt.LoadDataRow(NewRow, False)

            End With

        Next

        dt.EndLoadData()

        For Each dr As DataRow In dt.Rows
            Dim Test As Integer = dt.Rows.IndexOf(dr)
            MessageBox.Show([String].Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9}", _
                                            dr("Model"), _
                                            dr("Document"), _
                                            dr("Description"), _
                                            dr("ByteSize"), _
                                            dr("FileSize"), _
                                            dr("Status"), _
                                            dr("Location"), _
                                            dr("Crc32"), _
                                            dr("FileName"), _
                                            dr("Site")))
            da.Update(ds, dr(Test).ItemArray)

            MessageBox.Show("finished")
        Next


    End Sub

[M@dH@tter]

Hello is there anybody out there..just nod if you can hear..is there anyone at home? Pink Floyd !! yes you know the tune now sing Dang it lmao

I have scanned the MS web Site ,,i even went to a few pages that popped up as being virused or something..i can't find any info on what comes after the LoadDataRow. hell i'm not even sure i have the code going in the right direction cause of all the errors keep moving around on me..i mean i fix one area and then i get stuck at another..

[jmcilhinney]

It's really fairly simple. E.g.

Using connection As New OleDbConnection(connectionString),
      adapter As New OleDbDataAdapter(selectStatement, connection),
      insertCommand As New OleDbCommand(insertStatement, connection),
      updateCommand As New OleDbCommand(updateStatement, connection)
    'Add parameters to insertCommand and updateCommand here.
 
    adapter.InsertCommand = insertCommand
    adapter.UpdateCommand = updateCommand
 
    Dim table As New DataTable
 
    'Get the current data from the database.
    adapter.Fill(table)
 
    'Set the PK of the DataTable to the column we want to compare on.
    table.PrimaryKey = {table.Columns("FileName")}
 
    'Open the text file.
    Using reader As New TextFieldParser(filePath)
        reader.SetDelimiters("|")
 
        'Read the file line by line.
        Do Until reader.EndOfData
            Dim fields = reader.ReadFields()
 
            'If the value in the FileName column already exists then this will update that row,
            'otherwise it will add a new row.
            table.LoadDataRow(fields, False)
        Loop
    End Using
 
    'Save the changes back to the database.
    adapter.Update(table)
End Using

That assumes that the fields read from the file are in the same order as the columns in the table. If that's not the case then you would have to manipulate the values accordingly before calling LoadDataRow.

[M@dH@tter]

ok i have one problem with the code..i get error "Column 'FileName' does not allow nulls." i tried adding this

                    Dim column As DataColumn = New DataColumn("FileName")
                    column.AllowDBNull = True

but the error pops up just as the line " table.LoadDataRow(fields, False) " is Stepped on

[jmcilhinney]

If FileName is the column that you're using to determine duplicates then it doesn't seem to make sense that you don't have value for every record.

[M@dH@tter]

ok hold up i think i got it..the database already has a primary key set..i removed the line to set PK in the code and now i get this error " No value given for one or more required parameters. " on " adapter.Update(table)"

[jmcilhinney]

i removed the line to set PK in the code

I told you why that was required earlier. If you remove then you won't update any existing rows and just add all new ones, thus duplicating the FileName values. I'm afraid I feel like I'm wasting my time with this. Good luck but I won't be contributing further.

EDIT: Unless the FileName already was the PK and you just didn't think to mention that. That makes the fact that you have empty values for that column even more bizarre though. Anyway, I'm off.

[M@dH@tter]

well crap!!!! i put that line back in after i realized that without it it wouldnt know what or where to compare..when i stepped thru the program is went thru the line with no problem at all..so i let it run and then after a few min i got this " The CLR has been unable to transition from COM context 0x4589d0 to COM context 0x458b40 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations. "

[jmcilhinney]

well crap!!!! i put that line back in after i realized that without it it wouldnt know what or where to compare..when i stepped thru the program is went thru the line with no problem at all..so i let it run and then after a few min i got this " The CLR has been unable to transition from COM context 0x4589d0 to COM context 0x458b40 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations. "

OK, we seem to be getting somewhere so I'll overturn my previous decision for now.

That issue relates to the fact that you're stepping through a secondary thread. I would suggest that you use a single thread until you get this code working. If you have difficult to fix issues then it's best to isolate them so you know that you're not mixing up these issues with others. You might even just create a new project to test just this code. Once it's working correctly, then you can integrate it back into your current project.

[M@dH@tter]

one step ahead of ya :-) i copied it over to a new project ..i'll tinker with it some more in the morning..dang i lost track of time it's 3:18 AM lol i'll let ya know how things go tomorrow evening bud..thanks for not given up on me..i'm abit slow sometimes..and i'm def thick headed..so sometimes ya have to hit me with a brick or something before i catch on..i can only say please be patient with me :-) i remember the last conversation we had about the background worker took close to 3 days before that one sunk in..but i eventually got the hang of it..background workers are no problem now..thanks to you :-) and a few others too i believe

[M@dH@tter]

ok everything works until it gets to the line "adapter.Update(Table)" and then it says "No value given for one or more required parameters."

I thought my Insert and Update statements might have been the problem,,but nop they seem to be working ok.
shouldn't that look something like this? "adapter.Update(Table, "Table Name here")"

[M@dH@tter]

this is odd indeed i added a ref to ADO cause i thought that might fix it but nop

[M@dH@tter]

ok according to your comment.. 'If the value in the FileName column already exists then this will update that row,
'otherwise it will add a new row.
Table.LoadDataRow(Fields, False)

So when this line is stepped on it will call either
adapter.InsertCommand = insertCommand
or
adapter.UpdateCommand = updateCommand

if i set a breakpoint on both of those, neither of them get called when the LoadDataRow is stepped

[jmcilhinney]

ok everything works until it gets to the line "adapter.Update(Table)" and then it says "No value given for one or more required parameters."

I thought my Insert and Update statements might have been the problem,,but nop they seem to be working ok.
shouldn't that look something like this? "adapter.Update(Table, "Table Name here")"

That error message means that one of the parameters in the SQL code has had no value assigned to it. You have to assign something to every parameter. If you want no value to be saved then you must assign DBNull.Value. Each parameter's Value is Nothing by default and if you assign Nothing or don't assign anything at all then that exception will result. If you're using a data adapter then that suggests that your SQL code contains a parameter not mapped to a source column.

this is odd indeed i added a ref to ADO cause i thought that might fix it but nop

Why would referencing ADO help when you're not using ADO. ADO.NET has nothing whatsoever to do with ADO other than that they both serve the same purpose.

ok according to your comment.. 'If the value in the FileName column already exists then this will update that row,
'otherwise it will add a new row.
Table.LoadDataRow(Fields, False)

So when this line is stepped on it will call either
adapter.InsertCommand = insertCommand
or
adapter.UpdateCommand = updateCommand

if i set a breakpoint on both of those, neither of them get called when the LoadDataRow is stepped

Why would you think that? Why would adding a row to a DataTable cause a property of a data adapter to be set? LoadDataRow adds a DataRow to the DataTable or updates an existing DataRow and that's it. If the PK value is found then that row is updated and its RowState is set to Modified if it was previously Unchanged and if the PK was not found then a new row is added with a RowState of Added. When you then call Update, the adapter goes through all the DataRows in the DataTable and executes the InsertCommand, UpdateCommand or DeleteCommand for those rows that have a RowState of Added, Modified or Deleted respectively. Unless you're using a command builder, you have to have set those command properties yourself already. If the command property required to save a row based on its RowState is not set then an exception will be thrown.

[M@dH@tter]

probably is something wrong with em.. i just don't see whats wrong with em..

I attached the project and also included a sample txt file,and a blank database..the program is setup to access both from the C:\ drive

Module Module1
    Public SelectStateMent As String = "SELECT * FROM AssistDB"

    Public UpdateStatement As String = "UPDATE AssistDB SET Model = ?," & _
                                    "Document = ?," & _
                                    "Description = ?," & _
                                    "ByteSize = ?, " & _
                                    "FileSize = ?," & _
                                    "Status = ?," & _
                                    "Location = ?, " & _
                                    "Crc32 = ?," & _
                                    "FileName = ?," & _
                                    "Site = ? WHERE FileName = ?"

    Public InsertStatement As String = "INSERT INTO AssistDB " & _
                                    "(Model, Document, " & _
                                    "Description, " & _
                                    "ByteSize," & _
                                    "FileSize, " & _
                                    "Status, " & _
                                    "Location, " & _
                                    "Crc32, " & _
                                    "FileName," & _
                                    "Site) " & _
                                    "VALUES (Model, " & _
                                    "Document, " & _
                                    "Description," & _
                                    "ByteSize, " & _
                                    "FileSize, " & _
                                    "Status, " & _
                                    "Location, " & _
                                    "Crc32, " & _
                                    "FileName, Site)"
End Module

[jmcilhinney]

And where have you added the parameters to the commands, as it says to in my code example:

'Add parameters to insertCommand and updateCommand here.

[M@dH@tter]

aaahh that's by bad I've gotten so used to the way MS comments there sample code that i assumed the 2 lines you had directly below that comment was just showing how to do that ..any who i got it working :-)

My hats off to ya jmcilhinney your code worked as stated..I'm glad you didn't give up on me :-)

[M@dH@tter]

ok as i said above i did get this working,,but there's one thing that's still not clear,,

i keep thinking there's something missing..and i'm trying to picture this in head..
if i import a text file and all lines and column data match whats already in the db file shouldn't Adapter.Update(Table) be skipped over since there is no reason to run that line?
just thinking out loud here..i'm still trying to get a grasp on the whole database thing lol

[jmcilhinney]

aaahh that's by bad I've gotten so used to the way MS comments there sample code that i assumed the 2 lines you had directly below that comment was just showing how to do that ..any who i got it working :-)

My hats off to ya jmcilhinney your code worked as stated..I'm glad you didn't give up on me :-)

When I comment code, if a comment applies to a line of code then it will appear directly above it, as you can see through the rest of my example. If a comment appears with a blank line after it then it doesn't apply to any specific code.

ok as i said above i did get this working,,but there's one thing that's still not clear,,

i keep thinking there's something missing..and i'm trying to picture this in head..
if i import a text file and all lines and column data match whats already in the db file shouldn't Adapter.Update(Table) be skipped over since there is no reason to run that line?
just thinking out loud here..i'm still trying to get a grasp on the whole database thing lol

The call to Update is not going to be skipped over unless you skip over it, i.e. write an If statement to only execute it under certain circumstances. If you haven't done that then Update is going to be called no matter what.

What actually happens when you call Update is another matter. That depends completely on the RowState values of the DataRows in the DataTable. Internally, Update is going to test the RowState of every DataRow. Any rows with a RowState of Unchanged will be ignored so, if all rows are Unchanged, nothing will be saved to the database. In this case, you are calling LoadDataRow for every line in the file. When you call LoadDataRow, you are either going to update and existing row, which will set its RowState to Modified, or add a new row with a RowState of Added. When you call Update, the UpdateCommand is executed for each Modified row and the InsertCommand is executed for each Added row. If you edit a row, it's RowState will be set to Modified even if you don't actually change the value of any fields.

[M@dH@tter]

ok i found how to check the RowState when the LoadDataRow is called..when i Import the index.txt file into a completely blank db file all the RowState's are "add" ..Sounds that's normal :-)
if i ReImport the same index.txt file once more without any changes to it..all the RowState goto "Modified" does that sound normal??

edit: hold on think i figure this out :-)

[jmcilhinney]

Did you read this?

When you call LoadDataRow, you are either going to update and existing row, which will set its RowState to Modified, or add a new row with a RowState of Added.

If so then your question is already answered.

[M@dH@tter]

I'm almost ready to wrap this topic up bud..i have something else i can't figure out
I can use the below code to check if a filename exist in the datatable

       AssistTable.PrimaryKey = {AssistTable.Columns("FileName")}
        If AssistTable.Columns(8).Table.Rows.Contains(Fields(8).ToString) Then

          '''' I can't figure out how to get the index of the item found

        End if

[jmcilhinney]

That code is completely wrong. You are getting the DataTable, then getting one of the DataColumns, then getting the DataTable that owns that column (so you're back where you started) then getting the Rows of that DataTable, then seeing if that DataRowCollection contains a particular String. Given that a DataRowCollection contains nothing but DataRows, that's always going to be False. If you want to know whether a specific column contains a specific value then here is one way to do it:

If myDataTable.Select(String.Format("ColumnName = '{0}'", value)).Any() Then

[M@dH@tter]

ok i'll try that,, but i still need to find the index of the item found tho

[jmcilhinney]

Do you? Or do you actually need the item found itself? If you have the index then wouldn't you just be using it to get the item? DataTable.Select returns an array of DataRows. If no match is found then that array will be empty and if one or more matches are found then the array will contain those rows.

[M@dH@tter]

oh sorry i shoulda mentioned what i trying to do..ok i'm trying to get around having my whole index dumped into the AssistTable.LoadDataRow(Fields, False) line
you mentioned earlier that a loop should be used ..since its marking all items that are exactly the same as the last time the index was imported as "Modified" it takes way to long to run thru very large indexes..soooo
i thought why not just test the index.txt file data against the lines in the table. just before that line..if the return is false then it will get added..if it returns true then skip it.
this should hopefully only make updates or inserts for only data that needs it..thus far it's been adding all the data ..only workaround i could think of given my extremely limited knowledge in vb :-)

   Public Function TableTest(ByRef Check As Boolean, ByVal Fields As String(), ByVal ReflibTable As DataTable)

        If ReflibTable.Select(String.Format("FileName = '{0}'", Fields(8).ToString)).Any() Then

                Dim Test() As Object = ReflibTable.Columns(0).Table.Rows(IntRow).ItemArray  ' < i need something to put in IntRow else this wont work
                
                If Test(0).ToString = Fields(0).ToString And Test(1).ToString = Fields(1).ToString And _
                             Test(2).ToString = Fields(2).ToString And Test(3).ToString = Fields(3).ToString And _
                                Test(4).ToString = Fields(4).ToString And Test(5).ToString = Fields(5).ToString And _
                               Test(6).ToString = Fields(6).ToString And Test(7).ToString = Fields(7).ToString And _
                               Test(9).ToString = Fields(9).ToString Then Check = True
            End If
          Return Check
   End Function

[jmcilhinney]

Just call a method like this instead of DataTable.LoadDataRow:

Private Sub LoadDataRow(table As DataTable, fieldValues As String())
    Dim existingRow = table.Select(String.Format("FileName = '{0}'", fieldValues(0))).FirstOrDefault()

    If existingRow Is Nothing Then
        'No matching row so add a new one.
    ElseIf row(1).ToString() <> fieldValues(1) OrElse row(2).ToString() <> fieldValues(2) Then
        'Matching row contains some different values so edit existing row.
    End If
End Sub

[M@dH@tter]

:-) at first glance i didn't think that was going to work..but then i realized that your code was returning the row that it found the match on and then comparing that to the data from imported data..yup that will work i think this thread is now resolved thank you much for ya help bud

[jmcilhinney]

:-) at first glance i didn't think that was going to work..but then i realized that your code was returning the row that it found the match on and then comparing that to the data from imported data..yup that will work i think this thread is now resolved thank you much for ya help bud

Ah yes, that should have been 'existingRow' rather than just 'row'. Too easy to make mistakes when writing code into a post rather than an IDE.

[M@dH@tter]

hehe i know what ya mean,