[RESOLVED] Beginner trying to learn SQLite database

In lesson 0, I talked about SQLite and SQL. In lesson 1, I cracked my knuckles and made sure my SQLite project worked. Now it's time to actually start talking about using SQLite.

Lesson 2 - Commands

There's a method on the connection object, CreateCommand(), that I glossed over. It's time to show off what it does.

Commands are the ADO .NET concept of 'a thing that can execute SQL'. In order to execute any SQL, you have to create a command, configure it, and make the relevant method call.

IDbConnection.CreateCommand() returns an object of type IDbCommand. This object has several properties and methods, these are the most important:

• Connection is the associated connection.
• CommandText is the text for the SQL query that will be executed.
• Parameters is a collection of SQL parameters and values for the query. That's for a future lesson, but very important.
• CreateParameter() is used to create parameters.
• ExecuteNonQuery(), ExecuteReader(), and ExecuteScalar() are the subject of this lesson!

Each database-specific ADO .NET implementation will provide its own types. For example, the SQLite implementation uses SqliteCommand, the MSSQL implementation uses SqlCommand, etc. Note that the connection's CreateCommand() returns an IDbCommand, not the specific type, so you usually have to cast with CType() after calling it.

Note that IDbCommand is IDisposable, so you should always make sure to call Dispose() or use a Using statement.

Why are there three 'Execute' methods? Well, different SQL queries do different things, but you can't make an Execute() method that returns different types that makes a lick of sense. So each command has a purpose that tends to match up with a certain kind of SQL query.

ExecuteScalar - Returns one value.
Some SQL queries don't return rows as results, they instead return a single value. This value is called a "scalar". For example, SQL has a function called 'count()' that will count the rows returned by a query and return that.

Code:

---

sqlite> select * from customers;
2|Alice|Programmer|555-555-5555
3|Twilight|Sparkle|555-555-5555
sqlite> SELECT count() FROM customers;
2

---

See how it just returns the number 2? That's why ExecuteScalar() returns an object. It takes some trial and error to figure out what kind of a number comes back. SQLite seems to like returning a Long, and I screw up and try to cast it to Integer. Do note that the documentation points out there is some tricky behavior around 'null':

• If no results are returned at all, ExecuteScalar() will return Nothing.
• If a result is returned, and that result is null, ExecuteScalar() will return DBNull.Value.

This gets newbies and veterans all the time. The database does not use Nothing to represent null, and we've learned that it means many things in VB anyway. Be very, very careful that when you compare database values, and you expect null, you use DBNull.Value.

So, how would we use this in code? It's pretty simple. You need a connection. You use it to create a command. You set the command text, then call ExecuteNonQuery(), casting to the result you expect and praying that's what you get.

Code:

---

Using countCommand As SQLiteCommand = connection.CreateCommand()
    countCommand.CommandText = "SELECT count(*) FROM customers"
    Dim result As Long = CLng(countCommand.ExecuteScalar())
    Console.WriteLine(result)
End Using

---

Don't forget to Open() the connection!

ExecuteNonQuery()
Some SQL queries don't really return results at all, instead they do things to the database. INSERT, UPDATE, and DELETE come to mind. Those are the commands ExecuteNonQuery() can represent. ExecuteNonQuery() returns an Integer, and that Integer usually indicates the number of rows that were affected by the query. That can be a decent sanity check: if you execute a DELETE you think should delete 10 rows, and 20 get deleted, you know you screwed up. If, for whatever reason, it doesn't get a result, it returns -1.

Using it is just like wiht ExecuteScalar():

Code:

---

Using deleteCommand As SQLiteCommand = connection.CreateCommand()
    deleteCommand.CommandText = "DELETE FROM customers WHERE id = 3"
    Dim result As Integer = deleteCommand.ExecuteNonQuery()
    Console.WriteLine(result) ' Probably 1 or 0.
End Using

---

ExecuteReader()
This is one I'd love to make its own lesson, but it really feels wrong stopping short of here. When you make a SELECT query that returns rows, ADO .NET can't know in advance what kind of objects you want to put that data in. So the best it can do is return some object that represents the rows and columns of a table and let you sort it out. You may be thinking, "Aha! DataTable!", but that's thinking a little too far ahead.

IDbCommand.ExecuteReader() returns an object of type IDataReader. This object has several important methods and properties:

• FieldCount tells you how many columns are in the current row.
• Item(Integer) and Item(String) are 'indexers', that let you treat it like an array or Dictionary.
• Lots of Get???() methods exist, including a more general GetValue(). These all return the value for the column with the index you give them, either as an Object or the type the method is named after.
• IsDBNull() tells you if a column contains DBNull.Value, which seems silly until you realize GetInt32() and several other Get methods can't return it.
• Read() tries to move to the next row. If it runs out of rows, it returns False. If there is a row, it returns True.

That's a lot to keep in mind! But it's simple once you've used it a time or two.

IDataReader is IDisposable, I think you know what that means by now. I think you can also guess that, depending on which ADO .NET provider you're using, you might get SqliteDataReader, SqlDataReader, etc.

I can't just paste a code example here, you have to think about how to use this. The general pattern is you get your IDataReader from ExecuteReader(), then start a While loop that ends when IDataReader.Read() returns False. Inside the loop, you use the indexers or Get methods to get the values out of each row's columns. Odds are those values are bound for an object, but they don't have to be.

It looks a little like this, if you can imagine a Customer class:

Code:

---

Using connection As New SQLiteConnection(ConnectionString)
    Dim selectQuery As String = "SELECT id, firstName, lastName FROM customers"
    connection.Open()
    Using selectCommand As SQLiteCommand = connection.CreateCommand()
        selectCommand.CommandText = selectQuery

        Using reader As SQLiteDataReader = selectCommand.ExecuteReader()
            While reader.Read()
                Dim thisCustomer As New Customer()
                thisCustomer.Id = CInt(reader("id"))
                thisCustomer.FirstName = reader("firstName").ToString()
                thisCustomer.LastName = reader("lastName").ToString()

                Console.WriteLine("This is {0} {1}, with Id {2}.",
                                  thisCustomer.FirstName, thisCustomer.LastName, thisCustomer.Id)
            End While
        End Using

    End Using
End Using

---

I included more code there, because it's important to know what columns you are working with. You either have to memorize indexes (which makes editing the command a pain) or make sure you spell column names right. They might be case sensitive, so consider using constants to eliminate mistakes.

It probably seems tedious. It sort of is. Slowly, as we learn more concepts, I want to start talking about "how to make this less terrible" as opposed to "how to make this work". There's no time like the present. Do not get in the habit of scattering database code throughout your program. Whether you are using DataAdapter or IDataReader, you should make sure database code is in a special place.

So let's show off a bit of code that demonstrates all of the concepts put together. This is what my submission for a 'challenge' to write an app demonstrating these concepts would look like. It will create a database (deleting one if it already exists), then add a few rows. It will update some of the rows, delete some rows, printing the contents of the database along the way. Pay close attention to how the database code is separated. Note how, instead of returning things like IDataReader, I return objects like Customer? This is an important way to hide ugliness. Good developers do this almost every chance they get.

You keep getting hung up on "How do I know the ID if it's auto-incremented?" This project shows one solution: When I call GetAll(), I get all of the data in the database as a Customer object. Each Customer object has an Id property that represents its database Id. This can go pear-shaped in a hurry if there are multiple users accessing a database at the same time. We're not worried about that right now, and don't need the ridiculously sophisticated solution.

Someone is going to criticize this and say I hit the database too many times, or that I could cache connections, or that I could keep the Customers in memory. This is true, but it's also a tutorial. I felt like the code was easier to follow when I did these 'bad' things. When writing database code for real, we do want to minimize connections, especially when those connections are made over a network. But when we're hacking out our initial functionality, it's OK to be a little sloppy so long as we actually make the time to clean it up. I find I don't tend to do that, so in 'real' code I try to pay attention.

Look it over. Change it. Enhance it. Break it wide open then put it back together. The next lesson will be easier: parameters are just lipstick on this.

*edit*
Ha. I didn't actually use ExecuteScalar(). Whoops. I don't tend to use it all that much, but it could fit, in a few places. For example, I could do a "SELECT count()" in GetAll() to pre-initialize the array. That'd save on some memory allocations. Oh well.

Ok, just to regroup here... The code below works and shows the use (although not the best) of the ID field. I'm hard coding the ID but just wanted to show that it worked

I've been told that there's an example coming up soon with a much better way to handle the ID field. I anxiously await :)

Code:

---

    Public Sub CreateDatabase()
        Using objConn As New SQLiteConnection(connectionStringCreateDatabase)
            Using objCommand As SQLiteCommand = objConn.CreateCommand()
                Try
                    With objCommand
                        objConn.Open()

                        .CommandText = "Create TABLE customer('ID',
                            'First Name' Text Not NULL, 'Last Name' Text Not NULL,
                            Address Text Not NULL, 'Phone Number' Text Not Null);"
                        .ExecuteNonQuery()

                        .CommandText = "INSERT INTO customer ('ID',
                          'First Name', 'Last Name',
                          'Address', 'Phone Number') VALUES('1','Tom','Jones',
                          'Sin City','555.4444');"
                        .ExecuteNonQuery()

                        .CommandText = "INSERT INTO customer ('ID',
                            'First Name', 'Last Name', 'Address', 'Phone Number')
                            VALUES('2','Baby Face','Johnson','Jackson Wy', '555.4242');"
                        .ExecuteNonQuery()

                    End With
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End Using
        End Using
    End Sub

---

Your CREATE TABLE has a problem.

The first field is just called ID - without a data type and without the keywords that would make it the primary key.

Thanks Sz, before I try to apply that, is this how it should be done?
'ID as integer'

I do not do SQLite - but I can see your AS syntax is not in use in the CREATE TABLE you have in the code.

Regardless - you should test a CREATE table in the management tool and make sure you understand it.

I thought sitten has a post here with exactly how tables get created in SQLite.

Lesson zero.

Heck. Let's make a table of contents, and pretend I didn't retroactively add it.

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.

Thanks again for 0
And thank you Sz

edit: I'm on it... head down

To review:

Code:

---

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

---

I keep typing INTEGER PRIMARY KEY because that's important. Without that part, 'Id' is just "a column", has no special meaning, and if you don't provide a value it has null as a value. SQL is just like VB: you can't leave words out of your program because you don't want to type them. There's nothing magic about the column name 'Id'. I tend to use it because it's obvious, and I expect if I tell someone "this is the Id column" they'll assume it's a primary key, or at least unique.

Code:

---

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

---

This is an INSERT query into that table. Note how there is no 'Id' column? Because it is INTEGER PRIMARY KEY, I'm not supposed to provide a value for it.

Code:

---

UPDATE customers SET PhoneNumber = '555-123-4567' WHERE Id='1';

---

This is an UPDATE query for that table. 'Id' is used here, because if we don't specify which rows are being updated then all rows will be updated. So it's generally normal to use Id here, but you have to know the right Id.

DELETE is very similar to UPDATE. SELECT only incidentally needs the Id. I'm not really sure why there is so much focus on the Id. You seem to believe it's some SQLite-specific concept. rowId, and how it relates to this one type of column, is a SQLite-specific thing. But ask szlamany:

Quote:

---

Hey, szlamany, do you use primary key columns in MSSQL? Are they typically integers? Do they autoincrement?

---

If you want to talk SQL, then we can get into a deep discussion of what a primary key is and the functions it serves. But that is not a VB discussion, and I can't lie: everything I pretend I know will be lifted from some documentation somewhere.

Thanks S, I'm not going to reply a lot because I need to keep my head down for a while and read your post and learn how to do this. I'm a little frustrated because I've been stuck on this part about ID's for far too long.

I'll get it though... promise

Hah! I think I have it now. I know you've been beating it into my head S, but as I always say... it's pretty thick. I just got this to work about a minute ago:

And 'believe it or not', I have been reading the documentation over and over about everything. Don't want you thinking I haven't.

Code:

---

.CommandText = "Create TABLE customer('Id' INTEGER PRIMARY KEY ASC,
        'First Name' Text Not NULL, 'Last Name' Text Not NULL,
        Address Text Not NULL, 'Phone Number' Text Not Null);"

        .ExecuteNonQuery()

---

edit: please tell me this is right <sigh>

I don't know if the ASC is strictly required, but I say leave it! I think I saw something, somewhere, about DESC doing something special. I think it's just better to learn what this kind of primary key looks like and stick with it until there's a reason to use something else.

Thank goodness. I'll try it without the ASC just to see. I'm sure it doesn't need it, but I want to be sure. I had an image that I was going to upload to show what the end result was in the program, but I can't get the attach files thing to work.

Just tried it without ASC and can't tell any difference.

No more posts for the night, have to cut grass in a minute, plus that will give me more time afterward to do more things with the project.

I don't want to slow progress but after reading a little about SQLLite and what I consider "different" behavior, I think maybe SQLExpress may be easier to learn. It at least use the same conventions as SQL and very simular to an Access database. As far as I know it's still free. Maybe something to consider for future projects. Would probably be easier to get help, you don't see very many SQLLite posts on this forum. Lots of SQL, SQLExpress, MySql, Access.

That has been a constant gripe in this thread. Here's my deal.

I don't use SQL Express. I don't plan on it. I don't have room in my career for Microsoft-only solutions.

If someone else can write a guide to:

• Setting up a SQL Express project.
• Deploying that project to another machine.
• Why it even matters for a one-table application doing no-join SELECT queries.

