[RESOLVED] Beginner trying to learn SQLite database

Quote:

---

Originally Posted by jumper77

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

---

---

No, not close. Read my previous post

Just to be clear, you have to use the original Datatable that you used as the dgv datasource, to
update the database. Because that is what contains all the data in the dgv.

Oops, I didn't know that you replied right before I posted. I was reading it when I saw there was another post. I know it looks like a mess, but I'm just throwing things against the wall to see if anything sticks. I think what you've posted should work and I'm going to give it a try.

Will let you know in just a minute.

Sorry, but the lady had me do a few things so I was AFK. I'm reading your previous message about using the same datasource right now.
Plus THANK YOU for the help, I was about to get drunk :)

Ok, still not having any luck and I don't know why. here's the code in the button save code. The code runs without error, but when the app loads, the values are not changed.

Code:

---

        dt.GetChanges()
        dt.AcceptChanges()
        da.Update(dt)

---

Here's the current state of MainDB_Show

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
                Dim numRows As Integer = da.Fill(dt)
                da.Fill(dt)
                Me.dgvCustomer.DataSource = dt
            End Using

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

---

I don't know if this has been explained to you before but there's an important concept of ADO .Net that you need to remember. ADO .NET uses Disconnected objects. Which means, that a DataAdapter retrieves the data from the database, puts it into a DataTable. The DataTable is not connected to the database. The database has no idea what your doing with the data. Any changes that are made to the data, is made to the DataTable, not the database. The only way to get the changes you made to the data in the DataTable back to the database is to use the DataAdapter.Update method on the DataTable you've been working with.

There's people here that can explain this better but I hope you understand what I'm saying.

Quote:

---

Originally Posted by jumper77

Ok, still not having any luck and I don't know why. here's the code in the button save code. The code runs without error, but when the app loads, the values are not changed.

Code:

---

        dt.GetChanges()
        dt.AcceptChanges()
        da.Update(dt)

---

Here's the current state of MainDB_Show

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
                Dim numRows As Integer = da.Fill(dt)
                da.Fill(dt)
                Me.dgvCustomer.DataSource = dt
            End Using

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

---

---

Well you didn't change the scope of the object like I showed you. They need to have a Form level scope.

Once your code hits "End Using" All those objects are disposed of.

Ok and thanks, let me change the load section.
brb

I'm trying my best Wes, honest. Here's where I have a question. You said what I had to do to write the info back to the database. Well, I thought the code below did what you were talking about.

Code:

---

        dt.GetChanges()
        dt.AcceptChanges()
        da.Update(dt)

---

If I only use: da.Update(dt) as you originally said, an exception is thrown. The error is:
{"Update requires a valid UpdateCommand when passed DataRow collection with modified rows."}

So when I put the first two lines back in it doesn't produce an exception, but it doesn't update either.

Any idea? I think I'm getting close.

Sorry but I sort of misspoke.

First, before you can use the DataAdapter Update method, you must create an UpdateCommand for the dataadapter. Have you done this? Also, your using a dgv to make the changes. I'll have to lookup how that's going to work with the dataadapter. I almost always use a TableAdapter and Bindingsource when I use a dgv to modify data. Let me see what I can find.

Ok great. No, I don't think I've done an updateCommand for an adapter.
Will wait til you come back.

Wes, thank you for all your help. I mean it. But I need to head to bed because I usually get up at 4 or 5 in the morning.
I'll check the thread in the morning.

Have a great night my friend

