SQLite insert into on indexed column

Discussion in 'Questions (Windows Mobile)' started by jostr, Mar 9, 2009.

  1. jostr

    jostr Member Licensed User

    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
     
  2. Scubaticus

    Scubaticus Active Member Licensed User

    Try:

    Code:
    CREATE TABLE "MyTable" ("Ndx" INTEGER PRIMARY KEY AUTOINCREMENT)
     
  3. jostr

    jostr Member Licensed User

    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?
     
  4. Scubaticus

    Scubaticus Active Member Licensed User

    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?
     
  5. Erel

    Erel Administrator Staff Member Licensed User

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

    jostr Member Licensed User

    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.
     
  7. Erel

    Erel Administrator Staff Member Licensed User

    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.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice