# Visual Basic > Database Development >  CRUD - Relational Database (How to write the commands?)

## The_Hobbyist

I am working with a relational database comprised of three separate tables.  My trouble arises when trying to build the command for the Create Read Edit Delete functions.  I feel that I am able to READ the tables accurately.  I'll show how I do that and perhaps somebody might check my work and confirm this?

Before I get to my question, I'll show my layout.  My tables are structured like this (the primary key for each table is the ID field):

Table: Master
ID
ManufacturerID
ModelID
Year

1
1
1
2007

2
1
1
2006

3
2
4
2005

4
3
8
2007

5
2
5
2001

6
3
7
2010

7
2
6
2009

8
3
9
2000



Table: Maker
ID
Man

1
Cadillcac

2
Dodge

3
Ford



Table: Model
ID
Mod

1
CTS

2
DTS

3
STS

4
Avenger

5
Intrepid

6
Caravan

7
Crown Victoria

8
Escape

9
F150



Like I said, I am able to populate my datagridview with all of the relevant data which makes me think that I am READing from the tables correctly.  I do that with this code:


```
Public Class frmMain

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

    Private adMaster As New SQLiteDataAdapter("SELECT Master.ID, Maker.Man, Model.Mod, Year FROM Master FULL JOIN Maker ON Master.ManufacturerID=Maker.ID LEFT JOIN Model ON Master.ModelID=Model.ID", dbCon)

    Private dtMaster As New DataTable
    Private bsMaster As New BindingSource

    Private My_CommandBuilder As New SQLiteCommandBuilder(adMaster) With {.QuotePrefix = "[", .QuoteSuffix = "]"}

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


    Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        adMaster.Fill(dtMaster)
        bsMaster.DataSource = dtMaster
        Me.DGV.DataSource = bsMaster

        SetBindings()

    End Sub

End Class
```

I do have an issue with the databinding to a combobox but textboxes are fine.  Scrolling through the dropdown list of the combobox seems to alter the data displayed in the DGV but I'll come back to that, perhaps in another thread. I'm really focused on the CRUD at the moment and need to keep my head in that space.