I'm not sure what Sitten has shown or explained to you about updating a database or if he even wants you to do your updates using a DataAdpter. So I'm not going to go further with this subject right now.
But here is a simple example of what I was talking about. Notice I created the Dataadapter UpdateCommand using an OleDbCommandBuilder (because I'm using an Access database).

Code:

---

Imports System.Data.OleDb
Imports System.IO
Public Class Form6
    Private con As New OleDbConnection(My.Settings.waterConnectionString), da As New OleDbDataAdapter("select crop, testfield from crops", con), dt As New DataTable
    Private cmdBuilder As OleDbCommandBuilder
    Private Sub Form6_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Try

            cmdBuilder = New OleDbCommandBuilder(da)

            da.Fill(dt)
            Me.DataGridView1.DataSource = dt


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

    Private Sub SaveButton_Click(sender As System.Object, e As System.EventArgs) Handles SaveButton.Click


        da.Update(dt)
    End Sub
End Class

---

Ok boys and girls, I have finally found the solution to this issue. Needless to say, I'm pretty darn happy at the moment. This code can save as many edits the user wants to make before saving! Yea!!!!

Here is the code that works now:

Code:

---

    Private Sub btnUpdateAll_Click(sender As Object, e As EventArgs) Handles btnUpdateAll.Click
        Using objConn As New SQLiteConnection(db.CreateConnection())
            da.SelectCommand = New SQLiteCommand("SELECT * from customer", objConn)
            Dim DbCommandBuilder = New SQLiteCommandBuilder(da)
            dt = CType(dgvCustomer.DataSource, DataTable)
            da.Update(dt)
            dt.AcceptChanges()
        End Using
    End Sub

---

Found new issue. read next post

I've found a new issue that's part of what Wes helped me with (note: what Wes did was good. I'm not complaining about anything he did) . He said I needed to change the scope of the adapter, data Table and Data Set And that's one of the keys to making it work now.

Here's the code that I put in the declaration section at the top of the class.

Code:

---

    Private objConn As New SQLiteConnection(db.CreateConnection()), da As New SQLiteDataAdapter("select * from customer", objConn), dt As New DataTable

---

There's just small side effect. When the app first comes up, it checks to see if the database exists or not. But now it NEVER creates a database no matter what. That's because the above declaration creates a zero byte file when the app first comes up.

So when the code checks to see if the database exists, it always returns true.

any ideas?

edit: as long as there's a good database already there, everything works great.

Right now, I'm trying to see if I can use the length of the database file to know when to create a new one. Even though explorer says it's zero bytes, it's actually 12 bytes. Does anyone know at what point explorer thinks the file to be greater than zero? I know they have some algorithm that says: if file is greater than 'x' bytes, make it a 1k file.

what do you think?

If the database is automatically created while connecting you must find a way to check if it's good for you, probably by checking their tables and create them accordingly.

What a great idea Carlos. I will try to implement that right now. I'll let you know what happens

Thanks again Carlos. Works like a charm.

Code:

---

        Using objConn As New SQLiteConnection(db.CreateConnection())
            Try
                db.GetAll()
            Catch ex As Exception
                db.CreateDatabase()
            End Try
        End Using

---

spoke too soon... need to change some things

If you want to do the create database stuff (which is just adding another level of complexity and right now just clouds the issue of Retrieving/Updating data), then just declare the objects at form level, then create them later,

Code:

---

Imports System.Data.OleDb

Public Class Form6
    Private con As OleDbConnection, da As OleDbDataAdapter, dt As DataTable
    Private cmdBuilder As OleDbCommandBuilder
    Private Sub Form6_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Try
            con = New OleDbConnection(My.Settings.waterConnectionString)
            con.Open()

            da = New OleDbDataAdapter("select crop from crops", con)
            dt = New DataTable

            cmdBuilder = New OleDbCommandBuilder(da)

            da.Fill(dt)
            Me.DataGridView1.DataSource = dt


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

    Private Sub SaveButton_Click(sender As System.Object, e As System.EventArgs) Handles SaveButton.Click


        da.Update(dt)
    End Sub
End Class

---

I don't understand why you don't make your code like my example, except using SQLLite.

If you don't want a database file to be created if one does not already exist when you try to open the connection, then add

FailIfMissing = True;

to the end of your connection string.

If you are using Sitten's CustomerDatabase Class, then add it to the end of the __ConnectionStringTemplate constant.


Edit: having played with SQLite a little longer, I now realise the above is utter rubbish. Please ignore.

@Carlos... Took me a minute but I have it now. Had to change things up some.

Code:

---

Using objConn As New SQLiteConnection(db.CreateConnection())
            Try
                Dim command As SQLiteCommand = New SQLiteCommand(objConn)
                command.CommandText = "Select * from customer"
                command.ExecuteNonQuery()
            Catch ex As Exception
                db.CreateDatabase()
            End Try
        End Using

---

When there's no data in the database, I create one. And it doesn't matter because there's nothing there.

But when it has data, the database is not created.

Bit of a fudge for now until I can find something better

Wow Inferrd, didn't know about that one. let me see if I can make it work. If it works, it's a lot better than the hack I made.

I see I cross posted with Wes. His advice will solve your problem in a more logical way. Just put your check for file/create db code before the code he shows in his Sub Form6_Load.

This might work,

Code:

---

    Private Sub btnUpdateAll_Click(sender As Object, e As EventArgs) Handles btnUpdateAll.Click
        Using objConn As New SQLiteConnection(db.CreateConnection())
            da.SelectCommand = New SQLiteCommand("SELECT * from customer", objConn)
            Dim DbCommandBuilder = New SQLiteCommandBuilder(da)
            dt = CType(dgvCustomer.DataSource, DataTable)
            da.Update(dt)
            dt.AcceptChanges()
        End Using
    End Sub

---

But again, all your doing is complicating the problem.

Doesn't my code seem much simplier?

Well I'll be darned... I could never get your code to work before, but it sure works great now. And YES it is much more simple than what I did. I'm going to stick with your code :)

Spoke too soon again. I have to apologize to everyone. I don't know why but I've been really out of it for the past 2 or 3 days. I sure hope I don't feel this way tomorrow.

Back to square one Wes. I didn't fully test your code because I didn't try to save anything to the database. I just tried a minute ago and got an exception

Here's the exception:
Update requires a valid Update command when passed DataRow collection with modified rows

If you think of anything, let me know.
thanks

Post your code. Does your code match my code? My code works.

Wes, I changed what I had in the _shown event to your code and it's working. I'm really not trying to drive you crazy. Just had some bad days in a row.

But now that it's working, I can put this one to bed.

Thank you and everyone for the help getting over this hump :)

