System data sqlite sqliteexception unknown error insufficient parameters supplied to the command

• 
• VBForums
• Visual Basic
• Database Development
• [RESOLVED] SQLite — CRUD

1. Jan 17th, 2023, 11:11 AM

   
   

   #1

   

   Thread Starter

   
   Addicted Member
   

   

   ---

   [RESOLVED] SQLite — CRUD
   

   Working with SQLite, sorting out my CRUD functions and data bindings has proven very challenging for me and no doubt frustrating for those offering their guidance. With plenty of help, direction and sample code from the gurus here, I have a project that is coming along. With that said, my project still isn’t adding a new record to the database nor is it editing existing records as it should. My main objective at this point is to sort that out. In an effort to be clear, I will show much of my code here in the first post and as it evolves, I’ll re-share it in the following posts.

   First, I’ll show my database tables along with a few records, just so we’re all on the same page (I’m a visual guy):

   This is the Parent table and is called Gemstones

   ID Primary Key	Name	HardA	HardB	DensA	DensB	Formula	CrystallographyID Foreign Key
   1	Acanthite	2.0	2.5			Ag2S	1
   2	Achroite			2.90	3.40	A(D3)G6(T6O18)(BO3)3X3Z	2
   3	Actinolite	5.5	6.0	3.03	3.24	Ca2(Mg,Fe)5Si8O22(OH)2	1
   4	Adamite	3.5	3.5	4.32	4.38	Zn2(AsO4)(OH)	3
   5	Adamite (Cuprian)	3.5	3.5	4.32	4.38	(Zn,Cu)2AsO4OH	4
   6	Adularia	6.0	6.5	2.55	2.63	KAlSi3O8	1
   7	Aegirine	6.0	6.0	3.50	3.60	NaFe3+(Si2O6)	1

   This is a Child table and is called Crystallography

   CrystalID Primary Key	CrystalStructure
   1	Monoclinic � Prismatic
   2	Trigonal
   3	Orthorhombic � Dipyramidal
   4	Orthorhombic
   5	Triclinic � Pinacoidal

   I’ve removed some of the non-related code but this is the general idea of what I have going on in the coding department:

   Code:

   Imports System.Data.SQLite
   
   Public Class frmMain
   
       Private dbCon As New SQLiteConnection("Data Source=" & Application.StartupPath & "Database.db;" & "Version=3;New=False;Compressed=True;")
   
       Dim Adapter1 As String = ("SELECT * FROM Gemstones ORDER BY Name ASC")
       Dim Adapter2 As String = ("SELECT * FROM Crystallography ORDER BY CrystalStructure ASC")
   
       Private adGemstones As New SQLiteDataAdapter(Adapter1, dbCon)
       Private adCrystallography As New SQLiteDataAdapter(Adapter2, dbCon)
   
       Private dtGemstones As New DataTable
       Private bsGemstones As New BindingSource
   
       Private dtCrystallography As New DataTable
       Private bsCrystallography As New BindingSource
   
       Dim SQLite_Connect As SQLite.SQLiteConnection
       Dim SQLite_Command As SQLite.SQLiteCommand
   
       'Private My_CommandBuilder As New SQLiteCommandBuilder(adGemstones) With {.QuotePrefix = "[", .QuoteSuffix = "]"}
   
   
   
       Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
   
           Me.DGV.SelectionMode = DataGridViewSelectionMode.FullRowSelect
   
           'dbCon.Open()
   
           SetUpAdapter()
   
           adGemstones.Fill(dtGemstones)
           adCrystallography.Fill(dtCrystallography)
           bsGemstones.DataSource = dtGemstones
           Me.DGV.DataSource = bsGemstones
   
           SetUpBindings()
   
       End Sub
   
   
   
       Private Sub SetUpAdapter()
           Dim parentInsertCommand As New SQLiteCommand("INSERT INTO Gemstones (Name, HardA, HardB, DensA, DensB, Formula) VALUES (@Name, @HardA, @HardB, @DensA, @DensB, @Formula)", dbCon)
           Dim parentUpdateCommand As New SQLiteCommand("UPDATE Gemstones SET Name = @Name, CrystallographyID = @CrystallographyID WHERE Id = @Id", dbCon)
           Dim parentDeleteCommand As New SQLiteCommand("DELETE FROM Gemstones WHERE Id = @Id", dbCon)
   
           With parentInsertCommand.Parameters
               .Add("@Name", DbType.String, 50, "Name")
               .Add("@HardA", DbType.String, 50, "HardA")
               .Add("@HardB", DbType.String, 50, "HardB")
               .Add("@DensA", DbType.String, 50, "DensA")
               .Add("@DensB", DbType.String, 50, "DensB")
               .Add("@Formula", DbType.String, 50, "Formula")
               .Add("@CrystallagraphyID", DbType.Int32, 0, "CrystallographyID")
           End With
   
           With parentUpdateCommand.Parameters
               .Add("@Name", DbType.String, 50, "Name")
               .Add("@HardA", DbType.String, 50, "HardA")
               .Add("@HardB", DbType.String, 50, "HardB")
               .Add("@DensA", DbType.String, 50, "DensA")
               .Add("@DensB", DbType.String, 50, "DensB")
               .Add("@Formula", DbType.String, 50, "Formula")
               .Add("@CrystallagraphyID", DbType.Int32, 0, "CrystallographyID")
               .Add("@Id", DbType.Int32, 0, "Id")
           End With
   
           With parentDeleteCommand.Parameters
               .Add("@Id", DbType.Int32, 0, "Id")
           End With
   
           With adGemstones
               .InsertCommand = parentInsertCommand
               .UpdateCommand = parentUpdateCommand
               .DeleteCommand = parentDeleteCommand
           End With
   
       End Sub
   
   
   
       Private Sub SetUpBindings()
   
           bsGemstones.DataSource = dtGemstones
   
           Me.lblID.DataBindings.Add("Text", bsGemstones, "ID")
           Me.txtName.DataBindings.Add("Text", bsGemstones, "Name")
           Me.txtHardnessA.DataBindings.Add("Text", bsGemstones, "HardA")
           Me.txtHardnessB.DataBindings.Add("Text", bsGemstones, "HardB")
           Me.txtDensityA.DataBindings.Add("Text", bsGemstones, "DensA")
           Me.txtDensityB.DataBindings.Add("Text", bsGemstones, "DensB")
           Me.txtFormula.DataBindings.Add("Text", bsGemstones, "Formula")
   
           Me.cmbCrystalSystem.DataBindings.Add("Selectedvalue", bsGemstones, "CrystallographyID")
           Me.cmbCrystalSystem.DisplayMember = "CrystalStructure"
           Me.cmbCrystalSystem.ValueMember = "CrystalID"
           Me.cmbCrystalSystem.SelectedValue = "CrystalID"
           Me.cmbCrystalSystem.DataSource = dtCrystallography
   
           Me.DGV.DataSource = bsGemstones
   
       End Sub
   
   
   
       Private Sub btnSaveNew_Click(sender As Object, e As EventArgs) Handles btnSaveNew.Click
   
           Dim NewRecordName As String = Me.txtName.Text
   
           If MessageBox.Show(String.Format("Do you want to save a new record for " & NewRecordName & "?"), "Confirmation", MessageBoxButtons.YesNo) = DialogResult.Yes Then
   
               bsGemstones.EndEdit()
               adGemstones.Update(dtGemstones)
   
               dtGemstones = New DataTable
               adGemstones.Fill(dtGemstones)
               bsGemstones.DataSource = dtGemstones
               Me.DGV.DataSource = bsGemstones
   
               MessageBox.Show("The new record has been saved successfully")
   
           Else
   
               MessageBox.Show("The new record creation has been canceled")
   
           End If
   
       End Sub
   
   
   
       Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
   
           Dim SelectedRow As DataGridViewRow
           SelectedRow = DGV.CurrentRow
           Dim ID As Integer = SelectedRow.Cells(0).Value
   
           If MessageBox.Show(String.Format("Do you want to save the edits to {0}", SelectedRow.Cells("Name").Value), "Confirmation", MessageBoxButtons.YesNo) = DialogResult.Yes Then
   
               bsGemstones.EndEdit()
               adGemstones.Update(dtGemstones)
   
               dtGemstones = New DataTable
               adGemstones.Fill(dtGemstones)
               bsGemstones.DataSource = dtGemstones
               Me.DGV.DataSource = bsGemstones
   
               MessageBox.Show("The edit was successful")
   
           Else
   
               MessageBox.Show("The edit has been canceled")
   
           End If
   
       End Sub
   
   
   
       Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
   
           Dim SelectedRow As DataGridViewRow
           SelectedRow = DGV.CurrentRow
           Dim ID As Integer = SelectedRow.Cells(0).Value
   
           If MessageBox.Show(String.Format("Do you want to delete {0}", SelectedRow.Cells("Name").Value), "Confirmation", MessageBoxButtons.YesNo) = DialogResult.Yes Then
   
               If Me.DGV.SelectedRows.Count > 0 Then
                   bsGemstones.RemoveCurrent()
                   bsGemstones.EndEdit()
                   adGemstones.Update(dtGemstones)
   
                   MessageBox.Show("Delete was successful")
   
               End If
   
           Else
   
               MessageBox.Show("Delete was canceled")
   
           End If
   
       End Sub
   
   
       Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
           Me.Close()
       End Sub
   
   
   End Class

   With that out of the way I want to address my questions and issues with the code I’ve shared. They are as follows:

   • The Command Builder line:

     Private My_CommandBuilder As New SQLiteCommandBuilder(adGemstones) With {.QuotePrefix = «[«, .QuoteSuffix = «]»}

     I had this line in an earlier rendition of my project. It appears to be a leftover but I am hesitant to remove it. I realize that it is building a command but the application doesn’t seem to need it in order to run. As a result I have block deleted this line of code out of my project for the time being.

     Goal: to understand what this line does and why I need it (if I do).

   • The Opening of the database call:

     dbCon.Open()

     This line doesn’t seem to effect the application. It came to my in some sample code and when I was trying to deconstruct that code so as to better understand it, I was stumped as to why this line was there. So I have block deleted the line out of my code and the application appears to run as it should? I understand that it is calling to open the database but I don’t understand why it would do so at this point in the code.

     Goal: to understand why this line should be there (if it should be there).

   • The «Save New» function doesn’t work:

     When trying to write code that would create a new record and save it to the database, some sample code was written for me in an effort to put me on the right track. Two simple lines were put into a button click event, those two lines were:

     bsGemstones.EndEdit()
     adGemstones.Update(dtGemstones)

     This does not appear to save a new record to the database. I did have some luck with this code when the database was just a single table structure however, it worked more like an EDIT function. What I mean by that is that the button click event would alter whichever line was highlighted in the DGV and then write that alteration to the database file. It didn’t seem to create a new record then nor does it now. In fact, now that I have moved to a two table database, the second line (update) throws an error. That error is:

     System.Data.SQLite.SQLiteException: ‘unknown error
     Insufficient parameters supplied to the command’

     Goal: To be able to create a new record and write it to the database file with the button click event.

   • The «Edit» function doesn’t work:

     As stated above, when some sample code came my way for saving a new record, that code acted more like an edit record function. So, I was using it in a button click event to edit records in my database. It was simple, highlight a line in the DGV, enter the new data into the corresponding text/combo boxes and click the button. When I was working with a single table database, this code appeared to worked. Now that I am working with a two table database, this code throws an error. Since this code is exactly the same as the «Save New» function above, it throws the same error as listed above.

     Goal: To be able to highlight a DGV line, enter alternate data into the data bound form objects (text/combo boxes) and re-write that record with a button click event, using the new data from the text/combo boxes.

   • The DGV population:

     With the above code, my DGV shows the «CrystalographyID» field from the parent table («Gemstones») table however, I can’t seem to find a way for the DGV to also display the «CrystalStructure» field from the child table (Crystalography) at the same time. I’m nearly certain this was shown to me at some point but I can’t seem to find that example now.

     Goal: to have the DGV display fields from both the parent and child tables simultaneously. This is not a main objective at this time but is more of a curiosity. Frankly, I haven’t truly decided whether the grid will display the mentioned data in the finished application or not. For now, this is merely a curiosity as previously stated.

   ---

