Re: Application crashing when doing a database update via DAO.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

First obvious q, do you have rights to add new records to the database?

/Henning

"Paul Lambert" <paul.lambert@xxxxxxxxxxxxxxxxxx> skrev i meddelandet
news:45f25c3b$0$16376$88260bb3@xxxxxxxxxxxxxxxxxxxx
Hi all,

Excuse the longwindedness of this... I've tried putting in all the
relevant code and other information that I can.

I have an application written in VB 6.0 which uses DAO3.6 to connect to
a PostgreSQL database and pump some data in.

When I add a new record to the database my application crashes when
calling the update method. If however I pipe the exact same data into my
program to update the database but the record as identified by the
primary key already exists and thus my program is merely updating the
existing record... it works fine.

[I.e. if I have a table with 2 fiels, id (primary key) and value, my
program is given (1,"test") to update to the database. If a record
exists in the table with a value of 1 in the ID field, regardless of the
value of the other field or fields my program will work fine in passing
the (1,"test") and the database will be updated. If however no record
exists with a value of 1 in the ID field and thus the program is adding
a new record, the application crashes and according to my event viewer I
end up with an acces violation.

Recordset object is defined as thus:

Private Debtor_table As Recordset
About 30 odd times for the various different tables - this is then
passed to the function whos code is below which receives it as variable
name "table"

Other relevant variable declarations:
Private autodrs_db As DAO.Database
Private autodrs_work As DAO.Workspace

Database is opened as follows:
Set autodrs_work = CreateWorkspace("autodrs", g_strUserName,
g_strPWD, dbUseODBC)
Set autodrs_db = autodrs_work.OpenDatabase("autodrs", _
dbDriverNoPrompt, False, "ODBC;DATABASE=" & g_strDBName & _
";UID=" & g_strUserName & ";PWD=" & g_strPWD & ";DSN=" &
g_strDBDSN & ";")


The code causing the error is as follows:

Call debug_message(60, "Criteria = " & criteria)
task = "Check for Update or Add"
criteria_orig = criteria
criteria = "Select * from " & table_name & " where " & criteria
Call debug_message(60, "Opening table with criteria=" & criteria)
Set table = autodrs_db.OpenRecordset _
(criteria, dbOpenDynamic, 0, dbOptimistic)
If table.RecordCount = 0 Then
Call debug_message(60, "Record not found, adding new")
task = "Add"
table.AddNew
Else
Call debug_message(60, "Record found, updating")
task = "Update"
table.Edit
End If

lngStatusDB = load_xxx_to_db(table_name, table, keyname,
keyname2, keyname3, keyname4, keyname5)
Call debug_message(60, " - load_xxx_to_db exit status " +
Str(lngStatusDB))
If lngStatusDB = 0 Then
Call debug_message(60, " + updating table")
table.Update
Call debug_message(60, " - updating table")
Else
table.CancelUpdate
load_xxx = lngStatusDB
GoTo subroutine_exit
End If

The line "table.Update" is where the access violation is occuring. As
explained before the error only occurs if the update is adding a new
record to the table, updating existing records works fine.

The function load_xxx_to_db called just before the update basically
loops through the message received and puts the data into the
appropriate field in the "table" buffer - the code is as follows:

Private Function load_xxx_to_db(table_name As String _
, table As Recordset _
, keyname As String _
, keyname2 As String _
, keyname3 As String _
, keyname4 As String _
, keyname5 As String) As Long

Dim ddmmyy As String

On Error GoTo error_trap

indexx = key_id_field + 1
Call debug_message(80, " + load_xxx_to_db")
If table_name = "Employees" Then
'Last 60 fields of employee record are loaded to a different
table, bypass them in this load.
item_count = item_count - 60
End If

' The following section sets all the fields from the DMQ message
into the appropriate fields in the database.
Do Until (indexx > item_count)
Select Case field_type(indexx)
'Straight text/string.
Case "T"
Call debug_message(90, " + load_xxx_to_db >
Setting " & _
field_name(indexx) &
".value to " & _
field_contents(indexx))
table(field_name(indexx)).value _
= field_contents(indexx)
'Date in the formate dd-mmm-yyyy
Case "X", "J", "I", "E"
If ((field_contents(indexx) = "") Or
(field_contents(indexx) = "00000000000")) Then
'Yes I know we shouldn't use Nulls, but this is
replicating another database not designed/managed by me
'and I can't change this fact.
Call debug_message(90, " + load_xxx_to_db >
Setting " & _
field_name(indexx) &
".value to Null")
table(field_name(indexx)).value = Null
Else
Call debug_message(90, " + load_xxx_to_db >
Setting " & _
field_name(indexx) &
".value to " & _
field_contents(indexx))
table(field_name(indexx)).value _
= field_contents(indexx)
End If
'Time
Case "V"
Call debug_message(90, " + load_xxx_to_db > Setting
" & _
field_name(indexx) & ".value to
" & _
field_contents(indexx))
table(field_name(indexx)).value _
= cvt_time(field_contents(indexx))
'Numeric
Case "B", "W", "L", "F", "M", "1", "2", "3", "4", "5", "6",
"7", "8", "9"
If (IsNumeric(field_contents(indexx))) Then
Call debug_message(90, " + load_xxx_to_db >
Setting " & _
field_name(indexx) &
".value to " & _
field_contents(indexx))
table(field_name(indexx)).value _
= Val(field_contents(indexx))
Else
'Yes I know we shouldn't use Nulls, but this is
replicating another database not designed/managed by me
'and I can't change this fact.
Call debug_message(90, " + load_xxx_to_db >
Setting " & _
field_name(indexx) &
".value to Null")
table(field_name(indexx)).value = Null
End If
'Other unknown data type.
Case Else
Call log_load_error(table_name, "Unsupported data type")
load_xxx_to_db = -10
GoTo subroutine_exit
End Select
indexx = indexx + 1
Loop
load_xxx_to_db = 0
subroutine_exit:
Exit Function

error_trap:
Dim MyError As Error
For Each MyError In DBEngine.Errors
With MyError
Call debug_message(10, "--ODBC update error, " + Str(.Number)
+ " : " + .Description)
End With
Next MyError

End Function

Relevant section of the resulting logfile: (I've added a lot more than
normal debugging lines to try tracking down what is causing it.


"9/03/2007 6:12:29 AM dbg 80- [+loading sundry product table]"
"9/03/2007 6:12:29 AM dbg 70-

[+load_table(Sundry_Product,Product_id,Dealer_id,Franchise,Workshop,Price_Ty
pe)]"
"9/03/2007 6:12:29 AM dbg 60- [Criteria = Product_id = 'BULLBAR' and
Dealer_id = 'F65' and Franchise = 'BLANK' and Workshop = '0' and
Price_Type = '0']"
"9/03/2007 6:12:29 AM dbg 60- [Opening table with criteria=Select * from
Sundry_Product where Product_id = 'BULLBAR' and Dealer_id = 'F65' and
Franchise = 'BLANK' and Workshop = '0' and Price_Type = '0']"
"9/03/2007 6:12:46 AM dbg 60- [Record not found, adding new]"
"9/03/2007 6:12:46 AM dbg 80- [ + load_xxx_to_db]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
DEALER_ID.value to F65]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
DATE_CHANGED.value to 06-Mar-2007]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
TIME_CHANGED.value to 1809]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
PRODUCT_ID.value to BULLBAR]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
DES_1.value to Bullbar]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
DES_2.value to ]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
DES_3.value to ]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
DES_4.value to ]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
PRODUCT_TYPE.value to S]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
PRODUCT_SALES_GROUP.value to 45]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
PRICE_1.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
PRICE_2.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
PRICE_3.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
PRICE_4.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
COST.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
PARTS_HANDLING.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
INCLUDING_SALES_TAX.value to ]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
PARTS_HANDLING_LIMIT.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
HANDLING_LIMIT_PER_PART.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
DISC_TYPE.value to ]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
DISC_PERCENTAGE.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
MARK_UP_PERCENTAGE.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
ROUND_UP_TO.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
SUBTRACT_FROM_ROUND_UP.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
DISC_MINIMUM.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
DISC_MAXIMUM.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
SUPPLIER_NO.value to 113]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
HANDLING_LIMIT_PER_RO.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
OBSOLETE.value to ]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
FRANCHISE.value to BLANK]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
WORKSHOP.value to 0]"
"9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting
PRICE_TYPE.value to 0]"
"9/03/2007 6:12:46 AM dbg 60- [ - load_xxx_to_db exit status 0]"
"9/03/2007 6:12:46 AM dbg 60- [ + updating table]"
<logfile stops here everytime showing that the table.Update line is the
point of failure>