I have what I think is an easy question. I have a method that creates alternating row colors. But it makes the grid load very slow. If I leave that method out, it loads a lot faster. I could understand the form/grid load time if I had hundreds of records, but I only have around 10.

Is there any way to use the colors without bogging the grid down?
thanks.
Color method below:

Code:

---

  Public Sub SetAltColor()
        With dgvCustomer
            .RowsDefaultCellStyle.BackColor = Color.Bisque
            .AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        End With
    End Sub

---

What's with the Website?

edit: Yea! It's working again

Now that things are working again, I have yet another question...

I have searched the net for a way to get the selected row, but all the examples (especially MSDN) were terrible

This should be a easy one. How do you get the selected row?

I've really searched the net a lot for this one

Just got it to work (I wish I could figure out everything this fast).

Here's the code that returns the selected row. However, why does it always return 1 less than the row you clicked on?

Code:

---

  Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvCustomer.CellClick
        Dim i As Integer
        i = e.RowIndex
        MsgBox(i.ToString)
    End Sub

---

That row number must start at zero. It is code sided.

Quote:

---

Originally Posted by jumper77

Wes, I changed what I had in the _shown event to your code and it's working. I'm really not trying to drive you crazy. Just had some bad days in a row.

But now that it's working, I can put this one to bed.

Thank you and everyone for the help getting over this hump :)

---

Well you drove me crazy enough to download and install SQLite because I couldn't figure out why you were having so much trouble. Plus the fact you didn't show your leads me to believe you aren't doing it correctly.
Here is a working example using SQLite

Code:

---

Imports System.Data.SQLite
Imports System.IO
Public Class SQLiteSampleForm
    Private objConn As SQLiteConnection, da As SQLiteDataAdapter, dt As DataTable, cmdBuilder As SQLiteCommandBuilder
    Private Const CONNECTION_STR As String = "Data Source= customers.db;Version=3"

    Private Sub SaveButton_Click(sender As System.Object, e As System.EventArgs) Handles SaveButton.Click
        da.Update(dt)

    End Sub

    Private Sub SQLiteSampleForm_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Try
            If Not File.Exists("customers.db") Then
                CreateDatabase()
            Else
                objConn = New SQLiteConnection(CONNECTION_STR)
                objConn.Open()
            End If

            da = New SQLiteDataAdapter("select ID, FullName from customer", objConn)

            cmdBuilder = New SQLiteCommandBuilder(da)

            dt = New DataTable
            da.Fill(dt)

            Me.DataGridView1.DataSource = dt
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub

    Private Sub CreateDatabase()
        '===================================================
        ' Create a new database and populate it with some data
        '===================================================

        Dim objCommand As SQLiteCommand

        Try
            'Create a new database connection
            'Note - use New=True to create a new database
            objConn = New SQLiteConnection(CONNECTION_STR & ";New=True;")

            'Open the connection
            objConn.Open()

            'Create a new SQL command
            objCommand = objConn.CreateCommand()

            'Setup and execute the command SQL to create a new table
            objCommand.CommandText = "CREATE TABLE customer (id integer primary key, FullName varchar(100));"
            objCommand.ExecuteNonQuery()

            'Insert a couple of records into the table
            objCommand.CommandText = "INSERT INTO customer (id, FullName) VALUES (1, 'John Smith');"
            objCommand.ExecuteNonQuery()
            objCommand.CommandText = "INSERT INTO customer (id, FullName) VALUES (2, 'Jane Jones');"
            objCommand.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

    End Sub


