[RESOLVED] [2008] MDB not updating with da.update

[rhijaen]

I think it was working a few days ago but now it's not. The funny thing is if I have the mdb open and I run this, the values get replaced with "#Deleted" and when I close it and reopen they are gone. But, if I have it closed, nothing happens.

What could be the problem?

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        OpenFileDialog1.Filter = "mdb files(*.mdb)|*.mdb"

        OpenFileDialog1.ShowDialog()

        con.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & OpenFileDialog1.FileName
        con.Open()
        sql = "Select * From Files"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Files")


        maxrows = ds.Tables("Files").Rows.Count
        ReDim filename(maxrows)
        ReDim setid(maxrows)
        ReDim filesize(maxrows)

        For i = 0 To maxrows - 1
            setid(i) = ds.Tables("Files").Rows(i).Item(0)
            filename(i) = ds.Tables("Files").Rows(i).Item(2)
            filesize(i) = ds.Tables("Files").Rows(i).Item(4)
        Next
        Dim filename2 As String() = DirectCast(filename.Clone(), String())
        Array.Sort(filename, setid)
        Array.Sort(filename2, filesize)

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        For i = 0 To maxrows - 2
            If filename(i) = filename(i + 1) And setid(i) = setid(i + 1) Then
                RichTextBox1.Text = RichTextBox1.Text & vbCrLf & setid(i)
                RichTextBox1.Text = RichTextBox1.Text & vbCrLf & setid(i + 1)
                RichTextBox2.Text = RichTextBox2.Text & vbCrLf & filename(i)
                RichTextBox2.Text = RichTextBox2.Text & vbCrLf & filename(i + 1)
                RichTextBox3.Text = RichTextBox3.Text & vbCrLf & filesize(i)
                RichTextBox3.Text = RichTextBox3.Text & vbCrLf & filesize(i + 1)
                If filesize(i) > filesize(i + 1) Then
                    ds.Tables("Files").Rows(i).Delete()
                    da.Update(ds, "Files")
                ElseIf filesize(i) < filesize(i + 1) Then
                    ds.Tables("Files").Rows(i + 1).Delete()
                    da.Update(ds, "Files")
                Else
                    MsgBox(filename(i) & " has a dupe filesize")
                End If
            End If
        Next
        con.Close()
    End Sub

[rhijaen]

I added a MsgBox(ds.Tables("Files").Rows(i).Item(2)) just to do a little debugging..turns out the values are totally not what I want them to be. Is this because I sorted the array beforehand? How do I get around this problem?

                If filesize(i) > filesize(i + 1) Then
                    MsgBox(ds.Tables("Files").Rows(i).Item(2))
                    ds.Tables("Files").Rows(i).Delete()

                    da.Update(ds, "Files")

[rhijaen]

Bump can anyone help me out?

[jmcilhinney]

Don't bump your threads after an hour and 16 minutes. It's against forum policy.

I don't really know exactly what you're trying to achieve because you've chosen not to tell us. I think it was I who helped you sort those arrays but now that I see what you're doing it was a waste of time. You've already got the data in a DataTable so you should simply set its DefaultView.Sort property. Shazam! The data is sorted without need for any arrays.

How about you explain what the purpose of all this is and then we can provide the most relevant advice? I think it's fair to say that I would recommend something quite different to what you have there, but I really can't say what because I don't know what it's supposed to do. If you provide a full and clear description in the first place then we can avoid wasting time like this. Each time you post:

1. This is what I want to do (include full and clear description).
2. This is how I'm trying to do it (include relevant code).
3. This is what happens when I do (full and clear description, including relevant error messages and where they occur).

[rhijaen]

Okay well

In my database I have some dupe filenames. I want to delete the row of the dupe with the highest filesize. The table is called "Files". Relevant columns are "Set ID", "File ID" (Unique), "Filename" and "Size".

Each filename belongs to a setid which includes several filenames.

So if there is a duplicate filename with the same setid, I want to delete the biggest one in filesize.

Thanks

[jmcilhinney]

Excellent. You could actually do all of this just with SQL if you cared to build up the appropriate statement. If you do want to do it in VB then you should at least use the SQL code to sort the data:

SELECT * FROM Files ORDER BY SetID, FileName, Size DESC

That will produce a DataTable where all files with the same SetID will be grouped together and, within those groups, they will be grouped together by FileName. Within those groups they will be sorted in descending order by Size. That means that all you have to do is check each row and if the next row has the SetID and FileName then you delete the current row.

Dim currentRow As DataRow
Dim nextRow As DataRow
Dim rows As DataRowCollection = myDataTable.Rows
 
For index As Integer = 0 To rows - 2
    currentRow = rows(index)
    nextRow = rows(index + 1)
 
    If CStr(nextRow("FileName")) = CStr(currentRow("FileName")) AndAlso _
       CInt(nextRow("SetID")) = CInt(currentRow("SetID")) Then
        currentRow.Delete()
    End If
