[RESOLVED] Beginner trying to learn SQLite database

SS, I need to ask about something that's puzzling me. Because you use what's below in your code, I tried to find some references to it (IEnumerator, IEnumerable) Here's the first thing I found. I tried searching through your code, but found 'none' of this.

Quote:

---

First, we need to define a class to implement IEnumerable. Here's a start ...

Public Class Parties
Implements IEnumerator, IEnumerable

Public Function GetEnumerator() _
As IEnumerator _
Implements IEnumerable.GetEnumerator
Return CType(Me, IEnumerator)
End Function

This satisfies the requirement of IEnumerable because it implements GetEnumerator. How does it do that? It simply uses the CType operator to change the class itself into an IEnumerator and returns it.

---

Would you mind filling in some blanks? On the first sentence of the quote, it says to first create a class, but I saw none of that in your code. Of course it must be because you don't 'have to'.

Can you help me put these pieces together?

thanks

Quote:

---

Originally Posted by wes4dbt

I don't think that's a good idea. Earlier you posted a form with a datagridview and a couple of button. Why take a step backwards. Your already able to display data in a dgv. I think it would be easier for you and us to review your program results that way.

I mean,
[snip]
Boom.... Done

---

There's some tensions tugging RE: that decision. You're not wrong, and at some point I've got to start posting DataGridViews.

The console is easy to understand. I can put an entire console application in one copy/paste or one attached .vb file. Then someone can see all the code, laid bare, and see how it's put together.

WinForms projects, especially ones with DataAdapters, are not quite so easy. I can't post just one file, it has to be a zipped project. Or, I have to take the time to detail the 5 or 6 properties that always need to be set on the DGV, and then the 5 or 6 event handlers I might want to register, and that's not even counting the 10 or 15 properties and events you might want to use in various cases. It's remarkably complex, and very hard for new users to peek at and figure out how to reproduce. And Heaven help you if you're using the old .NET 1.x era "typed datasets" generated by code. I had my fun trying to move those around from machine to machine. I didn't touch a database again for something like 6 years.

But the console is really only useful for "this is how you put all the pieces together for a DataAdapter". Obviously few people want to make and use console applications. I wager it's easier to learn DataGridView by itself, rather than try to learn both DataGridView and DataAdapter at the same time. My wager comes from "that's how I learned it, it stunk, and I wish I'd taken my time because I think I might have written some more successful applications."

That said, working with DataAdapter from the console's painful enough I can only predict using it for a handful of more posts. It's convenient, but for magic to happen we need DataGridView and Windows Forms data binding.

Quote:

---

Originally Posted by jumper77

SS, I need to ask about something that's puzzling me. Because you use what's below in your code, I tried to find some references to it (IEnumerator, IEnumerable) Here's the first thing I found. I tried searching through your code, but found 'none' of this.



Would you mind filling in some blanks? On the first sentence of the quote, it says to first create a class, but I saw none of that in your code. Of course it must be because you don't 'have to'.

Can you help me put these pieces together?

thanks

---

I don't understand this question, what that block of text means, where it's from, or what you're asking me. That article looks very old, because modern code sort of shuns IEnumerable for IEnumerable(Of T). Long story. Don't get me started.

IEnumerable(Of T) is an interface in .NET that just means "this is a collection you can iterate". Other programmers call it "The Visitor Pattern", but Microsoft doesn't want their developers to be able to talk to other people, I guess, so they called it "enumeration".

There is a very formal version of the pattern that you need to know if you ever want to write your own collection type. That will not be needed at all in anything I'm expecting to talk about. In a nutshell:

Consider the numbers 1-5. That's an "enumerable collection of integers", or an IEnumerable(Of Integer).

IEnumerable(Of T) is an interface that defines one function: GetEnumerator() returns an IEnumerator(Of T). What's an enumerator?

Now consider how we count the numbers 1-5. You start with a closed fist. You extend one finger, and say "one". You extend another and say "two", and so on. Your hand, in this case, is an IEnumerator(Of Integer), because it knows how to iterate over the numbers 1-5, and it tells you when it's finished (it runs out of fingers.)

I didn't do any of the stuff in the post because you don't typically write your own collection type. It was much more common in .NET 1.x, because there were no generics and ArrayList/HashSet were a pain in the butt without them. Many .NET types already implement IEnumerable(Of T): arrays, List(Of T), Dictionary(Of TKey, TValue), etc. Almost every collection you find implements IEnumerable(Of T).

If I spend much more time on the topic, we have to implement one, and honestly I think it's extra work.

My opinion is whatever article you're reading looks like .NET from 2002-2004, and back then we had to do a lot of painful stuff we consider stupid today. A lot of what it says about DataAdapter probably still applies. But a lot of what it tells you to do may not be necessary in 2016.

Quote:

---

I don't think that's a good idea. Earlier you posted a form with a datagridview and a couple of button. Why take a step backwards. Your already able to display data in a dgv. I think it would be easier for you and us to review your program results that way.

---

Hi Wes, I apologize, but I missed your post. I've had to reconfigure my email software so I would get proper notice when someone replies to the thread.

But, thank you for noticing the GridView form. I always struggle with myself about posting UI stuff. I want people to see what I'm doing, but something makes me think it's not a good thing to do. Not really sure why.

So if nobody minds, I'll go back to the form project. It's as up to date as the console version. Plus, the WinForm version is much easier to test with just a click of a button.

Thanks for the post and thanks for sharing that. I feel better that I did before :)

Quote:

---

That said, working with DataAdapter from the console's painful enough I can only predict using it for a handful of more posts. It's convenient, but for magic to happen we need DataGridView and Windows Forms data binding.

---

Before I read this post, I talked with Wes about it being easier to understand if it's a WinForm project. If there are only a few more posts using a console, then I'll stick with the UI code. Unless I misunderstand what you're saying.

Plus I'm really looking forward to data binding. I've heard it mentioned for years, but never had a reason to check it out.

Quote:

---

I don't understand this question, what that block of text means, where it's from, or what you're asking me. That article looks very old, because modern code sort of shuns IEnumerable for IEnumerable(Of T). Long story. Don't get me started.

---

Ok, I won't get you started but here's the link to the article:
Article

And for the rest of your message... again, it was a big help. I'm not diving too deep though so I can continue with the project. If it's not needed now, I can come back to it.

