# database.insertRecord error

**URL:** <https://forum.xojo.com/t/database-insertrecord-error/34522>\
**Category:** Databases\
**Created:** [November 17, 2016, 12:12am UTC](https://forum.xojo.com/t/database-insertrecord-error/34522 "2016-11-17T00:12:14Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Craig\_Grech](https://forum.xojo.com/user_avatar/forum.xojo.com/craig_grech/32/1499_2.png) [@Craig\_Grech](https://forum.xojo.com/u/Craig_Grech)\
**Post date:** [November 17, 2016, 12:12am UTC](https://forum.xojo.com/t/database-insertrecord-error/34522/1 "2016-11-17T00:12:14Z")

</div>

I’m trying to import any tab delimited text file of various columns and headings into an SQLite database  
eg:

First Name Last Name Email User Name \*Employee ID  
Paul Blogs [paul.Blogs@email.com](mailto:paul.Blogs@email.com)[paul.blogs@email.com](mailto:paul.blogs@email.com) 123  
Rodney Smith rodney.smith @email.au rodney.smith @email.au 456

The database is created with the correct columns and the databaseRecord looks ok in the debug window but I get the error message -

(this one appears once)  
Database error: near “\*”: syntax error

(then this one repeats for each row)  
Database error: near “)”: syntax error

I realise the files have spaces in the field names and there is star characters but it doesn’t seem to make any difference to creating the table. It only fails on entering the rows.

[code] dim txtin as TextInputStream  
dim s as String  
dim lines() as string

```
txtin = TextInputStream.Open(dataFile) //

while not txtin.EOF
  lines.append( txtin.ReadLine )
wend
txtin.close

dim i,count As integer

'Create table
if db.Connect then
  db.SQLExecute ("CREATE TABLE main (ID INTEGER NOT NULL, PRIMARY KEY(ID));")
  
  'Create Headings
  dim columnCount as integer = lines(0).CountFields(chrb(9))
  for i = 0 to columnCount - 1
    dim strHeading as string = lines(0).NthField(chrb(9),i+1)
    dim pstrSQL as string = "ALTER TABLE main ADD COLUMN '" + strHeading + "' Varchar"
    db.SQLExecute (pstrSQL)
  next
  db.Commit
  
  'Enter data
  
  dim intRows as integer =lines.Ubound
  Dim rs As RecordSet = db.FieldSchema("main")
  for i=1 to intRows
    dim c as integer
    dim intFieldCount as integer = rs.FieldCount - 1
    
    // Make database record
    dim row as new DatabaseRecord
    for c = 1 to intFieldCount
      dim strFieldText as string = lines(i).NthField(chrb(9),c)
      
      rs.MoveNext // start with second column (not id)
      dim colName As String = rs.IdxField(1).StringValue
      row.Column(colName) = strFieldText
    next
    db.InsertRecord("main",row)
    if DB.Error Then
      MsgBox("Database Error: " + db.ErrorMessage)
    end if
  next
  db.commit
else
  msgbox db.ErrorMessage
end if[/code]
```

---

<div class="post-metadata">

**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\
**Post date:** [November 17, 2016, 12:52am UTC](https://forum.xojo.com/t/database-insertrecord-error/34522/2 "2016-11-17T00:52:42Z")

</div>

ok… fails WHERE? what error message? on 1st record? or some time into the read?

personally I’d skip all that insertrecord, movenext code and just use straight SQL,  
create an “INSERT INTO MAIN values(”")

and don’t do a boat load of ALTER TABLES… build a string that is a proper CREATE TABLE and execute ONE time

and that “\*” has gotta go

---

<div class="post-metadata">

**Author:** ![Craig\_Grech](https://forum.xojo.com/user_avatar/forum.xojo.com/craig_grech/32/1499_2.png) [@Craig\_Grech](https://forum.xojo.com/u/Craig_Grech)\
**Post date:** [November 17, 2016, 1:01am UTC](https://forum.xojo.com/t/database-insertrecord-error/34522/3 "2016-11-17T01:01:54Z")

</div>

The error message occurs here:

` db.InsertRecord("main",row)
        if DB.Error Then
          MsgBox("Database Error: " + db.ErrorMessage)
        end if`  
I don’t have much control over characters and spaces in the field names as the users create anything they like in Excel which is acceptable then export to tab delimited text.  
The star error is on the first record only, then the bracket error for every record after that.  
Removing the star from the text file still produces the bracket errors.

---

<div class="post-metadata">

**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\
**Post date:** [November 17, 2016, 1:04am UTC](https://forum.xojo.com/t/database-insertrecord-error/34522/4 "2016-11-17T01:04:02Z")

</div>

This is NOT tested, and for ILLUSTRATION ONLY… but it is a much better method… there may be some syntax errors, as I took your original code and altered it … up to you to decide if you want to go this way, and to make the required changes to make it fit your process.

```auto
  Dim txtin As TextInputStream
  Dim s As String
  Dim lines() As String
  dim ColumnCount as integer
  txtin = TextInputStream.Open(dataFile) //
  s=txtin.readall
  s=ReplaceLineEndings(s,EndOfLine.UNIX)
  lines.Split(s,EndOfLine.UNIX)
  txtin.close
  
  Dim i,count As Integer
  Dim SQL As String
  
  'Create table
  If db.Connect Then
    SQL="CREATE TABLE main (ID INTEGER NOT NULL, PRIMARY KEY(ID)")
    
    'Create Headings
    columnCount = lines(0).CountFields(ChrB(9))
    For i = 0 To columnCount - 1
      Dim strHeading As String = lines(0).NthField(ChrB(9),i+1)
      strHeading=ReplaceAll(strHeading," ","_")
      strHeading=ReplaceAll(strHeading,"*","")
      SQL=SQL+ "," + strHeading + " Varchar"
    Next
    SQL=SQL+")"
    db.SQLExecute (SQL)
    db.Commit
    //
    //Enter data
    //
    Dim intRows As Integer =lines.Ubound
    For i=1 To intRows
      Dim c As Integer
      
      
      // Make database record
      
      SQL="INSERT INTO main VALUES("
      For c = 1 To columnCount
        Dim strFieldText As String = lines(i).NthField(ChrB(9),c)
        sql=sql+"'"+strFieldText+"'"
        if c<columnCount then sql=sql+","
      Next
      sql=sql+")"
      db.SQLExecute (SQL)
    Next
    
    db.commit
  End If
```

---

<div class="post-metadata">

**Author:** ![Craig\_Grech](https://forum.xojo.com/user_avatar/forum.xojo.com/craig_grech/32/1499_2.png) [@Craig\_Grech](https://forum.xojo.com/u/Craig_Grech)\
**Post date:** [November 17, 2016, 2:15am UTC](https://forum.xojo.com/t/database-insertrecord-error/34522/5 "2016-11-17T02:15:40Z")

</div>

Thanks I got it to work, with a few alterations.  
One question about using

```auto
"INSERT INTO main VALUES( ...."
```

If the first field is my primary key how do skip it or make it auto-fill without specifying the name of all the other fields and their assigned value?

---

<div class="post-metadata">

**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\
**Post date:** [November 17, 2016, 2:42am UTC](https://forum.xojo.com/t/database-insertrecord-error/34522/6 "2016-11-17T02:42:08Z")

</div>

[quote=298804:@Craig Grech]Thanks I got it to work, with a few alterations.  
One question about using

```auto
"INSERT INTO main VALUES( ...."
```

If the first field is my primary key how do skip it or make it auto-fill without specifying the name of all the other fields and their assigned value?[/quote]  
you’re correct… I just typed that off the top of my head 🙂

it would be more along the lines of “INSERT INTO main (”+") VALUES( …

it could be built at the same time you were building the CREATE TABLE statement

---

<div class="post-metadata">

**Author:** ![Craig\_Grech](https://forum.xojo.com/user_avatar/forum.xojo.com/craig_grech/32/1499_2.png) [@Craig\_Grech](https://forum.xojo.com/u/Craig_Grech)\
**Post date:** [November 17, 2016, 4:50am UTC](https://forum.xojo.com/t/database-insertrecord-error/34522/7 "2016-11-17T04:50:08Z")

</div>

I ditched the primary key for now.  
It works ok but extremely slow on large files (8000 lines)

[code] 'Enter data  
dim intRows as integer =lines.Ubound

```
  for i=1 to intRows
    dim c as integer
    // Make database record
    SQL="INSERT INTO main VALUES("
    For c = 1 To columnCount
     
      Dim strFieldText As String = lines(i).NthField(ChrB(9),c)
      sql=sql+"'"+strFieldText+"'"
      if c<columnCount then sql=sql+","
    Next
    sql=sql+")"
    db.SQLExecute (SQL)
    if DB.Error Then
      MsgBox("Database Error: " + db.ErrorMessage)
    end if
  Next
  db.commit
  msgbox "Data imported"[/code]
```

---

<div class="post-metadata">

**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\
**Post date:** [November 17, 2016, 5:27am UTC](https://forum.xojo.com/t/database-insertrecord-error/34522/8 "2016-11-17T05:27:09Z")

</div>

here is an idea that might speed it up a bit…

Ditch the NthField method  
you have a string that is ^^  
where ^ is a tab (chrb(9))  
and you want ‘’,’’,’’ right?

so `s="'"+replaceAll(lines(i),chrb(9),"','")+"'" `should do the same as that FOR C loop

---

<div class="post-metadata">

**Author:** ![Craig\_Grech](https://forum.xojo.com/user_avatar/forum.xojo.com/craig_grech/32/1499_2.png) [@Craig\_Grech](https://forum.xojo.com/u/Craig_Grech)\
**Post date:** [November 18, 2016, 2:37am UTC](https://forum.xojo.com/t/database-insertrecord-error/34522/9 "2016-11-18T02:37:21Z")

</div>

` // Make database record
        SQL="INSERT INTO main VALUES("
        SQL = SQL + "'" + replaceAll(lines(i),chrb(9),"','")+"'" + ")"
        db.SQLExecute (SQL)`

That’s definitely simpler but it’s still very slow on large files.  
I even put it in a thread with no noticeable difference.

Funny I have code to copy the same data into a listbox and 50,000 rows loads in less than 5 seconds.

---

<div class="post-metadata">

**Author:** ![John\_A\_Knight\_Jr](https://forum.xojo.com/user_avatar/forum.xojo.com/john_a_knight_jr/32/275_2.png) [@John\_A\_Knight\_Jr](https://forum.xojo.com/u/John_A_Knight_Jr)\
**Post date:** [November 19, 2016, 10:33am UTC](https://forum.xojo.com/t/database-insertrecord-error/34522/10 "2016-11-19T10:33:40Z")

</div>

Insert this before `for i=1 to intRows` :

```auto
db.SQLExecute("BEGIN TRANSACTION;")
```

Using a 10,000 row file of random data records matching your example,  
inserted 5 times for 50,000 total rows (with primary key) takes less than 1 second.

---

<div class="post-metadata">

**Author:** ![Jean-Yves\_Pochez](https://forum.xojo.com/user_avatar/forum.xojo.com/jean-yves_pochez/32/21769_2.png) [@Jean-Yves\_Pochez](https://forum.xojo.com/u/Jean-Yves_Pochez)\
**Post date:** [November 19, 2016, 10:37am UTC](https://forum.xojo.com/t/database-insertrecord-error/34522/11 "2016-11-19T10:37:54Z")

</div>

you can also group the “insert” commands into one sentence (with endofline between and “;”)  
then you send a string with 1000 “insert” inside to the database.  
this really speeds things up.

---

<div class="post-metadata">

**Author:** ![Maximilian\_Tyrtania](https://forum.xojo.com/user_avatar/forum.xojo.com/maximilian_tyrtania/32/294_2.png) [@Maximilian\_Tyrtania](https://forum.xojo.com/u/Maximilian_Tyrtania)\
**Post date:** [November 20, 2016, 7:12am UTC](https://forum.xojo.com/t/database-insertrecord-error/34522/12 "2016-11-20T07:12:34Z")

</div>

Or: INSERT INTO ‘tablename’  
SELECT ‘data1’ AS ‘column1’, ‘data2’ AS ‘column2’  
UNION ALL SELECT ‘data1’, ‘data2’  
UNION ALL SELECT ‘data1’, ‘data2’  
UNION ALL SELECT ‘data1’, ‘data2’

as recommeded [here](http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database) . This trick appears to be working with 500 records maximum though. UNION ALL is used here for maximum speed. You may want to use UNION if you need to skip duplicates.

---

<div class="post-metadata">

**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\
**Post date:** [October 29, 2020, 6:33pm UTC](https://forum.xojo.com/t/database-insertrecord-error/34522/13 "2020-10-29T18:33:25Z")

</div>