That would influence me to try and write my future articles with more of a tilt towards SQL Express.

But right now, the only written statements are that I should totally be using it. I'm not convinced. And so far I'm the only person writing tutorials. And so far, I'm the least qualified person in the thread.

So help me write. SQLite is what I know. I'm not going to try and teach what I don't know. If you don't want to help write, I can't help you.

SS,

Quote:

---

That has been a constant gripe in this thread

---

I don't know if your off your meds or what? I'm the only one who has mentioned SQLExpress in this thread and I mentioned it once in the previous post.

Quote:

---

But right now, the only written statements are that I should totally be using it

---

I never said that, all I said was

Quote:

---

I think maybe SQLExpress may be easier to learn

Maybe something to consider for future projects

---

But don't worry, I won't in this thread anymore. I'll leave you alone to play with your mental blocks.

I'm not trying to chase you out. It could be some other threads that are blurring together. It seems every time I mention SQLite someone breaks out in hives. It's a bit frustrating, considering how widespread it is in my work.

It's not perfect, and there's plenty of places where I totally agree SQL Express does things better. But because my work hasn't led me to use SQL Express so far (most of my programs end up running on Linux/Android/iOS, and before that I did zero database work since about 2005), I'm literally ignorant about it. If it's easy to set up out of the box, I'd love to know, because the Windows community tends to treat anything non-MS like it has cooties. If it's easy to deploy, I'll raise a dang beer to it because MSSQL was a nightmare to deploy with an application back in 2004. The story went something like, "You should use merge modules, but we're not publishing merge modules for MSSQL." Thanks, MS. I had to hand-edit an MSI custom action in ORCA to launch the installer.

I'm also rigging the game. I'm focusing on stuff SQLite does well. If you try to do advanced things with it, it's going to fall on its face and I wouldn't recommend it for those things. All I want to do is teach how to do CRUD, and for simple CRUD SQLite's plenty sufficient. I'm a bit peeved that I've had 4 or 5 people yell at me for using SQLite, because 4 or 5 years ago it was me saying "Ugh don't use Access for this" with everybody screaming "TOOT TOOT, ACCESS IS ENTERPRISE READY, DON'T LISTEN TO THE C# GUY". SQLite has made me raise my eyebrows as much as Access ever has, but I don't have to buy Office to deploy SQLite so I forgive its flaws.

Seriously. If someone writes out how to get SQL Server Express in a project, I'll do the extra work to make examples that work with it. Or if someone else wants to transliterate what I'm writing into SQLSE I'll be fine with that too. But I've got about 1,200 pages of Xamarin documentation I should be reading instead of working on this, so please forgive me if I don't sidestep to spend a day learning how to set up a new database engine. You've spent that day. I bet you could teach me in an hour. If you teach me, and I like SQL Express, we both win.

Also I take great offense to the phrase "off your meds", if you had a close relationship with people who had depression you'd be ashamed.

Come on boys. Let's play nice. Ok, back to our regularly scheduled program... I finally 'get' the idea behind Primary Key and Auto-Increment. If I'm wrong, just tell me. When I create a database table, I define one column as Primary Key. Primary Key will turn into an Auto-Increment automatically if you don't reference it anymore.

Here's what I mean:

Code:

---

  Public Sub CreateDatabase()
        Using objConn As New SQLiteConnection(connectionStringCreateDatabase)
            Using objCommand As SQLiteCommand = objConn.CreateCommand()
                Try
                    With objCommand
                        objConn.Open()

                        .CommandText = "Create TABLE customer(
                            'Row Count' INTEGER PRIMARY KEY,'First Name' Text Not NULL,
                            'Last Name' Text Not NULL, Address Text Not NULL,
                            'Phone Number' Text Not Null);"

                        .ExecuteNonQuery()

                        .CommandText = "INSERT INTO customer ('First Name', 'Last Name',
                              'Address', 'Phone Number')
                              VALUES('Tom','Jones','Sin City','555.4444');"

                        .ExecuteNonQuery()

                    End With
                Catch ex As Exception
                    'Do something here.
                    MsgBox(ex.Message)
                End Try
            End Using
        End Using
    End Sub

---

Plus, here's the code that works with a window that allows the user to enter a new record/row.

Code:

---

  Public Sub addNewCustomer(firstName As String, lastName As String,
                address As String, phoneNumber As String)

        Using objConn As New SQLiteConnection(connectionString)
            Using objCommand As SQLiteCommand = objConn.CreateCommand()
                Try
                    With objCommand
                        objConn.Open()

                        .CommandText = "INSERT INTO customer ('First Name', 'Last Name',
                              'Address', 'Phone Number') VALUES('" & firstName & "','"
                              & lastName & "','" & address & "','" & phoneNumber & "');"

                        .ExecuteNonQuery()
                    End With
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End Using
        End Using
        MainDB.dgvCustomer.Refresh()
    End Sub

---

So I'm glad I got this through my head. Many thanks to Sz and SS.

I would suggest one thing. Yes you can create field names with spaces in them. Second NEVER EVER create field names with spaces in them. I am a DBA and would turn any code for development back to the developer if they sent me tables with fields containing spaces in them. They are hard to work with and a mess to remember that they need special consideration to use. If you want to display the field name to a user with that space then alias the column in your select statement:

Select FirstName as 'First Name', LastName AS 'Last Name' from usernames

Hi Gary, let me look at this for just a minute and I'll post a reply. And thanks for the help.

Ok, the idea is what I thought you meant. After the changes, I can see that the column headers no longer has spaces.

Is there a way to keep the database field a name without spaces, but still keep the column name 'with' spaces? Or is that bad too?

thanks

When you write the select statement you can alias the field as I showed in my Select example

Select fieldname AS 'Some other name' FROM tablename

Quote:

---

If I'm wrong, just tell me. When I create a database table, I define one column as Primary Key. Primary Key will turn into an Auto-Increment automatically if you don't reference it anymore.

---

This is probably close enough. Some of this is specific to SQLite, so let's make sure we air that part out because if you do try out another database you need to know this. I'm going to mark each paragraph with a kind of attribute. "[General]" means I'd be surprised if you find a database where it isn't true.

[General]
Every table can have one 'primary key'. It can be of any type supported by the database. When a column is a primary key column, every value in that column must be unique. Trying to add a row with a non-unique value in a primary key column will result in an error in any sane database. It is not illegal to set a value for a primary key column, and generally you are required to set a value because obviously only one null value can exist. ([i]The technical motivation: the database sorts its internal data structures in a way that makes searching/sorting by primary keys very, very fast, as opposed to 'very fast'.)

[General]
Auto-increment is a different topic. It's very common to want a unique integer column (whether or not it's a primary key). Auto-increment columns keep track of internal state and always assign an incrementing value to the column. Because the database assigns this value, it is usually not legal to set the value yourself. Sometimes you can do so, but you should be very careful if you do. Auto-increment columns are often primary keys, but they are not required to be primary keys.

