Deleting from a dataset

[Cheetah]

Hi there,

I have a problem deleting records froma dataset. It works for some but not for all.

Could you help determine what the problem is please:

    Public Sub DeleteRecord(ByVal formObjects As List(Of Control), ByVal table As String, ByVal row As Integer, ByVal lastRow As Integer)

        dataSet.Tables(table).Rows.RemoveAt(row)
        lastRow -= 1

        If dataSet.Tables(table).Rows.Count > 0 Then

            If (row - 1) > 0 Or (row - 1) = 0 Then

                currentRow = (row - 1)
                Call FillData(formObjects, table, currentRow)

            ElseIf row < lastRow Or row = lastRow Or row = 0 Then

                currentRow = row
                Call FillData(formObjects, table, currentRow)

            End If

        Else

            Call AddNewRecord(formObjects)
            addNew = True

        End If

    End Sub

What it is supposed to do is delete the record from the dataset and either move back one record and if it can't move forward one. If there arent any more records call addnewrecord.

I do have 3 different screens for entering data into three different tables, but whenever i move screen the currentRow is always set to 0 and FillData is run with the current row as 0.

Thanks.

EDIT:

I think it only doesn't work once i have deleted one record.

 

[cjard]

If you use the bindingsource and delete the current item, then this will be easier.. it will automatically move to the next one for you.. Im not sure what happens with these datamodel changes

 

[Cheetah]

If you use the bindingsource and delete the current item, then this will be easier.. it will automatically move to the next one for you.. Im not sure what happens with these datamodel changes

I prefer to be in full control, so i prefer to do this all by code.

I have managed to delete it from the dataset but I am having problems sync'ing this with the datasource.

How would i go about doing this?

 

[cjard]

I prefer to be in full control, so i prefer to do this all by code.

Hmm, I'm always dubious of those people that claim to want to be in "full control" by micromanaging everything, to obviate microsoft's programmed behaviour - in another sense (and I dont mean to sound offensive) it's hypocrisy because anyone truly wanting to micromanage everything from the low level, would be writing in assembler, not using any APIs..

How would i go about doing this?

BindingSource.RemoveCurrent() or BindingSource.Current.Remove() i think - i dont remember exactly.. But it is "code", so youre still reasonably "in control" of it

 

[Cheetah]

Well not full control, but more control than you get in GUI type mode thingy.

I am not using binding sources and data whatits, I am doing things by code.

I have a table adapter, which fills my dataset. When i delete a record from the dataset i want it to sync back to the datasource.

Is there anyway to do this without deleting from the dataset firsth, then using a sql command to delete it from the datasource?

Thanks!

 

[cjard]

Im starting to get confused.. How can a record be deleted from a query?

Read the DW2 link in my signature - it'll tell you everything that you want to know, and it's not "losing control" because is a GUI thingy