Next

Only after you'ce completed that loop do you call Update, thus saving all the changes in one go.

[rhijaen]

Here is my full code

I'm getting an error: Error 'Rows' is not a member of 'System.Data.DataSet'

I know it has to be a datatable but I'm not sure how to set one up.

Also this is an access database(mdb) not an sql database.

Imports System.Data.OleDb
Public Class Form1
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        OpenFileDialog1.Filter = "mdb files(*.mdb)|*.mdb"

        OpenFileDialog1.ShowDialog()

        con.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & OpenFileDialog1.FileName
        con.Open()
        sql = "SELECT * FROM Files ORDER BY SetID, FileName, Size DESC"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Files")
        con.Close()

        Dim currentRow As DataRow
        Dim nextRow As DataRow
        Dim rows As DataRowCollection = ds.Rows - 2

        For index As Integer = 0 To 2000
            currentRow = rows(index)
            nextRow = rows(index + 1)

            If CStr(nextRow("FileName")) = CStr(currentRow("FileName")) AndAlso _
               CInt(nextRow("SetID")) = CInt(currentRow("SetID")) Then
                currentRow.Delete()
            End If
        Next

        da.Update(ds, "Files")

    End Sub
End Class

[jmcilhinney]

You don't need a DataSet at all. Just use a DataTable. Just create a DataTable and pass that to Fill and Update, as I've done. Follow the Data Access link in my signature if you need an example.

For future reference, this:

da.Fill(ds, "Files")

is filling a DataTable named "Files" in your DataSet and this:

da.Update(ds, "Files")

is updating the same table. To get a direct reference to that DataTable you would use ds.Tables("Files") but, in this case, there's no point. A DataSet containing a single DataTable is useless when you could just use a DataTable on its own.

[rhijaen]

On the line with For index As Integer = 0 To rows - 2, I get an error: Error Operator '-' is not defined for types 'System.Data.DataRowCollection' and 'Integer'.

If I change it to a static number like 1000, it runs but I get an error at da.Fill(dt): IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

Imports System.Data.OleDb
Public Class Form1
    Dim con As New OleDb.OleDbConnection
    Dim dt As New DataTable
    Dim da As New OleDb.OleDbDataAdapter
    Dim sql As String

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

        OpenFileDialog1.Filter = "mdb files(*.mdb)|*.mdb"

        OpenFileDialog1.ShowDialog()

        con.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & OpenFileDialog1.FileName
        con.Open()
        sql = "SELECT * FROM Files ORDER BY SetID, FileName, Size DESC"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(dt)
        con.Close()

        Dim currentRow As DataRow
        Dim nextRow As DataRow
        Dim rows As DataRowCollection = dt.Rows

        For index As Integer = 0 To 1000
            currentRow = rows(index)
            nextRow = rows(index + 1)

            If CStr(nextRow("FileName")) = CStr(currentRow("FileName")) AndAlso _
               CInt(nextRow("SetID")) = CInt(currentRow("SetID")) Then
                MsgBox(currentRow("FileName"))
                currentRow.Delete()
            End If
        Next

        da.Update(dt)

    End Sub
End Class

[jmcilhinney]

Sorry, that should be "rows.Count - 2". The upper limit of the loop counter needs to be the index of the second to last row in the table.

[rhijaen]

I get an error at da.Fill(dt): IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

What is causing this error and how do I fix it?

[jmcilhinney]

No idea. Is your SQL code definitely valid? Is your connection string definitely valid?

[rhijaen]

I fixed it..had to be sql = "SELECT SetID, Filename, [Size] FROM Files ORDER BY SetID, Filename, [Size] DESC"

But now I get: No value given for one or more required parameters.

Why can't this just work?

        con.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & OpenFileDialog1.FileName
        con.Open()
        sql = "SELECT * FROM Files ORDER BY SetID, Filename, [Size] DESC"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(dt)
        con.Close()

[formlesstree4]

No idea. Is your SQL code definitely valid? Is your connection string definitely valid?

Having an invalid connection string wouldn't raise that error, unless he didn't do an error trapper.

Same with the query. Use the Try ability and run the connection there:
(Excuse the code, It's in MySQL format, which is almost the same as regular SQL (MSSQL))

Dim con As New OleDb.OleDbConnection
Dim con.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & OpenFileDialog1.FileName
Try
con.open
Catch ex as Exception
messagebox.show(ex.message)
End try

Try that code. Do the same with the query, but inside the Try statement, replace that with your query.

[jmcilhinney]

You said earlier that your table contained a column named "Set ID", rather than "SetID". I suggest that you change your column names so they don't include spaces but if you're going to use spaces then you need to include them. Of course, you'll need brackets again.

[rhijaen]

Nevermind, figured it out, thanks for the help.