[RESOLVED] Beginner trying to learn SQLite database

Hi everyone, I've heard about people using lists with databases and would like to learn how to do that. I would also like to use SQLite as the database because SQL Server would be overkill for what I need.

I know it's tough to post a thread like this, because so many people have their own way of doing this. But I'm going to give it a shot.

thanks a bunch.

edit: if SQLite is a bad way to go, I'm cool with that.

edit2: I just read where someone suggested not using a database at all (if it's just a small amount of data). Not sure how you would store the information though. If I knew the format, I could save information in an XML file. I already have a XML file that contains the records I want, so that might be a good way to go.

Well, there are several ways to store data. One way is to store everything in text files. You can move up to encrypt them. XML files are good too, but are tedious to work with IMO. Access DB is another small solution, but has been depreciated using that over the years (even though I still use it a lot for my projects). Moving to SQLite is the next step up, and there are literally hundreds of examples that are easy to find.

It really depends on what you want to eventually use it for. The easiest thing I can suggest is to make a new project for new concepts you want to use so you will get a better grasp of how to handle everything. Then you can plan and port that functionality into a project.

Hi Recoil, thanks for replying. Quick question about encrypting the text files. I already have an encryption routine, so that's not a big deal. You talk about using Access for a project, I would like to (even though people advise against it). However, I haven't found a version of Access that's free. I used it in shops before, but they already had licensed copies.

Thanks about the POC idea. It might become ugly code, but I would at least understand the basics.

thanks

I missed the question about the encrypted text files. And no, Access is not free, I just have experience with it from school, like 10 years ago, LOL.

If you have experience with Access already though, SQLite would be the best route for a flat-file DB because you already understand how to query with SQL functions.

I'd strongly recommend against Access. It's not free, there's some pitfalls getting it to play nice in the presence of x64 machines or versions of Windows newer than XP that vary wildly depending on circumstance. A lot of the world's moved on to SQLite, but Access was the de facto solution for a long time so there's lots of VB tutorials about it, which tends to lead people to believe it's the only solution.

There's some good bickering that could be had about if SQLite is "a good database", but for the purposes of "learning database access" there's nothing that presents a problem.

"Learn to use lists with databases" is a very vague question. I think you'll get the most traction if you try to implement some program that uses a database for storage, then ask questions when stuck or, if you finish, ask for comments about how you designed it.

Database questions are much tougher to field, as they ask the person on the other end to do some setup or you have to distribute a sample database file.

Hi S, good to hear from you.

Quote:

---

There's some good bickering that could be had about if SQLite is "a good database", but for the purposes of "learning database access" there's nothing that presents a problem.

---

I have read a lot about SQLite and think it might a good way to go. But I know that people who work in an MS environment might not approve.

Quote:

---

"Learn to use lists with databases" is a very vague question.

---

Quote:

---

I think you'll get the most traction if you try to implement some program that uses a database for storage, then ask questions when stuck or, if you finish, ask for comments about how you designed it.

---

Thanks for the suggestion. I'm sure that's a better way to go about this.


And the reason I talked about using Lists with databases is because I can't remember something I read about it. So yes, it's vague to me too. Maybe what I was looking at were classes to deal with databases and using the right tools for storing data inside the software before it's written back to the database.

Quote:

---

Database questions are much tougher to field, as they ask the person on the other end to do some setup or you have to distribute a sample database file.

---

Ok. I understand that. So I will have a go at doing it myself before asking questions.

thanks

I believe this issue is resolved. Thanks to everyone who helped.

I mean, conceptually, there's a lot of things to approach.

Your program talks about its data in terms of the objects that represent the data. There are Person and Invoice and Customer classes, because those are what the code manipulates. I'm going to call these "domain types", that's a common professional term for them.

When we write to a file, we have to find a way to represent those objects, usually as "lines" or "an XML element". To assist with this, we tend to write "in between" code that knows how to turn "an Invoice" into "XML" and vice versa. This is sort of like how France and Spain share a border but do not speak the same language, so to interact they need people who speak both languages.

The database only knows how to talk about tables and rows. When you execute a SQL SELECT statement, what you get back is esentially a giant, comma-separated text file where each line represents a row. If, for every result set, you had to write code to parse that text into the relevant objects, you could. But it would be a pain. So we use a translator. ADO .NET acts as the translator. It interprets the text and returns a DataReader (maybe an IDataReader, I'm not sure.), which is a type that represents the text as many rows separated into columns. It even goes so far as to parse the text values into appropriate .NET data types, when the column's type information allows it. Sadly, since it's a very old .NET API and predates Generics, it operates in terms of Object variables so you have to mind your casts. There are many ADO .NET tools that try to help you further by creating DataSets or DataTables that take the metaphor even further, trying to give you something as convneient as possible.

But, you might notice, none of those constructs are Invoice, or Customer, or any of the objects that our program knows how to deal with. So while ADO .NET takes the burden of a lot of text parsing away from us, we still have to implement code to convert those ADO .NET objects like DataTable and DataReader into the relevant domain types. Many, many programs don't take this extra step. For small enough programs, it's fine. But I find my code works overall better if I do take that extra step.

When I write code, I draw two lines in the conceptual sand:

Code:

---

                            |  In between, I convert from  |
On this side, I only use    |  DB types like DataReader to | On this side, I only use
domain objects like Invoice. |  and from domain types like  | db types like DataReader.
                            |  Invoice.                    |

---

Those are high-level concepts to keep in mind, and I want you to get there. But first, you need to learn the basics.

I think your first steps should be to focus on just loading and saving some random objects. Learn how to create a connection. Learn how to execute INSERT, SELECT, UPDATE, and DELETE queries. Learn the difference between INSERT, UPDATE, and how to figure out which you need. Learn about SQL parameters, and get used to using them early, in fact you should endeavor to never write queries without using them.

We call a basic database editing app 'CRUD'. It stands for "Create, Remove, Update, Delete". Ignoring all concerns for architecture, you should work at the above concepts (and ask questions when stuck) until you can write two or three different CRUD applications without needing help from the forums. (It's OK if you have to look up documentation, or re-read the forums. Finding answers ourselves is as good as knowing the answers.) What's an example of a CRUD application? The contact list we did before is one. A check register's a good one. A to-do list is a good example. Practically everything we do involves CRUD in some form. Don't bother with 'fancy', it will slow you down. You're trying to learn, here.

When you can write a functional CRUD application, you're operating at a level where you can start asking questions about writing a good CRUD application.

Thanks for the help. It's very much appreciated. I will take your suggestions and start just trying to understand then design (loosely) the database. I'll pop up with a question when needed.

getting to work now :)

I'm proud to announce that after hours of work, I have SQLite working in Visual Studio. I haven't done much yet, but I can create a database. Everything from here will be smooth sailing compared to setting everything up.

edit: On Hold for now

Hi everyone, I have just started working with databases and for the most part, I'm clueless. I'm trying to do an INSERT statement and somehow get the results (row with values?).

I did post this in the Database forum, but haven't heard a word in hours.
thanks for looking

Here's the code:

Code:

---

      'Declare the main SQLite data access objects
        Dim objConn As New SQLiteConnection
        Dim objCommand As New SQLiteCommand

 Try
            objConn = New SQLiteConnection(CONNECTION_STR & "New=True;")

            objConn.Open()

            objCommand = objConn.CreateCommand()

            objCommand.CommandText = "Create TABLE customer(ID Int PRIMARY KEY Not NULL,
                firstName Text Not NULL, lastName Text Not NULL, phoneNumber Text Not NULL, Birthday Text Not Null);"
            objCommand.ExecuteNonQuery()

            objCommand = objConn.CreateCommand()
            objCommand.CommandText = "INSERT INTO customer (ID, firstname, lastName, phoneNumber, birthday)
            VALUES(1, 'Paul', 'Jones', '222-6666', 'May 16 1954');"

            objCommand.ExecuteNonQuery()

        Finally
            'close the connection
            If Not IsNothing(objConn) Then
                objConn.Close()
            End If
        End Try

---

Quote:

---

Originally Posted by jumper77

edit: On Hold for now

Hi everyone, I have just started working with databases and for the most part, I'm clueless. I'm trying to do an INSERT statement and somehow get the results (row with values?).

I did post this in the Database forum, but haven't heard a word in hours.
thanks for looking

Here's the code:

Code:

---

      'Declare the main SQLite data access objects
        Dim objConn As New SQLiteConnection
        Dim objCommand As New SQLiteCommand

 Try
            objConn = New SQLiteConnection(CONNECTION_STR & "New=True;")

            objConn.Open()

            objCommand = objConn.CreateCommand()

            objCommand.CommandText = "Create TABLE customer(ID Int PRIMARY KEY Not NULL,
                firstName Text Not NULL, lastName Text Not NULL, phoneNumber Text Not NULL, Birthday Text Not Null);"
            objCommand.ExecuteNonQuery()

            objCommand = objConn.CreateCommand()
            objCommand.CommandText = "INSERT INTO customer (ID, firstname, lastName, phoneNumber, birthday)
            VALUES(1, 'Paul', 'Jones', '222-6666', 'May 16 1954');"

            objCommand.ExecuteNonQuery()

        Finally
            'close the connection
            If Not IsNothing(objConn) Then
                objConn.Close()
            End If
        End Try