[SQLite]
SQLite makes some decisions to try and streamline common tasks. The developers noticed it is so common to want an auto-incrementing integer primary key column, they'd shorten the syntax. So instead of "INTEGER AUTOINCREMENT PRIMARY KEY", you can type "INTEGER PRIMARY KEY" and get the same thing. That means the column definition "INTEGER PRIMARY KEY" in SQLite has both behaviors: it must be unique because it is a primary key, and you shouldn't set it yourself because it is auto-incrementing.

It's a syntax sugar. I think it makes people mad because it hides that auto-incrmenting is happening, and many other database engines are very explicit about that. While that is a valid concern, I think there are worse things to worry about.

[SQLite]
If you need a column that is an integer primary key but does not autoincrement, you must not type the exact string "INTEGER PRIMARY KEY". It is sufficient to say "INT PRIMARY KEY". That column will only have the "must be unique" restriction of being a primary key column, but not the "you can't set it" restriction of auto-incrementing because it doesn't auto-increment.

That's another place that tends to make people used to explicit database engines angry. Type assignment in SQLite is a little strange, so if you have very strict type definition needs it's not the database for you.

It is true that this means moving from SQLite to another SQL database will require some extra thought, as most others don't make assumptions for you. The more I mull it over, the more I want to make a "What's different in SQLExpress" series, just to learn myself. But now's not the time, and I'd be pleased if someone else wrote that part for me before I got there.

Quote:

---

Originally Posted by GaryMazzone

When you write the select statement you can alias the field as I showed in my Select example

Select fieldname AS 'Some other name' FROM tablename

---

Ok, thanks. I'm going to learn how to do this and will post some code when I "think" I got it.

Sitten Spynne: Be careful with general statements like this:

[General]
Auto-increment is a different topic. It's very common to want a unique integer column (whether or not it's a primary key). Auto-increment columns keep track of internal state and always assign an incrementing value to the column. Because the database assigns this value, it is not legal to set the value yourself. These columns should never be part of an UPDATE or INSERT. Auto-increment columns are often primary keys, but they are not required to be primary keys.


There are way to insert value into an Auto-incrementing field in different database systems. In MS SQL you issue the statement SET IDENTITY_INSERT ON. Do you insert statement including the value you want for that Auto increment field then issue the statement SET IDENTITY_INSERT OFF

Good Morning.

Quote:

---

[SQLite]
If you need a column that is an integer primary key but does not autoincrement, you must not type the exact string "INTEGER PRIMARY KEY". It is sufficient to say "INT PRIMARY KEY". That column will only have the "must be unique" restriction of being a primary key column, but not the "you can't set it" restriction of auto-incrementing because it doesn't auto-increment.

---

I'm familiar with using INT instead of INTEGER. That was back when I was using Sybase. And that's before MS bought them and mysteriously came out with SQL Server not long after that (remember, this is MY memory and may not be correct).

Quote:

---

That's another place that tends to make people used to explicit database engines angry. Type assignment in SQLite is a little strange, so if you have very strict type definition needs it's not the database for you.

---

This is true. SQLite will let you put a string in a field that was defined as INT (again, I think this is right). But I never do any of that. If I define a field of a certain type, I always use that type.

Quote:

---

Originally Posted by GaryMazzone

Sitten Spynne: Be careful with general statements like this:

[General]
Auto-increment is a different topic. It's very common to want a unique integer column (whether or not it's a primary key). Auto-increment columns keep track of internal state and always assign an incrementing value to the column. Because the database assigns this value, it is not legal to set the value yourself. These columns should never be part of an UPDATE or INSERT. Auto-increment columns are often primary keys, but they are not required to be primary keys.


There are way to insert value into an Auto-incrementing field in different database systems. In MS SQL you issue the statement SET IDENTITY_INSERT ON. Do you insert statement including the value you want for that Auto increment field then issue the statement SET IDENTITY_INSERT OFF

---

Huh. Good to know. I'm going to go back and update it to be more of an "It's USUALLY illegal". I bet it's also USUALLY a bad idea, but I also bet if I spent a year doing large DB projects I'd find myself needing it.

*edit*: I updated it to read more like "this is a bad idea, and sometimes you have to jump through hoops to do it."

Totally agree on spaces in column names. It's legal. But to most DB developers it's 'not right'. Everyone's used to column names being one word. I don't know what the consensus is on camelCasing vs. PascalCasing, I tend to prefer camelCasing and try to stick with it.

Here's an update Gary. In the CreateDatabase sub, I was inserting data just so I could see some values when the form/DataGrid came up. I changed it to only defining the database table. In another form, the user can add new customers. In that section, I'm using the value of textboxes when I insert a customer field.

For that, it's working like it should. But I think I need to find another place where I can use the actual database field names. I'm going to look into that now too.

Here's the code I am talking about. And FYI: I'm going to stop trying to format the code blocks so you don't have to scroll to the right. It's a pain. Plus, it's less readable that way.

CreateDatabase sub:

Code:

---

    Public Sub CreateDatabase()
        Using objConn As New SQLiteConnection(connectionStringCreateDatabase)
            Using objCommand As SQLiteCommand = objConn.CreateCommand()
                Try
                    With objCommand
                        objConn.Open()

                        .CommandText = "Create TABLE customer('Row Count' INTEGER PRIMARY KEY,'First Name' Text Not NULL, 'Last Name' Text Not NULL, Address Text Not NULL, 'Phone Number' Text Not Null);"
                        .ExecuteNonQuery()

                    End With
                Catch ex As Exception
                    'Do something here.
                    MsgBox(ex.Message)
                End Try
            End Using
        End Using
    End Sub

---

And here's the sub that calls Add Customer:

Code:

---

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        Dim custmr As New Customer
        If txtFirstName.Text = "" Then
            MsgBox("First Name field cannot be blank")
            Return
        ElseIf txtLastName.Text = "" Then
            MsgBox("Last Name cannot be blank")
            Return
        ElseIf txtAddress.Text = "" Then
            MsgBox("Address cannot be blank")
            Return
        Else
            If txtPhoneNumber.Text = "" Then
                MsgBox("Phone number cannot be blank")
                Return
            End If
        End If

        custmr.addNewCustomer(txtFirstName.Text, txtLastName.Text, txtAddress.Text, txtPhoneNumber.Text)
    End Sub

---

And finally, the Add Customer code itself:

Code:

---

    Public Sub addNewCustomer(firstName As String, lastName As String, address As String, phoneNumber As String)
        Using objConn As New SQLiteConnection(connectionString)
            Using objCommand As SQLiteCommand = objConn.CreateCommand()
                Try
                    With objCommand
                        objConn.Open()
                        .CommandText = "INSERT INTO customer ('First Name', 'Last Name', 'Address', 'Phone Number')
                              VALUES('" & firstName & "','" & lastName & "','" & address & "','" & phoneNumber & "');"
                        .ExecuteNonQuery()
                    End With
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End Using
        End Using
    End Sub

---

You win Gary :) Since I named things with a space in it, the rest of the code is really a pain! So much so, I'm going back to using: FirstName, LastName, etc.

I created a test sub just to see how it worked with spaces, and it doesn't. I tried for a bit to make it work, but as I said, it's too much of a pain.

So..... here I am with one word field names :)

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 - Parameters
There are a lot of problems that can happen when you take user input and add it to a SQL query. Suppose I have a search box for looking up customers by last name. It'd make sense to see the query built this way:

Code:

---

String.Format("SELECT id, firstName, lastName FROM customers WHERE lastName LIKE '{0}%'", input)

---

Bad things happen if the user knows a little SQL and wants to do bad things. For example, suppose they input the last name "'; DROP TABLE contacts; --" That's going to make our query look like:

Code:

---

SELECT id, firstName, lastName FROM customers
    WHERE lastName LIKE ''; DROP TABLE customers; --%'

---

The user's input tells SQL the SELECT is finished. The semicolon says "Let's do another query." DROP TABLE is a command I didn't mention, it completely deletes the table. Ouch. The '--' is a SQL comment, and stops the rest of the original SELECT query from being parsed (and likely causing an error that prevents the DROP TABLE from running.) You've probably heard of a 'SQL injection' attack. This is how they work. It's worse when they're used to steal sensitive data, but it's never good to allow this to happen.

So how do you prevent them? The best thing to do is called 'sanitizing' user input. This means checking it before putting it in the query. If you find any symbols that mean something special to the database, you escape them. This is sort of like how if you want a " inside a string in VB, you have to use "" instead.

But it is tedious to write your own sanitizer, and programmers often forget to call the sanitize routine. Making just one mistake is all the attacker needs. So it is far, FAR better to write your code in such a way that you can never forget to sanitize it. SQL parameters are one way to do this.

Let's look at String.Format() again. It's like good old printf(). You give it a string with special placeholders inside that might have formatting information. You also give it a list of data items. It looks for each placeholder, then picks a data item that lines up with it, converts that data item to a string, and replaces the placeholder with the string. So this code produces the result beneath it:

Code:

---

String.Format("{0} '{1}' {2}", 25, "hi", New Button())

"25 'hi' {System.Windows.Forms.Button}"

---

It would be nice if we had a String.Format() that knew how to sanitize SQL for us, wouldn't it? That's how working with parameters behaves.

Frustratingly, every database I've encountered uses slightly different syntax for parameters in the SQL query. Some use "?". Some use "{0}" like String.Format(). Most have a couple of different ways, I like the ones that let me give a name to my parameters. SQLite's syntax for a parameter looks like ':name'. It looks like MSSQL/SQLExpress uses '@name'.

So the first thing we do is take a SQL statement that doesn't use parameters and convert it to one that does. This is the reason I've been using String.Format(), all of my placeholder values are where parameters would go. I do this as a reminder to myself that I should really be using parameters. So the top line in this snippet is wrong, and the bottom line is right:

Code:

---

query = String.Format("SELECT id, firstName FROM customers WHERE lastName LIKE '{0}%'", input)

' sqlite
query = "SELECT id, firstName FROM customers WHERE lastName LIKE :lastName"
' MSSQL
query = "SELECT id, firstName FROM customers WHERE lastName LIKE @lastName"

---

Notice I didn't put the '' around the parameter. The parameter engine should be smart enough to do this on its own. So there's another benefit: you can't forget to close a ' in your query if you use parameters. I can't count how many times I've done that.

We obviously have to do some work on the code end. There's a lot of moving parts, and I wish it didn't feel so clunky. Let me try and guide you through.

IDbCommand has a Parameters collection. If parameters are being used, this collection must contain one parameter object per parameter in the query. If you don't provide one per query parameter, you will get a SQL error when the command is executed.

What type are parameters? Well, there is an IDbParameter, and it is implemented by an abstract class DbParameter. Your ADO .NET library will come with a specific class like SqlParameter or SqliteParameter. There are a few important properties and methods of parameters:

• ParameterName is the name of the placeholder the parameter object represents. Some databases/syntaxes don't use a name, those are bad but you don't use ParameterName with them.
• Value is an Object that represents the thing you want to go in place of the parameter. It's usually an Integer, Double, String, or DateTime. Check your documentation to see what types are supported before trying much else.

There's some others, like DbType and IsNullable, that do seem important. But it turns out we can get away without setting them ourselves. We might find a use for them, I'll explain them when and if we get there.

Sadly, parameters are not auto-generated by most IDbCommand types. So every time you make an IDbCommand, you also have to initialize its Parameters collection, setting the names and values for each parameter. And curiously, there aren't often good syntax sugars for doing it all in one go. The API is optimized for creating parameters without a value, and for this stage of the tutorial we want to set both name and value at the same time. It gets tedious. But here is the example query from before, top to bottom, using a parameter:

Code:

---

Using connection As SQLiteConnection = CreateConnection()
    Using command As SQLiteCommand = connection.CreateCommand()
        Dim lastNameParameter As New SQLiteParameter("lastName", input)
        command.Parameters.Add(lastNameParameter)

        Using reader As IDataReader = command.ExecuteReader()
            ' ...
        End Using
    End Using
End Using

---

It's often useful to prepare a helper method to set up the parameters for a command. More on that later. Notice the ":" or "@" isn't part of the parameter's REAL name, it's just a sign to the SQL engine that the token that follows is a parameter name.

If you're making a lot of INSERT statements, you'll be setting up a lot of parameters. One trick is to reuse the same command object, so you only have to initialize the parameters once. I wrote some code that did that in a previous project, it ended up looking something like this:

Code:

---

Sub InsertAll(ByVal data As ???())
    Using connection As SQLiteConnection = CreateConnection()
        Using command As SQLiteCommand = connection.CreateCommand()
            InitializeInsertParameters(command)
            For Each dataItem In data
                UpdateParameters(command, dataItem)
                command.ExecuteNonQuery()
            Next
        End Using
    End Using
End Sub

Private Sub InitializeInsertParameters(ByVal command As SqliteCommand)
    command.Parameters.Add(New SqliteParameter("first"))
    ...
End Sub

Private Sub UpdateParameters(ByVal command As SqliteCommand, ByVal item As ???)
    command.Parameters("first").Value = item.First
    ...
End Sub

---

This way I didn't clutter up my main block of code with lots of lines devoted to parameter management.

Parameters work in INSERT, UPDATE, DELETE, and SELECT queries. You should get in the habit of using them for EVERY place where you need to provide something variable, especially when you take user input.

