-
Jun 13th, 2016, 11:38 AM
#201
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
spoke too soon... need to change some things
-
Jun 13th, 2016, 12:37 PM
#202
Re: Beginner trying to learn SQLite database
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.
-
Jun 13th, 2016, 12:38 PM
#203
Re: Beginner trying to learn SQLite database
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.
Last edited by Inferrd; Jun 14th, 2016 at 06:52 AM.
Reason: talking bollox
-
Jun 13th, 2016, 12:44 PM
#204
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
@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
-
Jun 13th, 2016, 12:50 PM
#205
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
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.
-
Jun 13th, 2016, 12:55 PM
#206
Re: Beginner trying to learn SQLite database
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.
-
Jun 13th, 2016, 12:59 PM
#207
Re: Beginner trying to learn SQLite database
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?
-
Jun 13th, 2016, 01:15 PM
#208
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
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
-
Jun 13th, 2016, 01:50 PM
#209
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
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
-
Jun 13th, 2016, 02:21 PM
#210
Re: Beginner trying to learn SQLite database
Post your code. Does your code match my code? My code works.
-
Jun 13th, 2016, 04:37 PM
#211
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
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
-
Jun 13th, 2016, 05:19 PM
#212
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
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
-
Jun 13th, 2016, 07:18 PM
#213
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
What's with the Website?
edit: Yea! It's working again
Last edited by jumper77; Jun 13th, 2016 at 07:30 PM.
-
Jun 13th, 2016, 07:48 PM
#214
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
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
-
Jun 13th, 2016, 08:01 PM
#215
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
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
-
Jun 13th, 2016, 08:02 PM
#216
Re: Beginner trying to learn SQLite database
That row number must start at zero. It is code sided.
-
Jun 13th, 2016, 08:49 PM
#217
Re: Beginner trying to learn SQLite database
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
-
Jun 13th, 2016, 08:59 PM
#218
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
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
-
Jun 13th, 2016, 09:06 PM
#219
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
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
-
Jun 14th, 2016, 06:15 AM
#220
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
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
-
Jun 14th, 2016, 06:18 AM
#221
Re: Beginner trying to learn SQLite database
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.
-
Jun 14th, 2016, 06:26 AM
#222
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
Good thinking. I forgot about the name of the hidden row field. Will change it now.
-
Jun 14th, 2016, 06:43 AM
#223
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
Ok, changed the query string to:
Code:
"delete from customer where ID = " & dgvCustomer.CurrentRow.Cells
Now everyone is happy.
-
Jun 14th, 2016, 06:49 AM
#224
Re: Beginner trying to learn SQLite database
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?
-
Jun 14th, 2016, 06:57 AM
#225
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
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.
-
Jun 14th, 2016, 07:05 AM
#226
Re: Beginner trying to learn SQLite database
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.
-
Jun 14th, 2016, 07:12 AM
#227
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
Got to go for a bit. Will write when I get back.
-
Jun 14th, 2016, 10:27 AM
#228
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
Last edited by jumper77; Jun 14th, 2016 at 06:57 PM.
-
Jun 14th, 2016, 02:13 PM
#229
Re: Beginner trying to learn SQLite database
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.
-
Jun 14th, 2016, 03:03 PM
#230
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
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
-
Jun 14th, 2016, 03:06 PM
#231
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
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
-
Jun 14th, 2016, 04:25 PM
#232
Re: Beginner trying to learn SQLite database
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
-
Jun 14th, 2016, 04:29 PM
#233
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
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
-
Jun 15th, 2016, 01:56 PM
#234
Thread Starter
PowerPoster
Re: Beginner trying to learn SQLite database
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
-
Jun 15th, 2016, 03:23 PM
#235
Re: [RESOLVED] Beginner trying to learn SQLite database
If you want to close this thread, go to the top of the page and find Thread Tools->Mark Thread Resolved
-
Jun 15th, 2016, 03:34 PM
#236
Thread Starter
PowerPoster
Re: [RESOLVED] Beginner trying to learn SQLite database
thank you so much, but it already says resolved in the title (or subject line)
-
Jun 15th, 2016, 04:14 PM
#237
Re: [RESOLVED] Beginner trying to learn SQLite database
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?
-
Jun 15th, 2016, 04:39 PM
#238
Thread Starter
PowerPoster
Re: [RESOLVED] Beginner trying to learn SQLite database
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.
Last edited by jumper77; Jun 15th, 2016 at 04:42 PM.
-
Jun 15th, 2016, 04:47 PM
#239
Re: [RESOLVED] Beginner trying to learn SQLite database
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.
-
Jun 15th, 2016, 04:54 PM
#240
Thread Starter
PowerPoster
Re: [RESOLVED] Beginner trying to learn SQLite database
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|