2. Jan 17th, 2023, 12:37 PM

   
   

   #2

   Re: SQLite — CRUD
   

   are you sure that these values …

   Code:

     .Add("@HardA", DbType.String, 50, "HardA")
               .Add("@HardB", DbType.String, 50, "HardB")
               .Add("@DensA", DbType.String, 50, "DensA")
               .Add("@DensB", DbType.String, 50, "DensB")

   are strings ?

   in your visual Table they look like double or decimal values. also I would use a Treeview to display the Data not a Datagridview
   but that is just my opinion

   to hunt a species to extinction is not logical !
   since 2010 the number of Tigers are rising again in 2016 — 3900 were counted. with Baby Callas it’s 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

   ---

3. Jan 17th, 2023, 01:16 PM

   
   

   #3

   

   Thread Starter

   
   Addicted Member
   

   

   ---

   Re: SQLite — CRUD
   

   Originally Posted by ChrisE
   

   are you sure that these values …

   Code:

     .Add("@HardA", DbType.String, 50, "HardA")
               .Add("@HardB", DbType.String, 50, "HardB")
               .Add("@DensA", DbType.String, 50, "DensA")
               .Add("@DensB", DbType.String, 50, "DensB")

   are strings ?

   in your visual Table they look like double or decimal values.

   Noted and corrected to specify decimal as shown below:

   Code:

           With parentInsertCommand.Parameters
               .Add("@Name", DbType.String, 50, "Name")
               .Add("@HardA", DbType.Decimal, 10, "HardA")
               .Add("@HardB", DbType.Decimal, 10, "HardB")
               .Add("@DensA", DbType.Decimal, 10, "DensA")
               .Add("@DensB", DbType.Decimal, 10, "DensB")
               .Add("@Formula", DbType.String, 50, "Formula")
               .Add("@CrystallagraphyID", DbType.Int32, 0, "CrystallographyID")
           End With
   
           With parentUpdateCommand.Parameters
               .Add("@Name", DbType.String, 50, "Name")
               .Add("@HardA", DbType.Decimal, 10, "HardA")
               .Add("@HardB", DbType.Decimal, 10, "HardB")
               .Add("@DensA", DbType.Decimal, 10, "DensA")
               .Add("@DensB", DbType.Decimal, 10, "DensB")
               .Add("@Formula", DbType.String, 50, "Formula")
               .Add("@CrystallagraphyID", DbType.Int32, 0, "CrystallographyID")
               .Add("@Id", DbType.Int32, 0, "Id")
           End With

   Thank you.

   ---

