Direct Update of Database

[Sware]

VB6 application named GHM using the VFPOLEDB Provider with a dBase database file named GHM_V70.DBF. conn1 is Connection String; rs1 is Recordset; PA() is an array of field values. There are 87 fields in each record.

The following code for record updates (used versus rs1.Update) works perfectly when run in the VB IDE but fails with the compiled application -- Windows (XP) error: "GHM has encountered a problem and must close. Please tell Microsoft about this problem." Sending the error report does not result in any information about the problem

UpdStr = ""
For i = 0 to 86
  UpdStr = UpdStr & Space(1) & rs1.Fields(i).NAME & "=[" & PA(i) & "]" & ","
Next i
UpdStr = Left(UpdStr, Len(UpdStr) - 1) & Space(1)
UpdStr = "UPDATE GHM_V70 SET " & UpdStr & "WHERE GS_ID = "[" & PA(0) & "]"
conn1.Execute UpdStr

The error occurs on the conn1.Execute UpdStr statement.
Thanks in advance for any ideas about why compiled code fails when IDE run doesn't fail, and how to resolve the situation.

 

[Mike Lewis]

Sware,

Can you tell us what code you are actually sending to the OLEDB provider? In other words, the expanded version of your UPDATE command. I find it hard to figure out the VB code.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Sware]

Mike, good to hear from you again. If you will recall, use of the direct Update command was the result of a long thread awhile back.

The following is a truncated version of the code generated by the UPDATE command. There are 87 fields so the code below shows just the first few fields (the rest follow the same type of content) and then skips to the WHERE clause.

UPDATE GHM_V70 SET gs_id=[00100],p_sex=[M],p_card=[Y],rounds_ytd=[ 8], ......... WHERE GS_ID = [00100]

Again, the code runs fine in the VB IDE but fails when the compile code is run.

 

[Sware]

I should add that I can successfully run the copiled program (.EXE) from the folder in which VB is installed. What doesn't run successfully is the .EXE generated by the VB Package and Deployment Wizard -- that is, the program as installed from CD with the setup program created by the Wizard.

 

[Mike Lewis]

Sware,

My first thought was that either the UPDATE command was too long (max. 8192 character per command in VFP) or was too complex (governed by the SYS(3055) setting). But that wouldn't explain why it ran successfully in your development environment.

It's more likely to be some poblem with the way the program was deployed -- some missing component, perhaps. Do other calls to the same OLE DB provider work in the deployed system?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Sware]

Resolved and my apologies. The problem was not the P&DW. It was related to a format and field name conversion of the original .DBF file to the file used by the VB app.