---

---

Earlier post said you create the database already, why are you creating a table at run time.

Code:

---

I'm trying to do an INSERT statement and somehow get the results (row with values?).

---

Not sure what that means, do you want to get back the values you just inserted? You already have them. If you want to retrieve data from a database then you would use a SQL "Select" statement.
Here is a post by jmc thats a good way to start. http://www.vbforums.com/showthread.p...ses&highlight=
Don't worry that it's use an SQL database, most of the SQL is the same.

If you search for "visual basic ado net tutorial" there's lots of information

Hi Wes, good to hear from you.

Quote:

---

Earlier post said you create the database already, why are you creating a table at run time.

---

Actually when I said I created the database, I meant at run time. It's only called if the database does not exist.

Quote:

---

Not sure what that means, do you want to get back the values you just inserted? You already have them. If you want to retrieve data from a database then you would use a SQL "Select" statement.
Here is a post by jmc thats a good way to start. http://www.vbforums.com/showthread.p...ses&highlight=
Don't worry that it's use an SQL database, most of the SQL is the same.

If you search for "visual basic ado net tutorial" there's lots of information

---

I have only just come to think that I should try a select statement outside the the 'CreateDatabase()' sub. I'm thinking (and hoping) that it will work then.

And yea, I did hear from jmc, from my post in the database forum.

And for the last, I'll do more searching for tutorials. But I've been searching all day long for anything you can think of <laughs>.

This should keep you busy for a while. https://msdn.microsoft.com/en-us/library/jj943772.aspx

One bit of advise, START SIMPLE. I've noticed you like to run before you can walk. You'll end up spending less time if you start simple and once you understand the basics, then start building in more complex code.

Thank you wes, and yes I'm guilty running. Probably has to do with my years of experience before I retired. And yes, I will start simple. I get ahead of myself and forget about 'baby steps'.

I really appreciate your help. Looks like I've go some reading to do :)

Damn, just found out that my first form has lost all it's components. That will probably fix the issue.

I've spent enough time on this trying to make it work, and I give up. So I'm asking questions. The code below is taken from a MSDN project where they tell you what code to insert where. But this one has problems. I get an error on every piece of code that's bold. Also, if it helps, this code was taken from the MSDN link that Wes gave me.

And there are two more classes that are doing the same exact thing.

First is InitializeComponent. Error is: x is not declared and may be inaccessible... yada yada
Next error type is on all the buttons. Error is: handles clause requires WithEvents variable

I know these errors are caused by something simple, but I don't know what that is. I've never had an error on a Button Click before.

Any help appreciated.
thanks

Code:

---

Namespace SimpleDataApp
    Partial Public Class Navigation
        Inherits Form
        Public Sub New()
            InitializeComponent()
        End Sub

        ' Open the NewCustomer form as a dialog box, which will return focus to the calling form when it closes.
        Private Sub btnGoToAdd_Click() Handles btnGoToAdd.Click
            Dim frm As Form = New NewCustomer()
            frm.Show()
        End Sub

        ' Open the FillorCancel form as a dialog box.
        Private Sub btnGoToFillOrCancel_Click() Handles btnGoToFillOrCancel.Click
            Dim frm As Form = New FillOrCancel()
            frm.ShowDialog()
        End Sub

        ' Close the application, not just the Navigation form.
        Private Sub btnExit_Click() Handles btnExit.Click
            Me.Close()
        End Sub
    End Class
End Namespace

---

If this is from the link that wes supplied my guess would be that you skipped some steps, like this one https://msdn.microsoft.com/en-us/lib...andaddcontrols . If your project doesn't have three forms named like they point out then you did skip steps.

When MS gives in depth walk-throughs you can't skip any steps. My advice is start over.

Hi db, I found out what the problem was. For reasons unknown, two of my forms lost all there controls (buttons and fields). But thank God for source control. I checked out my last commit and got two of the forms back. I'm working on the third form now.

I'm sure that's what's causing the issue. Will let you know something when I can. But I will be gone for a while today and may not be able to post again for a while.

Well, I've got all of the above errors corrected. Moving on to the last part of the UI.

