sqltie questions insert into

[faustf]

hi guys 

i try to insert into db  sqlite a 2 variable , my db is:

|id|name|xml_code|

1 questions? sqlite can set autoincrement a id ???

2 questions ?

this syntax is correct???

_SQLite_Startup()
    If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!" & @CRLF & @CRLF & _
            "Not FOUND in @SystemDir, @WindowsDir, @ScriptDir, @WorkingDir, @LocalAppDataDir\AutoIt v3\SQLite")
   ; Exit -1
    EndIf
    Local $DB=_SQLite_Open(@ScriptDir&"\WEB-SITE\eBay\eBay.db")
    If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't create a memory Database!")
    ;Exit -1
    EndIf

    _SQLite_Exec(-1,"INSERT INTO eBayNewItem (titolo,xml_code) VALUES ("&$titolo&","&$_XML_item_insert&");")
    If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't insert in  Database!")
    ;Exit -1
    EndIf
    _SQLite_Close()
    _SQLite_Shutdown()

i have  return error can't insert in Database!

 

[Danyfirex]

Hello. Check the error message _SQLite_ErrMsg().

 

Also make sure you've created your id field INTEGER PRIMARY KEY AUTOINCREMENT.

 

Saludos

[jchd]

In most SQLite uses, the AUTOINCREMENT qualifier is superfluous and in general doesn't mean what people think it means. Declaring an explicit rowid as Id INTEGER PRIMARY KEY  is good enough for most uses. Making it not nul is also good due to historic reasons.

Note that your DB is not memory-based, contrary to the error message shown. Also if the DB is new, you need to explicitely create a table to hold your data.

In SQL, literal text has to be enclosed into single quotes. Then if the text variables you're inserting themselves contain one or more single quote, the statement will fail. A good precaution is to always protect against text containing single quotes by escaping (doubling) them.

Albeit SQLite is flexible enough to let you store any datatype in any column regardless of that column's declaration, I recommend you explicitely declare the column along with their expected datatype.

Your code might then be (untested):

_SQLite_Startup()

If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!" & @CRLF & @CRLF & _
            "Not FOUND in @SystemDir, @WindowsDir, @ScriptDir, @WorkingDir, @LocalAppDataDir\AutoIt v3\SQLite")
    Exit -1
EndIf
Local $DB=_SQLite_Open(@ScriptDir&"\WEB-SITE\eBay\eBay.db")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't create a disk Database!")
    Exit -1
EndIf

_SQLite_Exec(-1,"create table if not exists eBayNewItem (id integer not nul primary key, titolo text, xml_code text)")

_SQLite_Exec(-1,"INSERT INTO eBayNewItem (titolo, xml_code) " & _
                    "VALUES (" & _SQLite_FastEscape($titolo) & "," & _SQLite_FastEscape($_XML_item_insert) & ");")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't insert in Database!")
    Exit -1
EndIf
_SQLite_Close()
_SQLite_Shutdown()

 

Edited October 30, 2016 by jchd
typo

[faustf]

the  table is  created ofcourse  not null integer primiry key , and used  your  code

_SQLite_Exec(-1,"INSERT INTO eBayNewItem (titolo, xml_code) " & _
                    "VALUES (" & _SQLite_FastEscape($titolo) & "," & _SQLite_FastEscape($_XML_item_insert) & ");")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't insert in Database!")
    Exit -1
EndIf

but nothing  return the same error can't insert in database ??

is possible have some details of error???

thankz

 

[jchd]

Why not displaying SQLite error message instead of yours?

If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg (-1))
    Exit -1
EndIf

It should be more informative.

[faustf]

o thankz  i  do  right now

[faustf]

i dont know why  now  go , i only modify the  error message

[jchd]

Do you mean it now works flawlessly?

[faustf]

yes i  did do only this  two modify  add a new columns at structure of the table  and  insert  your code for debug

 

now  my table is

|id|titolo|xml_code|Item_spec|

the code is this

_SQLite_Startup()
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!" & @CRLF & @CRLF & _
                "Not FOUND in @SystemDir, @WindowsDir, @ScriptDir, @WorkingDir, @LocalAppDataDir\AutoIt v3\SQLite")
        ; Exit -1
    EndIf
    Local $DB = _SQLite_Open(@ScriptDir & "\WEB-SITE\eBay\eBay.db")
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't create a memory Database!")
        ;Exit -1
    EndIf
    _SQLite_Exec(-1, "INSERT INTO eBayNewItem (titolo, xml_code) " & _
            "VALUES (" & _SQLite_FastEscape($titolo) & "," & _SQLite_FastEscape($_XML_item_insert) &") ;")
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg(-1))
        ;Exit -1
    EndIf
    _SQLite_Close()
    _SQLite_Shutdown()

now  work  but if i try to insert  a new  columns like this

_SQLite_Startup()
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!" & @CRLF & @CRLF & _
                "Not FOUND in @SystemDir, @WindowsDir, @ScriptDir, @WorkingDir, @LocalAppDataDir\AutoIt v3\SQLite")
        ; Exit -1
    EndIf
    Local $DB = _SQLite_Open(@ScriptDir & "\WEB-SITE\eBay\eBay.db")
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't create a memory Database!")
        ;Exit -1
    EndIf
    _SQLite_Exec(-1, "INSERT INTO eBayNewItem (titolo, xml_code,item_spec) " & _
            "VALUES (" & _SQLite_FastEscape($titolo) & "," & _SQLite_FastEscape($_XML_item_insert) &",Multi Variation) ;")
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg(-1))
        ;Exit -1
    EndIf
    _SQLite_Close()
    _SQLite_Shutdown()

i have  this error

sqlite near Varation syntax error

 

[Danyfirex]

set quotes to this part too 'Multi Variation'

 

Saludos

Edited October 31, 2016 by Danyfirex

[jchd]

... that or better _SQLite_FastEscape() the variable.

[faustf]

thankz so much work