# Visual Basic > Database Development >  [RESOLVED] SQLite - Attempt to get my CRUD functions coded correctly, specifically DELETE

## The_Hobbyist

Note: I am using VB.NET as my coding language and SQlite as my database engine.

I start by making a connection to my database file then use that file to populate a DataTable using a DataAdapter.  I then bind that DataTable to a BindingSource and use that BindingSource to populate my DataGridView.  If I understand the advice given to me by the gurus here then I believe this to be the correct method to populate my DataGridView using a database file but please correct me if I am wrong!  Below is my code for doing this:

1.  I call for the Import of the SQLite data readers & connections and then I create a database connection string which points to my database file:


```
Imports System.Data.SQLite

Public Class frmMain

    Private dbCon As String = ("Data Source=" & Application.StartupPath & "\The_Database.db;" & "Version=3;New=False;Compressed=True;")
```


2.  I create a DataAdapter and use it to populate a DataTable with a table from the database file.  I then bind that DataTable to a BindingSource.  Lastly, I create an SQLiteConnection string and an SQLiteCommand string.


```
    Private WithEvents MasterTable_Adpater As New SQLiteDataAdapter("SELECT * FROM MasterTable", dbCon)
    Private MasterTable_DataTable As New DataTable
    Private MasterTable_BindingSource As New BindingSource

    Dim SQLite_Connect As SQLite.SQLiteConnection
    Dim SQLite_Command As SQLite.SQLiteCommand
```


3.  I then use a button click event to populate the DataGridView with the DataTable using the BindingSource:


```
Private Sub btnLoad_Click(sender As Object, e As EventArgs) Handles btnLoad.Click

    MasterTable_DataTable = New DataTable   '<--- NOTE: Without this line, if the button is clicked again it will add more rows to the DataGridView.  I am unsure if this is the correct way around that?
    MasterTable_Adpater.Fill(MasterTable_DataTable)
    MasterTable_BindingSource.DataSource = MasterTable_DataTable
    Me.DGV.DataSource = MasterTable_BindingSource

End Sub
```

So far, so good?  Please, point out my errors if they are present!  I really want to get this right!!

Assuming the above is correct, I now want to create a DELETE function which allows the user to select a row by clicking on the DGV and delete that row upon a button click event.  I feel like I have successfully written code which deletes the row from the DGV however, I struggle to understand how to UPDATE the database file afterwards (or during?).  I do understand that by altering the DGV alone that the database file is not altered.  It is my belief that once I have removed the row from the DGV I must then update the DataTable which was used to populated that DGV and from there I then must update the database file using the DataTable as the datasource?  I'll start by sharing my code for removing the row from the DGV with the button click event:



```
    Private Sub btnDeleteRow_Click(sender As Object, e As EventArgs) Handles btnDeleteRow.Click

        If Me.DGV.SelectedRows.Count > 0 Then
            MasterTable_BindingSource.RemoveCurrent()
        End If

     End Sub
```

This is where I get lost...Really lost!  To start with, for example lets say that I have 8 rows loaded into the DGV and by using my DELETE function above, I delete row number 7.  The removal of row number 7 from the DGV appears successful however, the list of rows now shows in sequence of 1,2,3,4,5,6,8.  I suspect this is because the DGV needs to be updated using an updated DataTable?  This is just a guess on my part but it seems logical to me; I need help with this.  I'm unsure how to update the DataTable (if that is indeed my next step) based on the current state of the DGV.

Furthermore, at this current stage I have the DataGridView showing the data that I want my database file to contain (with the exception of the row numbering being out of sequence).  The issue now is that I am unsure how to go about altering the database file so that is matches the DGV.  It seems logical to me that I must edit the DataTable to represent the current state of the DGV and then use that DataTable to write to the database file.  Is that the case?  Do I have to write an entirely new database file or can I edit the current one to match the current DataTable?  I don't know where to start with this...

I'm trying to understand the UPDATE function because I feel this is the correct function to use when updating a database file but it confuses me.   I do have some code that writes an entirely new row of data into my database file by taking text from a handful of textboxes but this is NOT an update.  With that said, I feel like somewhere within that code is my answer or at the very least a pointer in the right direction to my answer - its just not clear to me.  In fact, its as clear as mud.  This code (shown below) obviously doesn't utilize a DataTable to define the new record being written but instead writes the new record directly to the database file straight from the textboxes.  I understand this is not the preferred method and I am trying to correct that.  A DataTable must come into play here somewhere.  I'm only showing this snippet of code because like I said, I feel like the answer to my "delete row from DGV and then UPDATE the database file to match" troubles is hovering around this code.  I could be entirely wrong!  Help. 