And db, you have so little faith in me. <sigh> Debugging happens to be something I'm pretty good at. And while I'm not that good at OOP, debugging hasn't changed. So my couple of years experience pays off sometimes.

It is not about faith, but what wes mentioned, running before walking. You have a lot of visual studio problems that I don't understand and have never encountered. In 10+ years of writing VB code I can count on one hand how many times I have had major issues with VS. That could be because I write really simple things.

When you get the walk through done your debugging skills will show you what is going on.

Yes, and I told Wes that he was correct about running before walking. Wes has been very helpful. If fact everyone has been helpful, but the only thing you've said is:

Quote:

---

If this is from the link that wes supplied my guess would be that you skipped some steps, like this one https://msdn.microsoft.com/en-us/lib...andaddcontrols . If your project doesn't have three forms named like they point out then you did skip steps.

When MS gives in depth walk-throughs you can't skip any steps. My advice is start over.

---

What you said doesn't sound like you were trying to help. I won't say what it sounds like because I don't want to respond in kind. But I do appreciate your support.

Quote:

---

Originally Posted by jumper77

Yes, and I told Wes that he was correct about running before walking. Wes has been very helpful. If fact everyone has been helpful, but the only thing you've said is:

What you said doesn't sound like you were trying to help. I won't say what it sounds like because I don't want to respond in kind...

---

OK. I'll make sure there is no confusion in the future.

db, I have a question... (or you to Wes). One of the main points in this exercise was to be able to use SQLite instead of SQL Server. The walk-through I'm working on is made to use SQL Server. SQL Server would require learning something else I don't know about. Is it best just to learn SQL Server while I'm at it?

thanks.

Guess everyone's out having fun :)
Looks like I'm going to learn SQL Server.

Well - that ends up being a question for you and an important one before we all get invested in a thread.

You started this thread with a title about SQLite (I guess from another thread with JMC).

Somehow you got on a MS tut about MS SQL - different product.

The difference between using SQLite and MS SQL are probably minimal.

Installing MS SQL and SSMS (Management Studio) is a whole new kind of pain-in-the-arse.

SQL server costs money, requires setting up (at least a local) server instance, and is far more complex. If you walk down that path, I'm mostly going to be unable to help.

On the other hand, for the most part, SQL is just SQL. The main differences between SQLite and MSSQL to a .NET developer will be:

• You use classes that behave the same but have different names, like "SqlConnection" instead of "SqliteConnection".
• You'll have to figure out the new connection string format.
• The placeholders for query parameters will likely be different.

There's a handful of other things, but SELECT, INSERT, UPDATE, and DELETE are basically the same on every DB engine.

Thanks so much, sz and S. Yes, the main point was to use SQLite. I had it working on another application, but still needed to find out a few things. Wes was trying to help by giving me the link to the database walk-through. But because of my usually "thick" head, I didn't think that it would lead me to SQL Server. I should have known though...

And S, I'm going to go back to the 'little' things like you said. Just learning the normal stuff (select, insert, update, and delete). Setting up SQL Server is not an easy thing and not what I had in mind. SQLite is easy and even easier to deploy (if I were going to do that).

Oh.... I'm using a wrapper (I think that's the right thing to say in this case) called SQLite.Net. It's supposed to be an ADO version of SQLite.

So I'm going back to square one. Take my time, and do baby steps (instead of running). Thanks to you both for putting me back on track.

And Wes and db, thank you for helping. I really appreciate it. Don't think any of the message is intended to put you in a bad light, because I did learn some things :)

thanks to everyone...

The walkthrough was meant to show you the important basic elements of working with a database. It doesn't matter if it's a SQL db or a SQLLite db.

I my opinion you need to learn these basic tasks,

1. How to connect to a database
2. How to Add records
3. How to Update records
4. How to Delete records
5. How to Retrieve records

I've written database applications in BBX, dBaseIII, Clipper, C+, VB3-VB6, VB .Net and it always come down to understanding those 5 elements.

Hi wes, you are correct about the things I need to learn. I'm working on the things you point out right now, but I've gone back to SQLite. I think throwing SQL Server in the mix was a bit much for what I wanted to learn. Plus I'm using SQLite.NET which is an ADO version of it.

I AM taking this seriously and will work on 1 through 5 for a while. Just that, is enough to keep me busy for quite some time.

And Wow... you were back in the days I was. I never expected to see dBaseIII or Clipper mentioned on the forum. When I was in college, I used to work in the computer lab. At night I would help students with their homework, and that included dBaseIII and dBaseIV.

Quote:

---

Originally Posted by wes4dbt

2. How to Add records
3. How to Update records
4. How to Delete records
5. How to Retrieve records

---

Commonly referred to as CRUD - create, read, update and delete

It should also sound like familiar advice:

Quote:

---

I think your first steps should be to focus on just loading and saving some random objects. Learn how to create a connection. Learn how to execute INSERT, SELECT, UPDATE, and DELETE queries. Learn the difference between INSERT, UPDATE, and how to figure out which you need. Learn about SQL parameters, and get used to using them early, in fact you should endeavor to never write queries without using them.

---

SQL parameters are a MUST- 100% of the time - don't skimp on this one...

Since it is a security concern, being able to say you use ONLY SQL parameters in a program - 100% of the time - is a good thing. Red flag otherwise...

My database classes actually query the DB for the parameters of SPROCS at runtime - nothing is known in advance or hardwired in code.

Hi guys, I see you both approve of using parameters. I haven't read anything about it, but I've seen it mentioned. I'm glad you caught me early on this too. I just now opened a database and used INSERT, and for the first time, it worked :) Before I didn't know if it worked at all because the SQLite admin program doesn't show data. Only tables and fields.

But I just now learned how to use the command-line query tool and can verify that the values ARE in the database. So I'm going to check out parameters. May have some questions about that too because I have no idea of how to use it.

sz, I had to look up SPROCS (plus add it to my spell checker). I remember a long time ago that ads for a db person always mentioned stored procedures as a must for getting hired. I'm guessing it's still that way.

Once I get through all the CRUD (makes it sound like it came from a Linux person), I'll checkout parameters and SPROCS.
Plus, I'm all for not repeating code.

I think this time I'm going to take a different approach to see how it goes. I'll still answer questions. But occasionally, I'm going to pick one step of the process and explain it as thoroughly as I see fit. And if a question comes up about that topic again, I'm going to link to that post if I feel like the answer is contained within. This time there's no challenges, other than "If you really learn what this thread will be about, you can write any application that uses databases, albeit perhaps with some effort."

Table of Contents