Apologies again for the length of this, I just want to make sure I
include any relevant information.

Regards,
Paul.

--
Posted via a free Usenet account from http://www.teranews.com



.

Relevant Pages

  • Application crashing when doing a database update via DAO.
    ... I have an application written in VB 6.0 which uses DAO3.6 to connect to a PostgreSQL database and pump some data in. ... , keyname As String _ ... , keyname2 As String _ ... Do Until (indexx> item_count) ...
    (microsoft.public.vb.database)
  • Re: A little Rolodex [revised]
    ... including alpha sort and searching for any embedded string, ... In this application, a database is a directory, ... you may optionally provide any alternate UCASE program, ... NN -> first store NN as key length ...
    (comp.sys.hp48)
  • Re: return multiple rows from sql statement
    ... strings from input values is almost certainly a safe path to SQL ... Also, being a MySQL function, it knows what MySQL needs or uses. ... All characters that are entered in the fields make their way into the database unaltered. ... The insert of what surprisinlgly was NOT a syntax error, but a string called "mysql_insert_id" into an integer field resulted in the value zero being put in. ...
    (comp.lang.php)
  • Re: Code to delete/unlink Linked tables
    ... Public intLinkODBCTables As Variant, intLinkDB2Tables As Variant ... Public strLinkBackendDB As String, strLinkDSNname As String, strLinkLibName ... ' MsgBox "This database is in MDE format...I will delete/recreate ODBC ... Public Sub fncLinkDB2Table() ...
    (microsoft.public.access.modulesdaovba)
  • Re: Establish connection with and transferring data to Access
    ... The specific problem with your code is that you need to quote string ... Dim vConnection As New ADODB.Connection ... Experiment with the object model or check the documentation for the ... Doug's code opens and writes to an existing database. ...
    (microsoft.public.word.vba.general)