```
    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

        Dim strSQL As String = "INSERT INTO MasterTable (FirstName, LastName, Age) VALUES (@FirstName, @LastName, @Age)"

        SQLite_Connect = New SQLite.SQLiteConnection(dbCon)
        SQLite_Connect.Open()
        SQLite_Command = New SQLite.SQLiteCommand(strSQL, SQLite_Connect)
        SQLite_Command.Parameters.AddWithValue("@FirstName", Me.txtFirstName.Text)
        SQLite_Command.Parameters.AddWithValue("@LastName", Me.txtLastName.Text)
        SQLite_Command.Parameters.AddWithValue("@Age", Me.txtAge.Text)
        SQLite_Command.ExecuteNonQuery()
        SQLite_Command.Dispose()
        SQLite_Connect.Close()

        MasterTable_DataTable = New DataTable
        MasterTable_Adpater.Fill(MasterTable_DataTable)
        MasterTable_BindingSource.DataSource = MasterTable_DataTable
        Me.DGV.DataSource = MasterTable_BindingSource

    End Sub
```

Any guidance, sample code or insight is greatly appreciated.  Thank you.

----------


## wes4dbt

> The removal of row number 7 from the DGV appears successful however, the list of rows now shows in sequence of 1,2,3,4,5,6,8.


You removed row 7, so what is wrong.  Did you think your record Id's would change?  Of course not, you wouldn't want them to.

As for updating the database, the easiest way for a simple one table query is to use a CommandBuilder.  Or you could write the Insert/Update/Delete commands for the dataadapter yourself.

I've given you an example of using a Commandbuilder before but here is a quick example again.



```
Imports System.Data.SQLite
Public Class Form3
    Private con As New SQLiteConnection("Data Source= C:\AJunk2019\Rocks.db;Version=3")

    Private WithEvents scienceAdapter As New SQLiteDataAdapter("select Id, Name, Density, ChemistryID, MagneticYes From ScientificData", con)
    Private scienceDatatable As New DataTable
    Private scienceBindingsource As New BindingSource
    Private scienceCommandBuilder As New SQLiteCommandBuilder(scienceAdapter) With {.QuotePrefix = "[", .QuoteSuffix = "]"}


    Private Sub Form3_Load(sender As Object, e As EventArgs) Handles Me.Load

        scienceAdapter.Fill(scienceDatatable)
        scienceBindingsource.DataSource = scienceDatatable
        Me.DataGridView1.DataSource = scienceBindingsource

    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        scienceBindingsource.EndEdit()
        scienceAdapter.Update(scienceDatatable)

    End Sub

End Class
```

----------


## wes4dbt

Here is an example of creating the dataadapter update commands yourself.  This was done by jmc and uses Sql server database not Sqlite so I'm not sure retrieving the next Autonumber for Inserts is exactly the same.



```
    Dim parentInsertCommand As New SqlCommand("INSERT INTO Parent (ParentName) VALUES (@ParentName); SELECT ParentId = SCOPE_IDENTITY();", connection)
        Dim parentUpdateCommand As New SqlCommand("UPDATE Parent SET ParentName = @ParentName WHERE ParentId = @ParentId", connection)
        Dim parentDeleteCommand As New SqlCommand("DELETE Parent WHERE ParentId = @ParentId", connection)

        With parentInsertCommand.Parameters
            .Add("@ParentName", SqlDbType.VarChar, 50, "ParentName")
        End With

        With parentUpdateCommand.Parameters
            .Add("@ParentName", SqlDbType.VarChar, 50, "ParentName")
            .Add("@ParentId", SqlDbType.Int, 0, "ParentId")
        End With

        With parentDeleteCommand.Parameters
            .Add("@ParentId", SqlDbType.Int, 0, "ParentId")
        End With

        With parentAdapter
            .InsertCommand = parentInsertCommand
            .UpdateCommand = parentUpdateCommand
            .DeleteCommand = parentDeleteCommand
        End With
```

----------


## The_Hobbyist

> You removed row 7, so what is wrong.  Did you think your record Id's would change?  Of course not, you wouldn't want them to.
> 
> As for updating the database, the easiest way for a simple one table query is to use a CommandBuilder.  Or you could write the Insert/Update/Delete commands for the dataadapter yourself.
> 
> I've given you an example of using a Commandbuilder before but here is a quick example again.



My gosh!!  You are absolutely right!!  How could I possibly think that by deleting a row of data that the ID numbers would change for the remaining records?  Those ID numbers are specific to the record - DUH! That was a real brainfart on my behalf.  A little embarrasing.... Thank you...