• Lesson 0: SQL/SQLite introduction. CREATE TABLE, SELECT, INSERT, UPDATE, DELETE. Link to the syntax pages/SQLite documentation.
• Lesson 1: NuGet, setup, IDbConnection.
• Lesson 2: IDbCommand, focus on ExecuteNonQuery(), ExecuteReader(), ExecuteScalar(). Attached example of a working application with a layer of abstraction.
• Lesson 3: IDbParameter, parameters in general, stern advice to only use parameters from now on.

Lesson Zero - The one that's currently off-topic.
For the first topic, let's make sure you're familiar with what a database is, or, more specifically, a "relational database", which is how most SQL databases call themselves. Lacking these fundamentals makes it sort of moot to figure out how to use VB .NET to access the database.

No one's asked this question. No one's suggested this discussion. I think it's important, because if you can't write SELECT, INSERT, UPDATE, and DELETE queries then you won't get very far with ADO .NET. If you can't fire up a command-line SQLite client to peek at your tables, it will be harder to debug. (Optional: find a GUI client, but you can't really escape having to write SQL.) And I don't know exactly how much you know about these things, so I think a whirlwhind tour of the very basic concepts of SQL will do someone, somewhere, some good. Don't want to read it? Move on and wait until I actually talk about ADO .NET.

So for this part, you don't need Visual Studio. You can play along if you have some tool that lets you edit/manipulate a SQLite database. But I'm using the command-line client that comes with most SQLite installs. If it's on your PATH, you should be able to just type 'sqlite3' plus a filename to get busy. Try that out. If it doesn't work, make sure you have a sqlite3.exe somewhere on your machine. If you don't, you should probably get the SQLite Tools for Windows from the downloads page and make sure the right folder's on your path. Or, if you have a GUI tool you like that can execute SQL, use that.

It also helps to have the syntax page ready at all times. SQL is generally the same in all databases, but sometimes there are tiny differences.

All databases are fancy ways to store data in files. Relational databases represent data as tables. Tables are made of columns and rows. A 'column' represents some property of a data item, and usually has a type. A 'row' represents an entire data item. This is analagous to objects in OOP: a table is like a list of some class type. The columns represent the properties of the class. The rows represent instances of the object.

In SQLite, 'type' is a very loose concept. A column can have a type, but SQLite doesn't get too angry if you put the wrong type of data in the column. So even if you define a column as numeric, you can put a String inside of it. This can be really convenient if you're in a hurry and prototyping. It can also be a disaster later in a project. Thankfully, ADO .NET's Object-focused API helps us remember we can't trust the data types of columns. (This is one of the few times I'll praise this decision.) But do remember: the onus is on YOU to make sure you put the right data in the right column.

To create a table, we use a CREATE TABLE SQL statement. It generally asks you to define a name for the table, then a list of columns with types and any 'constraints'. The types that matter, for now, are INTEGER, REAL, and TEXT, and you can imagine what they stand for. Two other things can matter:

• AUTOINCREMENT means an INTEGER column will be automatically be given a value one greater than the last value that was assigned.
• PRIMARY KEY means a column represents the 'index' of the row, it must be unique.
• INTEGER PRIMARY KEY is automatically AUTOINCREMENT, so it's actually a syntax error to try and make AUTOINCREMENT INTEGER PRIMARY KEY.

Those two usually go together, because an AUTOINCREMENT INTEGER column is going to be unique.

So here's a table for some imaginary Customer type with a first name, last name, and phone number field:

Code:

---

CREATE TABLE customers (
  Id INTEGER PRIMARY KEY,
  FirstName TEXT,
  LastName TEXT,
  PhoneNumber TEXT);

---

The Id column is important: if we don't have a way to uniquely define which row we're talking about, editing or deleting them can get ambiguous. I think it's legal to have two contacts with the same name and/or phone number, so none of the other columns are good for uniquely identifying a row. When that's the case, we make an ID column. That has implications in our code, but I'm not talking about code yet.

To put data into the table, we use an INSERT query. The syntax for this query is a little janky, as is most SQL. It goes something like:

Code:

---

INSERT INTO table_name (column_list) VALUES (value_list)

---

The column list is a comma-separated list of column names. The value list is a comma-separated list of values in the same order as the column list. You don't have to list every column in the table, though the rules for that are a little more complex than this tutorial. Let's say we want to add "John Smith" with phone number "555-555-5555" into the table. That requires:

Code:

---

INSERT INTO customers (FirstName, LastName, PhoneNumber)
    VALUES ('John', 'Smith', '555-555-5555');

---

Note it's best to surround string values with ''. Where's the Id column? Well, it's an INTEGER PRIMARY KEY. That means it's auto-incremented and the value is assigned automatically. So we don't have to include it. This is only true for auto-incremented columns. (Or some other cases I won't discuss.)

To check if it worked, we have to learn how to SELECT data. The syntax for this statement is very complex, but what you need to know right now is:

Code:

---

SELECT column_list FROM table_name

---

The column list is just like it was in INSERT. You can use * as a shortcut for 'all columns'. I don't like doing that, it can make the code end of the equation more difficult. I suggest not making it a habit in code, but right now it can be harmless. Just remember, you don't always have to fetch all columns. Here's how I can check my customer was added:

Code:

---

sqlite> SELECT Id, FirstName, LastName, PhoneNumber FROM customers;
1|John|Smith|555-555-5555

---

This shows each column's value, and that the ID for this row is 1.

I'm going to add some more rows, then show you the next most important thing about SELECT: the WHERE clause. Add some more yourself. Here's my current data set:

Code:

---

sqlite> select * from customers;
1|John|Smith|555-555-5555
2|Alice|Programmer|555-555-1234
3|Twilight|Sparkle|555-555-5554

---

Now let's say I only want to get the customer record for "people who have the first name John". You can stick, on the end of a SELECT statement, a 'WHERE' clause that represents an expression. If the expression evaluates to True for a row, that row will be in the results. There are a lot of expressions to learn, that's part of the "learning SQL" part of working with databases. Here's some examples:

Code:

---

sqlite> SELECT PhoneNumber FROM customers WHERE FirstName='John';
555-555-5555
sqlite> SELECT Id FROM customers WHERE LastName='Sparkle';
3
sqlite> SELECT PhoneNumber FROM customers WHERE PhoneNumber LIKE '555-%';
555-555-5555
555-555-1234
555-555-5554
sqlite> SELECT PhoneNumber FROM customers WHERE PhoneNumber LIKE '555-%' AND FirstName = 'Twilight';
555-555-5554

---

Note that there's a chance of getting anything between 0 and the entire table's worth of results. That is important to remember. Also note that you can have multiple expressions joined with AND/OR.

So we have CREATE TABLE, INSERT, and SELECT. What if "John Smith" changes his phone number? We can't use INSERT, because that'd create a new row. In this case, we need to use the UPDATE command, and this is where the Id finally becomes important. It will look similar to what we've already done.

The syntax for UPDATE is:

Code:

---

UPDATE table_name SET column_sets WHERE expression

---

The 'column_sets' itself is a comma-separated list of:

Code:

---

column_name = expression

---

And an expression is, well, an expression.

You can read this in English as, "In this table, update these columns to these values for any row that matches the WHERE expression." Getting the expression right is important: if more than one row matches, all will be updated. That's usually not what you want. So, usually, the Id is used as part of the WHERE expression. For demonstration, using the last data set:

Code:

---

sqlite> select * from customers;
1|John|Smith|555-555-5555
2|Alice|Programmer|555-555-1234
3|Twilight|Sparkle|555-555-5554

sqlite> UPDATE customers SET PhoneNumber = '555-555-5555' WHERE PhoneNumber LIKE '555-%';
sqlite> select * from customers;
1|John|Smith|555-555-5555
2|Alice|Programmer|555-555-5555
3|Twilight|Sparkle|555-555-5555

sqlite> UPDATE customers SET PhoneNumber = '555-123-4567' WHERE Id='1';
sqlite> select * from customers;
1|John|Smith|555-123-4567
2|Alice|Programmer|555-555-5555
3|Twilight|Sparkle|555-555-5555

---

This demonstrates you can update multiple rows, and that narrowing them down with Id is possible. This is one reason we try really hard to make sure every row has SOME way we can uniquely identify it.

Now, let's say we want to forget about John Smith and remove him from our life. That is the job of the DELETE statement. Its syntax should look pretty familiar:

Code:

---

DELETE FROM table_name WHERE expression

---

As you can imagine, this is pretty dangerous if you aren't sure that the expression refers to JUST the row you want. Again, an Id column of some sort tends to be used to make sure we only get the rows we want. When debugging or using the console, I like to 'dry run' by using a SELECT with the same WHERE clause before I delete a row.

Code:

---

sqlite> SELECT * FROM customers WHERE Id = 1;
1|John|Smith|555-123-4567
sqlite> DELETE FROM customers WHERE Id = 1;
sqlite> SELECT * FROM customers;
2|Alice|Programmer|555-555-5555
3|Twilight|Sparkle|555-555-5555

---

That's a whirlwind tour of creating tables, INSERT, SELECT, UPDATE, and DELETE. Those five things represent 90% of the database interactions you will have. If you're trying to do something with a DB, step one is figuring out the SQL query that corresponds to what you want to do. Things do get a bit more complex if the data types are more complex, but for right now I think messing with the JOIN clause for SELECT is a bit too advanced.

Before writing much ADO .NET, you ought to be comfortable with using the command-line tool or some GUI tool to run these queries against a database. The GUI tools are more useful in general, but all ADO .NET code has to execute these SQL queries. If you can't write one yourself, you won't be able to write code that does it.

So the next logical topic is discussing how ADO .NET enables doing those things. That's for another day, I've spent too long already. And someone's going to fuss at me for going "too basic" or being "too whirlwind".

So, important takeaways in bullet form:

• Databases are like fancy files.
• Each table stores one 'type' of data.
• Table 'columns' represent individual properties of data items.
• Table 'rows' represent each data item, and consist of values for each table column.
• SQLite is pretty loose with typing, and supports INTEGER, REAL, and TEXT most importantly.
• PRIMARY KEY columns must be unique for every row, and by default are Integers that auto-increment and do not need to be assigned.
• Most tables will want to have a PRIMARY KEY column, or some other way to uniquely identify specific rows.
• You use a CREATE TABLE query to create tables, though you don't tend to do that as much as other things.
• You use an INSERT query to add a row to a table.
• You use an UPDATE query to change the values in the column(s) of a particular row.
• You use a DELETE query to remove a particular row.
• You use a SELECT query to find rows that match some criteria.

Master those tidbits, and ADO .NET won't seem so hard. Many of these things are also true for MSSQL, mysql, or any other relational database.

Oh, also. SQLite doesn't have Stored Procedures (SPROCS). So for that leg, if you want to take it, you'll have to have an MSSQL/mysql/etc. instance laying around.

I'm not stupid enough to call them 'unimportant', but for most applications that should use SQLite it's not a big deal that it doesn't have them. The two important things stored procedures introduce are better performance (because they're in-process with the server) and better security (because the query cannot be tampered with by client-side means.) Applications that need the performance tend to have dozens of other reasons why SQLite is completely inappropriate.

