[RESOLVED] Beginner trying to learn SQLite database

[jumper77]

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

[jumper77]

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!

[szlamany]

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

[jumper77]

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.

[szlamany]

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

[jumper77]

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

    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()

[jumper77]

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

    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)

   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.

[szlamany]

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?

[jumper77]

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.

[jumper77]

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

[jumper77]

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

    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

[jumper77]

edit: posted too soon

[jumper77]

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.

[jumper77]

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

[Sitten Spynne]

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.

[jumper77]

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

    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.

[jumper77]

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

 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

    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

[jumper77]

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:

    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

[jumper77]

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

    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

[wes4dbt]

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

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.

 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,

 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,

da.Update(dt)

[wes4dbt]

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

    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

[wes4dbt]

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.

[jumper77]

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.

[jumper77]

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

[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.

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

Here's the current state of MainDB_Show

   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

[wes4dbt]

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.

[wes4dbt]

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.

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

Here's the current state of MainDB_Show

   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.

[jumper77]

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

[jumper77]

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.

        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.

[wes4dbt]

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.

[jumper77]

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

[jumper77]

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

[wes4dbt]

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).

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

[jumper77]

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:

    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

[jumper77]

Found new issue. read next post

[jumper77]

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.

    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.

[jumper77]

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?

[Carlos Rocha]

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.

[jumper77]

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

[jumper77]

Thanks again Carlos. Works like a charm.

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