The attachment for this lesson started based on the Lesson 2 file. I updated every query to use parameters, and for illustrative purposes added a loop to the end that lets you do a search against the database. Try typing a harmful string, or anything that might break the query like "'''''". This meant losing most of the benefits of my ExecuteNonQuery() helper method from Lesson2, and as a result Lesson3.vb is a bit more verbose. I know some ways to cut down on this, but they seemed 'too complex for tutorials' so I'm going to put them aside for a future lesson about building a baby ORM.

Don't be scared off by this extra 'complexity' in the form of having to repeat the Using statements. It cannot be understated how important it is to use parameters. I will go so far as to say code that doesn't is stupid code, not just bad code. That said, when I'm in a hurry and writing something JUST for myself, sometimes I don't use them. I'd never write a real program without them.

Other databases like MSSQL have other ways to deal with security in addition to parameters, such as user 'roles' that don't allow DELETE/DROP TABLE commands, etc. Stored procedures also help, I believe you can't make one take data WITHOUT using parameters. Those are good things to learn about if you are administrating one of those databases. If you're using SQLite, parameters are the safety net. Don't omit them.

Next up: I'm not sure.

At this point, if you master parameters, you know enough to write a full-scale CRUD application backed by a database. Your code will be clunky, because there are mechanisms that can take some tedium out of working with ADO .NET. And we haven't talked about GUI at all, which has some additional neat features.

So the natural path is probably to discuss DataSet/DataTable/DataAdapter next. From there, the relationship between those and DataGridView can be explored. That's a great segue into Windows Forms data binding. After that, some non-DataAdapter techniques for WinForms data binding can be investigated, though I think they're not as often used. I think, after that, the remaining topics are all "do your own research" because they'll have to be tailored to your specific database engine and application requirements.

Thank you S for the file. I was not thinking about those kind of things because I was just trying to get the basics to work. But the file really points out a much better way of doing things. I'm going to integrate the code into what I have now.

One other thing... I will be gone for a few hours. Need to take care of some business.

And thanks for the "Whack on the side of the head". (did you read the book)

I have a question about properties (yet again). Are they used only to protect private data or is there something else too?

You can put logic in the property - right? That makes it more then just protecting private data, on just that point alone.

That's an odd question for this thread, but OK.

The major advantage properties have over fields is they give you a getter and setter where you can do something when the value is changed or retrieved. The most common thing to do is implement INotifyPropertyChanged. But there are other things.

Ok, thanks guys. I guess it IS off topic. I guess I was wondering out loud. But I'm glad I did. I think I just added a little more to my understanding of what they can do :)

S, I looked up INotifyPropertyChanged and I think that's something really neat. I'm sure sometime (not yet) we can use it in the project.

edit: have to leave again <sigh>

Hi everyone, the code below comes from the file SS attached. He's done something in a way that I don't know about. Can anyone explain this to me? I don't know if you need more code or not.

thanks

Code:

---

Dim updateQuery As String = "UPDATE customers SET " &
            "firstName = :firstName, lastName = :lastName address = :address, phoneNumber = :phoneNumber" &
            "WHERE id = :id"

---

If it's just another way to do a query, then I'm all for it. No messing with the weird string quotes and single quotes.

edit: I'm guessing that it has to do with combining a SQL Query with properties

That is an UPDATE query using parameters. I didn't go through every kind of query in the lesson, because it'd have been redundant. The example I used was a SELECT:

Code:

---

query = "SELECT id, firstName FROM customers WHERE lastName LIKE :lastName"

---

In SQLite, named parameters begin with a ':' symbol, then the name follows. It's an '@' symbol in MSSQL.

Quote:

---

Originally Posted by Sitten Spynne

In SQLite, named parameters begin with a ':' symbol, then the name follows. It's an '@' symbol in MSSQL.

---

For the sake of completeness in SQLite one can use any of ':', '@' or '$' to identify named parameters.

Sitten, a couple of other slightly pedantic but important corrections to your post 83. Both are to do with the first general point about primary keys:-

1. You say there can only be one null value. There can't be any nulls on a primary key. You can have a single null on a unique index but the primary key has "No Nulls" as an additional constraint.
2. You say the records are ordered according to the primary key. They're actually ordered according to the clustered index (unless the table is a "heap") which can differ from the primary key. Typically you'll put the clustered index on the primary key but the querying pattern can dictate that the clustered index is more valuable on a different field. E.g. Date fields and "ordering" fields are often better candidates.

Sorry to be pedantic but those are two classic gotcha questions at interviews and the like.

Quote:

---

Totally agree on spaces in column names. It's legal. But to most DB developers it's 'not right'. Everyone's used to column names being one word. I don't know what the consensus is on camelCasing vs. PascalCasing, I tend to prefer camelCasing and try to stick with it.

---

I'd say that the most common convention is to Use_Under_Scores. People also tend to favour EVERYTHING_IN_UPPER_CASE. The conventions in DB world seem to be a lot more relaxed than in Dev world though and different DBAs seem to have different standards.

Quote:

---

Originally Posted by Sitten Spynne

That is an UPDATE query using parameters. I didn't go through every kind of query in the lesson, because it'd have been redundant. The example I used was a SELECT:

Code:

---

query = "SELECT id, firstName FROM customers WHERE lastName LIKE :lastName"

---

In SQLite, named parameters begin with a ':' symbol, then the name follows. It's an '@' symbol in MSSQL.

---

Thanks for the answer. I need to study up on parameters because it's a new thing to me. Also, I just checked the thread and didn't know I had replies. I think my mail program has lost it's mind :)

And Dex, thank you for the info too. I've never liked the use of all caps for a variable. This goes all the way back to my C coding days with things like MAX_PATH. I know it's standard in the database world though. I guess I should probably start doing it that way.

And hi Carlos, it's nice to meet you. I thank you as well for the info. I've seen the dollar sign used before, but I have to admit that I like the way ':' looks, so I will probably stick with that. Thanks for joining in on the thread.

jumper77

Since SQLite will allow "@" for parameters it is better for you to select that character for parameters.

Otherwise once again you are marrying yourself to this very low profile database engine.

Forget that ":" and "$" are allowed - use "@".

As for column names (which are part of the table object) - I always use proper casing without spaces. MasName, PerName, MasId, SSN, DOB, Birth, Gender, DateEntered, DatePaid, AmountPaid - stuff like that.

And I am 100% consistent - for 20 years of doing SQL now. That is the most important.

All UPPERCASE looks terrible in any form given to the user. Tables in database can be reviewed using EXCEL - I would never want have ALL UPPERCASE column headings in EXCEL.

My personal preference - but remember it's not just you seeing these field names. They creep up everywhere.

And really important - they are not VARIABLE names like you kind of implied in your last post.