The security is a concern, but I think the idea is that a SQLite database is local, so someone who tampers with their client will only be shooting their own foot off. In short: since SQLite doesn't support remote connections, it won't provide either of the benefits of SPROCS, so they are not implemented.

Parameters are part of CRUD, but be mindful you don't run before you walk. Parameters are important, but easiest to learn if you're already proficient with working in their absence. And working without them for a little while helps you understand the reasons why they are so gosh darn helpful.

When/if I make it to individual kinds of queries, I'll talk about them in general first, then point out how parameters fit in.

Another reason that SPROCS enhance security is that you only allow EXECUTE permission on SPROCS to your users.

Users never need SELECT or INSERT or UPDATE or DELETE access to tables - as long as you are 100% SPROC.

I'm not going to argue they're unimportant. But I don't have experience with them so I wouldn't feel right writing a tutorial about them. My understanding is, aside from the administration concerns, they're roughly the same as writing parameterized SQL queries. So if you want to supplement anything I say with "This is how you'd do it with stored procedures", I'm not going to gripe. I don't think I could explain how to use them. I just kind of know what they are and that the projects I've written haven't needed a SQL engine that supports them. (Which is more a quirk of the applications I write than a general condemnation, most phone apps don't directly know they're using a database.)

TONS of great stuff guys. And S, this is 'my' thread and you can be as simple or complex as you want it to be. I just hope you save complex for later :)

No way to respond to anything without making a HUGE post, so just know I agree with all that you and sz are saying. I think one of the biggest things is getting used to the command-line tool and use it to practice queries. I think that might help me more than anything right now. And spending time with it, certainly has no bad effects.

So, I'm off to the first step of knowing what a 'relational' database is (for the 100th time). Meaning I've done it many times before but I'll be darned if I can spit out it's meaning right now :)

So...... guess I'm off to battle now. Will have questions later.
thanks

Had my first chance to verify something S said. A rowID IS automatically inserted when creating a database. At first I didn't know that and put an ID field into the query. Not needed, so I modified the software's creating of a database to not include the ID field. It was not apparent that a rowID had been created though. I had to do a query to show rowID to be able to see it. But I downloaded a different Admin software and it shows the rowID by default.

thanks S

rowID is an implementation detail of SQLite. In my opinion, you should still make an Id column for rows that don't have a different way to be uniquely identified. If you don't, it's harder to decide you want to use a different database later. Or, put another way, the SQL you learn won't work quite right on any other database, and you'll have to re-learn a few things later.