My only complaint is UI projects are impossible to remote-debug. You can paste your whole code file, but it's only half of the story. The other half is in the designer code that sets all the DGV properties. And if it's complex enough I need to run it, you have to zip the whole project (remembering to remove the /bin and /obj directories) and upload it. Then, when I get it, the first thing I have to do is look over each property of the DGV, and each registered event handler, THEN go look at the code and see if you changed any of those in code.

It may sound trivial. I just counted roughly 190 properties on DataGridView, each of them related to at least one of something like 20 features. I counted more than 500 methods, with I can't tell how many available to override for extensibility. There's about 170 events. When I open a GUI project with a DataGridView, I have to go investigate all of those properties and event handlers to start getting a feel for what's going on.

Or, I can copy/paste an entire console application. Kablam, instant tweakable project. It creates types. It works with types. It calls Console.WriteLine() a lot. You can even it them and watch it run live at http://dotnetfiddle.net. (Except that doesn't support any database packages sooooo yeah.)

Again, it's screaming into the void. Once you know enough about database interaction, GUI is the only exciting topic left.

Quote:

---

When I open a GUI project with a DataGridView, I have to go investigate all of those properties and event handlers to start getting a feel for what's going on.

---

No problem at all. I'll stick with the console app if or until you think we should go the other way. I still have it loaded right now, so there's nothing I need to do. The only thing I have left to do at the moment is figure out how to test it in a console project.

edit: I don't mean to say that I understand everything at this point, because I don't. There's still much to learn. Also, I didn't realize how much you had to go through with a form app.

Use what you're comfortable with. A better way to phrase what I'm saying is this:

If I'm trying to teach some concept about some thing that doesn't need UI, I'm using a console application to reduce clutter and keep the focus on the concept.

But there are so many needs for CRUD apps in the world, WinForms has a ton of facilities for them. I can't talk about DataGridView without a Form. So when I get there, I'll go to the trouble of setting up a UI. And in that case, while it's "trouble" to explain what I did in the designer, the whole point of the 'lesson' will be that "trouble", so it's not worth complaining about.

I think we should stay with the console app. It's simple, plus it's easy to focus on what you're learning. A UI only slows the process because half the time you're fighting with the controls and other things that are just not necessary. And I would rather spend more time on the things I want to learn.

And now I have something to throw back at you :) There really is no need to have the UI/DataGrid in this lesson. As long as I understand the mechanisms behind the UI implementation, I can always learn the UI parts later on. But I'm guessing that you've already put some time in already working with the DataGrid (and maybe even having fun), so if you want to keep that as part of the project, that's ok with me.

We could have dueling DataGrids :)

edit: I already know how to refresh the DataGrid so it shows the information you just inserted or updated. Plus I have alternating colors on my rows already. :cool:

Now, back to work. There's something I don't understand going on after I tried to use your INSERT sub. The sub now makes much more sense to me (but I've only been working on it today). I'll include the relevant code, but let me see if I can describe it first.

In my app, I have 4 fields, not 2, so I altered your code to deal with the extra fields. When the program hits the Insert sub, I'm not getting the results I'm expecting. When assigning parameters a .value, the first name is correct, but lastname, address, and phoneNumber all have the value of phoneNumber?? It's probably my fault though.

So, here's where the code starts:

Code:

---

    Sub Main()
        Const databaseName As String = "customers.db"
        Dim dbcustomer As New List(Of Customer)()
        Dim customer As New Customer
        Dim db As New CustomerDatabase(databaseName)

        If Not Exists(databaseName) Then
            db.CreateDatabase()
            'db.Reset()
        End If

        customer.FirstName = "Joe"
        customer.LastName = "bob"
        customer.address = "20 west main street"
        customer.phoneNumber = "444.1212"

        dbcustomer.Add(customer)

        db.Insert(dbcustomer)
    End Sub

---

Next it's sent to the Insert sub:

Code:

---

    Public Sub Insert(ByVal customers As IEnumerable(Of Customer))
        ' Lesson 3: the query is converted to use parameters.
        Dim insertStatement As String = "INSERT INTO customer (firstName, lastname, address, phoneNumber) VALUES (:firstName, :lastName, :address, :phoneNumber)"

        Using connection As SQLiteConnection = CreateConnection()
            Using command As SQLiteCommand = connection.CreateCommand()
                command.CommandText = insertStatement
                command.Parameters.Add(New SQLiteParameter("firstName"))
                command.Parameters.Add(New SQLiteParameter("lastName"))
                command.Parameters.Add(New SQLiteParameter("address"))
                command.Parameters.Add(New SQLiteParameter("phoneNumber"))

                For Each customer In customers
                    command.Parameters("firstName").Value = customer.FirstName
                    command.Parameters("lastName").Value = customer.LastName
                    command.Parameters("lastName").Value = customer.address
                    command.Parameters("lastName").Value = customer.phoneNumber

                    If command.ExecuteNonQuery() = -1 Then
                        Console.WriteLine("INSERT failed!")
                    End If
                Next
            End Using
        End Using
    End Sub

---

And as I said,
firstname.value = first name
lastname.value = phoneNumber
address.value = phoneNumber
phoneNumber.value = phoneNumber

There's a pattern there, but I didn't expect last name to be phoneNumber. I thought the 2 parameters I added would be affected, not one of yours.

edit: Almost forgot, I'm using my sub for creating the database if not found.

Code:

---

    'Creates New Database if not found
    Public Sub CreateDatabase()
        Using objConn As New SQLiteConnection(__connectionStringCreateDatabase)
            Using objCommand As SQLiteCommand = objConn.CreateCommand()
                Try
                    With objCommand
                        objConn.Open()

                        .CommandText = "Create TABLE customer('Row Count' INTEGER PRIMARY KEY,'FirstName' Text Not NULL, 'LastName' Text Not NULL, Address Text Not NULL, 'PhoneNumber' Text Not Null);"
                        .ExecuteNonQuery()

                    End With
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End Using
        End Using
    End Sub

---

See if you find something fishy about these lines of code:

Code:

---

                    command.Parameters("firstName").Value = customer.FirstName
                    command.Parameters("lastName").Value = customer.LastName
                    command.Parameters("lastName").Value = customer.address
                    command.Parameters("lastName").Value = customer.phoneNumber

---

Hint: copy/pasting can save a lot of time. But you have to make sure you actually update everything.

I once saw a study where a guy wrote a bot to analyze thousands of GitHub projects for copy/paste errors. The fascinating finding was not how many he found, but how dramatic the probability swayed towards the last line being the error.

In this case, it's more than the last line.

Crap (he says hanging his head in shame). I was looking at the values on the right, not the one's in the middle. Well, that post was for nothing.

And yes, it's more than one.

I'll make the changes and then see what happens,
thanks a bunch. Will let you know how it turns out (in a few minutes).

That was quick. Probably took 30 seconds to get the result. Everybody is happy now and there are NO errors (except in my brain).
Thanks for the help and slap upside the head.

Before I spend too much time with this issue, I'm going to ask a question.

• First I insert a new row in customers database
• Then I call a function to update the record. It changes the last name in the record.
• Before doing the ExecuteNonQurey, the value contains the correct last name to be changed.
• After ExecuteNonQurey, the last name is not changed.

this is because ID always has the value of 0, and I'm not sure why. So here's the create database and calling code:

Code:

---

  'Creates New Database if not found
    Public Sub CreateDatabase()
        Using objConn As New SQLiteConnection(__connectionStringCreateDatabase)
            Using objCommand As SQLiteCommand = objConn.CreateCommand()
                Try
                    With objCommand
                        objConn.Open()

                        .CommandText = "Create TABLE customer('Id' INTEGER PRIMARY KEY,'FirstName' Text Not NULL, 'LastName' Text Not NULL, Address Text Not NULL, 'PhoneNumber' Text Not Null);"
                        .ExecuteNonQuery()

                    End With
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End Using
        End Using
    End Sub

---

Code:

---

Module Module1
    Const databaseName As String = "customers.db"
    Private objConn As New SQLiteConnection
    Private objCommand As New SQLiteCommand
    Private dbcustomers As New List(Of Customer)()
    Private updateCustomer As New Customer
    Private db As New CustomerDatabase(databaseName)
    Private conString As New CustomerDatabase(connectionString)

    Sub Main()
        Dim customer As New Customer

        If Not Exists(databaseName) Then
            db.CreateDatabase()
        End If

        TestInsert()
        TestUpdate()
    End Sub

    Sub TestUpdate()
        Dim customer As New Customer

        With Customer
            .FirstName = "Joe"
            .LastName = "bob"
            .address = "20 west main street"
            .phoneNumber = "444.1212"
        End With

        db.Update(customer)
    End Sub

    Public Sub TestInsert()
        Dim customer As New Customer

        With customer
            .FirstName = "Joe"
            .LastName = "Pendalton"
            .address = "20 west main street"
            .phoneNumber = "444.1212"
        End With

        dbcustomers.Add(customer)
        db.Insert(dbcustomers)
    End Sub

---

Now, the Insert and Update subs:

Code:

---

  Public Sub Insert(ByVal customers As IEnumerable(Of Customer))
        ' Lesson 3: the query is converted to use parameters.
        Dim insertStatement As String = "INSERT INTO customer (firstName, lastname, address, phoneNumber) VALUES (:firstName, :lastName, :address, :phoneNumber)"

        Try
            Using connection As SQLiteConnection = CreateConnection()
                Using command As SQLiteCommand = connection.CreateCommand()
                    command.CommandText = insertStatement
                    command.Parameters.Add(New SQLiteParameter("firstName"))
                    command.Parameters.Add(New SQLiteParameter("lastName"))
                    command.Parameters.Add(New SQLiteParameter("address"))
                    command.Parameters.Add(New SQLiteParameter("phoneNumber"))

                    For Each customer In customers
                        command.Parameters("firstName").Value = customer.FirstName
                        command.Parameters("lastName").Value = customer.LastName
                        command.Parameters("address").Value = customer.address
                        command.Parameters("phoneNumber").Value = customer.phoneNumber

                        command.ExecuteNonQuery()
                        'If command.ExecuteNonQuery() = -1 Then
                        '    Console.WriteLine("INSERT failed!")
                        'End If
                    Next
                End Using
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Sub Update(ByVal customer As Customer)
        Dim updateQuery As String = "UPDATE customer SET " &
        "firstName = :firstName, lastName = :lastName, address = :address, phoneNumber = :phoneNumber WHERE id = id"

        Try
            Using connection As SQLiteConnection = CreateConnection()
                Using command As SQLiteCommand = connection.CreateCommand()
                    command.CommandText = updateQuery
                    command.Parameters.Add(New SQLiteParameter("firstName", customer.FirstName))
                    command.Parameters.Add(New SQLiteParameter("lastName", customer.LastName))
                    command.Parameters.Add(New SQLiteParameter("address", customer.address))
                    command.Parameters.Add(New SQLiteParameter("phoneNumber", customer.phoneNumber))
                    command.Parameters.Add(New SQLiteParameter("id", customer.Id))

                    command.ExecuteNonQuery()

                    'If command.ExecuteNonQuery() = -1 Then
                    '    Console.WriteLine("An UPDATE failed!")
                    'End If
                End Using
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

---

Anyone have an idea of what be doing on? I can't seem to see it.

edit: The ID field should auto-increment, but it's not.

You have to check your terminology - you do not put new rows in a database. A database contains tables (amongst other objects) and it is the table that receives a new row.

INSERT will put a new row in a table.

There is no need for any other ACTION QUERY after this - INSERT does just this simple task of putting a new row in table.

UPDATE will alter an existing row in a table.

ExecuteNonQuery is a method that takes an ACTION QUERY and performs it.

Have you played a lot in a query window hand typing any INSERT's or UPDATE's?? And of course lots of SELECT's so you can see the changes done by the INSERT's and UPDATE's??

You need to get familiar - really familiar - with the DB operation. I think you are in a tough spot writing code without a stronger foundation in the data side.

Quote:

---

You have to check your terminology - you do not put new rows in a database. A database contains tables (amongst other objects) and it is the table that receives a new row.

---

I will try to get better with the terminology.

Quote:

---

There is no need for any other ACTION QUERY after this - INSERT does just this simple task of putting a new row in table.

UPDATE will alter an existing row in a table.

ExecuteNonQuery is a method that takes an ACTION QUERY and performs it.

---

I did not know that I did not need to use ExecuteNonQuery after the INSERT, thanks

Quote:

---

Have you played a lot in a query window hand typing any INSERT's or UPDATE's?? And of course lots of SELECT's so you can see the changes done by the INSERT's and UPDATE's??

You need to get familiar - really familiar - with the DB operation. I think you are in a tough spot writing code without a stronger foundation in the data side.

---

Yes I have be doing some queries. But most of the are simple SELECT queries. I have not tried too many inserts and updates, but will start doing it more.

I understand that I need a better foundation. It's the reason I started the thread. I also know that I hardly no anything about databases because I only worked with 'one' my whole career before I retired.

I really don't mean this to be rude, but I think sometimes people don't remember what it was like when they first started learning about databases and didn't know a thing. Well, that's exactly where I am.

But I do thank you for your help, Sz. I know you are VERY good with databases and I appreciate your insights.

Quote:

---

Originally Posted by szlamany

...Have you played a lot in a query window hand typing any INSERT's or UPDATE's?? And of course lots of SELECT's so you can see the changes done by the INSERT's and UPDATE's??...

---

I have this real love / hate relationship with DB's. I do what you suggested, using the query window, before I write a single line of code. A couple of months ago I got access to a DB Admin person. She speaks DB better than I do VB. A real eye opener in how much I don't know.

I think a lot of people have that love/hate thing for databases.

Okay, I'm now able to do an update at the command-line. The one thing you must have (in the queries I did) is the ID value. I could change the first name in a row if I knew the ID. Which brings me to something that I've wondered about all along... In code, how do you know what the 'right' ID is?

edit: I can visualize how you can do it with a GridView because you have access to all of the information in a 'selected' row.

edit: if you don't know, I'm doing a console app

Quote:

---

Originally Posted by jumper77

I think a lot of people have that love/hate thing for databases.

Okay, I'm now able to do an update at the command-line. The one thing you must have (in the queries I did) is the ID value. I could change the first name in a row if I knew the ID. Which brings me to something that I've wondered about all along... In code, how do you know what the 'right' ID is?

edit: I can visualize how you can do it with a GridView because you have access to all of the information in a 'selected' row.

edit: if you don't know, I'm doing a console app

---

If your updating a row then you would have already retrieved the row from the database using a Select statement.

If you want to update customer '12345', Unique Identifier

Code:

---

select * from Customers Where ID='12345'"
make updates
Update .... Where ID = '1234'

---

If you don't know the ID to begin with then it's more complicated.

Code:

---

Select * from Customers Where LastName = 'Smith'
This could bring back multiple rows, so you need to cycle thru them
For Each row as Datarow in yourDataTable.Rows
    If "this is the Smith I'm looking for" Then
        row("address") = "new address"
        Update ... Where ID = row("ID").ToString
    End If

---

This is just a quick example, you could use parameters

Quote:

---

Originally Posted by jumper77

I did not know that I did not need to use ExecuteNonQuery after the INSERT, thanks

---

I did not say this - I actually kind of said the opposite.

ExecuteNonQuery is a .Net method.

UPDATE is a SQL Action statement.

INSERT is a SQL Action statement.

You execute these statements against a database by using ExecuteNonQuery.

You see - if you play a real lot in a management tool with doing an INSERT followed by a SELECT - each time hitting what probably looks like a little RUN button to do the execute - you will get a better understanding of the fact that you are simply throwing TEXT STATEMENTS at a parser who will do your command against the database.

ExecuteNonQuery is just the method used by a RUN button.

The context of the query window is what goes into CommandText.

But again - I have to say that using brand new .Net methods that you have no experience with leaves you in the spot of not knowing if the SQL is wrong or the CODE executing the SQL is wrong.

That's a burden you can eliminate by simply playing in some SQL query window.

[edit] matter of fact I see you are debating using a grid view or making a console app. Why not create your own little management tool - put a simply text box for entering some SQL. A Run button to do the EXECUTE - and a result window to show the results. This would make you a SQL expert without stretching into a bunch of new .Net areas.

Then once you have that mastered - SQL Expert yourself - then do a grid or something more complicated.

Just a suggestion - I don't want to step on sitten's toes here...[/edit]

wes4dbt is right:

If you want to UPDATE, you have to know the ID. Sometimes, like in a contact list program, you might've loaded the whole list into memory. Then you know every ID. Other times, like in a huge customer records system, you don't.

Think about how a program like this would work, though. People don't memorize IDs, they memorize things like customer names. So suppose Alice is an employee updating a record for the customer "Firestone". She'd do something like this:

• Type "Firestone" into some "customer name" search box.
• This would execute a SELECT query to list all customers with the name "Firestone". The SELECT would get the name and probably the ID.
• The results are displayed on the screen. She double-clicks one.
• This tells the program which customer she wants to edit. A new SELECT is issued, using the ID to single out a row. This SELECT gathers ALL of the details for the customer.
• She messes with the form that displays the data, then clicks "Save".
• This issues an UPDATE with the new information, using the ID that was fetched in step 2.

This is how most CRUD software tends to work. A user asks for some records, and internally you keep track of the IDs. When the user edits/updates records, you use that ID to tell the database which rows to modify.

So the workflow is almost always:

Code:

---

10 SELECT
20 (maybe) DETAILED SELECT
30 INSERT or UPDATE or DELETE
40 GOTO 10

---

DataAdapter follows a similar pattern with different words.

You give it a SELECT statement that picks out a lot of rows and their data (and also INSERT, UPDATE, and DELETE as needed.) This is sort of like the "detailed SELECT". Then you call Fill() to populate a DataSet with those rows and display them to the user somehow. The user makes edits, and you update the rows. At some point, you call AcceptChanges() on the DataSet, then Update() on the DataAdapter. This causes it to issue the correct INSERT, UPDATE, and DELETE queries on your behalf.

Quote:

---

If your updating a row then you would have already retrieved the row from the database using a Select statement.

If you want to update customer '12345', Unique Identifier

select * from Customers Where ID='12345'"
make updates
Update .... Where ID = '1234'

---

Wes, are you saying that you UPDATE a row where the ID is one less than than the SELECT query? And if so, is it because IDs are not zero based? (don't mean to be too dumb)
_________________________________________________________
SZ:

Quote:

---

That's a burden you can eliminate by simply playing in some SQL query window.

[edit] matter of fact I see you are debating using a grid view or making a console app. Why not create your own little management tool - put a simply text box for entering some SQL. A Run button to do the EXECUTE - and a result window to show the results. This would make you a SQL expert without stretching into a bunch of new .Net areas.

---

I wasn't exactly deciding on whether to use a console or form app. I was considering which one would be better as a learning tool. I already have the form app with a DataGrid on it. All the form does is load the database into the GridView when it first comes up.

I was leaning towards the console app because that eliminated the need to learn a lot about the GridView. But I'm also thinking that a form app would be more of a 'real world' type project, and you would have access to all the information based on user selection.
_________________________________________________________

Quote:

---

DataAdapter follows a similar pattern with different words.

You give it a SELECT statement that picks out a lot of rows and their data (and also INSERT, UPDATE, and DELETE as needed.) This is sort of like the "detailed SELECT". Then you call Fill() to populate a DataSet with those rows and display them to the user somehow. The user makes edits, and you update the rows. At some point, you call AcceptChanges() on the DataSet, then Update() on the DataAdapter. This causes it to issue the correct INSERT, UPDATE, and DELETE queries on your behalf.

---

Good information. I played with DataAdapter and DataSets in the very beginning. But just a tiny bit.

It's appearing that I must go back to my form app at this time. I don't think I can anymore without it. It needs to be a real world application. Not as complicated of course, but enough where I can get information from the DataGrid. I need to have access to the Grid to implement what we've been working on.

Now: I hope everyone is having a great day and life is treating you good :)

Quote:

---

Wes, are you saying that you UPDATE a row where the ID is one less than than the SELECT query? And if so, is it because IDs are not zero based?

---

No, sorry that was just a typo. You use the same ID for Select/Update/Delete

No problem my friend... You had me wondering.

Thanks

Quote:

---

Originally Posted by jumper77

Wes, are you saying that you UPDATE a row where the ID is one less than than the SELECT query? And if so, is it because IDs are not zero based? (don't mean to be too dumb)

---

This question leads me to believe that you still have a "my data is a variable" kind of association.

Data in a table is just like data in a spreadsheet (s/s). The idea of a primary key is to have one column in the s/s with a unique value for each row.

It makes logical sense to people like us to use a sequential number - with no duplicates (to satisfy the unique requirement).

Now why is it unique?

So that when you UPDATE a row it's going to always find the correct single row to update.

Since it's not an array of data - there is no off-by-one or starting at zero 'ness even remotely possible.

So with that said - you could have SSN as the unique value if you were doing a human resources type of system. Matter of fact our entire health care network (Anthem BC/BS-type world) still uses the SSN as the primary key identifier for there data.

Database design people of today realize that's a bad idea - since SSN's can be wrong and need to be adjusted - they make a poor unique id to identity Joe Smith.

So - just to reinforce this.

A table of data is a matrix - just like a spreadsheet - with rows and columns. You design each column to fill a particular need in describing the record of data in the domain of that table (a "customer" or a "patient" or whatever that table models in real life).

Now - once you get that matrix of data into your code you might be tempted to refer to the first row as #1 (or #0) - but that's only meaningful on your program side.

The "data" side - where the UPDATE is going to actually happen - only knows about each cell in each row of the spreadsheet that we are calling a table here.

Quote:

---

This question leads me to believe that you still have a "my data is a variable" kind of association.

---

I guess I do. I guess because what in to in code is manipulate data using variable names. It would be hard to think otherwise. But I do understand that it's real data that's being stored in variables. Tangible data. Other than the spreadsheet example. Can you give me the correct way to think about the data?

Quote:

---

Data in a table is just like data in a spreadsheet (s/s). The idea of a primary key is to have one column in the s/s with a unique value for each row.

---

Quote:

---

Now - once you get that matrix of data into your code you might be tempted to refer to the first row as #1 (or #0) - but that's only meaningful on your program side.

The "data" side - where the UPDATE is going to actually happen - only knows about each cell in each row of the spreadsheet that we are calling a table here.

---

I understand what you mean about referring to the first row being 0 or 1. But I really only thought of it when I was working on the admin tool. Since the 'unique' ID was always 0 (in the code), I tested the queries WHERE the ID was 1,2,3..... but in code, I think of it as a means to get the correct row.

There are many things I need to learn, and there's no way to understand it all in a single thread. Even if I had all the tools I needed in my brain, using the correct tools is usually not enough. There has to be an effort to make the 'correct' way of doing things implemented in a real life setting.

Let me know if you have that 'way' of looking at things that will help me understand things more.

Sz. I just thought of a question I wanted to ask. Do you 'ever' use a global piece of code to reference the database?

I would also like to ask something else... I use the code below to make the DataGrivd update itself. The code is something that I pieced together. But is it a good way to do it?

I imagine there's a much better way to do this.

Code:

---

    Public Sub RefreshData()
        'makes the gridview update after SQL updates, inserts, etc.
        Using objConn As New SQLiteConnection(connectionString),
              da As New SQLiteDataAdapter("select * from customer", objConn), dt As New DataTable
            da.Fill(dt)
            Me.dgvCustomer.DataSource = dt
        End Using
    End Sub

---

I'm not sure I see how that last post does any update - it's a SELECT to get data from the database into client code side.

As for you other question - I do not write traditional database applications. I've always created what would be called a 4GL - fourth generation language. When I ask for a grid of data I get back metadata first that tells my 4GL how to build the grid - what to call the columns - headings, width - special edit capabilities for cells. Actually before this metadata is more general meta data on how the screen itself is going to handle this record - maybe MODIFY is disabled because it's an historical record - stuff like that.

So in a way that's a much more global piece of code. It doesn't matter if I'm showing vendors to be maintained in a financial package, participants in a guardian ad-litem social workers scenario - bids for contractors to download for purchasing departments. Every single one of those user experiences is using a single routine to serve out the data to the GUI.

4GL's make me happy - I've done this on 4 different hardware/software platforms since 1980 :)

Each one lasted a decade plus!

Had to write out own database engines until we started using SYBASE (what MS purchased to make MS SQL decades ago - MS SQL is NOT a Bill Gates product!)

Quote:

---

Do you 'ever' use a global piece of code to reference the database?

---

I'll intercept, but I think szlamany will agree. This is a weasely question.

The right way to write .NET code is to write classes that either instantiate the objects they need or ask for them via parameters and properties. Programmers have been preaching the evils of globals/static for longer than I've been alive.

Sometimes, it's easier to just dump all your objects into properties on a Module. Then you don't have to worry about passing them around. "Aha," you'll chuckle, "I showed those fancy C# guys, I'm going to be done in half the time!" Sooner or later, you get in a bind. Something puts the object in an odd state, and since the Module property's referenced from umpteen different places, the only way you can figure out where to put breakpoints involves "Find References" and trial and error. It's much easier to manage objects with lifetimes if you pass instances around between instances.

I wouldn't say I 'never' use global database code. I don't think ADO .NET is a good fit for it, considering Command objects tend to have a lifetime of "one method call" and they're the workhorses. Maybe a globally-available connection. In a very small project. Where I'm going to be the only developer. But debugging's just easier if you stop leaning on Modules.

What I would much rather do, if I had a burning urge to use a Module, is write a Module that manages its own connection/command/data reader objects and returns types like Customer so the things calling it don't even know a database is there. For example, think about the difference between System.IO.File.ReadAllText() and using a System.IO.StreamReader. ReadAllText() is probably using a StreamReader inside, but it's easier to use ReadAllText() when you don't care about the details.

But I still don't like it. Static code cannot participate in a pattern called Inversion of Control that is critical for implementing unit tests. Whatever I make static, I can't test. So I don't make static code.

Quote:

---

Had to write out own database engines until we started using SYBASE (what MS purchased to make MS SQL decades ago - MS SQL is NOT a Bill Gates product!)

---

love it, love it, love it!!! I have been posting this for a while and no one ever commented on it. When I said I only worked on 1 database? It was SYBASE. I also mentioned how MS 'mysteriously' came out with SQL Server not long after that :) You're a good egg, I think I like you :)

