SQLite returning zero records

Question

I have exhausted all options and my brain is fried, with that said, I'm using visual studio enterprise 2022 I have installed the NUGET package System.Data.SQLite into my project.

In DB Browser for SQLite I created the database file with two tables.

documentTypes:

CREATE TABLE "documentTypes" (
    "docTypeID"   INTEGER NOT NULL UNIQUE,
    "docType"   TEXT NOT NULL,
    "active"    INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY("docTypeID" AUTOINCREMENT)
);

documentFields:

CREATE TABLE "documentFields" (
    "fieldID"   INTEGER NOT NULL UNIQUE,
    "docType"   TEXT NOT NULL,
    "fieldName" TEXT NOT NULL,
    "fieldDataType" TEXT NOT NULL,
    "active"    INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY("fieldID" AUTOINCREMENT)
);

Inserted some data:

INSERT INTO documentTypes (doctype) VALUES ('FUNDING NOTICES');
INSERT INTO documentTypes (doctype) VALUES ('CANCELS');
INSERT INTO documentTypes (doctype) VALUES ('WHOLESALES');
INSERT INTO documentTypes (doctype) VALUES ('DEALER TRADE');
INSERT INTO documentTypes (doctype) VALUES ('FACTORY');
INSERT INTO documentTypes (doctype) VALUES ('SC, CL, GAP STATEMENTS');
INSERT INTO documentTypes (doctype) VALUES ('RESERVE STATEMENTS');

INSERT INTO documentfields (docType, fieldName, fieldDataType) values ('FUNDING NOTICES','DATE','dte');
INSERT INTO documentfields (docType, fieldName, fieldDataType) values ('FUNDING NOTICES','STORE','text');
INSERT INTO documentfields (docType, fieldName, fieldDataType) values ('FUNDING NOTICES','LIEN HOLDER','text');
INSERT INTO documentfields (docType, fieldName, fieldDataType) values ('FUNDING NOTICES','AMOUNT','currency');

Data is there:

Select * from documentTypes;

select * from documentFields;

In my application when I run this code the query returns the data:

Dim SQLiteconn As New SQLiteConnection(ConnectionString())
Dim sqlitecmd As New SQLiteCommand("SELECT docTypeID,doctype FROM documentTypes WHERE active=1;", SQLiteconn)
Dim dt As New DataTable
SQLiteconn.Open()

Dim dr As SQLiteDataReader = sqlitecmd.ExecuteReader()

If dr.HasRows Then
    dt.Load(dr)
    If dt.Rows.Count > 0 Then
       lstvDocTypes.DataSource = Nothing
       lstvDocTypes.DataSource = dt
       lstvDocTypes.ValueMember = dt.Columns(0).ToString
       lstvDocTypes.DisplayMember = dt.Columns(1).ToString
    End If
End If
SQLiteconn.Close()

and I populate the List View:

When I select a value on the list view it should load the data from the table documentFields into the second list view:

Dim SQLiteconn As New SQLiteConnection(ConnectionString())
Dim sqlitecmd As New SQLiteCommand($"SELECT * FROM documentFields WHERE docType = '{strDocType}';", SQLiteconn)
Dim dt As New DataTable
Try
    SQLiteconn.Open()

    Dim dr As SQLiteDataReader = sqlitecmd.ExecuteReader()

    If dr.HasRows Then
        dt.Load(dr)
        If dt.Rows.Count > 0 Then
            lstvDocTypeFields.DataSource = Nothing
            lstvDocTypeFields.DataSource = dt
            lstvDocTypeFields.ValueMember = dt.Columns(0).ToString
            lstvDocTypeFields.DisplayMember = dt.Columns(1).ToString
        End If

    End If

    SQLiteconn.Close()

In debug mode I checked the first query against the table documentTypes, and the data reader has rows and populates the list view.

, then I select an item in the list view and check on that data reader and is empty.

I checked the select command and looks good, I copied and pasted it into the DB Browser and retrives the data.

I also noticed that if I inspect the data reader in debug mode, shows that it does have rows, but after the inspection shows no rows at all. It looks like is getting emptied when inspected; but that's a different issue that I'll take care of it.

Here's what I have done to find out what's going on, I removed the SQLite package and reinstalled it, updated the package, I have even tried to use the Microsoft.data.SQlite package and still no luck, if any of you can point me in the right direction I really appreciate it.

• I want to thank everyone who took the time to at least read the question. I have found the answer:

Dim sqlitecmd As New SQLiteCommand("SELECT * FROM documentFields WHERE docType = @DocType;", SQLiteconn)
 sqlitecmd.Parameters.AddWithValue("@DocType", strDocType)

As suggested by Daniel A. White, at first didn't worked because I was enclosing the parameter in single quotations, after the removal; the query performs perfect.

Answer 1

I have found the answer:

Dim sqlitecmd As New SQLiteCommand("SELECT * FROM documentFields WHERE docType = @DocType;", SQLiteconn)

 sqlitecmd.Parameters.AddWithValue("@DocType", strDocType)

As suggested by Daniel A. White, at first didn't worked because I was enclosing the parameter in single quotations, after the removal; the query performs perfect.