If you dig in the documentation, you'll find that "PRIMARY KEY INTEGER" columns are an alias for rowId anyway, so it's not like it's wasting space.

Hey, thanks for that. Guess I was doing it the right way at first (by luck) but just didn't know it. So I'll go back to using an ID column as primary key.

Now I have another question....
I know I'm just throwing stuff at the wall trying to do this, but can't get it to work. I'm trying to load a table/dataset in the form load event. But there are some issues with it that I didn't see at first. Maybe someone knows about this.

When I add da and ds to the watch list, ds looks fine, but da has an error inside it at 'da.selectCommand'. Here's the error:

Quote:

---

+ SelectCommand (System.Data.Common.DbDataAdapter) 'DirectCast(da, System.Data.Common.DbDataAdapter).SelectCommand' threw an exception of type 'System.InvalidCastException' System.Data.Common.DbCommand {System.InvalidCastException}

---

But... I don't get an error when stepping through the code. The code is below. Does anyone have some ideas?

thanks

Code:

---

      ' connectionstring =  Const connectionString As String = "Data Source=customers.db;Version=3;"
        Dim da As New SQLiteDataAdapter
        Dim dt As New DataTable
        Dim ds As New DataSet
        Dim objConn As New SQLiteConnection
        Dim objCommand As New SQLiteCommand

        objConn = New SQLiteConnection(connectionString)
        objCommand = objConn.CreateCommand()
        da.SelectCommand = New SQLiteCommand("select * from customer", objConn)

        objConn.Open()

        da.Fill(ds)
        dgvCustomer.DataSource = dt

---

I just read up on SQLite and this odd RowId that is exposed to users.

https://www.sqlite.org/autoinc.html

Yikes - this is just horrible. Forget it exists! You won't find it elsewhere (like in MS SQL) and the re-use and assignment algorithms scare the heck out of me!

Sometimes the debugger isn't very smart, and can confuse itself. One question: why do you call CreateCommand() then never use the command you get back? That's more idiomatic than calling the SQLiteCommand constructor. Other than that, I don't know. I don't use the DataAdapter, but I'm sure someone else might be able to fill you in.

Don't use Load. Use Shown. If an exception's being thrown, it's certain that Load is hiding that.

Quote:

---

Originally Posted by szlamany

I just read up on SQLite and this odd RowId that is exposed to users.

https://www.sqlite.org/autoinc.html

Yikes - this is just horrible. Forget it exists! You won't find it elsewhere (like in MS SQL) and the re-use and assignment algorithms scare the heck out of me!

---

Same. SQLite is very much like Access: compared to a "real" database like MSSQL there are quite a few backwards things. It's "good enough" for local client databases that don't want to support multiple concurrent users. Get outside that case, and it starts getting foolish to use SQLite. No one sane would back an e-commerce or banking site with it. But a phone application's data? It's a good fit.

jumper77: be careful reading into implementation details. SQLite is documented as it is because it's fully open, and they want everyone to understand how it's supposed to work. But many, many features are intended only for advanced use cases, such as the above linked page's discussion of re-use and other concepts. The takeaway is supposed to be: "Don't try to guess what rowID will be assigned, instead create a row and ask via query."

Storytime: MSSQL is no saint either. At my last job, we drank the TFS kool-aid. It stores lots of things in MSSQL, among them build errors/warnings as part of continuous integration. It turns out that 300 people submitting builds all day, with failed builds producing many thousands of errors/warnings very quickly overflowed the auto-increment ID column used to store those. Apparently that thought never crossed Microsoft's mind. What happened in response?

The entire TFS system broke. No one could check out code, no one could check in code. MS's solution? Basically a "DROP TABLE; CREATE TABLE..." script. It took four days to complete, and failed twice, so we lost two weeks of development effort.

So do make sure, when choosing auto-increment as an ID column, that you're reasonably certain having several billion rows won't be a common case. If it is, you need a better ID scheme. GUIDs come to mind as a good one.

Ok, I have read the posts from both of you and it's time to say something I'm thinking. I think that using your own ID field does not make sense. Once I found out the database did it anyway, I was not going to mess with it. Why would you want to add another layer of complexity when it's not necessary?

Quote:

---

"Don't try to guess what rowID will be assigned, instead create a row and ask via query."

---

You say that creating a your own auto-increment field would help you queries. I really don't understand how using your own ID has any benefits because rowID is already there. I mean... what does it buy you? Maybe if you could explain more about why it's a good thing, I could understand better. I'm not trying to fight the issue, just trying to understand it :)

And about running out of unique row Ids, This is something I will never have to worry about. And the numbers it can use is HUGE!

And S, I can't believe you said "Real" database. I would call a "real" database the one that fits the needs of the application being built. I'm not a fan of SQL Server and I've seen it fall on it's face too many times. As I told you before, in one shop I worked at, we tried to make MSSQL work for about 6 months, only to realize that it would never work for our application. We tossed it out the door and used another database much like SQLite :p

That's bizarre about running out of ID numbers. Were you trying to build your own source control software? Just curious. And YES, in that situation, I think a GUID might be a good way to go.

Note: I'll be leaving the house in a little while to take care of some business out of town, but should be back in a few hours.
Thanks guys

It's really rather simple.

This RowId internal value is kind of unique to SQLite from what I can see.

Seems this is an accepted fact as if you create a simple INTEGER PRIMARY KEY field on a table in SQLite it uses the RowId internal value.

With that said if you want to use an ascending sequential integer id # for a primary key make a field.

That way if you port to another SQL database you can use this same common practice of making a primary key field.

If you make reference to the internal RowId value without using a normal field you are making yourself married to SQLite.

The link I sent you said that AutoIncrement in SQLite is to be avoided. That advise seems like bad advise. Without AutoIncrement there is a possibility that a "deleted" RowID will be re-used on a future new record. This is bad in my opinion - very, very, very bad.

That is what made me say to forget RowId even exists.

In SQLite it will use it for you when you make a simple PK of integer type.

And I would suggest that you carefully consider the AutoIncrement keyword in SQLite. I find re-use of keys from deleted records to be a deal breaker.

Seems like a lot of extra steps, this is how I would do it

Code:

---

        Try
            Using con As New OleDbConnection(My.Settings.ContactsConnectionString), da As New OleDbDataAdapter("select * from contacts", con), dt As New DataTable
                da.Fill(dt)
                Me.DataGridView2.DataSource = dt
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

---

A DataAdapter will open the connection for you if it's not open. On more complex things, especially in loops, I do open the connection first because it helps the speed.

Also, especially in the form load event, always put your code in a Try/Catch block. Sometimes Form Load will ignore errors and keep on running without letting you know.