As a compare: If you use the forms designer, rather than script all this code by hand:

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
    Inherits System.Windows.Forms.Form

    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    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.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container
        Me.BackgroundWorker1 = New System.ComponentModel.BackgroundWorker
        Me.ErrorProvider1 = New System.Windows.Forms.ErrorProvider(Me.components)
        Me.Panel1 = New System.Windows.Forms.Panel
        Me.Button1 = New System.Windows.Forms.Button
        Me.CheckBox1 = New System.Windows.Forms.CheckBox
        Me.ComboBox1 = New System.Windows.Forms.ComboBox
        Me.DateTimePicker1 = New System.Windows.Forms.DateTimePicker
        Me.TextBox1 = New System.Windows.Forms.TextBox
        CType(Me.ErrorProvider1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'ErrorProvider1
        '
        Me.ErrorProvider1.ContainerControl = Me
        '
        'Panel1
        '
        Me.Panel1.Location = New System.Drawing.Point(24, 93)
        Me.Panel1.Name = "Panel1"
        Me.Panel1.Size = New System.Drawing.Size(188, 71)
        Me.Panel1.TabIndex = 0
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(78, 62)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(86, 31)
        Me.Button1.TabIndex = 1
        Me.Button1.Text = "Button1"
        Me.Button1.UseVisualStyleBackColor = True
        '
        'CheckBox1
        '
        Me.CheckBox1.AutoSize = True
        Me.CheckBox1.Location = New System.Drawing.Point(91, 184)
        Me.CheckBox1.Name = "CheckBox1"
        Me.CheckBox1.Size = New System.Drawing.Size(81, 17)
        Me.CheckBox1.TabIndex = 2
        Me.CheckBox1.Text = "CheckBox1"
        Me.CheckBox1.UseVisualStyleBackColor = True
        '
        'ComboBox1
        '
        Me.ComboBox1.FormattingEnabled = True
        Me.ComboBox1.Location = New System.Drawing.Point(82, 230)
        Me.ComboBox1.Name = "ComboBox1"
        Me.ComboBox1.Size = New System.Drawing.Size(89, 21)
        Me.ComboBox1.TabIndex = 3
        '
        'DateTimePicker1
        '
        Me.DateTimePicker1.Location = New System.Drawing.Point(70, 215)
        Me.DateTimePicker1.Name = "DateTimePicker1"
        Me.DateTimePicker1.Size = New System.Drawing.Size(74, 20)
        Me.DateTimePicker1.TabIndex = 4
        '
        'TextBox1
        '
        Me.TextBox1.Location = New System.Drawing.Point(73, 32)
        Me.TextBox1.Name = "TextBox1"
        Me.TextBox1.Size = New System.Drawing.Size(70, 20)
        Me.TextBox1.TabIndex = 5
        '
        'Form1
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(292, 273)
        Me.Controls.Add(Me.TextBox1)
        Me.Controls.Add(Me.DateTimePicker1)
        Me.Controls.Add(Me.ComboBox1)
        Me.Controls.Add(Me.CheckBox1)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.Panel1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.ErrorProvider1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)
        Me.PerformLayout()

    End Sub
    Friend WithEvents BackgroundWorker1 As System.ComponentModel.BackgroundWorker
    Friend WithEvents ErrorProvider1 As System.Windows.Forms.ErrorProvider
    Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
    Friend WithEvents DateTimePicker1 As System.Windows.Forms.DateTimePicker
    Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox
    Friend WithEvents CheckBox1 As System.Windows.Forms.CheckBox
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents Panel1 As System.Windows.Forms.Panel

End Class

Then saying "I refuse to use the dataset designer because its a GUI thingy and I have no control of it" is something of a hypocrisy..

The designer makes many things easier, properly encapsulates your code in a good OO fashion, and writes more property accessors and low level ways of doing things in 10 seconds than the average developer could do in a week. Using the designer improves the structure of your program by putting all the data access code in one, strongly typed, low level accessible, sensibly named place very quickly.

This alone has to be a far better option than taking weeks to scatter poorly formed SQLs throughout all the project's button handlers, and not using the database in a modern, secure fashion..

 

[Cheetah]

I have edited my post for it to make more sense. At the moment i am deleting from the dataset and then manually deleting from the datasource via an sql command. Is there anyway to delete from the dataset and for that to sync back to the datasource?

I will have a look at your link...thanks.

I can understand where you are coming from with the whole full control and I can see that i cant use that as an excuse anymore, its just im set in my ways and I find it easier to do it via code as i can see everything that is happening and although there is more likely to be errors and things going wrong, i can see them and rectify them.

It also allows me to easily manipulate the data before i present it to the user.

 

[cjard]

I have edited my post for it to make more sense. At the moment i am deleting from the dataset and then manually deleting from the datasource via an sql command. Is there anyway to delete from the dataset and for that to sync back to the datasource?

Yes, you do this:

myTableAdapter.Fill(myDataTable)
'show the datatable dataon screen, edit it, delete rows, etc etc
myTableAdapter.Update(myDataTable)


.Update should be called .Persist, or .SyncDataBase or something more descriptive of the job that it does - namely update the DB to look like the datatable

I can understand where you are coming from with the whole full control and I can see that i cant use that as an excuse anymore, its just im set in my ways and I find it easier to do it via code as i can see everything that is happening and although there is more likely to be errors and things going wrong, i can see them and rectify them.

Once you get used to the "sensible" way of doing things, youll wonder why you bothered, honest!

It also allows me to easily manipulate the data before i present it to the user.