4. Jan 17th, 2023, 01:59 PM

   
   

   #4

   Re: SQLite — CRUD
   

   Where to start, lol

   The dbCon.Open isn’t necessary if your only using SQLiteDataadapters. The dataadapter will open the connection for you. But if you use a SQLiteCommand you have to open the connection yourself.

   You only need one button to save any changes whether it’s Add/Edit/Delete or several of each.

   Code:

       Private Sub btnSaveNew_Click(sender As Object, e As EventArgs) Handles btnSaveNew.Click
   
              If MessageBox.Show(String.Format("Do you want to save a new record for " & NewRecordName & "?"), "Confirmation", MessageBoxButtons.YesNo) = DialogResult.Yes Then
   
               bsGemstones.EndEdit()
               adGemstones.Update(dtGemstones)
   
               
               MessageBox.Show("The new record has been saved successfully")
   
           Else
   
               MessageBox.Show("The new record creation has been canceled")
   
           End If
   
       End Sub

   That’s all you need.

   ---

5. Jan 17th, 2023, 02:19 PM

   
   

   #5

   Re: SQLite — CRUD
   

   Next,

   Edit/Update error tells you exactly what’s wrong.

   System.Data.SQLite.SQLiteException: ‘unknown error
   Insufficient parameters supplied to the command’

   Thought actually it looks like your using more parameters than fields in the command statements. The point is the number of fields versus the number of parameters most be correct.

   Your Update command is only updating two fields. Your Insert command doesn’t include the «CrystallagraphyID» field.

   Yes we have been over how to store an ID but display the description.

   I’ll repost this example, pay attention to how the FormulaComboBox is setup in SetUpBinding.

   Code:

   Imports System.Data.SQLite
   
   Public Class Form5
       Private con As New SQLiteConnection("Data Source= C:AJunk2019Rocks.db;Version=3")
       Private WithEvents scienceAdapter As New SQLiteDataAdapter("select Id, Name, ChemistryID 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
   
       Private WithEvents scienceBindingsource As New BindingSource
   
       Private Sub Form5_Load(sender As Object, e As EventArgs) Handles Me.Load
   
           con.Open()
   
           SetUpAdapter()
   
           scienceAdapter.Fill(scienceDataTable)
           chemistryAdapter.Fill(chemistryDataTable)
   
           SetUpBindings()
       End Sub
   
       Private Sub SetUpAdapter()
           Dim parentInsertCommand As New SQLiteCommand("INSERT INTO scientificdata (Name,ChemistryId) VALUES (@Name, @ChemistryId)", con)
           Dim parentUpdateCommand As New SQLiteCommand("UPDATE scientificdata SET Name = @Name, chemistryId = @ChemistryId 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")
               .Add("@ChemistryId", DbType.Int32, 0, "ChemistryId")
           End With
   
           With parentUpdateCommand.Parameters
               .Add("@Name", DbType.String, 50, "Name")
               .Add("@ChemistryId", DbType.Int32, 0, "ChemistryId")
               .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
   
       Private Sub SetUpBindings()
   
           scienceBindingsource.DataSource = scienceDataTable
           Me.BindingNavigator1.BindingSource = scienceBindingsource
   
           Me.IdTextBox.DataBindings.Add("Text", scienceBindingsource, "ID")
           Me.NameTextBox.DataBindings.Add("Text", scienceBindingsource, "Name")
   
   
           Me.FormulaComboBox.DataBindings.Add("selectedvalue", scienceBindingsource, "chemistryid")   ' Notice I use the combobox "selectedvalue" property NOT the Text property
   
           Me.FormulaComboBox.DisplayMember = "Chemicalformula"
           Me.FormulaComboBox.ValueMember = "ID"
           Me.FormulaComboBox.SelectedValue = "ID"
           Me.FormulaComboBox.DataSource = chemistryDataTable
   
           Me.scienceDataGridView.DataSource = scienceBindingsource
   
   
       End Sub
   
       Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
           scienceBindingsource.EndEdit()
           scienceAdapter.Update(scienceDataTable)
       End Sub
   End Clas

   This example just uses a standard combobox, not a DatagridviewCombobox. You keep switching between updating the data from the grid and updating using Textboxes/combobox. Though I did also show you how to setup both kinds of Comboboxes. I’ll if I can find the dgv example.

   Edit: Look at post #2 here https://www.vbforums.com/showthread….97#post5591897

   Last edited by wes4dbt; Jan 17th, 2023 at 02:59 PM.

   ---

6. Jan 17th, 2023, 02:55 PM

   
   

   #6

   Re: SQLite — CRUD
   

   The Command Builder line:

   Private My_CommandBuilder As New SQLiteCommandBuilder(adGemstones) With {.QuotePrefix = «[«, .QuoteSuffix = «]»}

   I had this line in an earlier rendition of my project. It appears to be a leftover but I am hesitant to remove it. I realize that it is building a command but the application doesn’t seem to need it in order to run. As a result I have block deleted this line of code out of my project for the time being.

   Goal: to understand what this line does and why I need it (if I do).

   If you use a Commandbuilder then you don’t need to manually setup the dataadapter Insert/Update/Delete commands, it will do that for you.

   You wouldn’t need this,

   Code:

       Private Sub SetUpAdapter()
           Dim parentInsertCommand As New SQLiteCommand("INSERT INTO Gemstones (Name, HardA, HardB, DensA, DensB, Formula) VALUES (@Name, @HardA, @HardB, @DensA, @DensB, @Formula)", dbCon)
           Dim parentUpdateCommand As New SQLiteCommand("UPDATE Gemstones SET Name = @Name, CrystallographyID = @CrystallographyID WHERE Id = @Id", dbCon)
           Dim parentDeleteCommand As New SQLiteCommand("DELETE FROM Gemstones WHERE Id = @Id", dbCon)
   
           With parentInsertCommand.Parameters
               .Add("@Name", DbType.String, 50, "Name")
               .Add("@HardA", DbType.String, 50, "HardA")
               .Add("@HardB", DbType.String, 50, "HardB")
               .Add("@DensA", DbType.String, 50, "DensA")
               .Add("@DensB", DbType.String, 50, "DensB")
               .Add("@Formula", DbType.String, 50, "Formula")
               .Add("@CrystallagraphyID", DbType.Int32, 0, "CrystallographyID")
           End With
   
           With parentUpdateCommand.Parameters
               .Add("@Name", DbType.String, 50, "Name")
               .Add("@HardA", DbType.String, 50, "HardA")
               .Add("@HardB", DbType.String, 50, "HardB")
               .Add("@DensA", DbType.String, 50, "DensA")
               .Add("@DensB", DbType.String, 50, "DensB")
               .Add("@Formula", DbType.String, 50, "Formula")
               .Add("@CrystallagraphyID", DbType.Int32, 0, "CrystallographyID")
               .Add("@Id", DbType.Int32, 0, "Id")
           End With
   
           With parentDeleteCommand.Parameters
               .Add("@Id", DbType.Int32, 0, "Id")
           End With
   
           With adGemstones
               .InsertCommand = parentInsertCommand
               .UpdateCommand = parentUpdateCommand
               .DeleteCommand = parentDeleteCommand
           End With
   
       End Sub

   It’s a good thing to learn to write your own commands, the commandbuilder has it’s limits. Not sure this is something you need to be tackling right now. You could always come back later after you have the system functioning and then write the commands manually. But your are very close to having the command setup correctly. It’s your choice.

   ---

7. Jan 17th, 2023, 03:01 PM

   
   

   #7

   Re: SQLite — CRUD
   

   BTW — Please start asking specific questions, not 10 at a time. It’ll make it easier to answer and easier for you to understand the reply.

   ---

8. Jan 17th, 2023, 03:12 PM

   
   

   #8

   

   Thread Starter

   
   Addicted Member
   

   

   ---

   Re: SQLite — CRUD
   

   Originally Posted by wes4dbt
   

   BTW — Please start asking specific questions, not 10 at a time. It’ll make it easier to answer and easier for you to understand the reply.

   Absolutely! Understood. I wanted to start this thread off in such a way as to outline everything I have going on, just in case they are connected and I didn’t realize it. I’m trying to get better at being less «all over the place». Moving forward, focus is the key.

   Thanks for your time today. I’m off to my day job now and will look at the above posts later tonight. I’m sure I’ll have questions — I’ll try to limit my posts to one specific question at a time as instructed.

   For the record, I didn’t even realize until after reading your post above just now that there was a difference between updating from the grid or the text/combo boxes. I’ll have to re-read this when I get off work. Furthermore, I was under the assumption that I would need different code for each of the «Save New», «Edit» or «Delete». I obviously don’t understand how to properly utilize the code:

   Code:

       Private Sub btnSaveNew_Click(sender As Object, e As EventArgs) Handles btnSaveNew.Click
   
              If MessageBox.Show(String.Format("Do you want to save a new record for " & NewRecordName & "?"), "Confirmation", MessageBoxButtons.YesNo) = DialogResult.Yes Then
   
               bsGemstones.EndEdit()
               adGemstones.Update(dtGemstones)
   
               
               MessageBox.Show("The new record has been saved successfully")
   
           Else
   
               MessageBox.Show("The new record creation has been canceled")
   
           End If
   
       End Sub

   I’m sure I’ll have questions. I just wanted to acknowledge your help and thank you for it. As I said, I’ll be back later. Thank you again!

   ---

9. Jan 17th, 2023, 03:40 PM

   
   

   #9

   Re: SQLite — CRUD
   

   For the record, I didn’t even realize until after reading your post above just now that there was a difference between updating from the grid or the text/combo boxes. I’ll have to re-read this when I get off work. Furthermore, I was under the assumption that I would need different code for each of the «Save New», «Edit» or «Delete». I obviously don’t understand how to properly utilize the code:

   You misunderstand. The DataAdapter doesn’t care if the data is bound to a dgv or Textboxes. It all has to do with the DataTable. Each datatable row of data (or record) has a RowState property. When you call the «Update» method on the dataadapter it goes through each row in the datatable and checks the RowState. If the rowstate is Unxhanged then it doesn’t do anything to that record, if the rowstate is «Added» then it adds the row to the database table, if the Rowstate is «Deleted» then it deletes that row, if the rowstate is «Modified» then it updates the row. When you modify the data in a bound control, the underlying datatable is also modified.

   So, the DataAdapter doesn’t care what was used the modify the datatable.

   The difference I was talking about is what’s necessary to setup the combobox properly so you get the data you want.

   Last edited by wes4dbt; Jan 17th, 2023 at 03:55 PM.

   ---

10. Jan 17th, 2023, 09:33 PM

    
    

    #10

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    Originally Posted by wes4dbt
    

    …it looks like your using more parameters than fields in the command statements. The point is the number of fields versus the number of parameters most be correct.

    I think I understand… While defining the commands, I failed to include all of the fields that were set out as parameters. I believe that I have this sorted out now. The code below now has all of my columns from the parent table listed in the command and the parameters however, I’m still getting the same error (System.Data.SQLite.SQLiteException: unknown error Insufficient parameters supplied to the command) on the same line: adGemstones.Update(dtGemstones)

    Currently my code for the INSERT command looks like this (I’m focused on the INSERT right now, one thing at a time):

    Code:

         Private Sub SetUpAdapter()
    
            Dim parentInsertCommand As New SQLiteCommand("INSERT INTO Gemstones (ID, Name, HardA, HardB, DensA, DensB, Formula, CrystallographyID) VALUES (@ID, @Name, @HardA, @HardB, @DensA, @DensB, @Formula, @CrystallographyID)", dbCon)
    
            With parentInsertCommand.Parameters
                .Add("@ID", DbType.Int32, 0, "ID")
                .Add("@Name", DbType.String, 50, "Name")
                .Add("@HardA", DbType.Decimal, 10, "HardA")
                .Add("@HardB", DbType.Decimal, 10, "HardB")
                .Add("@DensA", DbType.Decimal, 10, "DensA")
                .Add("@DensB", DbType.Decimal, 10, "DensB")
                .Add("@Formula", DbType.String, 50, "Formula")
                .Add("@CrystallographyID", DbType.Int32, 0, "CrystallographyID")
            End With
    
            With adGemstones
                .InsertCommand = parentInsertCommand
            End With
    
        End Sub

    Where the error occurs:

    Code:

        Private Sub btnSaveNew_Click(sender As Object, e As EventArgs) Handles btnSaveNew.Click
    
            Dim NewRecordName As String = Me.txtName.Text
    
            If MessageBox.Show(String.Format("Do you want to save a new record for " & NewRecordName & "?"), "Confirmation", MessageBoxButtons.YesNo) = DialogResult.Yes Then
    
                bsGemstones.EndEdit()
                adGemstones.Update(dtGemstones)   <----- ERROR HAPPENS HERE
    
                MessageBox.Show("The new record has been saved successfully")
    
            Else
    
                MessageBox.Show("The new record creation has been canceled")
    
            End If
    
        End Sub

    What am I missing here?

    ---

11. Jan 17th, 2023, 10:14 PM

    
    

    #11

    Re: SQLite — CRUD
    

    «ID» is an Autonumber field so don’t include it in the Insert statement.

    Try that, see if it solves your problem.

    ---

12. Jan 17th, 2023, 10:20 PM

    
    

    #12

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    Originally Posted by wes4dbt
    

    «ID» is an Autonumber field so don’t include it in the Insert statement.

    Try that, see if it solves your problem.

    I actually thought of that and have tried it both ways. It errors at the same point with the same error either way…

    ---

13. Jan 17th, 2023, 10:45 PM

    
    

    #13

    Re: SQLite — CRUD
    

    Why have you removed the dataadapter UpdateCommand aand DeleteCommand. I just tested and I get an error when I try to add a new record if there isn’t a valid UpdateCommand for the dataadapter. But it’s not the same error as you say your getting.

    This works,

    Code:

        Private Sub SetUpAdapter()
            Dim parentInsertCommand As New SQLiteCommand("INSERT INTO scientificdata (Name, Density, ChemistryId) VALUES (@Name, @Density, @ChemistryId)", con)
            Dim parentUpdateCommand As New SQLiteCommand("UPDATE scientificdata SET Name = @Name, Density = @Density, chemistryId = @ChemistryId 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")
                .Add("@ChemistryId", DbType.Int32, 0, "ChemistryId")
                .Add("@Density", DbType.Decimal, 10, "Density")
            End With
    
            With parentUpdateCommand.Parameters
                .Add("@Name", DbType.String, 50, "Name")
                .Add("@ChemistryId", DbType.Int32, 0, "ChemistryId")
                .Add("@Density", DbType.Decimal, 0, "Density")
                .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

    HOW are you trying to add a new record? Are you calling the «AddNew» method on bsGemstones?

    Post all relevant code. I’d suggest you stop making major changes to your code everytime. If it needed massive changes we would have told you. It’s to hard to solve problems that way.

    Last edited by wes4dbt; Jan 17th, 2023 at 10:48 PM.

    ---

14. Jan 17th, 2023, 10:57 PM

    
    

    #14

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    I removed the UPDATE and DELETE commands only from the code I shared, in an effort to focus on just the INSERT command. In my project, both of these commands are still present.

    I think this is the code your asking me for? This is how I thought I would insert a new record? Perhaps I misunderstood a previous post where you said that this would be all I need to INSERT/UPDATE/DELETE.

    Code:

        Private Sub btnSaveNew_Click(sender As Object, e As EventArgs) Handles btnSaveNew.Click
    
            Dim NewRecordName As String = Me.txtName.Text
    
            If MessageBox.Show(String.Format("Do you want to save a new record for " & NewRecordName & "?"), "Confirmation", MessageBoxButtons.YesNo) = DialogResult.Yes Then
    
                bsGemstones.EndEdit()
                adGemstones.Update(dtGemstones)
    
                MessageBox.Show("The new record has been saved successfully")
    
            Else
    
                MessageBox.Show("The new record creation has been canceled")
    
            End If
    
        End Sub

    ---

15. Jan 17th, 2023, 11:02 PM

    
    

    #15

    Re: SQLite — CRUD
    

    No that doesn’t tell me anything about what method your using to add a new record. Just post all your code in the form.

    What I mean. Do you have an «ADD» button you click when you want to a a new record or do you use a BindingNavigator Add button. Are you trying to Add a record using the DGV? Where is the bindingsource «AddNew» method being called.

    Last edited by wes4dbt; Jan 17th, 2023 at 11:06 PM.

    ---

16. Jan 17th, 2023, 11:05 PM

    
    

    #16

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    Code:

    Imports System.Data.SQLite
    
    Public Class frmMain
    
        Private dbCon As New SQLiteConnection("Data Source=" & Application.StartupPath & "Database.db;" & "Version=3;New=False;Compressed=True;")
    
        Dim Adapter1 As String = ("SELECT ID, Name, HardA, HardB, DensA, DensB, Formula, CrystallographyID FROM Gemstones ORDER BY Name ASC")
        Dim Adapter2 As String = ("SELECT CrystalID, CrystalStructure FROM Crystallography ORDER BY CrystalID ASC")
    
        Private adGemstones As New SQLiteDataAdapter(Adapter1, dbCon)
        Private adCrystallography As New SQLiteDataAdapter(Adapter2, dbCon)
    
        Private dtGemstones As New DataTable
        Private bsGemstones As New BindingSource
    
        Private dtCrystallography As New DataTable
        Private bsCrystallography As New BindingSource
    
        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
    
            SetUpAdapter()
    
            adGemstones.Fill(dtGemstones)
            adCrystallography.Fill(dtCrystallography)
            bsGemstones.DataSource = dtGemstones
            Me.DGV.DataSource = bsGemstones
    
            SetUpBindings()
    
        End Sub
    
    
    
        Private Sub SetUpAdapter()
            Dim parentInsertCommand As New SQLiteCommand("INSERT INTO Gemstones (Name, HardA, HardB, DensA, DensB, Formula, CrystallographyID) VALUES (@Name, @HardA, @HardB, @DensA, @DensB, @Formula, @CrystallographyID)", dbCon)
            Dim parentUpdateCommand As New SQLiteCommand("UPDATE Gemstones SET Name = @Name, HardA = @HardA, HardB = @HardB, DensA = @DensB, Formula = @Formula, CrystallographyID = @CrystallographyID WHERE Id = @Id", dbCon)
            Dim parentDeleteCommand As New SQLiteCommand("DELETE FROM Gemstones WHERE Id = @Id", dbCon)
    
            With parentInsertCommand.Parameters
                .Add("@ID", DbType.Int32, 0, "ID")
                .Add("@Name", DbType.String, 50, "Name")
                .Add("@HardA", DbType.Decimal, 10, "HardA")
                .Add("@HardB", DbType.Decimal, 10, "HardB")
                .Add("@DensA", DbType.Decimal, 10, "DensA")
                .Add("@DensB", DbType.Decimal, 10, "DensB")
                .Add("@Formula", DbType.String, 50, "Formula")
                .Add("@CrystallographyID", DbType.Int32, 0, "CrystallographyID")
            End With
    
            With parentUpdateCommand.Parameters
                .Add("@Name", DbType.String, 50, "Name")
                .Add("@HardA", DbType.Decimal, 10, "HardA")
                .Add("@HardB", DbType.Decimal, 10, "HardB")
                .Add("@DensA", DbType.Decimal, 10, "DensA")
                .Add("@DensB", DbType.Decimal, 10, "DensB")
                .Add("@Formula", DbType.String, 50, "Formula")
                .Add("@CrystallographyID", DbType.Int32, 0, "CrystallographyID")
            End With
    
            With parentDeleteCommand.Parameters
                .Add("@ID", DbType.Int32, 50, "ID")
            End With
    
            With adGemstones
                .InsertCommand = parentInsertCommand
                .UpdateCommand = parentUpdateCommand
                .DeleteCommand = parentDeleteCommand
            End With
    
        End Sub
    
    
    
        Private Sub SetUpBindings()
    
            bsGemstones.DataSource = dtGemstones
            Me.lblID.DataBindings.Add("Text", bsGemstones, "ID")
            Me.txtName.DataBindings.Add("Text", bsGemstones, "Name")
            Me.txtHardnessA.DataBindings.Add("Text", bsGemstones, "HardA")
            Me.txtHardnessB.DataBindings.Add("Text", bsGemstones, "HardB")
            Me.txtDensityA.DataBindings.Add("Text", bsGemstones, "DensA")
            Me.txtDensityB.DataBindings.Add("Text", bsGemstones, "DensB")
            Me.txtFormula.DataBindings.Add("Text", bsGemstones, "Formula")
            Me.cmbCrystalSystem.DataBindings.Add("Selectedvalue", bsGemstones, "CrystallographyID")
            Me.cmbCrystalSystem.DisplayMember = "CrystalStructure"
            Me.cmbCrystalSystem.ValueMember = "CrystalID"
            Me.cmbCrystalSystem.SelectedValue = "CrystalID"
            Me.cmbCrystalSystem.DataSource = dtCrystallography
    
            Me.DGV.DataSource = bsGemstones
    
        End Sub
    
    
    
        Private Sub btnSaveNew_Click(sender As Object, e As EventArgs) Handles btnSaveNew.Click
    
            Dim NewRecordName As String = Me.txtName.Text
    
            If MessageBox.Show(String.Format("Do you want to save a new record for " & NewRecordName & "?"), "Confirmation", MessageBoxButtons.YesNo) = DialogResult.Yes Then
    
                bsGemstones.EndEdit()
                adGemstones.Update(dtGemstones)
    
                MessageBox.Show("The new record has been saved successfully")
    
            Else
    
                MessageBox.Show("The new record creation has been canceled")
    
            End If
    
            dtGemstones = New DataTable
            adGemstones.Fill(dtGemstones)
            bsGemstones.DataSource = dtGemstones
    
        End Sub
    
    
        Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
            Me.Close()
        End Sub
    
    End Class

    Last edited by The_Hobbyist; Jan 17th, 2023 at 11:26 PM.

    ---

17. Jan 17th, 2023, 11:16 PM

    
    

    #17

    Re: SQLite — CRUD
    

    Change this,

    Code:

    Dim parentUpdateCommand As New SQLiteCommand("UPDATE Gemstones SET ID = @ID, Name = @Name, HardA = @HardA, HardB = @HardB, DensA = @DensB, Formula = @Formula, CrystallographyID = @CrystallographyID WHERE Id = @Id", dbCon)
    
    To
    
    Dim parentUpdateCommand As New SQLiteCommand("UPDATE Gemstones SET Name = @Name, HardA = @HardA, HardB = @HardB, DensA = @DensB, Formula = @Formula, CrystallographyID = @CrystallographyID WHERE Id = @Id", dbCon)

    You can’t set the ID

    Also please don’t use «*», type out the field names. I need to see exactly what’s being Selected from the Database. Makes it much easier to check for Typo’s and other things.

    Last edited by wes4dbt; Jan 17th, 2023 at 11:19 PM.

    ---

18. Jan 17th, 2023, 11:23 PM

    
    

    #18

    Re: SQLite — CRUD
    

    I told you before to get rid of this.

    Code:

              Me.DGV.DataSource = bsGemstones
                Me.DGV.Enabled = True
                Me.btnNew.Enabled = True
                Me.btnNew.Visible = True
                Me.btnNew.Location = New Point(13, 20)
                Me.btnSaveNew.Enabled = False
                Me.btnEdit.Enabled = True
                Me.btnDelete.Enabled = True
                Me.btnCancel.Enabled = False
                Me.btnCancel.Visible = False
                Me.btnCancel.Location = New Point(13, 50)

    ---

19. Jan 17th, 2023, 11:24 PM

    
    

    #19

    Re: SQLite — CRUD
    

    And all this,

    Code:

        Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
    
            Dim SelectedRow As DataGridViewRow
            SelectedRow = DGV.CurrentRow
            Dim ID As Integer = SelectedRow.Cells(0).Value
    
            If MessageBox.Show(String.Format("Do you want to save the edits to {0}", SelectedRow.Cells("Name").Value), "Confirmation", MessageBoxButtons.YesNo) = DialogResult.Yes Then
    
                bsGemstones.EndEdit()
                adGemstones.Update(dtGemstones)
    
                dtGemstones = New DataTable
                adGemstones.Fill(dtGemstones)
                bsGemstones.DataSource = dtGemstones
                Me.DGV.DataSource = bsGemstones
    
                MessageBox.Show("The edit was successful")
    
            Else
    
                MessageBox.Show("The edit has been canceled")
    
            End If
    
        End Sub
    
    
    
        Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
    
            Dim SelectedRow As DataGridViewRow
            SelectedRow = DGV.CurrentRow
            Dim ID As Integer = SelectedRow.Cells(0).Value
    
            If MessageBox.Show(String.Format("Do you want to delete {0}", SelectedRow.Cells("Name").Value), "Confirmation", MessageBoxButtons.YesNo) = DialogResult.Yes Then
    
                If Me.DGV.SelectedRows.Count > 0 Then
    
                    bsGemstones.EndEdit()
                    adGemstones.Update(dtGemstones)
    
                    MessageBox.Show("Delete was successful")
    
                End If
    
            Else
    
                MessageBox.Show("Delete was canceled")
    
            End If
    
        End Sub

    ---

20. Jan 17th, 2023, 11:27 PM

    
    

    #20

    Re: SQLite — CRUD
    

    Post your code after you make these changes and let me know what happens when you try to add a new record.

    ---

21. Jan 17th, 2023, 11:27 PM

    
    

    #21

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    OK. I have edited the code in my above post to reflect your requests.

    ---

22. Jan 17th, 2023, 11:28 PM

    
    

    #22

    Re: SQLite — CRUD
    

    Originally Posted by The_Hobbyist
    

    OK. I have edited the code in my above post to reflect your requests.

    And what happened??? Is it a secret. lol

    Wait, why did you edit the code in your post. Make the changes to your code, test, let me know what happens. If there is still a problem, post your current code, explain the problem or error.

    ---

23. Jan 17th, 2023, 11:32 PM

    
    

    #23

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    I’m sorry. Moving forward I wont edit previous posts showing code.

    The problem hasn’t changed. Same error at the same line.

    ---

24. Jan 17th, 2023, 11:33 PM

    
    

    #24

    Re: SQLite — CRUD
    

    ---

25. Jan 17th, 2023, 11:36 PM

    
    

    #25

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    Code:

    Imports System.Data.SQLite
    
    Public Class frmMain
    
        Private dbCon As New SQLiteConnection("Data Source=" & Application.StartupPath & "Database.db;" & "Version=3;New=False;Compressed=True;")
    
        Dim Adapter1 As String = ("SELECT ID, Name, HardA, HardB, DensA, DensB, Formula, CrystallographyID FROM Gemstones ORDER BY Name ASC")
        Dim Adapter2 As String = ("SELECT CrystalID, CrystalStructure FROM Crystallography ORDER BY CrystalID ASC")
    
        Private adGemstones As New SQLiteDataAdapter(Adapter1, dbCon)
        Private adCrystallography As New SQLiteDataAdapter(Adapter2, dbCon)
    
        Private dtGemstones As New DataTable
        Private bsGemstones As New BindingSource
    
        Private dtCrystallography As New DataTable
        Private bsCrystallography As New BindingSource
    
        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
    
            SetUpAdapter()
    
            adGemstones.Fill(dtGemstones)
            adCrystallography.Fill(dtCrystallography)
            bsGemstones.DataSource = dtGemstones
            Me.DGV.DataSource = bsGemstones
    
            SetUpBindings()
    
        End Sub
    
    
    
        Private Sub SetUpAdapter()
            Dim parentInsertCommand As New SQLiteCommand("INSERT INTO Gemstones (Name, HardA, HardB, DensA, DensB, Formula, CrystallographyID) VALUES (@Name, @HardA, @HardB, @DensA, @DensB, @Formula, @CrystallographyID)", dbCon)
            Dim parentUpdateCommand As New SQLiteCommand("UPDATE Gemstones SET Name = @Name, HardA = @HardA, HardB = @HardB, DensA = @DensB, Formula = @Formula, CrystallographyID = @CrystallographyID WHERE Id = @Id", dbCon)
            Dim parentDeleteCommand As New SQLiteCommand("DELETE FROM Gemstones WHERE Id = @Id", dbCon)
    
            With parentInsertCommand.Parameters
                .Add("@ID", DbType.Int32, 0, "ID")
                .Add("@Name", DbType.String, 50, "Name")
                .Add("@HardA", DbType.Decimal, 10, "HardA")
                .Add("@HardB", DbType.Decimal, 10, "HardB")
                .Add("@DensA", DbType.Decimal, 10, "DensA")
                .Add("@DensB", DbType.Decimal, 10, "DensB")
                .Add("@Formula", DbType.String, 50, "Formula")
                .Add("@CrystallographyID", DbType.Int32, 0, "CrystallographyID")
            End With
    
            With parentUpdateCommand.Parameters
                .Add("@Name", DbType.String, 50, "Name")
                .Add("@HardA", DbType.Decimal, 10, "HardA")
                .Add("@HardB", DbType.Decimal, 10, "HardB")
                .Add("@DensA", DbType.Decimal, 10, "DensA")
                .Add("@DensB", DbType.Decimal, 10, "DensB")
                .Add("@Formula", DbType.String, 50, "Formula")
                .Add("@CrystallographyID", DbType.Int32, 0, "CrystallographyID")
            End With
    
            With parentDeleteCommand.Parameters
                .Add("@ID", DbType.Int32, 50, "ID")
            End With
    
            With adGemstones
                .InsertCommand = parentInsertCommand
                .UpdateCommand = parentUpdateCommand
                .DeleteCommand = parentDeleteCommand
            End With
    
        End Sub
    
    
    
        Private Sub SetUpBindings()
    
            bsGemstones.DataSource = dtGemstones
            Me.lblID.DataBindings.Add("Text", bsGemstones, "ID")
            Me.txtName.DataBindings.Add("Text", bsGemstones, "Name")
            Me.txtHardnessA.DataBindings.Add("Text", bsGemstones, "HardA")
            Me.txtHardnessB.DataBindings.Add("Text", bsGemstones, "HardB")
            Me.txtDensityA.DataBindings.Add("Text", bsGemstones, "DensA")
            Me.txtDensityB.DataBindings.Add("Text", bsGemstones, "DensB")
            Me.txtFormula.DataBindings.Add("Text", bsGemstones, "Formula")
            Me.cmbCrystalSystem.DataBindings.Add("Selectedvalue", bsGemstones, "CrystallographyID")
            Me.cmbCrystalSystem.DisplayMember = "CrystalStructure"
            Me.cmbCrystalSystem.ValueMember = "CrystalID"
            Me.cmbCrystalSystem.SelectedValue = "CrystalID"
            Me.cmbCrystalSystem.DataSource = dtCrystallography
    
            Me.DGV.DataSource = bsGemstones
    
        End Sub
    
    
    
        Private Sub btnSaveNew_Click(sender As Object, e As EventArgs) Handles btnSaveNew.Click
    
            Dim NewRecordName As String = Me.txtName.Text
    
            If MessageBox.Show(String.Format("Do you want to save a new record for " & NewRecordName & "?"), "Confirmation", MessageBoxButtons.YesNo) = DialogResult.Yes Then
    
                bsGemstones.EndEdit()
                adGemstones.Update(dtGemstones)
    
                MessageBox.Show("The new record has been saved successfully")
    
            Else
    
                MessageBox.Show("The new record creation has been canceled")
    
            End If
    
            dtGemstones = New DataTable
            adGemstones.Fill(dtGemstones)
            bsGemstones.DataSource = dtGemstones
    
        End Sub
    
    
        Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
            Me.Close()
        End Sub
    
    End Class

    ---

26. Jan 17th, 2023, 11:40 PM

    
    

    #26

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    If this help at all, this is my entire project in a ZIP file.

    It would appear that the VB Forums website doesn’t allow direct downloading from links? You might have to right click, copy link address and then paste that link address into a new browser window. It’s a link to my personal website where I have the ZIP file stored.

    CRUD.zip

    *** Updated ZIP file as of post #40 ***

    Last edited by The_Hobbyist; Jan 18th, 2023 at 10:26 AM.

    ---

27. Jan 17th, 2023, 11:46 PM

    
    

    #27

    Re: SQLite — CRUD
    

    You were not suppose to remove «Id» from the parameters you create, just don’t Set ID = @ID. You need the @ID param for the Where claus.

    Change it back to,

    Code:

            With parentUpdateCommand.Parameters
                .Add("@Name", DbType.String, 50, "Name")
                .Add("@HardA", DbType.Decimal, 10, "HardA")
                .Add("@HardB", DbType.Decimal, 10, "HardB")
                .Add("@DensA", DbType.Decimal, 10, "DensA")
                .Add("@DensB", DbType.Decimal, 10, "DensB")
                .Add("@Formula", DbType.String, 50, "Formula")
                .Add("@ID",DBType.Int32,0,"ID")

    btw — This is why you always post the current code after making a change.

    Last edited by wes4dbt; Jan 17th, 2023 at 11:50 PM.

    ---

28. Jan 17th, 2023, 11:49 PM

    
    

    #28

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    Originally Posted by wes4dbt
    

    You were not suppose to remove «Id» from the parameters you create, just don’t Set ID = @ID. You need the @ID param for the Where clausd.

    Ah! Yes… This now seems to work with that @ID put back in:

    Code:

            With parentUpdateCommand.Parameters
                .Add("@ID", DbType.Int32, 0, "ID")
                .Add("@Name", DbType.String, 50, "Name")
                .Add("@HardA", DbType.Decimal, 10, "HardA")
                .Add("@HardB", DbType.Decimal, 10, "HardB")
                .Add("@DensA", DbType.Decimal, 10, "DensA")
                .Add("@DensB", DbType.Decimal, 10, "DensB")
                .Add("@Formula", DbType.String, 50, "Formula")
                .Add("@CrystallographyID", DbType.Int32, 0, "CrystallographyID")
            End With

    ---

29. Jan 17th, 2023, 11:50 PM

    
    

    #29

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    ---

30. Jan 17th, 2023, 11:51 PM

    
    

    #30

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    Originally Posted by wes4dbt
    

    btw — This is why you always post the current code after making a change.

    Noted!

    ---

31. Jan 17th, 2023, 11:51 PM

    
    

    #31

    Re: SQLite — CRUD
    

    Thank goodness because I was running out of ideas. Was ready to tell you to use a CommandBuilder and move on.

    ---

32. Jan 17th, 2023, 11:54 PM

    
    

    #32

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    Originally Posted by wes4dbt
    

    Thank goodness because I was running out of ideas. Was ready to tell you to use a CommandBuilder and move on.

    LoL…. Well, you have been exceptionally patient and forgiving with me. It is greatly appreciated! I’ll have to send you a beer when this comes to an end…

    In any event, I think for tonight I will call it quits. Tomorrow I will be back with more questions, I’m sure!

    But seriously, I can’t thank you enough. Cheers!

    ---

33. Jan 17th, 2023, 11:55 PM

    
    

    #33

    Re: SQLite — CRUD
    

    I’d still like to know the answer to this,

    Do you have an «ADD» button you click when you want to a a new record or do you use a BindingNavigator Add button. Are you trying to Add a record using the DGV? Where is the bindingsource «AddNew» method being called.

    If you don’t know the AddNew method is being called, that’s Ok. Just tell what you do when you want to add a new record.

    ---

34. Jan 17th, 2023, 11:58 PM

    
    

    #34

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    Originally Posted by wes4dbt
    

    I’d still like to know the answer to this,

    If you don’t know the AddNew method is being called, that’s Ok. Just tell what you do when you want to add a new record.

    I’m sorry, I must have missed this question!

    Well, I’m currently entering data into text/combo boxes and then I use an ADD button to create a new record. Likewise, I had envisioned an EDIT ROW and a DELETE ROW button as well.

    ---

35. Jan 18th, 2023, 12:15 AM

    
    

    #35

    Re: SQLite — CRUD
    

    Originally Posted by The_Hobbyist
    

    I’m sorry, I must have missed this question!

    Well, I’m currently entering data into text/combo boxes and then I use an ADD button to create a new record. Likewise, I had envisioned an EDIT ROW and a DELETE ROW button as well.

    This doesn’t make sense to me. These controls are bound, so they will be displaying the current record, they wont be blank. Unless there is no records in the db table. Are you just typing over the data that’s being displayed? If so, your not creating a new record, your just modifying an existing one.

    ---

36. Jan 18th, 2023, 12:20 AM

    
    

    #36

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    I made a button that clears the text and combo boxes. I don’t necessarily like it…. Wasn’t sure what was best in this case…

    ---

37. Jan 18th, 2023, 12:21 AM

    
    

    #37

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    I’m open to suggestions!

    ---

38. Jan 18th, 2023, 02:01 AM

    
    

    #38

    

    Thread Starter

    
    Addicted Member
    

    

    ---

    Re: SQLite — CRUD
    

    Now that you got the INSERT/UPDATE working for me, I can see how using these text/combo boxes the way I intend to, is not a great way of going about it. It felt like a good way of doing it until the CRUD started working…lol! You’re right (of course), as it stands now, I am unable to create a new record but rather I am limited to editing whichever record I have highlighted in the DGV at the time.

    I don’t know how to work around this. Entering data into the DGV seems more cumbersome than textboxes and it makes for a pretty unappealing interface if you ask me. It also doesn’t achieve the graphical design that I was shooting for either. Further down the road, I had hoped to use checkboxes as a means of collecting data that would be written to the database as well. It seems to me that these checkboxes, just like the text/combo boxes I am using now, will be bound to the DGV and therefore I will be in the same position with them as I am with the text/combo boxes now — limited to only editing current records.

    I’m going to spend some time thinking this over. Upon first thought, my gut tells me to display the data in the bound form objects like I am doing (to achieve the graphical aspect of the application) but then use a separate «pop up» form to add a new record. The edit and delete functions could run from the main form it would seem. With that said, it was also brought up in previous threads that a pop up window could make things messy.

    If I can’t come up with an idea to alleviate this problem, then I’m sure yourself or another guru here will chime in with some insight. I’m sure there is a preferred method or even a «standard» but my limited experience doesn’t bring anything to mind.

    ---

39. Jan 18th, 2023, 02:47 AM

    
    

    #39

    Re: SQLite — CRUD
    

    Code:

    I made a button that clears the text and combo boxes. I don't necessarily like it.... Wasn't sure what was best in this case...

    Funny I never saw a Button that had code to clear all the control. You trying to pull my leg???

    You don’t need to spend a lot of time thinking about it. We’ve given you the answer before.

    Just add a button control, then in the Button Click event you just call the AddNew method on the bindingsource.

    Code:

        Private Sub AddButton_Click(sender As Object, e As EventArgs) Handles AddButton.Click
            scienceBindingsource.AddNew()
        End Sub

    Last edited by wes4dbt; Jan 18th, 2023 at 03:12 AM.

    ---

40. Jan 18th, 2023, 02:52 AM

    
    

    #40

    Re: SQLite — CRUD
    

    And as i said in your other thread: A ForeignKey is like another column, BUT if correctly set up, it must be provided for an INSERT.

    Code:

    Dim parentInsertCommand As New SQLiteCommand("INSERT INTO Gemstones (Name, HardA, HardB, DensA, DensB, Formula) VALUES (@Name, @HardA, @HardB, @DensA, @DensB, @Formula)", dbCon)

    Your FK to Crytallography is missing!
    That’s why your insert fails!

    The ID of Crystallography MUST exist and be provided to parameters of your INSERT-Command

    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ——————————————————————————————————————————————————————————————————————————————-

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ———————————————————————————
    People call me crazy because i’m jumping out of perfectly fine airplanes.
    ———————————————————————————
    Code is like a joke: If you have to explain it, it’s bad

    ---

• 
• VBForums
• Visual Basic
• Database Development
• [RESOLVED] SQLite — CRUD

Posting Permissions