Thanks sz for making things clear concerning IDs and auto-increment. Portability is a good enough reason to do it that way. Sounds like good practice.

And thanks Wes. Lot of good information. I have never heard of errors being skipped during the form_load event. And when I can remember, I usually put things in the shown event.

Thanks for the code too. It's good to know. Right now I'm just trying to get things to even work :) But good practice/knowledge is always a good thing to hear about. And I'm going to try it right now.

Thanks to both of you.

I agree with szlamany's #47, though I'm not quite as leery of reused auto-increments, but this also comes from some assumptions I'm making.

It's only going to happen around an overflow scenario, and it's only going to use IDs that aren't currently being used. In order to be burned by it, you'd need to query a large set of rows and cache them while the 'last' ID is close to overflow. Meanwhile, some other process would have to delete a handful of rows. Then, you decide to add a few new rows. The database reuses one of the deleted IDs. Then, you update your cached records and submit an UPDATE. Whoops, you just overwrote some data by re-introducing a deleted row.

I think you have to do some really dumb things to get in that scenario. SQLite is bad at doing the things you'd be asking it to do. If you are caching data for that long, or accessing the database from multiple processes, or you routinely expect to reach row counts of 4 billion, no sane developer would suggest SQLite.

All of this falls way outside the parameters of an application that should be using SQLite. It's good for single-user applications that generate dozens or hundreds of rows. The most intensive program I've used with it generates a little south of one million rows per day. It will take it 11 years to overflow at this rate, and it never caches data in memory so it won't even notice the reuse. I don't make any assumptions about the ID. I'm pretty sure it won't notice when it overflows.

You have to be doing some really stupid things with SQLite to get burned by this feature. Those things might not be so stupid with MSSQL. That's why MSSQL makes stronger guarantees: it satisfies requirements that MSSQL will never require.

Quote:

---

And S, I can't believe you said "Real" database. I would call a "real" database the one that fits the needs of the application being built.

---

I don't like it either, but SQLite is only one notch better than Access, and I could never bring myself to call Access a "real" database.

See the above. There's plenty of common database scenarios where SQLite's developers have intentionally decided to omit some safety because they don't want SQLite to be anything more than a simple solution for a local file database. It is extremely important to understand that when I say "MSSQL is too complex for a tutorial" that "for a tutorial" part is the important part of the phrase. I wouldn't use SQLite to back a website, or anything else that can have multiple concurrent users. But you can learn a lot of lessons with SQLite that make learning the nuances of MSSQL/postgres/mysql much easier.

Quote:

---

That's bizarre about running out of ID numbers. Were you trying to build your own source control software?

---

No, this was an unaddressed issue in Microsoft's TFS, the only source control system so bloated and convoluted it uses MSSQL to store its data.

It wasn't just the re-use of deleted PK's - it's that it's using a different algorithm when you use Autoincrement keyword. Who knows how those algorithms will change over time. The Autoincrement seems to force the behaviors of assignments and gaps closer to MS SQL (maybe that's the intent).

I also have problems with IDENTITY keys in MS SQL - as gaps are ugly if the intent is a sequential ascending value (which Identity does not guarantee for you).

But this is kind of off-topic now...

Yeah. I think we're both more or less on the same page as to whether SQLite is extremely robust. It's more like "good enough", and for that sacrifice you get easier configuration.

Let's talk about databases again. This is going to be a disappointing lesson. But each of these is a building block. This is one of the blocks that goes at the bottom, so skip it at your peril.

Lesson One - Connections, Or The Lesson That Does Nothing

First, let's make sure we're on board with setting up SQLite in our project. If you're using MSSQL, you're on your own, though there's probably some similar steps, and it's a little easier because MS generally installs it for you.

One way to set it up is to download the SQLite DLL from the SQLite website, put it somewhere on your hard drive, and make sure you reference that DLL. That's probably what you're doing now. Want to keep doing it? Skip ahead. There's a slightly more convenient way.

Somewhere around VS 2013, a 'package manager' called NuGet was adopted from the open-source world by Microsoft. Since Microsoft adopted it, it suddenly lost all its cooties and everyone in the world but VB developers considered it OK. What does a package manager do? It lets someone who develops a library like SQLite set up an automatic way to add the right DLLs and references to a .NET project. You can use NuGet from the command line, and MS did invest some work in PowerShell commandlets that make it nice and easy. You can also right-click in your project and get the same thing done. A benefit to using NuGet is if you're using it, you can send your project file to someone else without the SQLite DLLs, and when they open/build it the package will magically be installed. This is very convenient if, say, you plan on uploading a zipped project to a forum that doesn't like you to include DLLs in zip files. It also means you can guarantee everyone using your project has the right version of the DLL, another common source of frustration on forums.

So I just made a new console project. If I right-click the project, in the menu I see a "Manage NuGet Packages..." item. I click that. I click on the "Browse..." tab, and I search for "SQLite". Now, you get presented with many options. You do NOT want the one named "SQLite .NET". That is a sort of ORM for SQLite that is useful in some cases, but will be absolutely useless if we want to learn how ADO .NET works. It'd be like "learning to ride a bike" by "riding a bus". The right one is called "System.Data.SQLite" and has about 700k downloads as of right now. I clicked it, then clicked the "Install" button that appeared in the summary. This summoned a dialog that told me which DLLs were being added. It's adding support for Entity Framework, and some other things I don't care to use. If I were really picky, I could find a SQLite package that doesn't come with all of that. I don't feel like being really picky, we'll deal with some extra dependencies right now.

Time passes. DLLs are downloaded. References are added. When the dust settles, I have a project ready to use SQLite. Joy. Let's actually talk about connections, now.

The real part
SQLite's types live in the namespace System.Data.SQLite, if you're using the same library as me. MSSQL uses something else, probably just System.Data. If you try using some of my code and it complains it doesn't know what the type is, make sure you've added "Imports System.Data.SQLite" at the top. If you do that and it complains it doesn't know what that is, make sure you added references/the package properly. If you're using MSSQL, do something different.

ADO .NET tries to be an abstraction of database operations. So for every type we want to use, there's an Interface or MustInherit class that represents the concept. The benefit to you is, in theory, if you write a lot of code that uses SQLite, you won't have to make many changes to convert that program to use MSSQL. I can say from experience that promise is mostly kept, though it's a big deal to change database engines. I'm going to talk about the interfaces first, then point out the types that implement them.

In ADO .NET, you need to have a "connection" to a database to use it, even if it isn't a networked database. Connections are represented by types that derive from IDbConnection. SQLite named theirs SQLiteConnection, MSSQL names its own SqlConnection, and other databases each have their own connection. Every connection implements IDbConnection, so they all have the same methods and properties. These are the most important:

• The ConnectionString property represents a string used to configure the connection. Each database has its own format for connection strings. It usually includes a path or address to the file/server, username/password information if needed, and other things you may or may not care about.
• Open() has to be called before you use the connection. What it does is database-specific, and you'll get reminded via exceptions if you forget to Open() your connection.
• Close() closes the connection. Dispose() exists as an analog of Close(), though they aren't guaranteed to do the same thing. Because stupid. Just keep in mind you'd better be manually calling Close() or, more commonly, using a Using statement.
• CreateCommand() creates a 'command object' associated with the connection. I'm not talking about those in this lesson. But remember it's there.

If we want to create a connection, we have to call a constructor. To construct a SQLiteConnection, we're going to need a connection string. Hm. It turns out there's a handy reference at Connection Strings dot com. You can see a lot of options there. You can, on your own, go read about them and play with them. All we care about is the "Basic" connection string. It is a semi-colon delimited set of 'name=value' pairs. It expects a path to a file for "Data Source", and after that it expects "Version=3". If it were MSSQL, we'd need to specify an IP address, maybe a username and password, etc.

Armed with that, it's easy to get a connection ready. This is how my humble tutorial application begins its life:

Code:

---

Imports System.Data.SQLite

Module Module1

    Private Const ConnectionString As String = "Data Source = test.db; Version = 3;"

    Sub Main()
        Using connection As New SQLiteConnection(ConnectionString)
            connection.Open()
        End Using
    End Sub

End Module

---

It does nothing! If it works. You might be asking what kind of an idiot makes a tutorial that does nothing. Well, it's more complicated than that.

If you can build and run this, you win! You learned about all you need to know about making connection objects, and you can properly create a .NET project that depends on SQLite. If anything goes wrong, you did something wrong! You need to figure out what went wrong and fix it. I recommend just starting a new project, and following the steps again. The reason I stopped here is so we can make sure there's something to start from. If you don't get this far, then everything beyond will fail. Plus, also, my lunch break is over.

The next thing to do: create a table. We'll have to learn about the command object to do that, and the three ways it can be used to submit queries. Then, I'll spend a little time on each type of command we know. When that's been covered, it's time to talk about how to make life easier with helper code. And, if you really twist my arm, I might even talk about DataAdapter.

That's ok Sz, I don't consider that off-topic.

Quote:

---

Somewhere around VS 2013, a 'package manager' called NuGet was adopted from the open-source world by Microsoft. Since Microsoft adopted it, it suddenly lost all its cooties and everyone in the world but VB developers considered it OK. What does a package manager do? It lets someone who develops a library like SQLite set up an automatic way to add the right DLLs and references to a .NET project. You can use NuGet from the command line, and MS did invest some work in PowerShell commandlets that make it nice and easy. You can also right-click in your project and get the same thing done. A benefit to using NuGet is if you're using it, you can send your project file to someone else without the SQLite DLLs, and when they open/build it the package will magically be installed. This is very convenient if, say, you plan on uploading a zipped project to a forum that doesn't like you to include DLLs in zip files. It also means you can guarantee everyone using your project has the right version of the DLL, another common source of frustration on forums.

---

Sounds like a really cool thing, but I've been trying to install several NuGet packages for SQLite, and it always fails. I guess I must be doing something wrong because it even failed on getting the NuGet Core. All of the NuGet packages that I've tried to install fail with the same error:
'Unable to find package...'

Quote:

---

PM> Install-Package NuGet.Core
Install-Package : Unable to find package 'NuGet.Core'
At line:1 char:1
+ Install-Package NuGet.Core
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Install-Package], Exception
+ FullyQualifiedErrorId : NuGetCmdletUnhandledException,NuGet.PackageManagement.PowerShellCmdlets.InstallPackageCommand

