SQLite insert into on indexed column

[jostr]

Hi,
I am stuck with SQLite. I have a text file out of MS Access which I want to load into SQLite as it comes from Access. So I use the CREATE TABLE command and afterwards I set an index on the first column with

strSQL = "CREATE UNIQUE INDEX IDX_kennr ON medien('kennummer')"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery

I thought to know that using an the insert into with NULL as entry for the indexed column should generate the next free index value but the column for the index remains empty.

What is my fault?
Can anyone help me?

Thanks in advance

 

[Scubaticus]

Try:

CREATE TABLE "MyTable" ("Ndx" INTEGER PRIMARY KEY AUTOINCREMENT)

 

[jostr]

thanks Scubaticus for the short term reply.
But I think if I used your code I would create a table with a single table. What I want to achieve is that I open an ascii file by Loadcsv into a table object and then import it into the sqlite database. The advantage I see is that the structure of the ascii file can alter and I can use the coding without adding new columns to the code of the create table statement.
Any idea?

 

[Scubaticus]

Sorry for the misunderstanding. It was just a sample of how to create an auto increment column in SQLite, not the solution for your problem.

Don't you create the table just before looping through the table records and inserting them? Perhaps you can be a little more clear about what exactly is your goal?

 

[Erel]

But I think if I used your code I would create a table with a single table. What I want to achieve is that I open an ascii file by Loadcsv into a table object and then import it into the sqlite database. The advantage I see is that the structure of the ascii file can alter and I can use the coding without adding new columns to the code of the create table statement.
Any idea?

You can convert the table to a SQL table with Connection.CreateSQLTable.

 

[jostr]

Hi Erel,
yes I used con.createsqltable to insert the ascii file through a table in a form into a new table in the sqlite database. That way is essential for me as the ascii file can change in number or order of columns and the coding can remain the same.

Afterwards I used
strSQL = "CREATE UNIQUE INDEX IDX_kennr ON medien('kennummer')"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery
to create an index in the kennummer column in the sqlite table medien. I understand from the sqlite documentation that this unique index forces if for added datasets this column is filled with the NULL expression the next free index value is inserted in thís dataset. But in my program the column remains empty.
Using your sql-Testing program, where I changed the structure view from table to index, I can see the index, but selecting all data from the table I see the empty column in the dataset. In my program the dataset is not shown, I think because of the missing index.
I have an idea: when I use this way all columns get the type string. Does the index functionality I described only work with integer? But then I think I can't use the con.createsqltable command?
Thanks for your help.

 

[Erel]

con.CreateSQLTable checks the table's columns types and accordingly sets the SQL columns types.
If you load a csv file without adding columns to the table control, all columns will be of String type. You can however first manually add the table columns and then load the csv file. That way you will be able to set the correct columns types.