Writing record to MSAccess DB

[ryshyn]

Hi guys - I used the examples you provided me in my last forum request with some success - I can get the DB to update when a record exists in the DB. But when it goes to INSERT a new record, I keep getting the error:


Error (RT) in Macro56: 0x80040E14,
[Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.

I don't see where that can be, it all seems to match. Here is what I have; (The first section pulls the data from a web page, and works fine; just including it for continuity)

I also have an issue that the
if (db7.Query(sql))
Always seems to return -1 whether a record exists or not. I changed it to
if (db7.Query(sql)) = 0
and that seems to work ok; just mentioning it to give all the facts...

My code follows. Any suggestions would be appreciated.

Note: I have 14 fields in the actual database-this query only populates 4 of them. Do I have to account for the remaining (blank) fields in the INSERT query in some way?)

Thanks in advance

; Activate Browser
act "Maxthon"
;Search for appropriate HTML to extract
MSHTML.IHTMLElement el=HtmlFind3("TABLE" "" "[]<TABLE cellSpacing=0 cellPadding=0 width=''100%'' bgColor=#e1e1e1 border=1><TBODY>[]<TR>[]<TD vAlign=top>[]<TABLE cellSpacing=0 cellPadding=2 width=''100%'' border=0>[]<TBODY>[]<TR>[]<TD vAlign=top>[]<TABLE cellSpacing=2 cellPadding=2 border=0>[]<TBODY>[]<TR>[]<TD align=right><FONT face=Arial,Helvetic" win("Nexicore Services - Maxthon Browser") 0 4 0x28)
str thetext=el.innerText

str s = el.innerText

; Tokenize & Extract data for DB Input
ARRAY(str) arr = s
nt = tok(s arr 3 ", ()" 8 arr2)
str CN=arr[0]
CN.findreplace("Customer Name:" "")
str SA=arr[1]
str SA
SA.findreplace("Street Address:" "")
str SA2=arr[2]
SA2.findreplace("Street Address 2:" "")
str CY=arr[3]
CY.findreplace("City:" "")
str ST=arr[4]
ST.findreplace("State:" "")
str ZP=arr[5]
ZP.findreplace("Zipcode:" "")
str CT=arr[6]
CT.findreplace("Country:" "")
str PH=arr[7]
PH.findreplace("Phone 1:" "")
str fp=PH
str PH1 PH2 PH3
PH1.get(fp 0 3)
PH2.get(fp 3 3)
PH3.get(fp 6 4)
PH.from("(" PH1 ") " PH2 "-" PH3)
str SN=arr[8]
SN.findreplace(" Service Notification:" "")
str Service_Co=arr[9]
Service_Co.findreplace("Service Company:" "")
str Service_Co_Short
Service_Co_Short.get(Service_Co 0 1)
str Ticket_No=arr[10]
Ticket_No.findreplace("PO #:" "")
str AS=arr[11]
AS.findreplace("Assigned To:" "")
str PSD=arr[12]
PSD.findreplace("Part Shipped Date:" "")
str Customer_Address
Customer_Address.from(CN "[]" SA " " SA2 "[]" CY ", " ST " " ZP "[]" PH)
str Comments.from("This is a " Service_Co " Warranty Call" "[]" "This Job has not yet been scheduled")

; Begin SQL actions
;Database: s:\spt\PactechDatabase\CIS.mdb
;Table: ptdb
;Fields: Ticket_No (Text) Service_Co (Text) Customer_Address (Text) Comments (Text)

;will insert or replace these values
str t=Ticket_No
str c=Service_Co
str a=Customer_Address
str m=Comments

;connect
Database db7
str connString=db7.CsAccess("s:\spt\PactechDatabase\CIS.mdb")
db7.Open(connString)
db7.conn.BeginTrans

;record exists?
str sql
sql.format("SELECT * FROM ptdb WHERE Ticket_No='%s'" t)
if (db7.Query(sql)) = 0 ;;yes
,out db7.Query(sql)
,;update record
,sql.format("UPDATE ptdb SET Service_Co='%s', Customer_Address='%s',Comments='%s' WHERE Ticket_No='%s'" c a m t)
else ;;no
,;insert record
,sql.format("INSERT INTO ptdb VALUES('%s','%s','%s','%s')" t c a m)
db7.Query(sql)
db7.conn.CommitTrans