Sorry... where was I...........

Quote:

---

As for you other question - I do not write traditional database applications. I've always created what would be called a 4GL - fourth generation language. When I ask for a grid of data I get back metadata first that tells my 4GL how to build the grid - what to call the columns - headings, width - special edit capabilities for cells. Actually before this metadata is more general meta data on how the screen itself is going to handle this record - maybe MODIFY is disabled because it's an historical record - stuff like that.

---

That sounds like a great way to go. And if you make your own 4GL, you should almost be able to do anything. The fact that you can use it as is in different product applications is really good. That has to be the ultimate in code reuse for databases.

Quote:

---

This is a weasely question.

---

??????? is that taking a shot?

I do understand that globals are not a good thing to do. I've known that from the days of C coding. But I have seen people in C++ shops that thought just 'one' object was so important that they used it as static/global. I don't share this thinking. If I try to do it, I already know that it is WRONG. I won't say I've never used one, but they were only a crutch to lean on until I understood what was going on and how to solve the issue without using globals.

post edited for content

I took your "global" reference to more or less mean "a single location for that logic" - as would be a class in modern .Net OOP jargon. Not using a module or something global/static.

Quote:

---

Originally Posted by Sitten Spynne

Sooner or later, you get in a bind. Something puts the object in an odd state, and since the Module property's referenced from umpteen different places, the only way you can figure out where to put breakpoints involves "Find References" and trial and error. It's much easier to manage objects with lifetimes if you pass instances around between instances.