<sigh>I'm obviously in pedantic mode today. I hope I don't put anyone's nose out of joint.</sigh>

Quote:

---

Forget that ":" and "$" are allowed - use "@".

---

This is a bit a Microsoft centric view of the world, the @ is hardly universal. Postgres, for example, uses $ and doesn't recognise the @ as a parameter. I don't think Oracle uses any symbol at all - it just requires that the name in the query matches the name of the parameter. Since we're a Microsoft orientated forum I think you're advice for Jumper to favour the @ is good but I'd temper it by saying, don't get wedded to any particular syntax for parameter handling. It's one of the things that isn't in the ANSII standard so will change from platform to platform.

edit>

Quote:

---

I've never liked the use of all caps for a variable

---

Me neither, I think it looks ugly. I'm not keen on the underscores either and tend to use Pascal case in my own projects. I recognise that this is a bit maverick, though, and has upset more than one DBA I've worked with. Mostly I just tow the line and follow whatever culture the company I'm contracted to favours, which is SHOUTY_UNDERSCORES 9 times out of 10.

Thanks for the advice Sz. You've been working with SQL quite a while. After working with it for that many years, you must be pretty good at it too. I think you're right about be constancy. Although I'm not great at it (but working on it).

And Dex, I smiled when I saw SHOUTY_UNDERSCORES . It does look awful. But you're right about one thing. Follow the ways of the company where you work. Something I always did because it seemed like every company has their own coding standards.

And SS, thanks again for the file, but in order to help me understand how to do things, I'm trying to write my own class. I need to understand and implement past lessons into this project. That will take more time, but well worth it.

Wow. Even doing the small version of DataAdapter is a big topic. It's not going to show up today, and the introduction is growing so big I worry it'll take multiple posts. Curse me for saying, "Oh, I'll show how to do it without FillSchema() then build up to that." Ugh.

Jumper: your #101 sort of... confuses me. Lesson 3 is entirely about parameters! I sort of expect the thread to be read in order. This is also the 3rd or 4th question about things directly inside the lessons. I know they're big, but there are lots of little gotchas worth knowing. Odds are if there's something weird in the .vb file, I explained it!

RE: "What symbol should I use for parameters?"

I'm not going to switch to '@' in this thread. I don't want to spend time editing posts AND updating code attachments, and I mentioned when I introduced parameters that each db engine might have different syntax. But in the future, I think I'm going to use '@', for the silly reason "it's easier to see than ':'". I'm comfortable expecting readers to be able to follow along.

RE: casing:

Being 100% consistent is what really matters. I'm actually breaking my rules, and feel sad now. For now, I'm going to continue to camelCase everything becasue it is what I've done so far. What I'd have done if I was smart is follow something similar to how we case things in .NET: 'important' things like tables and database names would be PascalCased. 'Less important' things like columns would be camelCased. I dropped the ball on this, so will stick with "everything is camelCased". The one thing I've noticed: we're batting 1.000 on "no one likes ALL_CAPS".

I do want to call this out:

Quote:

---

Otherwise once again you are marrying yourself to this very low profile database engine.

---

SQLite is used by quite a few big names. It ships with iOS and Android, and is the only database I know that runs native on those phones. It's in the flight software for the Airbus A350. It's used by both Firefox and Chrome. It's bundled with PHP, Python, and REALbasic. All of this accounts for millions of users depending on it as a critical piece of their software.

Please let's not continue to waste time on this. SQLite shines in terms of no-network data storage with simple relational schemas. This is a tutorial about CRUD with one table and ten rows. And you're worrying about what happens when autoincrement overflows a 64-bit integer, or whether the SQL is exactly portable to a database designed to support thousands of simultaneous requests. These are issues I never consider when writing applications with SQLite.

Or, in a nutshell, you're telling me that VB is a "stupid toy language" and I should be using C. It's the same pattern, the same line of thought, and it's really not a healthy conversation.

I'd really lke it if you could prepare a post about when you wouldn't use SQLite. I think you know a lot more than I do about databases, and you could probably write a very good guide. And I think you have far more experience with large-scale databases than I do. But I suspect your answer is "I'd never use it, not even in the cases where it's appropriate", and I don't think that makes me the one married to a technology.

RE: FunkyDexters's #100:

I'm going to edit those facts back in, but my feelings are:

1) I sort of guessed this, since values have to be unique it seemed intuitive that null would be a bad idea. Also boolean columns.

2) I guess "sorting" was a bad word, I meant this in the sense that primary keys are typically an index. And, treating the db engine as a balck bocx

Quote:

---

And SS, thanks again for the file, but in order to help me understand how to do things, I'm trying to write my own class. I need to understand and implement past lessons into this project. That will take more time, but well worth it.

---

I don't know what this means. What I read out of it is something like, "Thanks for writing a big post and a large code example, but I'd rather figure this out on my own, asking one question at a time."

I mean this with no anger, and no threat: if that is the case, please let me know. I'm sitting here planning something like 10 hours of work on the first couple of DataAdapter lessons, and it would be better for me to not write them if you find my large posts too hard to process. I still want to write them, but perhaps the best place to publish them would be a Code Bank series or a The Code Project series.

I said low profile - you worked yourself up a whole lot over a simple statement.

It is a low profile database engine - intentionally.

I do not care where it runs - that doesn't change what it's intended to be.

All I said was use "@" since it's allowed and aligns with the MS world. That alignment is not a bad thing.

Chill out dude :)

I've had about three cups of coffee now, is that chilling? ;) I get it now.

Quote:

---

Jumper: your #101 sort of... confuses me. Lesson 3 is entirely about parameters! I sort of expect the thread to be read in order. This is also the 3rd or 4th question about things directly inside the lessons. I know they're big, but there are lots of little gotchas worth knowing. Odds are if there's something weird in the .vb file, I explained it!

---

I apologize for the extra questions. With this project I have not been going back to your earlier posts, but I will change that.

Quote:

---

I mean this with no anger, and no threat: if that is the case, please let me know. I'm sitting here planning something like 10 hours of work on the first couple of DataAdapter lessons, and it would be better for me to not write them if you find my large posts too hard to process. I still want to write them, but perhaps the best place to publish them would be a Code Bank series or a The Code Project series.

---

I know you put in a lot of work into this, and I'm grateful. I also don't want to give you the idea that I'm not taking this seriously, because I am. So I believe that I should present myself in a more professional manner. And I will start doing this NOW.

Again, I do thank you for your help. I've learned enough things from you that would take years otherwise.

I kind of need an answer. ;) Big posts, or little posts?

Either would be fine. You choice. Large posts concerning lessons will take longer to assimilate. But that will shorten with I stop making off topic and sidetracked posts. Everyone here knows a lot more than I and I'm running as fast as I can to keep up. This is not a comment about the length of your messages, it's about my lack of knowledge in these areas.

