# SQLite "Error: SQLITE\_ERROR: no such column:"

**URL:** <https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655>\
**Category:** General\
**Created:** [24 July 2019 13:56 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655 "2019-07-24T13:56:34Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![YT2310](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/yt2310/32/35255_2.png) [@YT2310](https://discourse.nodered.org/u/YT2310)\
**Post date:** [24 July 2019 13:56 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655/1 "2019-07-24T13:56:34Z")

</div>

Hello,

I just try a hunderet times but I can't fix it..

I created a new table: CREATE TABLE 'erfassungsbeleg' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 'personal' INTEGER, 'name' CHAR,'kostentraeger' INTEGER,'stunden' INTEGER,'taetigkeit' INTEGER,'epoch' INTEGER, 'date' INTEGER)

This worked. But if i want to insert sth to the database there is always a problem with the column "name" because this is a word/name (if i set numbers in the string it worked but with words in the string it doesn t).

```auto
[{"id":"30814450.0881bc","type":"inject","z":"f8fb4f16.cdbf78","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":320,"y":200,"wires":[["8355bd56.24fd38"]]},{"id":"8355bd56.24fd38","type":"function","z":"f8fb4f16.cdbf78","name":"SQL","func":"// Leerer String wird als Variable sql angelegt\nvar sql = \"\";\n// Das Datum wird deklariert\nvar d = new Date();\nvar epoch = d.getTime();\nvar year = d.getFullYear();\nvar monthX = d.getMonth()+1; \nvar dayX = d.getDate();\nif(monthX.toString().length == 1) \n{\nvar monthX = '0'+monthX;\n}\nif(dayX.toString().length == 1) \n{\nvar dayX = '0'+dayX;\n} \nvar today = year+monthX+dayX;\n// Leeres Objekt wird erstellt\nvar outputs = [];\n\nvar nam = \"text\";\nvar id = \"2571\";\nvar kosten = \"23\";\nvar stunden = \"8\";\nvar index = \"40\";\n\n\n\n\n\nsql = \"INSERT INTO erfassungsbeleg (personal,name,kostentraeger,stunden,taetigkeit,epoch,date)\" +\n \"VALUES (\"+id+','+nam+\",\"+kosten+\",\"+stunden+\",\"+index+\",\"+epoch+\",\"+today+\")\";\n outputs.push({topic:sql});\n\n\nreturn [outputs];","outputs":1,"noerr":0,"x":570,"y":400,"wires":[["2c99cd56.4625ba"]]},{"id":"2c99cd56.4625ba","type":"sqlite","z":"f8fb4f16.cdbf78","mydb":"bff91f4.bf534e","sqlquery":"msg.topic","sql":"","name":"SQLite","x":790,"y":400,"wires":[[]]},{"id":"e0e361f4.136f48","type":"inject","z":"f8fb4f16.cdbf78","name":"","topic":"CREATE TABLE 'erfassungsbeleg' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 'personal' TEXT, 'name' CHAR,'kostentraeger' INTEGER,'stunden' INTEGER,'taetigkeit' INTEGER,'epoch' INTEGER, 'date' INTEGER)","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":480,"y":620,"wires":[["2c99cd56.4625ba"]]},{"id":"bff91f4.bf534e","type":"sqlitedb","z":"","db":"C:\\Users\\name\\SQLite\\sqlite","mode":"RW"}]

```

Maybe I m too stupid..

---

<div class="post-metadata">

