This topic is locked

Delete from a multitable query

3/3/2006 3:42:06 PM
ASPRunnerPro General questions
H
heets author

I encountered this problem previously and was guided past it by this forum posting Delete records from a multi-table query
I have since purchased ASPRunner professional and I have run into the same problem. Probably because of the newer version the code has changed. I tried modifying the ###_list.asp file but haven't had any luck. The delete statement must have changed with the 4.0 version.
Can anyone tell me how to modify the code to allow for deletes from a multi-table query?
Thanks for your help,

Bruce

H
heets author 3/5/2006

A little more detail:
Error reads - [Microsoft][ODBC Microsoft Access Driver] Could not delete from specified tables
SQL - delete from [xrefactivity] where [xrefactivity].[Name ID]='SLONPE01' and [xrefactivity].[Code]='LD-006' and [xrefactivity].[Active Date]=#3/4/2006#
The table I am trying to delete from is called XREF. The Query is called xrefactivity.
I tried going into xrefactivitylist.asp but I'm not sure how to change this:

' delete record(s)

if (Request("mdelete")<>"") then
for i=1 to Request("mdelete").count

ind = CLng(Request("mdelete")(i))

key=Request("mdelete1")(ind)

key2=""

if strKeyField2<>"" then key2=Request("mdelete2")(ind)

key3=""

if strKeyField3<>"" then key3=Request("mdelete3")(ind)
strSQL="delete from " & strOriginalTableName &


" where " & KeyWhere(key,key2,key3)
where = Mid(strSQL, Len("delete from " & strOriginalTableName)+1)

' BeforeDelete Event

RetVal = True

DoEvent "RetVal = BeforeDelete(KeyWhere(key,key2,key3))"
if RetVal = True then
On Error Resume Next

LogInfo(strSQL)

dbConnection.Execute strSQL

Call ReportError

On Error Goto 0
' AfterDelete Event

DoEvent "Call AfterDelete()"

end if

next
' AfterMassDelete Event

DoEvent "Call AfterMassDelete()"
end if

Sergey Kornilov admin 3/6/2006

Bruce,
please replace

strSQL="delete from " & strOriginalTableName & _ " where " & KeyWhere(key,key2,key3)



with

strSQL="delete from XREF where " & KeyWhere(key,key2,key3)

H
heets author 3/6/2006

I tried that change and now I get the following error message:

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3.
The error screen also displays this query:
delete from XREF where [xrefactivity].[Name ID]='SLONPE01' and [xrefactivity].[Code]='LD-006' and [xrefactivity].[Active Date]=#3/4/2006#
Any other ideas?
Thanks,

Bruce

Sergey Kornilov admin 3/6/2006

Bruce,
I recommend you to choose XREF tabke in ASPRunnerPro and copy&paste SQL from your query in MS Access to SQL query window in ASPRunnerPro.
In this case correct delete query will be built.

H
heets author 3/6/2006

Sergey,
xrefactivity is the query so I copied and pasted the following query into the sql window of ASPRunner for xrefactivity:

SELECT XREF.[Name ID], XREF.Code, XREF.[Active Date], XREF.[End Date], XREF.[Dependent Data], XREF.[Change Date], ACTIVITY.Activity, ACTIVITY.[Dependent Data Description]

FROM ACTIVITY INNER JOIN XREF ON ACTIVITY.Code = XREF.Code

ORDER BY XREF.[Name ID], XREF.Code, XREF.[Active Date] DESC;
When I rebuilt the application and tested it I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in ORDER BY clause.
/dances4/xrefactivity_list.asp, line 814
I tried altering the order by clause a couple of different ways but haven't had any luck.

Any more ideas?
Thanks,

Bruce

Sergey Kornilov admin 3/7/2006

Bruce,
please return to next to last advice, replace xrefactivity with XREF in "delete" SQL string and then make following changes:

open include/commonfunction.asp file, find GetFullFieldName(strTableN,strField) function and replace

if "xrefactivity"=strTable and "Name ID"=field then

GetFullFieldName="[xrefactivity].[Name ID]"

exit function

end if



with

if "xrefactivity"=strTable and "Name ID"=field then

GetFullFieldName="[Name ID]"

exit function

end if



Make analogous changes for Code and Active Date fields.

H
heets author 3/7/2006

Sergey,
You are the man!!!
Thanks for sticking with me. That did the trick.
Bruce <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=8402&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />

P
peter van mechelen 8/29/2006

I am sorry but this is not the solution. If you told the ASPRunner software to delete pictures or even downloaded files or documents in a multi table query (general function) then, at the moment the master record is going to be deleted you get a big hangup because of the result of the asp execution. The primary files are deleted but the master record don't. The Access database can not be closed and is broken and the internet explorer (read IIS function) can't be closed the *.lmb file.

Because of the GLOBAL function you can not choose which primary file's are deleted or not. A record can contain a picture or file that is also a member of a another record. In my application i have to manual delete the unused files from the database every week/month and i think there is not a another solution. Even the date/time stamp can solve the problem.

I hope that this problem is solved in to the next update of ASPRunner Pro, a software tool that i use every day with a lot of happyness
Sorry for my bad language, i am only a dutch software fighter.
peter van mechelen

Sergey Kornilov admin 8/31/2006

Peter,
I wish I understand what you trying to say.

P
peter van mechelen 9/3/2006

Peter,

I wish I understand what you trying to say.


Sergey,

I am trying to say that the "delete primary record if master record is deleted" function in ASP runner is a global function. So you can not choose what record in the primary files(s) are going to be deleted. Some times you would hold a record from a primary file because it is also related to a another record in the master table.

I hope you understand me now
Thanks for the reaction