1

I am using the following query in C# and it does not return any data (meaning there are 0 rows returned or the first reader.Read() returns false

var commandText = 
            "SELECT m.Id, m.LastName, m.FirstName, m.MembershipStartDate as     StartDate, m.MembershipEndDate as EndDate, t.Id as TierId, t.Name, t.Value as Dues " +
            "FROM Member m " +
            "INNER JOIN MembershipTiers t ON m.MemberShipTierId = t.Id";

var command = _connection.CreateCommand();
command.CommandText = commandText;
command.CommandType = CommandType.Text;

SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    var member = new Member();

    member.Id = Convert.ToInt32(reader["Id"]);
    member.LastName = Convert.ToString(reader["LastName"]);
    member.FirstName = Convert.ToString(reader["FirstName"]);
    member.StartDate = DateTime.Parse(Convert.ToString(reader["StartDate"]));
            member.EndDate = reader["EndDate"] == null ? DateTime.MaxValue : DateTime.Parse(Convert.ToString(reader["StartDate"]));
    member.IsVested = (Convert.ToInt32(reader["IsVested"]) != 0);

    member.Tier = new MemberShipTier();
    member.Tier.Id = Convert.ToInt32(reader["TeirId"]);
    member.Tier.Name = Convert.ToString(reader["Name"]);
    member.Tier.Value = Convert.ToInt32(reader["Dues"]);

    members.Add(member);
}

When I run this in the DB Browser for SQLite is works fine.

Successful Query

When I get rid of the INNER JOIN it works fine.

Here is the sql generated from the DB Export (just a test db to get going with)

BEGIN TRANSACTION;
CREATE TABLE `Sponsorship` (
`SponsorMemberId`   INTEGER,
`NewMemberId`   INTEGER,
`DateSponsored` TEXT
);
CREATE TABLE "MembershipTiers" (
`Id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`Name`  TEXT NOT NULL UNIQUE,
`Value` NUMERIC NOT NULL
);
INSERT INTO `MembershipTiers` VALUES (1,'Senior Family',3310);

CREATE TABLE `Member` (
`Id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`LastName`  TEXT NOT NULL,
`FirstName` TEXT NOT NULL,
`MemberShipTierId`  INTEGER NOT NULL,
`MembershipStartDate`   TEXT,
`MembershipEndDate` TEXT,
`IsVested`  INTEGER NOT NULL DEFAULT 0
);
INSERT INTO `Member` VALUES (1,'Dodge','Branden',1,'2015-10-20','',1);
COMMIT;
user1701907
  • 98
  • 11
  • 1
    When you say "it doesn't return any data", what do you mean exactly? How are you accessing the data? (Show us the code). – Gary McGill Dec 23 '15 at 15:53
  • 1
    are you sure you are connecting to the correct database? For example, will any query run successfully, or can you get part of the query to run maybe select * from Member ? – Alex Dec 23 '15 at 15:54
  • @Alex: OP said it works fine without the `INNER JOIN` – Gary McGill Dec 23 '15 at 15:56
  • It works with just the select * from member. I verified I am accessing the same database in both cases. By doesn't return any data I mean there are 0 rows in the resulting reader. – user1701907 Dec 23 '15 at 16:10
  • Please show how you are accessing the data - what do you do with the `reader` variable once it's initialized? – Gary McGill Dec 23 '15 at 16:11
  • Code snippet is updated – user1701907 Dec 23 '15 at 16:17
  • As another note I am using .net runtime 4.0 – user1701907 Dec 23 '15 at 16:23
  • Have you tried select * with the join, rather than naming the fields? Also, have you tried joining different tables to see if that works? And other types of joins eg left or right? – Alex Dec 23 '15 at 16:23
  • I tried just the * with no success. I really don't have any other data to try but I can make some. – user1701907 Dec 23 '15 at 17:01
  • There is no conceivable reason why this would be the case -- where it works without the inner join but does not work with it; unless you are reading two completely different database or you have an uncommitted insert/update within your browser. – Hambone Dec 23 '15 at 22:01
  • 1
    Thanks Everybody. @Hambone there was an uncommitted insert statement that was fine in the tool but was not yet written to the database. Even after pressing apply it wasn't there. I had to exit the tool and then it was there. – user1701907 Dec 24 '15 at 17:45

0 Answers0