I'm using sqlite for the first time, although I've been programming for over twenty years. I'm using C# through VS2022. I'm trying to derive an index value based on the date, so the keys for a given date (24th Jan 2023) would run 2023012401, 2023012402, 2023012403, and the following day would run 2023012501, 2023012502 etc. (Don't ask me why, it's a requirement of the contract).

When I try to enter the values into a field defined as " THISID, INTEGER, NOT NULL, UNIQUE, and set this table as the primary key, SQLite refuses to accept my ID number but reverts to an auto increment in the THISID field.

Have I missed something important here, it works in SQL server?

When I try to enter the values into a field defined as " THISID, INTEGER, NOT NULL, UNIQUE, and set this table as the primary key,

As Yoda said, "There is no try."

Assuming you want an integer primary key for some table, you should write: CREATE TABLE WhatEver (THISID INTEGER PRIMARY KEY, ...) . Note the absence of commas relative to above quote from your post.

Have I missed something important here, it works in SQL server?

Likely yes, and I doubt it works as written in SQL Server.

You may wish to peruse the CREATE TABLE syntax, and set aside some of what you remember from T-SQL.

Thanks Larry, I'm scratching my head over this one. I'm using DBBrowser for sqlite to create the table, but maybe this isn't the right tool? It won't let me write my own CreateTable queries, or maybe again I'm missing something?

George

If I had a "tool" which insisted upon rewriting the SQL I told it to run, that pretend-tool would go straight into my local bit-bucket.

However, I sometimes use a program named "DB Browser for SQLite" which happily executes DDL such as I recommended. Are you using a similarly named tool? If so, what is it, exactly? Or, are you availing yourself of some (overly) clever SQL editing assistance? If so, maybe that needs to be disabled or reconfigured.

That sounds like the tool I'm using, I did a search on 'SSMS for SQLite' and that's what it came up with. I'm using Entity framework to access the database, and I think there's something not right there. George

I'm using Entity framework to access the database

Is this your requirement, your preference, or company decision?

I only ask as there is system.data.sqlite.dll which is a wrapper/library for SQLite which allows very quick and easy database mangling. It would appear adding Entity Framework into the mix as well, you've a complex bunch of tools and not a clear direction whats going on (no offence inferred).

I use vb.net with sqlite and also DB Browser for SQLite so if I can assist, please yell. A small class is usually enough to do 97.45% of database needs...

It won't let me write my own CreateTable queries

Yes it does, but you have to go to the 'Execute SQL' tab. This is the tab where you execute SQL. Here you can enter SQL and execute it.

Can you show the actual CREATE TABLE you are using for thisid?

create table twits
(
  thisid, integer, not null, unique, 
...
);

is not a valid definition of the column thisid and without seeing your actual column definition rather than a description of it, no assistance is possible.

Well, actually it is a valid definition, the rest of it is not valid. However, that definition cannot explain the behaviour that you are describing.

It would probably be perspicacious to show the definition and the problem. A prose description without code is useless, and I doubt anyone wants to play 20 questions to find out the root of your difficulty.

Sorry Keith, My first time on this forum. I'm using DBBrowser to create the tables, and maybe this isn't the right tool?

Here's the definition DBBrowser wrote for me

CREATE TABLE "CODE" ( "ThisID" INTEGER NOT NULL, "TableName" TEXT NOT NULL, "Description" INTEGER NOT NULL, PRIMARY KEY("ThisID") );

George

Seems to work fine. What is the problem?

sqlite> CREATE TABLE "CODE" ( "ThisID" INTEGER NOT NULL, "TableName" TEXT NOT NULL, "Description" INTEGER NOT NULL, PRIMARY KEY("ThisID") );
sqlite> insert into code values (2022040101, 'Name1', 'This is name1');
sqlite> insert into code values (2022040217, 'Name2', 'This is name2');
sqlite> insert into code values (2022061502, 'Name3', 'This is name3');
sqlite> select * from code;
┌────────────┬───────────┬─────────────────┐
│   ThisID   │ TableName │   Description   │
├────────────┼───────────┼─────────────────┤
│ 2022040101 │ 'Name1'   │ 'This is name1' │
│ 2022040217 │ 'Name2'   │ 'This is name2' │
│ 2022061502 │ 'Name3'   │ 'This is name3' │
└────────────┴───────────┴─────────────────┘
sqlite>

So now you need to show your insert statements.

Here's the first save (insert) method where it fails:

The TABL Instance is : TABL.TAID = 2023012401; TABL.TableName = "NewTable"; TABL.Description = "This is a new table";

internal static bool SaveTable(TABL tABL) { using (StarEntities se = new StarEntities()) { try { se.TABLs.Add(tABL); se.SaveChanges(); return true; } catch (Exception) { return false; } } }

I'm using Entity Framework 5 to access the database. Watching the results, it works fine up to the se.TABLs.Add statement, But on the SaveChanges statement, when it commits to the database, TABL.TAID reverts to 1.

Maybe EF5 is stuffing it up?

(sorry, the formatting has been lost in the forum entry).

George

I'm going to re-write my project using ADO.Net rather than EF5 and see if that has the desired result. If it does, and I think it will, then that's the way I'll go.

George

I've re-written the code using ADO.Net, setting a connection and using insert statements, and it works fine, just like your examples do Keith. I guess the problem is in EF/SQLite interface , and I'm not qualified to go digging for that. I think I'll spend the rest of the day looking at EF6 for SQLite, although I don't hold much hope for that either.

Thanks for all your help,

George