Cant update my database.

[plenahan68]

I've made a small vb.net program to read my database and delete records
that are specific to my needs. Well, the program says it's complete but
nothing is deleted from my database. I've listed the code below.
Imports System.data.oledb
Imports System.Data

Public Class Form1
Inherits System.Windows.Forms.Form
Public Enum etif
efilename = 0
efilepath = 1
einvoice = 2
ejob = 3
eorder = 4

End Enum

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As
Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form
Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents OleDbDataAdapter1 As
System.Data.OleDb.OleDbDataAdapter
Friend WithEvents OleDbSelectCommand1 As
System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbInsertCommand1 As
System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbUpdateCommand1 As
System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbDeleteCommand1 As
System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbConnection1 As
System.Data.OleDb.OleDbConnection
Friend WithEvents DataSet11 As delete_tiff_records.DataSet1
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.Button1 = New System.Windows.Forms.Button
Me.OleDbDataAdapter1 = New System.Data.OleDb.OleDbDataAdapter
Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection
Me.DataSet11 = New delete_tiff_records.DataSet1
CType(Me.DataSet11,
System.ComponentModel.ISupportInitialize).BeginIni t()
Me.SuspendLayout()
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(40, 32)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(136, 144)
Me.Button1.TabIndex = 0
Me.Button1.Text = "Button1"
'
'OleDbDataAdapter1
'
Me.OleDbDataAdapter1.DeleteCommand = Me.OleDbDeleteCommand1
Me.OleDbDataAdapter1.InsertCommand = Me.OleDbInsertCommand1
Me.OleDbDataAdapter1.SelectCommand = Me.OleDbSelectCommand1
Me.OleDbDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "tiff", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("filename", "filename"), New
System.Data.Common.DataColumnMapping("filepath", "filepath"), New
System.Data.Common.DataColumnMapping("invoice", "invoice"), New
System.Data.Common.DataColumnMapping("job", "job"), New
System.Data.Common.DataColumnMapping("order", "order")})})
Me.OleDbDataAdapter1.UpdateCommand = Me.OleDbUpdateCommand1
'
'OleDbSelectCommand1
'
Me.OleDbSelectCommand1.CommandText = "SELECT filename,
filepath, invoice, job, [order] FROM tiff"
Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1
'
'OleDbInsertCommand1
'
Me.OleDbInsertCommand1.CommandText = "INSERT INTO
tiff(filename, filepath, invoice, job, [order]) VALUES (?, ?, ?, ?, ?"
& _
")"
Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("filename",
System.Data.OleDb.OleDbType.VarWChar, 50, "filename"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("filepath",
System.Data.OleDb.OleDbType.VarWChar, 50, "filepath"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("invoice",
System.Data.OleDb.OleDbType.Integer, 0, "invoice"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("job",
System.Data.OleDb.OleDbType.VarWChar, 50, "job"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("order",
System.Data.OleDb.OleDbType.Integer, 0, "order"))
'
'OleDbUpdateCommand1
'
Me.OleDbUpdateCommand1.CommandText = "UPDATE tiff SET filename
= ?, filepath = ?, invoice = ?, job = ?, [order] = ? WHE" & _
"RE (filename = ?) AND (filepath = ? OR ? IS NULL AND filepath
IS NULL) AND (invo" & _
"ice = ? OR ? IS NULL AND invoice IS NULL) AND (job = ? OR ? IS
NULL AND job IS N" & _
"ULL) AND ([order] = ? OR ? IS NULL AND [order] IS NULL)"
Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("filename",
System.Data.OleDb.OleDbType.VarWChar, 50, "filename"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("filepath",
System.Data.OleDb.OleDbType.VarWChar, 50, "filepath"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("invoice",
System.Data.OleDb.OleDbType.Integer, 0, "invoice"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("job",
System.Data.OleDb.OleDbType.VarWChar, 50, "job"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("order",
System.Data.OleDb.OleDbType.Integer, 0, "order"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_filenam e",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "filename", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_filepat h",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "filepath", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_filepat h1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "filepath", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_invoice ",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "invoice", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_invoice 1",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "invoice", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_job",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "job", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_job1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "job", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_order",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "order", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_order1" ,
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "order", System.Data.DataRowVersion.Original, Nothing))
'
'OleDbDeleteCommand1
'
Me.OleDbDeleteCommand1.CommandText = "DELETE FROM tiff WHERE
(filename = ?) AND (filepath = ? OR ? IS NULL AND filepath" & _
" IS NULL) AND (invoice = ? OR ? IS NULL AND invoice IS NULL)
AND (job = ? OR ? I" & _
"S NULL AND job IS NULL) AND ([order] = ? OR ? IS NULL AND
[order] IS NULL)"
Me.OleDbDeleteCommand1.Connection = Me.OleDbConnection1
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_filenam e",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "filename", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_filepat h",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "filepath", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_filepat h1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "filepath", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_invoice ",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "invoice", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_invoice 1",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "invoice", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_job",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "job", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_job1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "job", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_order",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "order", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_order1" ,
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "order", System.Data.DataRowVersion.Original, Nothing))
'
'OleDbConnection1
'
Me.OleDbConnection1.ConnectionString = "Jet OLEDB:Global
Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Data Source=""T:\SCANNING.MDB"";Jet OLEDB:Engine
Type=5;Provider=""Mi" & _
"crosoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet
OLEDB:SFP=False;persist se" & _
"curity info=False;Extended Properties=;Mode=Share Deny
None;Jet OLEDB:Encrypt Da" & _
"tabase=False;Jet OLEDB:Create System Database=False;Jet
OLEDB:Don't Copy Locale " & _
"on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;User ID=Admin;Je" & _
"t OLEDB:Global Bulk Transactions=1"
'
'DataSet11
'
Me.DataSet11.DataSetName = "DataSet1"
Me.DataSet11.Locale = New
System.Globalization.CultureInfo("en-US")
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 273)
Me.Controls.Add(Me.Button1)
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.DataSet11,
System.ComponentModel.ISupportInitialize).EndInit( )
Me.ResumeLayout(False)

End Sub

#End Region

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim x As Long
Dim search As String
Dim dr As DataRow
OleDbDataAdapter1.Fill(DataSet11)
For x = 181363 To 181748
search = "ANLIN-" & x & ".TIF"
For Each dr In DataSet11.tiff
If dr.Item(etif.efilename) = search Then
DataSet11.tiff.Rows.Remove(dr)
Exit For
End If
Next
Next
OleDbDataAdapter1.Update(DataSet11, "tiff")
MessageBox.Show("Done")
End Sub
End Class

[AlanM]

[e-mail] wrote:

I've made a small vb.net program to read my database and delete records that are specific to my needs. Well, the program says it's complete but nothing is deleted from my database. I've listed the code below.
<snipped>
For x = 181363 To 181748
search = "ANLIN-" & x & ".TIF"
For Each dr In DataSet11.tiff
If dr.Item(etif.efilename) = search Then
DataSet11.tiff.Rows.Remove(dr)
Exit For
End If
Next
Next
OleDbDataAdapter1.Update(DataSet11, "tiff")
MessageBox.Show("Done")
End Sub
End Class

Change this line: DataSet11.tiff.Rows.Remove(dr)

to this instead:
dr.Delete()

This will not remove the records from the DataSet, but it will remove
them from the database when you call OleDbDataAdapter1.Update().

If you want to subsequently remove the rows from the dataset, call
DataSet11.AcceptChanges() immediately after calling the Update()
method.

HTH,
->Alan

[AlanM]

Oops. You do not have to call AcceptChanges.

from MSDN: IDataAdapter.Update() method:
http://msdn.microsoft.com/library/de...asp?frame=true

When using Update, the order of execution is as follows:

1. The values in the DataRow are moved to the parameter values.
2. The OnRowUpdating event is raised.
3. The command executes.
4. If the command is set to FirstReturnedRecord, then the first
returned result is placed in the DataRow.
5. If there are output parameters, they are placed in the DataRow.
6. The OnRowUpdated event is raised.
7. AcceptChanges is called.

->AlanM

[plenahan68]

Now it says
An unhandled exception of type
'System.Data.DeletedRowInaccessibleException' occurred in
system.data.dll

Additional information: Deleted row information cannot be accessed
through the row.

[AlanM]

ok, I didn't look at the logic thoroughly.

When you've deleted a row, it's (generally speaking) inaccessible to
you for general usage (as indicated by your exception.

Here's the quick n dirty way to avoid that problem. This is not
terribly optimal, but I'm guessing you just want something that works:

For x = 181363 To 181748
search = "ANLIN-" & x & ".TIF"
For Each dr In DataSet11.tiff
If dr.RowState <> DataRowState.Deleted Then
If dr.Item(etif.efilename) = search Then
DataSet11.tiff.Rows.Remove(dr)
Exit For
End If
End If
Next
Next

I spent a lot of time reading and re-reading all the documentation
inside the System.Data namespace to get to understand this, but I think
Scep's book might be more helpful:
http://www.microsoft.com/mspress/books/5354.asp

->AlanM

[Cor Ligthert]

Plenham,

You told you deleted datarows however you are removing those.

The difference is that a datarow.delete set the rowstate to "Deleted" with
records which are originaly readed by the dataadapter.fill. Created records
after that will with a datarow.delete be removed when there has not been an
acceptchanges or an update before that time.

When the update for that row is done (and you take the original
dataset,table or row), than all the rows will be removed from the dataset by
the in the update integrated acceptchanges.

http://msdn.microsoft.com/library/de...eleteTopic.asp

Please sent next time only the relative part of your code in your message.
The by the designer created code do most of us know very well.

I hope this helps?

Cor