---

Quote:

---

PM> Install-Package System.Data.SQLite.Core
Install-Package : Unable to find package 'System.Data.SQLite.Core'
At line:1 char:1
+ Install-Package System.Data.SQLite.Core
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Install-Package], Exception
+ FullyQualifiedErrorId : NuGetCmdletUnhandledException,NuGet.PackageManagement.PowerShellCmdlets.InstallPackageCommand

PM>

---

You get the idea. Do you know what may be going wrong? I've installed other things before and they worked. Maybe it's caused by an update I did about a month ago. I installed: Update 2 of VS.

About System.Data.SQLite... I can't use it because I'm using something else. It's different, here's the import statement:
Imports Finisar.SQLite

I'm guessing you might 'fuss' about this one because the project is now closed. I found it by looking for something that was .Net specific. However, it would probably be a good thing to use something still supported. I can try system.data.sqlite, but it will take a bit to find out how to use it. Some things work without the Finisar reference, but some things don't. So I will have to adjust to new syntax.

Guess I've said enough for now. I'm going to see about using the import you suggested.

Hey!! It worked right out of the box and I didn't have to change a thing. Big time cool :cool:

Ha. Sounds like the other one followed the ADO .NET interfaces. It might've even worked with my lessons with some tweaks. If you figured out how to get System.Data.SQLite working, that's fine. I'd stick with that for now. I can't tell you what the heck is wrong with NuGet based on the errors you got, but it sounds like you found another way around. NuGet is nice. When it works.

I think it 'would' work with whatever you write, just because I didn't have to change 1 single thing. So I'm guessing they must be compatible. Plus I've looked at your code and it seems the same to me (I think).

And about NuGet, when it was working before, it wouldn't load some things, but it never got to the point of not loading 'anything'. And yes it's nice. I have used it on other projects before I came to the forum.

Here's another 'Hey!'... I've been working so much on the code that I never let the main form come up. Well, I just did and OMG. The dataGridView came up with all the columns and everything. I've never done that before and it looks neat :)

Sorry... just had to jump up and down for a minute....

Also sorry for all the posts, but... now that it works, I need to ask something. How do I implement the auto-increment ID in an INSERT command? I don't get it yet.

thanks

Quote:

---

Originally Posted by jumper77

Also sorry for all the posts, but... now that it works, I need to ask something. How do I implement the auto-increment ID in an INSERT command? I don't get it yet.

thanks

---

Re-read lesson 0. That table has a PRIMARY KEY INTEGER column, which auto-increments. Lesson 0 has a tutorial about INSERT queries. Thus, it also talks about how you handle auto-incrementing columns with INSERT queries.

Today's lesson will sort of cover it too, as we'll get to working with commands. But it will take me much longer to type that post, and if you can't type an INSERT query at the SQLite console, you will have a harder time understanding VB code that uses it.

You are correct about Lesson 0. I just found out I've been doing it right after all. I have verified that the ID is auto incremented in the DB table.

So sorry for the question. If I had waited just a little longer before asking, I would have found the answer (but of course there's no way of knowing that).

thanks