Well, nothing has changed in that regard.. DataSets are like very simplistic in-memory RDBMSs, you fill them with data from a source, which might or might not be a database, manipulate the data or let the user do it, and optionally sync it back to the source.. Its like using the windows filesystem api to read and write files rather than writing code that makes the bios change the 1s and 0s on the hard disk directly..

 

[Cheetah]

myTableAdapter.Fill(myDataTable)
'show the datatable dataon screen, edit it, delete rows, etc etc
myTableAdapter.Update(myDataTable)

Yeah that was the first thing i tried, but it doesn't seem to work i get the following error:

 

[cjard]

Ah, yes! That would be because the .Update() method requires that the TableAdapter has a valid SQL Delete command when passed a data collection with Deleted rows..

I cant really make it much more plain english than Microsoft already have but here goes:

Any adapter that is tasked with deleting things from a database, needs to know what delete query to use to do that. Specifically, the .DeleteCommand of the adapter must be set to something it can use. Usually the dataset designer takes care of this for you but you can add/change/refine the query if needs be..

An analogy could be you starting a new job, youre given a bunch of files and told "take the red ones to finance, the green to sales and the blue to the warehouse"
"But i dont know where those places are..."

 

[Cheetah]

So, I still have to control DELETE SQL statements.

Because at the moment, thats what I am doing and I am executing them like so:

        'Dim deleteCommand As New OleDb.OleDbCommand("DELETE FROM [" & tableName & "] WHERE " & sqlRows, dbConn)

        'dbConn.Open()

        'deleteCommand.ExecuteNonQuery()

        'dbConn.Close()

 

[cjard]

Er.. no. Its a bit like you pressing the cruise control button on your car, and the display saying "error: cruise control is not installed in this car"

The idea is that you use a tableadapter to push and pull database data - its a data adapter and a set of integrated sqls.. there can be hundreds of them but minimally you need to have a select, for bringing data in, insert for adding new rows, update for modifying rows and delete for deleting rows

Your tableadapter has no delete command, hence it cannot delete database rows if the related local row is marked as deleted.

This is a disconnected data model..


Just take a read of the link; it explains it all in far greater detail than i can here.. Do some of the examples, play with it, and youll understand..

 

[Cheetah]

Lol, you are confusing me with all these analogies.

So I can't use a table adapter to delete records as it cant do SQL Delete commands. It can only do select,insert and update commands.

Therefore I am guessing i will have to do it the way i am already doing it?

What link?

EDIT:

I think i see what you mean now.

Do you mean, that i need to get something to contruct the delete command, because the adapter can't do it itself?

 

[Cheetah]

Ok well this is what I got (and it works)

        dbConn.Open()

        Dim adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM [TblS]", dbConn)
        Dim deleteCommand As New OleDb.OleDbCommandBuilder(adapter)

        adapter.Update(dataSet.Tables(2))
        dataSet.AcceptChanges()

        dbConn.Close()

My question is, would it be more efficient to do it my way, which is by constructing the sql statement via concatenation and then running it (see above).

Because surely if it's selecting the whole database again, then just running my sql statement would be more efficient?

 

[cjard]

Lol, you are confusing me with all these analogies.

So I can't use a table adapter to delete records as it cant do SQL Delete commands. It can only do select,insert and update commands.

*sigh*
No.

Table Adapters can do everything. I, U, D, S

But they cant perform a delete op if you havent told it what the delete SQL is..


Can you open a can of soup without a tin opener? No.. Give the table adapter a tin opener before you ask it to open a can of soup!

Therefore I am guessing i will have to do it the way i am already doing it?

No. Read the DW2 link in my signatiure

What link?

The link that has a bold, arial black DW2: to the left of it

I think i see what you mean now.

Do you mean, that i need to get something to contruct the delete command, because the adapter can't do it itself?

Kind of. If the dataset designer can work out the priamry key of a table then it has all the info it needs to uniquely identify a row and it can form the I U D queries itself. If the select command of the adapter performs a join, or group, then the designer wont be able to work it out and youll have to add it manually.

Follw the DW2 link in my signature, and read about it. Do the simple examples; link in an access database and drag a table out of the database into the designer. Watch it write the queries for you and you will start to understand