---

Such a huge difference in requirements between client side code and backend methods.

When you write web applications - so you have a browser doing an AJAX (asynchronous) request to a server - you are asking the server to RUN a SINGLE FUNCTION.

That function will start and when it finishes it deliver a payload back to the browser.

So you aren't building up a class with references here and there for some long period of time.

You are actually doing something 180 degees opposite - you are wanting something to start and finish as fast as possible.

You can have dozens of people hitting the SAVE button at the same time in browsers all over carnation.

Goal is to load bare minimum logic into place - process request - leave the function.

There is no meaningful FORM LOAD moment where you instantiate a class and build it's layers and works it's methods.

It's - "I was asked to deliver a single recordset for this table with this primary key" - what do I have to do to accomplish that?

Since we have dozens of possible CONCURRENT requests we better make it multi-threaded (a nasty requirement of web backends) - and we better make it fast as possible.

When you have a traditional WIN FORM - you might create a class at the start of the app - do initial loads of all vendor names, for instance - and then be used for the duration of the UI running.

With a backend web method that server starts a thread for each request from each browser! Your program is running 50 times at once - all in the same memory space - but just once function (or different ones as browser requests flow in).

When you have a backend web method - if you want to make something "remembered" from one "request" to another "request" you have only one place to store it. So now all your "global" knowledge is put in a single place so having a class to hide and manage it doesn't have the same meaning as it would in this case. And you better not put too much into this "global - remembered" space (whatever too much is!)

