[RESOLVED] Beginner trying to learn SQLite database

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.