Hi All
I have got one here i can't work out. I am trying to add a manage links section to a links page where the links are sourced from a access database.
The basic table contains a link title field, a link field a description field and a link position field. the link position field controls the actual position in the links page and is just sequential numbers with 1 being the first link 2 being the second etc.
I can edit the links OK using the code i have created, even change the position of the links in the list. the problem occurs when i try to delete a link.
The actual link delete works, its the code that recalculates the link position field that seems to be causing the problem.
The problem i am seeing is when i delete link 3 say it actually deletes link 4, if i comment out the code that recalculates the linkPos, it deletes the correct link but obviously there is a gap in the linkPos sequence.
I have tried replacing the recalculate code with a loop that updates the records individually but this has the same effect.
Strange thing is i use basically the same code to move the link up and down the list. in the following code block you can see the code following the delete code.
I am not sure if this is a problem with the SQL code or the ASP code, any suggestions would be gratefully received.
Regards
Ian
I have got one here i can't work out. I am trying to add a manage links section to a links page where the links are sourced from a access database.
The basic table contains a link title field, a link field a description field and a link position field. the link position field controls the actual position in the links page and is just sequential numbers with 1 being the first link 2 being the second etc.
I can edit the links OK using the code i have created, even change the position of the links in the list. the problem occurs when i try to delete a link.
The actual link delete works, its the code that recalculates the link position field that seems to be causing the problem.
Code:
' *** if Delete then delete and Update all Link Positions
if request.form("chkDelete") = "ON" Then
' *** Delete record
StrSql = "DELETE FROM tblLinks WHERE LinkTitle = '" & lnkTitle & "'"
adoCon.execute (StrSql)
' *** Recalculate all positions
StrSql = "UPDATE tblLinks SET LinkPos=LinkPos-1 WHERE LinkPos >= " & hdnPosition
adoCon.execute (StrSql)
end if
The problem i am seeing is when i delete link 3 say it actually deletes link 4, if i comment out the code that recalculates the linkPos, it deletes the correct link but obviously there is a gap in the linkPos sequence.
I have tried replacing the recalculate code with a loop that updates the records individually but this has the same effect.
Code:
' *** if Delete then delete and Update all Link Positions
if request.form("chkDelete") = "ON" Then
' *** Delete record
StrSql = "DELETE FROM tblLinks WHERE LinkTitle = '" & lnkTitle & "'"
adoCon.execute (StrSql)
' *** Recalculate all positions
strSQL = "SELECT * FROM tblLinks ORDER BY LinkPos ASC"
rsLinks.Open strSQL, adoCon
varPos = 1
do While NOT rsLinks.EOF
if rsLinks("LinkPos") = varPos then
Else
StrSql = "UPDATE tblLinks SET LinkPos=" & varPos & " WHERE ID = " & rsLinks("ID")
adoCon.execute (StrSql)
End if
rsLinks.movenext
varPos = varPos + 1
Loop
rsLinks.Close
end if
Strange thing is i use basically the same code to move the link up and down the list. in the following code block you can see the code following the delete code.
Code:
if Request.querystring("function") = "update" then
lnkPos = Request.Form("valPos")
hdnPosition = Request.Form("hdnPos")
lnkCat = Request.Form("txtCategory")
lnkTitle = Request.Form("txtTitle")
lnkLink = Request.Form("txtLink")
lnkDesc = Request.Form("txtDesc")
lnkPhone = Request.Form("txtPhone")
' *** if Delete then delete and Update all Link Positions
if request.form("chkDelete") = "ON" Then
' *** Delete record
StrSql = "DELETE FROM tblLinks WHERE LinkTitle = '" & lnkTitle & "'"
adoCon.execute (StrSql)
' *** Recalculate all positions
strSQL = "SELECT * FROM tblLinks ORDER BY LinkPos ASC"
rsLinks.Open strSQL, adoCon
varPos = 1
do While NOT rsLinks.EOF
if rsLinks("LinkPos") = varPos then
Else
StrSql = "UPDATE tblLinks SET LinkPos=" & varPos & " WHERE ID = " & rsLinks("ID")
adoCon.execute (StrSql)
End if
rsLinks.movenext
varPos = varPos + 1
Loop
'StrSql = "UPDATE tblLinks SET LinkPos=LinkPos-1 WHERE LinkPos >= " & hdnPosition
'adoCon.execute (StrSql)
rsLinks.Close
end if
' *** if position change Update all Link Positions
if lnkPos <> hdnPosition Then
' *** Delete current version of record
StrSql = "DELETE FROM tblLinks WHERE LinkPos = " & hdnPosition
adoCon.execute (StrSql)
' *** Recalculate all positions
StrSql = "UPDATE tblLinks SET LinkPos=LinkPos-1 WHERE LinkPos >= " & hdnPosition
adoCon.execute (StrSql)
' *** Re-Position allowing for new position of record
StrSql = "UPDATE tblLinks SET LinkPos=LinkPos+1 WHERE LinkPos >= " & lnkPos
adoCon.execute (StrSql)
' *** Add record back in
StrSql = "INSERT INTO tblLinks (LinkPos,Category,LinkTitle,Link,LinkDesc,LinkPhon e) VALUES (" & lnkPos & ",'" & lnkCat & "','" & lnkTitle & "','" & lnkLink & "','" & lnkDesc & "','" & lnkPhone & "')"
adoCon.Execute (StrSql)
Else
StrSql = "UPDATE tblLinks SET LinkPos=" & lnkPos & ", Category='" & lnkCat & "', LinkTitle='" & lnkTitle & "', Link='" & lnkLink & "', LinkDesc='" & lnkDesc & "', linkPhone='" & lnkPhone & "' WHERE LinkPos = " & lnkPos
'response.write strSQL
adoCon.execute (StrSql)
end if
End ifI am not sure if this is a problem with the SQL code or the ASP code, any suggestions would be gratefully received.
Regards
Ian