With that all said, I believe the above code is correct for READing the tables and populating the datagridview.  So now I have moved onto the INSERT function.  I want to be able to create and insert an entirely new record into the relational database tables.  I feel that I know how INSERT a new record IF this were just a single table database (I'll show this below as a means to show that I really am trying to understand this stuff and not just looking for a handout).

If I were working with a single database table which had three fields (Manufacturer, Model, Year), I would INSERT a new record into the database table with the below code: 



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

        Dim SaveNewRecord As String = "INSERT INTO Master (Manufacturer, Model, Year) VALUES (@Man, @Mod, @Year)"

            SQLite_Connect = New SQLite.SQLiteConnection(dbCon)
            SQLite_Connect.Open()
            SQLite_Command = New SQLite.SQLiteCommand(SaveNewRecord, SQLite_Connect)

            SQLite_Command.Parameters.AddWithValue("@Man", Me.cmbManufacturer.Text)
            SQLite_Command.Parameters.AddWithValue("@Mod", Me.txtModel.Text)
            SQLite_Command.Parameters.AddWithValue("@Year", Me.txtYear.Text)

            SQLite_Command.ExecuteNonQuery()
            SQLite_Command.Dispose()
            SQLite_Connect.Close()

            dtMaster = New DataTable
            adMaster.Fill(dtMaster)
            bsMaster.DataSource = dtMaster
            Me.DGV.DataSource = bsMaster

End Sub
```

So to INSERT a new record into a single table, the command would look like this (from above):


```
"INSERT INTO Master (Manufacturer, Model, Year) VALUES (@Man, @Mod, @Year)"
```

Likewise, to UPDATE a record within that same single table, the command would like something like this:


```
("UPDATE Master SET Manufacturer=@NewManufacturer, Model=@NewModel, Year=@NewYear WHERE ID LIKE '" & Me.DGV.CurrentRow.Cells(0).Value & "'")
```

Now onto my question.  As explained to me in another thread, the CommandBuilder only works on simple one table queries and as a result will not work in my current situation.  I am in fact using a JOIN in my query (3 tables) and I understand that I must create my own commands.  This is where I get lost - way lost.

My JOIN is called here (as shown above):


```
("SELECT Master.ID, Maker.Man, Model.Mod, Year FROM Master FULL JOIN Maker ON Master.ManufacturerID=Maker.ID LEFT JOIN Model ON Master.ModelID=Model.ID", dbCon)
```

Since I suspect that the INSERT command is the less cumbersome command, I'll start here.  With a database structured as indicated (3 tables: Master, Maker, Model), with the Master table being the parent table and the Maker/Model tables being the lookup tables, how would I write a command to INSERT a new record, given that the ID field within each table is used as the primary key?

I don't know how to begin this, despite reading from several different sources.  I feel like the code for the entire function would look similar to the INSERT for a single table database except the INSERT command would be defined differently?  Does the INSERT command for a relational database look similar to that of a simple database?



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

        Dim SaveNewRecord As String = "INSERT INTO Master (Manufacturer, Model, Year) VALUES (@Man, @Mod, @Year)"
'<--- I FEEL THAT THE ABOVE LINE MUST CHANGE TO DEFINE THE DIFFERENT TABLES AND GIVE DIRECTION??

            SQLite_Connect = New SQLite.SQLiteConnection(dbCon)
            SQLite_Connect.Open()
            SQLite_Command = New SQLite.SQLiteCommand(SaveNewRecord, SQLite_Connect)

            SQLite_Command.Parameters.AddWithValue("@Man", Me.cmbManufacturer.Text)
            SQLite_Command.Parameters.AddWithValue("@Mod", Me.txtModel.Text)
            SQLite_Command.Parameters.AddWithValue("@Year", Me.txtYear.Text)

            SQLite_Command.ExecuteNonQuery()
            SQLite_Command.Dispose()
            SQLite_Connect.Close()

            dtMaster = New DataTable
            adMaster.Fill(dtMaster)
            bsMaster.DataSource = dtMaster
            Me.DGV.DataSource = bsMaster

End Sub
```

----------


## wes4dbt

I don't see why your using a join.  If it's just so you can show the Model name instead of the model ID then the join isn't necessary.  But you will have to setup the DGV manually.

Here is an example using the Rocks database from an early question you had, 



```
    Private WithEvents scienceAdapter As New SQLiteDataAdapter("select Id, Name, Density, ChemistryID, MagneticYes From ScientificData", con)
    Private WithEvents chemistryAdapter As New SQLiteDataAdapter("select ID, ChemicalFormula From ChemistryData", con)

    Private scienceDataTable As New DataTable
    Private chemistryDataTable As New DataTable
```

from the Form load event call SetUpDGV,



```
        scienceAdapter.Fill(data, "Science")
        chemistryAdapter.Fill(data, "Chemistry")

SetUpBinding
SetUpDGV
```



```
    Private Sub SetUpDGV()

        Dim col1 As New DataGridViewTextBoxColumn
        col1.DataPropertyName = "ID"
        col1.HeaderText = "ID"
        col1.Name = "ID"
        Me.ScienceDataGridView.Columns.Add(col1)

        Dim col2 As New DataGridViewTextBoxColumn
        col2.DataPropertyName = "Name"
        col2.HeaderText = "RockName"
        col2.Name = "Name"
        Me.ScienceDataGridView.Columns.Add(col2)

        Dim col3 As New DataGridViewTextBoxColumn
        col3.DataPropertyName = "Density"
        col3.HeaderText = "Density"
        col3.Name = "Density"
        Me.ScienceDataGridView.Columns.Add(col3)

        Dim col4 As New DataGridViewComboBoxColumn
        col4.DataPropertyName = "ChemistryId"
        col4.HeaderText = "ChemId"
        col4.Name = "ChemistryId"

        col4.DisplayMember = "chemicalformula"
        col4.ValueMember = "ID"
        col4.DataSource = chemistryDataTable

        Me.ScienceDataGridView.Columns.Add(col4)

    End Sub
```

In the SetUpbinding I added this,  



```
        Me.ScienceDataGridView.AutoGenerateColumns = False
        Me.ScienceDataGridView.DataSource = scienceBindingsource
```

You have to set the AutoGeneratecolumns property to False,  BEFORE you set the datasource.

As for how to write the CRUD commands, I've already given you an example.  If you want to write your commands go ahead.  If you have a problem you can't solve then let us know.  I can tell you one thing, you never saw "Like" in my example.

----------


## The_Hobbyist

Thank you for all that, wes4dbt.

Yes.  I thought I would have to JOIN the tables before populating the DGV as you suspected.  I see you have a way of setting the DGV up to display the desired data from multiple tables without JOINing them.  Interesting.  Maybe I have been looking in the wrong direction all along.  Mostly though, I thought that the tables needed to be joined so as to make the CRUD work?  Boy....I have much to learn!

As for your example on how to write the CRUD, yes, I appreciate that.  I don't quite understand it though.  I'll see if I can give it another go tomorrow and perhaps I'll be able to better formulate a question directly related to the CRUD coding.  In any event, I appreciate your time.

For now, I'm done for the night (been sitting here now for 12 straight hours). I'm pooped.  I'll return tomorrow and give this a more serious look over.

Cheers!

----------


## The_Hobbyist

Its the middle of the night and I can't sleep because I can't stop thinking about this darn code!

With SQLite, how are the CRUD commands coded differently for a single table database as opposed to a multi-table database?  Does there have to be a query for each table individually?  In the case of INSERT for example, is an INSERT command used for each table and then run in sequence or is the data sent to all of the tables at the same time with one INSERT command?

Despite viewing the examples, what I'm really hung up on is how does the command (seen in red below) change when working with multi-table databases?  Is it as simple as just changing the command?  I'll refer to my table examples from the original post above; how would the command differ for a 3 table database than the single table database below?


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

        SQLite_Connect = New SQLite.SQLiteConnection(dbCon)
        SQLite_Connect.Open()
        SQLite_Command = New SQLite.SQLiteCommand("INSERT INTO Master
                                                    (ManufacturerID, ModelID, Year)
                                                    VALUES (@Man, @Mod, @Year)", SQLite_Connect)

        SQLite_Command.Parameters.AddWithValue("@Man", Me.cmbManufacturer.Text)
        SQLite_Command.Parameters.AddWithValue("@Mod", Me.txtModel.Text)
        SQLite_Command.Parameters.AddWithValue("@Year", Me.txtYear.Text)

        SQLite_Command.ExecuteNonQuery()
        SQLite_Command.Dispose()
        SQLite_Connect.Close()

    End Sub
```

----------


## wes4dbt

Yes you insert into each table separately.  SQL Server will let you put multiple inserts statements in one command.  https://stackoverflow.com/questions/...tables-at-once  I'm not sure about Sqlite.

Now, having said that, I'll say maybe you can insert into multiple tables with one command.  But I don't know how, I'm not a SQL expert.

Though I'm not sure why your even worried about that.  Your example only has one table that is being updated.  The other two tables are just lookup tables and would be maintained separately. You seem to want to know how to do everything before you learn how to do one thing.  Try focusing on one thing at a time.

Have you tried the insert code you wrote?   Did it work?  If so, what's the problem?

----------