**Author:** ![Brian5600](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/brian5600/32/94271_2.png) [@Brian5600](https://discourse.nodered.org/u/Brian5600)\
**Post date:** [24 July 2019 14:21 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655/2 "2019-07-24T14:21:10Z")

</div>

Hello YT2310

You use “nam” instead of “name”, when You specify the colums in the insert statement in the function. The colums in the create and the insert statement must of couse be equal. Your create statement in the code and in the text is also not equal on nam/name.

Best regards  
Brian

---

<div class="post-metadata">

**Author:** ![YT2310](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/yt2310/32/35255_2.png) [@YT2310](https://discourse.nodered.org/u/YT2310)\
**Post date:** [25 July 2019 05:45 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655/3 "2019-07-25T05:45:00Z")

</div>

Hi Brian5600,  
that's not the problem. This is an error that has arisen since I have tried to find out if it is the name "name" (thought it might be because it is an instance or something in SQL ..). My problem is that although my "Name" tab is set as TEXT, I can read a string of numbers into the database, but not a string of letters.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [25 July 2019 06:25 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655/4 "2019-07-25T06:25:27Z")

</div>

Show us what a debug node showing the message going to the SQL node shows and the full text of the error that you see. Set the debug node to show complete message.

---

<div class="post-metadata">

**Author:** ![YT2310](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/yt2310/32/35255_2.png) [@YT2310](https://discourse.nodered.org/u/YT2310)\
**Post date:** [25 July 2019 06:45 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655/5 "2019-07-25T06:45:27Z")

</div>

This is what is going in to the sql node:  
{"topic":"INSERT INTO erfassungsbeleg (personal,name,kostentraeger,stunden,taetigkeit,epoch,date)VALUES (2571,Max,23,8,40,1564036958112,20190725)","\_msgid":"60ebd092.0d281"}

and this is the error which is shown:

"Error: SQLITE\_ERROR: no such column: Max"

> **screenshot**
>
> ![grafik](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/c/cc9980626f022d44ba82393f4d2aab26dc04e4ec.png)

---

<div class="post-metadata">

**Author:** ![ukmoose](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ukmoose/32/13_2.png) [@ukmoose](https://discourse.nodered.org/u/ukmoose)\
**Post date:** [25 July 2019 07:01 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655/6 "2019-07-25T07:01:01Z")

</div>

What happens if you put Max in quotes?

---

<div class="post-metadata">

**Author:** ![YT2310](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/yt2310/32/35255_2.png) [@YT2310](https://discourse.nodered.org/u/YT2310)\
**Post date:** [25 July 2019 07:30 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655/7 "2019-07-25T07:30:33Z")

</div>

the error still emerges exactly the same

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [25 July 2019 07:42 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655/8 "2019-07-25T07:42:20Z")

</div>

You used single `'` quotes correct ?  
Show us the input.

```auto
INSERT INTO erfassungsbeleg (personal,name,kostentraeger,stunden,taetigkeit,epoch,date) " +
        "VALUES ("+id+",'"+name+"',"+kosten+","+stunden+","+index+","+epoch+","+today+")"

```

---

<div class="post-metadata">

**Author:** ![YT2310](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/yt2310/32/35255_2.png) [@YT2310](https://discourse.nodered.org/u/YT2310)\
**Post date:** [25 July 2019 08:00 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655/9 "2019-07-25T08:00:23Z")

</div>

```auto
var sql = "";

var d = new Date();
var epoch = d.getTime();
var year = d.getFullYear();
var monthX = d.getMonth()+1; 
var dayX = d.getDate();
if(monthX.toString().length == 1) 
{
var monthX = '0'+monthX;
}
if(dayX.toString().length == 1) 
{
var dayX = '0'+dayX;
}   
var today = year+monthX+dayX;

var outputs = [];

var namen = "Max";
var id = 2571;
var kosten = 23;
var stunden = 8;
var index = 40;

sql = "INSERT INTO erfassungsbeleg (personal,name,kostentraeger,stunden,taetigkeit,epoch,date)" +
        "VALUES ("+id+","+namen+","+kosten+","+stunden+","+index+","+epoch+","+today+")";
        outputs.push({topic:sql});

return [outputs];

```

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [25 July 2019 08:01 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655/10 "2019-07-25T08:01:28Z")

</div>

You need to fix the sql part with single quotes.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [25 July 2019 08:11 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655/11 "2019-07-25T08:11:13Z")

</div>

Show us what the debug now shows, it needs to have quotes round Max. The reason is that without the quotes SQL assumes you want to insert the value of the column called Max into the record, whereas you want to insert a string "Max".

---

<div class="post-metadata">

**Author:** ![YT2310](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/yt2310/32/35255_2.png) [@YT2310](https://discourse.nodered.org/u/YT2310)\
**Post date:** [25 July 2019 08:17 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655/12 "2019-07-25T08:17:09Z")

</div>

now it shows this:

![grafik](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/7/750ee808ac8dece7726973968e244a72ff62d6af.png)

my problem was exatly what you just both discribed

---

<div class="post-metadata">

**Author:** ![YT2310](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/yt2310/32/35255_2.png) [@YT2310](https://discourse.nodered.org/u/YT2310)\
**Post date:** [25 July 2019 08:19 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655/14 "2019-07-25T08:19:18Z")

</div>

there is no error any more 🙂  
this fixed my problem!  
Thank you guys!

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [25 July 2019 08:20 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655/15 "2019-07-25T08:20:05Z")

</div>

> [@YT2310](#):
>
> my problem was exatly what you just discribed

Just to clarify, it is the error that both @bakman2 and myself both described.

---

<div class="post-metadata">

**Author:** ![YT2310](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/yt2310/32/35255_2.png) [@YT2310](https://discourse.nodered.org/u/YT2310)\
**Post date:** [25 July 2019 08:43 UTC](https://discourse.nodered.org/t/sqlite-error-sqlite-error-no-such-column/13655/16 "2019-07-25T08:43:54Z")

</div>

yes, see where my solution is set..