cant update or insert into access but can find all recs

[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?

[bangkok]

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.

[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

[snm]

Are you sure it's LC code?

Marek

[bangkok]

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 ?

[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.

[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

[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!

[Simon]

Maybe try "Save As" and select 2.7 or 2.4 legacy code.

Simon

[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

[bangkok]

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 ?

[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.

[bangkok]

OK. Create a "debug" field and add into your insert button, before do tScript as "vbscript":

Code: Select all

 put tScript into fld "debug"

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

[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.

[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.