End Class

---

Quote:

---

Originally Posted by szlamany

That row number must start at zero. It is code sided.

---

Thanks Sz. I thought that's what it was, but I seem to remember someone (have no idea who) shook their finger at me when I started talking about that.

thanks

Hi Wes, Didn't mean to make you have to download SQLite <snickers>. I tried to send some code, but I do think I waited to long to give you the code I did. I looked at the code I have and it's pretty much exactly like yours.

And you know what the save method does because it's that one-liner :)

And thank you for all your help. Today I'm feeling really good, but the last few days have really sucked. Not sure what happened. I'm just glad it's over <whew!>

Here's the code just so you know...

Code:

---

  Private Sub MainDB_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        Try
            'if database does not exist or if database is zero bytes, da.Fill(dt) causes an exception
            da.Fill(dt)
        Catch
            'if exception: Create the database.
            db.CreateDatabase()
        End Try
        Dim Connection = New SQLiteConnection(db.CreateConnection())

        da = New SQLiteDataAdapter("select * from customer", Connection) : dt = New DataTable

        Dim cmdBuilder = New SQLiteCommandBuilder(da)  'needed for the one-liner save to database: THIS IS A MUST!!!

        da.Fill(dt)
        Dim ds As New DataSet("customer")
        Me.dgvCustomer.DataSource = dt

        'SetAltColor()
    End Sub

---

Good morning all. New milestone. I now have the 'DeleteRow' method working.

Code:

---

    Private Sub deleteRow()
        Dim DeleteString As String = "delete from customer where rowID = " & dgvCustomer.CurrentRow.Cells(0).Value.ToString()

        Using command As SQLiteCommand = db.CreateConnection.CreateCommand()
            command.CommandText = DeleteString
            Dim result As Integer = command.ExecuteNonQuery()
        End Using
        da.Update(dt)
        RefreshData()
    End Sub

---

We discussed a way back about how SQLLite had this unique kind of RowId field that was supposed to be hidden.

You should have created a PRIMARY KEY with a different name like "custID" so that you could fully appreciate that it is a customer id and not something to do with some arbitrary row placement.

If that is not hard to change I would recommend a change now.

Good thinking. I forgot about the name of the hidden row field. Will change it now.

Ok, changed the query string to:

Code:

---

"delete from customer where ID = " & dgvCustomer.CurrentRow.Cells

---

Now everyone is happy.

You went with just "ID" as the field name - I had suggested "custID" so that it had more of a "connection" with the domain of customer that this table represents.

If you have nothing against that I would suggest you change it to "custID" so that confusion with row numbers and 0-starting or 1-starting is less likely.

Regardless - I assumed you changed it in all the placed you needed to - not just this DELETE statement - right?

I will change ID to custID. And the thing about changing it in other places my be why now command.ExecuteNonQuery throws an exception. Thanks for reminding me about that.

Yes - be very careful making this change. You have to have a TABLE create where this field needs to be specified correcting. Insert statements - Update statements.

Got to go for a bit. Will write when I get back.

edited for content

Quote:

---

Originally Posted by jumper77

Good morning all. New milestone. I now have the 'DeleteRow' method working.

Code:

---

    Private Sub deleteRow()
        Dim DeleteString As String = "delete from customer where rowID = " & dgvCustomer.CurrentRow.Cells(0).Value.ToString()

        Using command As SQLiteCommand = db.CreateConnection.CreateCommand()
            command.CommandText = DeleteString
            Dim result As Integer = command.ExecuteNonQuery()
        End Using
        da.Update(dt)
        RefreshData()
    End Sub

---

---

If your using a dgv then there's an easier way

Code:

---

    Private Sub DeleteButton_Click(sender As System.Object, e As System.EventArgs) Handles DeleteButton.Click
        If Me.DataGridView1.SelectedRows.Count > 0 Then
            'you may want to add a confirmation message, and if the user confirms delete
            Me.DataGridView1.Rows.Remove(Me.DataGridView1.SelectedRows(0))
        Else
            MessageBox.Show("Select 1 row before you hit Delete")
        End If
    End Sub

---

Just click on the dgv row header (far left column) to highlight the row. Then click the delete button.

Actually, you don't even need a Delete Button. If you Highlight a row and press the Keyboard Delete key, it will delete the row.

Remember you have to save your changes.

That's a good way to do it. Let's me know more about the gridview too. I did have to make one change before it worked. I had to add:

da.Update(dt) after rows.remove

Because before that, it would 'look' like it was deleted, but would show back up with you brought the app back up.
Thanks a bunch

edit: oh.. that must be what you meant about saving changes

Clicking the Save button did work with da.Update(dt) taken out, but I think I will leave it in because it saves an extra button click :)

Quote:

---

Originally Posted by jumper77

I'm back... I've been checking and I think I have completed everything assigned for this project. So I had an idea that might be fun. What if we start a new thread for doing something like create our own freeware software? We could take everyone's input, combine it and see what we could come up with.

I have to say that this has been a life long dream for me. I have probably written over 100 programs that I wanted to make for freeware, but never finished because either I lost faith in it being something people would want or I created something that didn't fill a need.

So what do you think? If you think it's a bad idea, just say so. But I think with all of us thinking, we should be able to come up with an idea that would work. I understand that all of you code for a living, but I don't and could devote myself to a project full time.

Guess I've said enough for now. Let me know something.

edit: plus it would give my boss Sitten a well deserved break :)

edit2: I just thought about something. I imagine that everyone would want to use SQL Sever instead of SQLite, so how easy would it be for me to transfer what I know from SQLite to SQL Server?

edit3: One thing to think about. SQLite would be the easiest one to put on someone's machine. No install whatsoever. Plus there's a NuGet package (that I use) that puts everything you need for a program in the debug folder (or release) Just saying.....

BUT, I already have one vote for SQL Server :)

---

Don't mean to hurt your feeling but I think your quite new to VB to be able to develop useful shareware that hasn't already been created. But if that's what you want to do, knock yourself out.

In the mean time, you've learn how to display/Add/Edit/Delete data in a Datagridview. How about taking the next step. Select a row from the dgv and display the complete Customers details in a second form. This is a fairly come scenario. A lot of the time you don't want the operator to edit data directly in the dgv. A couple of reasons would be, to much information for one line, it's easier to do more complex data validation using TextBoxes.

Just a thought

Quote:

---

Don't mean to hurt your feeling but I think your quite new to VB to be able to develop useful shareware that hasn't already been created. But if that's what you want to do, knock yourself out.

---

That's cool. Thanks for letting me know :)

Hi everyone. I think it's about time that we close this thread. It's long enough :)
I want to thank all of you for your help. All of you out there a good people and I like that. It's nice to see other developers extend a hand to help someone. I will follow your lead when I can.

Thanks

If you want to close this thread, go to the top of the page and find Thread Tools->Mark Thread Resolved

thank you so much, but it already says resolved in the title (or subject line)

Ah, you most have marked it while I was responding. Have you chosen what you want to do next or are you going to take a break?

Really good to hear from you Wes :)
I don't know what's next at the moment. I am certainly open to suggestions if you can think of something.
One of the things I've always wanted to do was be able to write themes for winForms. I know it could be pretty difficult, but I'm stubborn and have plenty of time <smiles> Besides, I could start off real simple and do one thing at a time. But I do think people here would love the thread.

I have used some in applications and I always liked them. I know there are some trade offs, I've heard that when you do that, VB no longer becomes portable. It won't be able to run in another OS.

So there you have it... "my deep and darkest desires" - Do you ever watch that show?

edit: I know there are other "theme" software out there, but there are more bad ones than good ones. Some of them look so "old school". I would like to do something that would fit in with the Windows 10 style.

I don't even know what "write themes for WinForms" means. :)

But there's probably people here that know how to do it, good luck.

I know your kidding me... But as much as I would like to do it... I always think, why reinvent something when there are many out there you can use. So, I still really don't know what's next and I'm STILL open to ideas. And I beat you have some. If so, maybe we can talk about them?

edit: Maybe it would be good to continue on to the next level of the database path.