I have an SQL query that selects multiple records from a database based upon customerID and PC name.
Here is the database info.
See image DBRecs.jpg in attachment
Here is the query info.
See image TheQuery.jpg
The query selected 12 record but it should be 7.
See image NbrRecsSelected.jpg
When I do a put tRec in the MsgBox and then copy paste into notepad I get 12 records of which 5 are incomplete?
See image WhatsInPut_tRecInMsgBox.jpg
I cant seem to find what I am doing wrong ? I have another update statement (follows this one) which does give correct results (7 touched records).
The query > See image UpdateQueryWorks.jpg
The result in tRes (7) > See image UpdateResultWorks.jpg
Maybe someone sees what I am doing wrong ?
Decided to add the routine with some explanation for you to test
KeyFields > put in here the fieldnames as in the database you wish to select the records on
e.g. "CustomerID,ProductID"
DataArrayKeyFields > put in here the data of the fields you wish to select the records on
e.g. put "C12345" into DataArrayKeyFields["" CustomerID"]
put "Acer54321" into DataArrayKeyFields["" ProductID"]
DatabaseID and TableName should be self explanatory
The output is:
ExistsYesNo > Yes is record exists, No is does not exist, Error is problem
theMessage > Holds a message if something went wrong
theFieldNames > Holds all fieldnames of the table in the database
RecordDataArray > Holds the selected records
Code: Select all
#######################################################################################
// Check how many records exists and get them
#######################################################################################
command CheckHowmanyRecordsExistsV1 DatabaseID, TableName, KeyFields, DataArrayKeyFields, @ExistsYesNo @theMessage @theFieldNames @RecordDataArray
// Use like this : CheckHowmanyRecordsExistsV1 DatabaseID, TableName, KeyFields, DataArrayKeyFields, ExistsYesNo, theMessage, theFieldNames, RecordDataArray
// Nbr of records and field info is in: RecordDataArray["nbrrecords"], RecordDataArray["nbrfields"], RecordDataArray["fieldslist"]
// The data is in : RecordDataArray [reccounter][theArrayFieldName]
if DatabaseID is empty then
put "CheckHowmanyRecordsExistsV1 routine error!: I need at least to have a database id number of the open database for it to work!" into theMessage
Put "Error" into ExistsYesNo
end if
if TableName is empty then
put "CheckHowmanyRecordsExistsV1 routine error!: You need to enter a table name to get the records from for it to work!" into theMessage
Put "Error" into ExistsYesNo
end if
if DataArrayKeyFields is empty then
put "CheckHowmanyRecordsExistsV1 routine error!: You need to enter of the key search field(s) data to determine what records to search for or get!" into theMessage
Put "Error" into ExistsYesNo
end if
if KeyFields is empty then
put "CheckHowmanyRecordsExistsV1 routine error!: You need to enter at least one key search fieldname from the table as it is defined in the database for it to work!" into theMessage
Put "Error" into ExistsYesNo
end if
if ExistsYesNo is "Error"
then
exit CheckHowmanyRecordsExistsV1
end if
// Build the sql string to get the records
set itemdelimiter to ","
put the number of items of KeyFields into nbrofKeyFields
put 1 into counter
repeat for nbrofKeyFields times
put item counter of KeyFields into tFieldname
if counter is 1 then Put tFieldname &"='" & DataArrayKeyFields[tFieldname] & "'" into AndString
if counter > 1 then put AndString &" AND " & tFieldname &"='" & DataArrayKeyFields[tFieldname] & "'" into AndString
add 1 to counter
end repeat
put "SELECT * FROM "& TableName&" WHERE " & AndString into tSQL
put revDataFromQuery(,,DatabaseID,tSQL) into tRec
// put revDataFromQuery(tab,return,DatabaseID,tSQL) into tRec
if tRec begins with "revdberr"
then
put "There was a problem accessing the "&TableName&" Table: " & tRec into theMessage
Put "Error" into ExistsYesNo
exit CheckHowmanyRecordsExistsV1
else
put "Yes"into ExistsYesNo
put revDatabaseColumnNames(DatabaseID, TableName) into theFieldNames
end if
if ExistsYesNo is not "Error" and tRec is empty then put "No" into ExistsYesNo
if ExistsYesNo is not "Error" and tRec is not empty then put "Yes" into ExistsYesNo
if ExistsYesNo is "Yes" then
put tRec into AllRecordData
//Build the dataArray
put the number of lines of AllRecordData into NbrRecordsInScope // Find out howmany record we are talking about.
set the itemdelimiter to ","
put the number of items of theFieldNames into NbrOfFields
put NbrOfFields into RecordDataArray["nbrfields"]
put theFieldNames into RecordDataArray["fieldslist"]
put NbrRecordsInScope into RecordDataArray["nbrrecords"]
put 1 into reccounter
repeat for NbrRecordsInScope times
put line reccounter of AllRecordData into RecordData
put 1 into fieldcounter
repeat for NbrOfFields times
set the itemdelimiter to tab
put item fieldcounter of RecordData into theData
set the itemdelimiter to ","
put item fieldcounter of theFieldNames into theArrayFieldName
put theData into RecordDataArray [reccounter][theArrayFieldName]
add 1 to fieldcounter
end repeat
add 1 to reccounter
end repeat
// End build dataArray
else
put empty into RecordData
put empty into RecordDataArray
end if
end CheckHowmanyRecordsExistsV1
// End check how many records exists