INSERT tries to use variable argument as a column name

[dochawk]

I'm plodding through this, and making progress. I have a unique primary field (uniqDna) and another to use as a working key (dnaKey).

After much negotiation, I can initialize a SQLite db and load and save a record. When I try to make a new record, though, I run into problems. First I check to see how many records are there:

Code: Select all

   put "SELECT count(*) FROM dna" into dCmd
   put revDataFromQuery(tab,,debtorDB,dCmd)  into lastRec
   
    
   put "INSERT INTO dna (uniqDna,dnaKey) VALUES (null,lastRec+1)" into tSQL
   revExecuteSQL debtorDB, tSQL
   put the result
   

The second block, though, yields

Code: Select all

no such column: theNewRec

in the messagebox.

I've tried putting parenthesis, single brackets, and double brackets around lastrec+1, and putting that value into a newValue variable to get around this, but it insists on interpreting it as a column (except for the doubles, which throw another error on the unrecognized closing token).

both of the columns of the table are INTEGER. I'm using the internal SQLite.

What am I missing here?

thanks

hawk

[shaosean]

Code: Select all

   put "INSERT INTO dna (uniqDna,dnaKey) VALUES (null,lastRec+1)" into tSQL

Because you are putting that whole thing into a variable, it is taking "lastRec+1" as a literal string and not as a variable that needs one added to it.. For that, you would need to do something like this:

Code: Select all

   put "INSERT INTO dna (uniqDna,dnaKey) VALUES (null," & lastRec+1 & ")" into tSQL

[dochawk]

Yikes.

Wow.

Showing once more the dangers of coding too late . . .

Even this morning, when I read your response, I wondered why I'd never seen the & operator, and wondering if it was some kind of SQL thing . . . still missing that I was working with a string in, well, my string . . .

Thank you.

feeling sheepish,

hawk

[shaosean]

I think I can speak for everyone here when I say we have all made those types of errors and then banged head against wall for hours trying to figure it out.. As my mom always said, "no such thing as stupid questions, just stupid people" (she's a funny one )

[dochawk]

I used to agree with that.

A few years as a professor taught me that there are, indeed, stupid questions--typically those that demonstrate the complete lack of thought before asking . . . (I learned about the stupid people as a lawyer, such as the guy that I represented that got rescued by police when, for unexplained reasons, he went *back* into the bar he just robbed with a toy Uzi, and which point the patrons recognized it as a toy, and had stabbed him 17 times, iirc, before the police arrived . . . or the one that ID'd the bank teller he robbed when she was brought to the site of his arrest . . . or . . .).



dochawk

[phaworth]

Hi,
Others have answered the specific question you asked but I think you're making life difficult for yourself by doing the "lastRec+1" calculation. SQLite allows you to define an INTEGER PRIMARY KEY field in a table which it will automatically update for you with the next available numeric value every time new row is added, no need to calculate it all yourself.

---Edited
Sounds like the dnaKey column is the one that should be an INTEGER PRIMARY KEY. If you go that route, just don't include the name of that column or a value for it in your INSERT statement.
-----


---Edited Again!
Just saw your later post which showed the create statement for your table. The primary key field must be defined as INTEGER, not int, in order for the automatic updating to work. You should also drop the NOT NULL for the primary key, not necessary.

Hope all this helps!
-------
Pete