Yes!  You absolutely did show me this command builder before however, I didn't realize that it was relevant here nor did I understand it at the time.  Three little lines of code seemed to have gotten the job done!

1.  Firstly, I declared the command builder:


```
    Private WithEvents MasterTable_Adpater As New SQLiteDataAdapter("SELECT * FROM MasterTable", dbCon)
    Private MasterTable_DataTable As New DataTable
    Private MasterTable_BindingSource As New BindingSource
    Private MasterTable_CommandBuilder As New SQLiteCommandBuilder(MasterTable_Adpater) With {.QuotePrefix = "[", .QuoteSuffix = "]"}

    Dim SQLite_Connect As SQLite.SQLiteConnection
    Dim SQLite_Command As SQLite.SQLiteCommand
```


Secondly, I added the EndEdit and the Update commands to my code:


```
    Private Sub btnDeleteRow_Click(sender As Object, e As EventArgs) Handles btnDeleteRow.Click

        If Me.DGV.SelectedRows.Count > 0 Then
            MasterTable_BindingSource.RemoveCurrent()
            MasterTable_BindingSource.EndEdit()
            MasterTable_Adpater.Update(MasterTable_DataTable)
        End If

     End Sub
```

I greatly appreciate your time and patience with me.

----------


## The_Hobbyist

Honestly, I don't truly understand what this line does.  I know it builds the command but I'm confused on what the last part of it does: 



```
Private MasterTable_CommandBuilder As New SQLiteCommandBuilder(MasterTable_Adpater) With {.QuotePrefix = "[", .QuoteSuffix = "]"}
```

What does this bit do?  It's defining something but I don't understand what exactly?


```
With {.QuotePrefix = "[", .QuoteSuffix = "]"}
```

----------


## techgnome

It's safe guarding your fields in case they have spaces in them. 
w/o it the line has a potential to look like this:


```
UPDATE my table set field one = 1 field2 = 2 where some value = True
```

That's invalid sql syntax will throw an error.
By including the prefix, it changes it to this:


```
UPDATE [my table] set [field one] = 1 [field2] = 2 where [some value] = True
```


however... that said... one shouldn't have spaces or special characters in their object names in the first place. But... in the chance you do, this is how you work around it.

-tg

----------


## The_Hobbyist

Excellent!!  Thank you!

----------


## wes4dbt

If you do want to manually create the dataadapter Update commands I found a difference you need to be aware of,



```
    Private Sub SetUpAdapter()
        Dim parentInsertCommand As New SQLiteCommand("INSERT INTO scientificdata (Name) VALUES (@Name)", con)
        Dim parentUpdateCommand As New SQLiteCommand("UPDATE scientificdata SET Name = @Name WHERE Id = @Id", con)
        Dim parentDeleteCommand As New SQLiteCommand("DELETE FROM scientificdata WHERE Id = @Id", con)

        With parentInsertCommand.Parameters
            .Add("@Name", DbType.String, 50, "Name")
        End With

        With parentUpdateCommand.Parameters
            .Add("@Name", DbType.String, 50, "Name")
            .Add("@Id", DbType.Int32, 0, "Id")
        End With

        With parentDeleteCommand.Parameters
            .Add("@Id", DbType.Int32, 0, "Id")
        End With

        With scienceAdapter
            .InsertCommand = parentInsertCommand
            .UpdateCommand = parentUpdateCommand
            .DeleteCommand = parentDeleteCommand
        End With
    End Sub
```

I couldn't get the Delete command to work and I finally found that in Sqlite you must use "FROM" in the Delete command.  "DELETE FROM scientificdata WHERE Id = @Id"

Stupid thing took me forever to figure out.  lol

----------


## The_Hobbyist

Thank you!

Question: is there a reason why one would want to create the commands manually?

----------


## wes4dbt

> Thank you!
> 
> Question: is there a reason why one would want to create the commands manually?


CommandBuilder only works on simple one table queries.  If your query had a "Join" or "Union"  then you have to create the Update commands yourself.

----------


## The_Hobbyist

Ah!  That makes sense.  Thank you again.

----------


## techgnome

> I couldn't get the Delete command to work and I finally found that in Sqlite you must use "FROM" in the Delete command.  "DELETE FROM scientificdata WHERE Id = @Id"
> 
> Stupid thing took me forever to figure out.  lol


What were you trying to use initially? I don't think I've ever NOT used a from in a delete clause...

-tg

----------


## wes4dbt

> What were you trying to use initially? I don't think I've ever NOT used a from in a delete clause...
> 
> -tg


If you look at post #3 you'll see a piece of sample code I posted from something jmc had provided.  Looks like Sql Server doesn't require the "FROM" clause.

----------

