|
Post by mackrackit on May 19, 2008 4:31:36 GMT -5
I am new to SQLite so this is most likely a dumb question. If I try to put a string in a table that has a ","(comma) everything after the comma is truncated. Is there a way around this? I can not find any other delimiters that work. Here is a piece of my code. input "Enter Part Name: ";p$ input "Enter Part Description: ";d$ input "Enter Amount: ";a$ input "Enter Part Cost: ";c$ input "Add new entry? (Y/N): ";f$ if upper$(f$)="Y" then query$ = "insert into Parts(part, description, amount, cost)values('"+p$+"', '"+d$+"', '"+a$+"', '"+c$+"')"
The Part Description may have a comma in it, or the Cost may have a comma for a thousand separator.
|
|
|
Post by mikeukmid on May 19, 2008 5:47:56 GMT -5
|
|
|
Post by kokenge on May 19, 2008 6:09:39 GMT -5
Hmmm! It should accept commas. As long as the field is enclosed in quotes. You code looks ok, so maybe there is another problem.
The thing you need to worry about however is fields that contain a quote mark.
|
|
|
Post by mackrackit on May 19, 2008 6:42:00 GMT -5
When I use "|" to create the table query$="create table Parts(part char(50)|description char(500)|amount char(100)|cost char(10))"
I get this. Runtime Error in program 'inventory': #inventory execute(query$) near "|": syntax error
I also tried "~", the same thing. Here is all of my code so far sqliteconnect #inventory,"inventory.db" query$="create table Parts(part char(50)|description char(500)|amount char(100)|cost char(10))" #inventory execute(query$) #inventory disconnect()
call setCSS
[display] cls gosub[countEntries] print "There are ";lastcount;" entries in the database" print " " link #add,"[Add]",[addToDatabase] link #read,"[Read All]",[readAll] #add cssclass("calcButton") #read cssclass("calcButton") print " " wait end
[addToDatabase] print chr$(160) input "Enter Part Name: ";p$ input "Enter Part Description: ";d$ input "Enter Amount: ";a$ input "Enter Part Cost: ";c$ input "Add new entry? (Y/N): ";f$ if upper$(f$)="Y" then query$ = "insert into Parts(part| description| amount| cost)values('"+p$+"'| '"+d$+"'| '"+a$+"'| '"+c$+"')" sqliteconnect #inventory,"inventory.db" #inventory execute(query$) #inventory disconnect() end if goto [display]
[countEntries] sqliteconnect #inventory,"inventory.db" query$ = "select count(*) from Parts" #inventory execute(query$) result$ = #inventory nextrow$(" ") lastcount = val(result$) #inventory disconnect() return
[readAll] print " " sqliteconnect #inventory,"inventory.db" query$ = "select * from Parts" #inventory execute(query$) if #inventory hasanswer()then for x = 1 to #inventory rowcount() print #inventory nextrow$(",") next x end if #inventory disconnect() link #done,"[Done]",[display] #done cssclass("calcButton") wait
sub setCSS
cssclass "a.calcButton", "{ text-decoration: none; font-size: 12pt; width: 100px; height: 25px; display: block; float: left; background: #FFF; margin-right: 5px; text-align: center }"
end sub
|
|
|
Post by kokenge on May 19, 2008 7:23:52 GMT -5
The problem is not the DB. You are using INPUT statements. The input statement thinks you are separating two values with a comma and tries to find another place for data after the comma. If you place a print statement after your input you will notice it has eliminated everything after the comma.
So use normal comma separators and everything should work.
I would add the following to take care of the quote in a string in case someone enters something like "don't"
query$ = "insert into Parts(part , description , amount , cost) values ('"+dblQuote$(p$)+"','"+dblQuote$(d$)+"','"+dblQuote$(a$)+"','"+dblQuote$(c$)+"')"
' ----------------------------------------- ' Convert single quotes to double quotes ' ----------------------------------------- FUNCTION dblQuote$(str$) i = 1 qq$ = "" while (word$(str$,i,"'")) <> "" dblQuote$ = dblQuote$;qq$;word$(str$,i,"'") qq$ = "''" i = i + 1 WEND END FUNCTION
HTH
|
|
|
Post by mackrackit on May 19, 2008 8:11:14 GMT -5
The problem is not the DB. You are using INPUT statements. The input statement thinks you are separating two values with a comma and tries to find another place for data after the comma. If you place a print statement after your input you will notice it has eliminated everything after the comma. Eliminating everything after the comma is what I do not want. What can I use other than an INPUT statement? Thanks for the double quote code. That works. But I still get 1 if I enter 1,567 .
|
|
|
Post by kokenge on May 19, 2008 8:32:59 GMT -5
Replace your input statements with something similar to this and see if it helps.
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>" html "<TR><TD COLSPAN=2 BGCOLOR=#FFCC99 ALIGN=CENTER>Part Maintenance</TD></TR>" html "<TR><TD BGCOLOR=#FFCC99 ALIGN=RIGHT>Name</TD><TD>" textbox #p, p$ html "</TD></TR><TR><TD BGCOLOR=#FFCC99 ALIGN=RIGHT>Description</TD><TD>" textbox #d, d$ html "</TD></TR><TR><TD BGCOLOR=#FFCC99 ALIGN=RIGHT>Amount</TD><TD>" textbox #a, a$ html "</TD></TR><TR><TD BGCOLOR=#FFCC99 ALIGN=RIGHT>Cost</TD><TD>" textbox #c, c$ html "</TD></TR><TR><TD COLSPAN=2 BGCOLOR=#FFCC99 ALIGN=CENTER>" link #add, "Add", [doAdd] html " " link #cnc, "Cancel", [doCancel] html "</TD></TR>" html "</TABLE>" wait [doAdd] p$ = trim$(#p contents$()) d$ = trim$(#d contents$()) a$ = trim$(#a contents$()) c$ = trim$(#c contents$())
' do your insert here
End
hth
|
|
|
Post by mackrackit on May 19, 2008 8:47:02 GMT -5
Thank you kokenge! It works!!! I was so tunnel visioned on learning the data base that when you mentioned INPUT I thought you were talking about inputing to the DB Now I can continue to bang my head on the rest of it.
|
|