OK, cool. I think I'll percolate a bit on the DataAdapter stuff and decide how big or small I want the posts to be. That'll give you some time to let parameters sink in.

Shortest possible version:

In a lot of queries, I was using String.Format to make "blanks" that I'd fill in with data. Something like this:

Code:

---

UPDATE customers SET firstName = _______ WHERE id = ________

---

When I use String.Format(), the 'blanks' are going to be {0} and {1}, and I have to remember whether to use '' or not.

When you use parameters, you put a symbol with your database's syntax for parameters where the blanks go. SQLite and MSSQL agree on '@', I think maybe I'm going to just switch to that and throw away what I said about consistency.

Code:

---

UPDATE customers SET firstName = @firstName WHERE id = @firstName

---

You can't use that string with a command as-is. You have to tell it what values to assign to which parameter. To do that, you add parameters (SqLiteParameter/SqlParameter) to the command's Parameters collection. Each parameter in the query should have a parameter with the same name in the collection. (Of note: the "@" sign or ":" sign only belongs in the SQL query to tell SQL where the parameters are. When you set your parameter object's name, don't include the symbol.)

When you do this, the code handles deciding if it needs '' around the value, or how it needs to be formatted, and even goes so far as to escape dangerous characters. Using parameters is a little more complex because of how you have to set them up, but they eliminate an entire universe of dangerous mistakes. So they're well worth it.

Quote:

---

You have to tell it what values to assign to which parameter. To do that, you add parameters (SqLiteParameter/SqlParameter) to the command's Parameters collection. Each parameter in the query should have a parameter with the same name in the collection.

---

Good information. I didn't know that Parameters are collections. This gives me another thing to look up and learn.

Post edited for content

Not quite. Part of what makes this hard is you have to use your imagination to visualize things, but it's hard to visualize something you can't see.

A "query" is the string of SQL that tells the database to do something. Imagine it is like a punch card.

A "parameter" is like a big hole in that query where there should be some data. Imagine if the punch card had a stencil-like blank space in it. Parameters have a name, to tell you which hole they represent, and a value, which is next.

A "value" is the data that goes in the parameter. It's like a bit of card stock you carefully punch holes into then tape to the hole in the punch card where a parameter is. This way, you can use the same punch card for a lot of different data.

A "command" is the object (like SqlCommand) that represents the punch card. A command:

• HAS A query, in the form of the CommandText property. That query might have "holes" that represent parameters.
• HAS A collection of parameters, one for each parameter in the query.

A "parameter" in this sense is an object (like SqlParameter) that represents the taped bit of punch card. It HAS A name and the value it represents.

Part of what is confusing is the word "parameter" is used to refer to both: the 'hole' in the query and the object the Command uses to map that hole to a value.

So, for illustration, suppose we have this SELECT query:

Code:

---

SELECT id, firstName, lastName FROM customers WHERE lastName = @lastName

---

This is a SQL query that says: "I will select rows from the table named 'customers'. Each row will have three columns, and my result set will only contain rows where the column 'lastName' is equal to a parameter 'lastName' that will be supplied when I am executed."

To use it in code, you have to create a command object with that query text. Then, you have to create a parameter object with the name 'lastName' and give it a value. Then, you add the parameter object to the command object. When you execute the command, the DB puts the value in the parameter object where the '@lastName' placeholder is in the query, taking care to format it correctly.

It would be SUPER COOL if I could demonstrate this by showing you the SQL generated by the command. Alas, I have yet to find a way to do that. I don't think it actually works that way, I think the process of assigning values to the parameters happens via calls to the SQLite C libraries. Don't quote me on that.

Update: The project is now a console app. No need to make the learning curve any longer than it should be. I'm not good at UIs anyway.

Quote:

---

A "parameter" is like a big hole in that query where there should be some data. Imagine if the punch card had a stencil-like blank space in it. Parameters have a name, to tell you which hole they represent, and a value, which is next.

---

Good way to look at it. Makes it easier to understand and remember.

Quote:

---

A "value" is the data that goes in the parameter. It's like a bit of card stock you carefully punch holes into then tape to the hole in the punch card where a parameter is. This way, you can use the same punch card for a lot of different data.

---

Same here where understanding is concerned. I'm all for punch cards that allow reuse.

Quote:

---

Part of what is confusing is the word "parameter" is used to refer to both: the 'hole' in the query and the object the Command uses to map that hole to a value.

So, for illustration, suppose we have this SELECT query:

Code:

---

SELECT id, firstName, lastName FROM customers WHERE lastName = @lastName

---

This is a SQL query that says: "I will select rows from the table named 'customers'. Each row will have three columns, and my result set will only contain rows where the column 'lastName' is equal to a parameter 'lastName' that will be supplied when I am executed."

---

Thanks, parameters make a little more sense now. "To me", I'm now thinking of parameters as values to be used. And notice, I put To Me in quotes. Meaning it might be a bad way to think about it, but it does help clear up things for me.

Quote:

---

To use it in code, you have to create a command object with that query text. Then, you have to create a parameter object with the name 'lastName' and give it a value. Then, you add the parameter object to the command object. When you execute the command, the DB puts the value in the parameter object where the '@lastName' placeholder is in the query, taking care to format it correctly.

---

Nice explanation. I hate to repeat myself, but this is making things more concrete. Understanding how things match up gives a clearer picture of what's going on.

Of course I'll still have to study your code some more, but the explanations in this post will make it much easier on me.

This is what I've been trying to explain with way too many words. You're making me want to retry my DataAdapter lesson. It's at... 300 paragraphs? I don't expect you'd come out of that with much, now that I think about it.

Quote:

---

Originally Posted by jumper77

Update: The project is now a console app. No need to make the learning curve any longer than it should be. I'm not good at UIs anyway.

---

I don't think that's a good idea. Earlier you posted a form with a datagridview and a couple of button. Why take a step backwards. Your already able to display data in a dgv. I think it would be easier for you and us to review your program results that way.

I mean,

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

---

Boom.... Done

Well I glad that I got it finally. Plus, I think 'you' just got the idea yourself. If you write a post trying to explain things and I can hit the down-page button 3 or 4 times before I get to the end... It's probably a bit long and much harder to understand. I just thought of a great way of saying it... You always tell me to take 'baby steps', why don't you take them with me? :)

Seriously though, it's so much easier for me to understand if you take things one point at a time. Plus, it sticks with me a whole lot more. But when I read one of your big posts about things, I end up saying to myself: Ok, I got to learn this, and that, and that, etccccccccccc. Plus, I do hope you know that I'm not fussing at you or anything. But I just realized that if we could meet in the middle somewhere, it would be so much better for the both of us. And PLEASE... not 300 paragraphs :)

So what do you think? Sounds like a plan to me.

edit: Maybe this has something to do with people saying I try to fly before I can walk? Not sure. Just guessing.