Page 1 of 2
cant update or insert into access but can find all recs
Posted: Thu Feb 14, 2013 2:03 am
by wee wullie
I've been trying to insert and update an access db via odbc using the custom properties, i can connect, find all records, but i cant seem to get it to insert or update, i get an execution error on both, i dont understand why it wont work, this db is based on the one by trevix, i have used this stack before with no problems, i have a stack with 11 cols and that works fine, the code in my latest stack is identical in terms of structure, obviously the db is named differently as are the columns within it, any ideas why it would work with one db and not the other?
Re: cant update or insert into access but can find all recs
Posted: Thu Feb 14, 2013 11:56 am
by bangkok
wee wullie wrote:I've been trying to insert and update an access db via odbc using the custom properties, i can connect, find all records, but i cant seem to get it to insert or update, i get an execution error on both, i dont understand why it wont work, this db is based on the one by trevix, i have used this stack before with no problems, i have a stack with 11 cols and that works fine, the code in my latest stack is identical in terms of structure, obviously the db is named differently as are the columns within it, any ideas why it would work with one db and not the other?
-what is the "execution error" you get ?
-what is your code ?
-what are the differences between the 2 DB ?
... we can't guess those informations.
Re: cant update or insert into access but can find all recs
Posted: Thu Feb 14, 2013 2:04 pm
by wee wullie
Hi Bangkok, the words "execution error" show up in the table field where the records normally appear, as i said i can find all records and i can look up by "ID" and by "Product", but whenever i try to update or insert i get the "execution error" showing up in the table field instead, the inset code is as follows:
Code: Select all
set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=The_Db_Path"
SQLString = "INSERT INTO lgefmt (Product, Desc, Length, Width, Height, Weight) VALUES ('NewProduct', 'NewDesc', 'NewLength', 'NewWidth', 'NewHeight', 'NewWeight')"
set rs = MyDB.Execute(SQLString)
SQLString2 = " SELECT @@IDENTITY"
set rs = MyDB.Execute(SQLString2)
result = rs(0)
set SQLString = nothing
set rs = nothing
MyDB.Close
set MyDB = nothing
and my update code is:
Code: Select all
Set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=The_Db_Path"
SQLString = "Update lgefmt SET Product = 'NewProduct', Desc = 'NewDesc', Length = 'NewLength', Width = 'NewWidth', Height = 'NewHeight', Weight = 'NewWeight' where ID = NewtheID"
set rs = MyDB.Execute(SQLString)
SQLString = "select * from lgefmt where ID = NewtheID"
set rs = MyDB.Execute(SQLString)
If rs.EOF then
result = "Not Found"
Else
While rs.EOF = False
result = result & rs("theID") & vbtab & rs("Product") & vbtab & rs("Desc") & vbtab & rs("Length") & vbtab & rs("Width") & vbtab & rs("Height") & vbtab & rs("Weight") & vblf
rs.MoveNext
Wend
End if
set rs = nothing
MyDB.Close
set MyDB = nothing
My other stack works perfectly using the same DB the only differences between the two DB's are the Column names have changed and the name of the DB also, all field names and corresponding button code has been changed to reflect the new col names including the table name in my stack has been, when that didn't work i tried copying my working stack and modified it to match the new DB, everything is exactly the same in both stacks except the above mentioned, i just cant figure out why it wont work,
hope this is a little clearer.
kind regards
Billy
Re: cant update or insert into access but can find all recs
Posted: Thu Feb 14, 2013 3:10 pm
by snm
Are you sure it's LC code?
Marek
Re: cant update or insert into access but can find all recs
Posted: Thu Feb 14, 2013 7:14 pm
by bangkok
snm wrote:Are you sure it's LC code?
Yes, I have to say : I'm lost too.
I still don't understand what wullie is trying to achieve.
From a LiveCode stack, you wish to perform select, update and insert into an Access database through ODBC, right ?
Re: cant update or insert into access but can find all recs
Posted: Fri Feb 15, 2013 1:51 am
by wee wullie
The code is vbscript which is used inside the stacks custom property, it works perfectly in my other stack but not in my new stack, i'm not trying to achieve anything other than to get the INSERT and UPDATE parts of my new stack to add new rows of data to my access DB and to update existing records respectively, i hope i'm being a bit more clear for you,
I don't understand why it doesn't work when it works in my other stack when the only difference between the two stacks are the number of columns and the column names, especially when i have created the stack and DB from scratch and when that failed i modified an existing working stack which now also fails, as i said in my earlier post i can connect to the DB and i can search it, i just cant insert or update, it doesn't make sense.
Re: cant update or insert into access but can find all recs
Posted: Fri Feb 15, 2013 2:38 am
by Simon
The code is vbscript which is used inside the stacks custom property
Sweet! I didn't know you could do that.
Because you say it worked for older stacks it might be a LC 5.5x version compatibility issue? (or should that say version 5x?).
Which version did you originally code the stack in?
Simon
Re: cant update or insert into access but can find all recs
Posted: Fri Feb 15, 2013 2:48 am
by wee wullie
Hi its 4.0.0.0 at the moment i think i done the original stack in 2.8.0.0 or 2.9.0.0 as far as i'm aware,
its strange!
Re: cant update or insert into access but can find all recs
Posted: Fri Feb 15, 2013 3:04 am
by Simon
Maybe try "Save As" and select 2.7 or 2.4 legacy code.
Simon
Re: cant update or insert into access but can find all recs
Posted: Fri Feb 15, 2013 3:46 am
by wee wullie
When i tried to download the legacy drivers i get this error:
stack "Legacy Driver Installer": execution error at line 330 (Handler: can't find handler) near "revZipOpenArchive", char 1
if that helps
Re: cant update or insert into access but can find all recs
Posted: Fri Feb 15, 2013 11:57 am
by bangkok
wee wullie wrote: can connect to the DB and i can search it, i just cant insert or update, it doesn't make sense.
OK. But what about your LC code ? I guess you have a bit of LC code to insert a new row for instance, right ?
You pass parameters (the data) to your VBscript ?
Can you intercept the SQL query just to check if everything is correct ? There might be a problem with the data you are trying to insert ?
Another question : why are you using a vbscript ? Why not doing everything in LC, using the ODBC link ?
Re: cant update or insert into access but can find all recs
Posted: Fri Feb 15, 2013 12:16 pm
by wee wullie
My stack does have buttons which have code that corresponds to the field names in the stack, insert into button code:
Code: Select all
global TheDbPath --the path of the database
on mouseUp
put the VB_NewRecord of this stack into tScript --stack custom property
replace "The_Db_Path" with TheDbPath in tScript --put the path of your db
replace "newproduct" with fld "product" in tScript --put the name for your new record
replace "newdesc" with fld "desc" in tScript --put the name for your new record
replace "newlength" with fld "length" in tScript --put the name for your new record
replace "newwidth" with fld "width" in tScript --put the name for your new record
replace "newheight" with fld "height" in tScript --put the name for your new record
replace "newweight" with fld "weight" in tScript --put the name for your new record
do tScript as "vbscript"
put the result into fld "Data1"
end mouseUp
and update button code:
Code: Select all
global TheDbPath --the path of the database
on mouseUp
put the VB_Update of this stack into tScript --stack custom property
replace "The_Db_Path" with TheDbPath in tScript --put the path of your db
replace "NewtheID" with fld "theID" in tScript
replace "NewProduct" with fld "Product" in tScript --put the name for your new record
replace "NewDesc" with fld "Desc" in tScript --put the name for your new record
replace "NewLength" with fld "Length" in tScript --put the name for your new record
replace "NewWidth" with fld "Width" in tScript --put the name for your new record
replace "NewHeight" with fld "Height" in tScript --put the name for your new record
replace "NewWeight" with fld "Weight" in tScript --put the name for your new record
do tScript as "vbscript"
put the result into fld "Data1"
end mouseUp
Data1 is the table field where the data is dispayed, product,desc,length,width,height and weight are input fields,
I dont know how to intercept the sql and the reason i'm using vbscript to do things is because i downloaded a working stack, designed one myself which also continues to work fine but i cant seem to get my latest stack to insert into or to update although it does let me connect and search for existing records it just wont let me add or amend any new records and it should work.
Re: cant update or insert into access but can find all recs
Posted: Fri Feb 15, 2013 2:07 pm
by bangkok
OK. Create a "debug" field and add into your insert button, before do tScript as "vbscript":
We need to see tScript, before it is processed. It's the only way because something in tScript creates the "execution error".
Then we will be able to see if there is a problem with :
-one of your data, coming from of one your fields
-or your TheDbPath
-or with the whole SQL insert query
Re: cant update or insert into access but can find all recs
Posted: Fri Feb 15, 2013 2:26 pm
by wee wullie
ok the insert into comes out as:
Code: Select all
set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/wgarvin/Desktop/lgefmt.mdb"
SQLString = "INSERT INTO lgefmt (Product, Desc, Length, Width, Height, Weight) VALUES ('78554', 'LUMIIR', '2000', '1000', '120', '800')"
set rs = MyDB.Execute(SQLString)
SQLString2 = " SELECT @@IDENTITY"
set rs = MyDB.Execute(SQLString2)
result = rs(0)
set SQLString = nothing
set rs = nothing
MyDB.Close
set MyDB = nothing
and the update comes out as:
Code: Select all
Set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/wgarvin/Desktop/lgefmt.mdb"
SQLString = "Update lgefmt SET Product = '46554', Desc = 'PENTA', Length = '3000', Width = '1500', Height = '70', Weight = '400' where ID = 18"
set rs = MyDB.Execute(SQLString)
SQLString = "select * from lgefmt where ID = 18"
set rs = MyDB.Execute(SQLString)
If rs.EOF then
result = "Not Found"
Else
While rs.EOF = False
result = result & rs("theID") & vbtab & rs("Product") & vbtab & rs("Desc") & vbtab & rs("Length") & vbtab & rs("Width") & vbtab & rs("Height") & vbtab & rs("Weight") & vblf
rs.MoveNext
Wend
End if
set rs = nothing
MyDB.Close
set MyDB = nothing
looks like its getting the data from the input flds.
Re: cant update or insert into access but can find all recs
Posted: Sat Feb 16, 2013 11:33 am
by bangkok
Indeed, the SQL looks fine.
Sorry can't help you.
An idea : is it possible to execute a SQL query directly within Access ?
If yes, try to run one of the queries (insert or update) created in tScript.
Last but not least : you should (in my opinion) do everything in LC script : connexion to the DB and executing SQL queries.
You would have more control.
You'll find on the forum and on line plenty of examples of very simple LC scripts to perform SQL operations.