Sure you keep your logic clean and singular - that's always a requirement of coding. If you code the same thing twice in two different place you deserve the pain you get back from that.

Mendhak told me a long time ago that I was destined for web programming - I could not imagine leaving win forms at that time.

Now I have this crazy freedom - my same backend .Net web methods talk to browser and Android apps that I write.

It has changed my life.

That and this second bottle of chianti...

____

Quote:

---

It has changed my life.

That and this second bottle of chianti...

---

______________________________

:p

Sz, when I said I did one database project, I didn't say but it was web based (sort of). No where near what you're doing by a long shot. But it still was fun. (here goes the old man again...) It was back in the day where there were no cell phones. Everybody used pagers. I wrote an email server for a company in Seattle. It would take emails, examine them and send it to peoples pagers, but the email still went to their company account. It also had to strip off any attachments before sending it to the pager, but keep the attachments for their regular mail account.

They wanted to have it so their people out in the field could respond to emails or know they needed to call the office. I was a fun project :)

I always liked those kinds of projects - a bit of hardware and a bit of software and it looks so good when shown at the end!

This is my last code of the night (thank goodness). Because of going back to the form app, the way I was doing things needed to be altered. I took Sitten's example of an insert and modified it to insert a new row into the database. I need to know if this looks ok or not. And if you're wondering, yes it works :) I worry that it's done in such a way that would make it harder on me as time goes by. Or make me think I should have done it another way... let me know what you think.

first, the sub itself:

Code:

---

    Public Sub addNewCustomer(customers As List(Of Customer), firstName As String, lastName As String, address As String, phoneNumber As String)

        Dim insertStatement As String = "INSERT INTO customer (firstName, lastname, address, phoneNumber)
                VALUES('" & ":" & firstName & "','" & ":" & lastName & "','" & ":" & address & "','" & ":" & phoneNumber & "');"

        Using connection As SQLiteConnection = CreateConnection()
            Using command As SQLiteCommand = connection.CreateCommand()
                With command
                    .CommandText = insertStatement
                    .Parameters.Add(New SQLiteParameter("firstName"))
                    .Parameters.Add(New SQLiteParameter("lastName"))
                    .Parameters.Add(New SQLiteParameter("address"))
                    .Parameters.Add(New SQLiteParameter("phoneNumber"))
                End With

                For Each customer In customers
                    With command
                        .Parameters("firstName").Value = customer.FirstName
                        .Parameters("lastName").Value = customer.LastName
                        .Parameters("address").Value = customer.address
                        .Parameters("phoneNumber").Value = customer.phoneNumber
                    End With

                    command.ExecuteNonQuery()
                Next
            End Using
        End Using
    End Sub

---

And here's the calling method

Code:

---

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles btnAdd.Click

        Dim db As New CustomerDatabase(databaseName)

        If txtFirstName.Text = "" Then
            MsgBox("First Name cannot be blank")
            Return
        ElseIf txtLastName.Text = "" Then
            MsgBox("Last Name cannot be blank")
            Return
        ElseIf txtAddress.Text = "" Then
            MsgBox("Address cannot be blank")
            Return
        Else
            If txtPhoneNumber.Text = "" Then
                MsgBox("Phone number cannot be blank")
                Return
            End If
        End If

        'build a customer object
        Dim customer As New Customer
        With customer
            .FirstName = txtFirstName.Text
            .LastName = txtLastName.Text
            .address = txtAddress.Text
            .phoneNumber = txtPhoneNumber.Text
        End With

        'add customer object to List(of Customer)()
        dbcustomers.Add(customer)

        db.addNewCustomer(dbcustomers, txtFirstName.Text, txtLastName.Text, txtAddress.Text, txtPhoneNumber.Text)
        MainDB.RefreshData()

        Close()
    End Sub

---

Quote:

---

I always liked those kinds of projects - a bit of hardware and a bit of software and it looks so good when shown at the end!

---

Yes, I love projects where the main application is a service. Nothing for anyone to critique. Oh, and a detail I left out... If the email did have an attachment, I would tell them it did at the end of the email I sent to the pager

If you have time and you're still around, take a look at the code I just did and tell me what you think

tanks

Ok everyone... I know this is one of those impossible things to do, but I really need to know how to do it if possible.

Is there ANY way to convert an Object to a string? I've tried every cast know to mankind, but with no luck.

Thanks, and happy Sunday!

There should be a .ToString() method on that object - have you looked at the intellisense list

Hi Sz, I have tried to use .ToString() but it still ends up with an Invalid Cast exception. I think that's what it says, but either way, it still has the exception.

And yes, I have looked at the Intellisense. I do that on almost anything that I can't get to work or something I'm really new at doing.

Then you must post your code - otherwise we are talking about something we both can't look at...

Always post code with a question like that - it gets an answer in half the posts :)

Took me a minute because I had to clean everything up...

Code:

---

    Private Sub DataGridView1_CellValueChanged(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvCustomer.CellValueChanged

        'build a customer object
        Dim customer As New Customer

        'add customer object to List(of Customer)()
        dbcustomers.Add(customer)
        db.Update(CType(dgvCustomer.CurrentCell.Value, Customer))

    End Sub

---

.

forgot, this probably helps. This is the definition of the method used in db.Update()

Took me a minute because I had to clean everything up... edit: cleaned up too much

Code:

---

    Private Sub DataGridView1_CellValueChanged(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvCustomer.CellValueChanged

        'build a customer object
        Dim customer As New Customer
        With customer
            .FirstName = txtFirstName.Text
            .LastName = txtLastName.Text
            .address = txtAddress.Text
            .phoneNumber = txtPhoneNumber.Text
        End With

        'add customer object to List(of Customer)()
        dbcustomers.Add(customer)
        db.Update(CType(dgvCustomer.CurrentCell.Value, Customer))

    End Sub

---

forgot, this is the method that I'm trying to send to (just the declaration)

Code:

---

  Public Sub Update(ByVal customer As Customer)

---

I think that's all

edit: all the things like txtFirstName.Text don't work. the values are not visible in this section.

Looks like you trying to put the cell value into a CUSTOMER object.

Seems that makes little sense since the CUSTOMER object must be much more complicated then being just a single cell value.

Do you have source that works you can look at - from another post here in this thread or just out on the interweb?

I had to edit the post and maybe you didn't see everything. I'm trying to send in the whole row. Plus it works in other sections of the code.

However, I did just think of something that might work.

nope, didn't work. I think the only way to get around it is just to write a different method.

I think I might be getting close to making this work. Here's what I'm playing with now:
The cast works for the datagrid. My thinking is, I want to send all the fields to

Code:

---

    Private Sub DataGridView1_CellValueChanged(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvCustomer.CellValueChanged

        'build a customer object
        Dim customer As New Customer
        Dim FirstName As String = CType(dgvCustomer.CurrentCell.Value, String)


        ''add customer object to List(of Customer)()
        'dbcustomers.Add(customer)
        'db.Update(CType(dgvCustomer.CurrentCell.Value, Customer))

    End Sub

---

edit: posted too soon

Ok How do I access all items in a row? I've tried indexing the grid, but can't

edit: for anyone watching this real time, stop laughing.

I'm going to stop posting because I'm probably driving you nuts. I did just figure out how to index the row though.

If you are using data binding, you don't write code like this at all. The data in the DGV /is/ the data in the DataSet.

That said, it's hard to tell. This code snippet uses variables like 'db' and 'dbcustomers', but you haven't showed us what those are. That's what szlamany means by "show working code", if we can't paste what you give us into a form with the right controls and reasonably expect it to work, we can't give you a lot of insight.

A DGV app usually works like (or it did years ago when I made it):

1. SELECT data into the DataSet.
2. Bind the DataSet to the DGV, via the DataSource property.
3. The user makes changes.
4. Provide some button, such as "Save". When it's clicked, call AcceptChanges() on the DataSet, then Update() on the DataAdapter.

If you update the database on every cell/row change, that can lead to problems in applications where the database is remote. Even with a file-oriented database, it can lead to issues. Suppose I want to update 5 or 6 columns of one row, and some error happens after the 4th and the application crashes. Now my row's in a weird state, half-updated and half-not. It's much better to let users do all edits, then submit them all at once, so they either succeed or fail, and can't be partially made.

Sorry for not providing enough code. Maybe this will help. It's in the CellValueChanged event of the gridview.

Code:

---

    Private Sub DataGridView1_CellValueChanged(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvCustomer.CellValueChanged

        Using objConn As New SQLiteConnection(db.CreateConnection()),
                da As New SQLiteDataAdapter("select * from customer", objConn), dt As New DataTable
            da.Fill(dt)
            Me.dgvCustomer.DataSource = dt
        End Using

    End Sub

---

When editing the gridview, pressing enter after making changes or leaving the cell after changes, fires the event. So I'm thinking it would be safer to update a single cell at the time it's been edited.

but how to I update the database using the datasource? I don't understand that part

Make sense?

I did try 'acceptChanges' by doing dt.acceptChanges() but I probably did do it right or something.

Here's the code when the form is coming up:
Does this help more?

Code:

---

Private Sub MainDB_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        If Not Exists(databaseName) Then
            db.CreateDatabase()
        End If

        Dim dgvCustomer = New DataGridView

        Try
            Using objConn As New SQLiteConnection(db.CreateConnection()),
                da As New SQLiteDataAdapter("select * from customer", objConn), dt As New DataTable
                da.Fill(dt)
                Me.dgvCustomer.DataSource = dt
            End Using

            SetAltColor()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

---

And here's the code on the CellChanaged Event

Code:

---

    Private Sub DataGridView1_CellValueChanged(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvCustomer.CellValueChanged

        Using objConn As New SQLiteConnection(db.CreateConnection()),
                da As New SQLiteDataAdapter("select * from customer", objConn), dt As New DataTable
            da.Fill(dt)
            Me.dgvCustomer.DataSource = dt
            dt.AcceptChanges()
        End Using


        'Dim celValue As String = dgvCustomer.CurrentCell.Value.ToString

    End Sub

---

I'm going to try this one more time. I apologize for being new at this because for one thing, it makes my terminology suck.

Here's what's going on...
________________________________________________
I have a datagrid on the startup form.
The grid is populated on startup by using a (SELECT * FROM database)
During this process, a dataset and data adapter are created
_________________________________________________

After the startup form comes up, a end user is allowed to alter cells
Originally I wanted to update the cells individually inside the: DataGridView1_CellValueChanged event

However, it was mentioned I should allow the user to change as many cells that they want, then commit the All changes. (that's cool)

THE END

So I put a button on the form that 'should' update the gridview, but it only reloads everything in the database.

I tried to bind the gridview to a datasource in design vew, but since I've never done that, I had no idea of what to select. So I moved on.

As suggested, I tried to use: AcceptChanges() on the DataSet, then Update() but it only load the values saved in the database. I tried it many different ways, but could not get it to work.

Here's how I tried to do that:

Code:

---

    Private Sub btnUpdateAll_Click(sender As Object, e As EventArgs) Handles btnUpdateAll.Click
        Using objConn As New SQLiteConnection(db.CreateConnection()),
                da As New SQLiteDataAdapter("select * from customer", objConn), dt As New DataTable
            Dim numRows As Integer = da.Fill(dt)
            Me.dgvCustomer.DataSource = dt
            dt.AcceptChanges()
            da.Update(dt)
        End Using
    End Sub

---

but again, it just reloads the grid from the database. I need to know how to write the current data in the gridview to the database, THEN reload the data into the gridview. If I knew how to do that, I would have NO problem.

At this point, I am frustrated. I have searched the net for hours and can't find anything I can use. If someone can tell a concept or understanding the I'm lacking, please let me know.

thanks

Is this close?... (it doesn't work)
However, It DOES do something. It makes the datagrid go blank.

Code:

---

    Private Sub btnUpdateAll_Click(sender As Object, e As EventArgs) Handles btnUpdateAll.Click

        Try
            Using objConn As New SQLiteConnection(db.CreateConnection()),
                da As New SQLiteDataAdapter("select * from customer", objConn), dt As New DataTable
                Dim ds As New DataSet()
                da.Fill(ds)
                dgvCustomer.DataSource = ds.Tables
                ds.GetChanges()
                ds.AcceptChanges()
                da.Update(ds)
            End Using

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

---

Of course it just reloads the data, because that's what your doing

Quote:

---

Private Sub btnUpdateAll_Click(sender As Object, e As EventArgs) Handles btnUpdateAll.Click
Using objConn As New SQLiteConnection(db.CreateConnection()),
da As New SQLiteDataAdapter("select * from customer", objConn), dt As New DataTable
Dim numRows As Integer = da.Fill(dt)
Me.dgvCustomer.DataSource = dt
dt.AcceptChanges()
da.Update(dt)
End Using
End Sub

---

I mean, read that code, what does it do, it loads a dataadapter, fills a DataTable, assigns it to the dgv datasource, then saves the data back to the database. That's all, nothing else.

If you want to send the updates from the dgv back then you need to change the scope of the DataAdapter and DataTable that you assign to the dgv.

Code:

---

Private Sub MainDB_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        If Not Exists(databaseName) Then
            db.CreateDatabase()
        End If

        Dim dgvCustomer = New DataGridView

        Try
            Using objConn As New SQLiteConnection(db.CreateConnection()),
                da As New SQLiteDataAdapter("select * from customer", objConn), dt As New DataTable
                da.Fill(dt)
                Me.dgvCustomer.DataSource = dt
            End Using

            SetAltColor()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

---

Change the scope,

Code:

---

Private objConn As New SQLiteConnection(db.CreateConnection()), da As New SQLiteDataAdapter("select * from customer", objConn), dt As New DataTable
 Private Sub MainDB_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        If Not Exists(databaseName) Then
            db.CreateDatabase()
        End If

        Dim dgvCustomer = New DataGridView

        Try
           
                da.Fill(dt)
                Me.dgvCustomer.DataSource = dt
            End Using

            SetAltColor()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

---

Then in the update function,

Code:

---

da.Update(dt)

---