Notifikationer

Markér alle som læst Log ud

pelkjaer Nybegynder

03. januar 2003 - 12:22 Der er 9 kommentarer og
1 løsning

sql problem

Jeg fik for lidt tid siden hjælp af Eagleeye til noget menusjov, således at jeg kan flytte id på menuer op og ned ift. hinanden:

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.RecordSet")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& server.mappath("*.mdb")

'flyt op/ned
dir = Request.QueryString("dir")
if dir <> "" then
oldId = Request.QueryString("SubMenuId")
tempID = 0

SQL = "SELECT TOP 1 SubMenuId FROM submenus WHERE SubMenuId "
if dir = "op" then
SQL = SQL & " < " & oldID & " ORDER BY SubMenuId DESC"
'newID = Int(oldId) - 1
else
SQL = SQL & " > " & oldID & " ORDER BY SubMenuId"
'newID = Int(oldId) + 1
end if
Response.Write SQL
set rs = conn.Execute(SQL)
newID = rs("SubMenuId")

SQL = "UPDATE submenus SET subMenuId = " & tempID & " WHERE subMenuId = " & oldID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & oldID & " WHERE subMenuId = " & newID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & newID & " WHERE subMenuId = " & tempID
Conn.Execute(SQL)
end if

Problem:
Nu vil jeg så udbygge det således at det filtreres efter "MainMenuId" i min tabel. Dvs. at hvis jeg tilgår siden med fx. side.asp?MainMenuId=2 så vises kun de menuer (subMenuId) der "passer" med MainMenuId 2.

Og det vil sq' ikke helt som jeg vil.

Jeg har forsøgt med:

<%
Dim rs__ColParam
rs__ColParam = "1"
If (Request.QueryString("MainMenuId") <> "") Then
rs__ColParam = Request.QueryString("MainMenuId")
End If
%>

og

SQL = "SELECT TOP 1 SubMenuId FROM submenus WHERE MainMenuId = " + Replace(rs__ColParam, "'", "''") + " AND SubMenuId "

osv. men alle subMenuId vises stadigvæk.

Håber I kan hjælpe mig.

Synes godt om

medions Nybegynder

03. januar 2003 - 13:14 #1

Prøv med dette!

Set Conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.RecordSet")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& server.mappath("*.mdb")

'flyt op/ned
dir = Request.QueryString("dir")
if dir <> "" then
oldId = Request.QueryString("SubMenuId")
tempID = 0

SQL = "SELECT TOP 1 SubMenuId FROM submenus WHERE MainMenuId = " + Replace(rsMenus__MMColParam, "'", "''") + " AND SubMenuId "
if dir = "op" then
SQL = SQL & " < " & oldID & " ORDER BY SubMenuId DESC"
'newID = Int(oldId) - 1
else
SQL = SQL & " > " & oldID & " ORDER BY SubMenuId"
'newID = Int(oldId) + 1
end if
Response.Write SQL
set rs = conn.Execute(SQL)
newID = rs("SubMenuId")

SQL = "UPDATE submenus SET subMenuId = " & tempID & " WHERE subMenuId = " & oldID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & oldID & " WHERE subMenuId = " & newID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & newID & " WHERE subMenuId = " & tempID
Conn.Execute(SQL)
end if

//>Rune

Synes godt om

medions Nybegynder

03. januar 2003 - 13:19 #2

Hmm prøv lgie med dette!

Set Conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.RecordSet")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& server.mappath("*.mdb")

'flyt op/ned
dir = Request.QueryString("dir")
if dir <> "" then
oldId = Request.QueryString("SubMenuId")
tempID = 0

SQL = "SELECT TOP 1 SubMenuId FROM submenus WHERE MainMenuId = " + oldId + " AND SubMenuId "
if dir = "op" then
SQL = SQL & " < " & oldID & " ORDER BY SubMenuId DESC"
'newID = Int(oldId) - 1
else
SQL = SQL & " > " & oldID & " ORDER BY SubMenuId"
'newID = Int(oldId) + 1
end if
Response.Write SQL
set rs = conn.Execute(SQL)
newID = rs("SubMenuId")

SQL = "UPDATE submenus SET subMenuId = " & tempID & " WHERE subMenuId = " & oldID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & oldID & " WHERE subMenuId = " & newID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & newID & " WHERE subMenuId = " & tempID
Conn.Execute(SQL)
end if

//>Rune

Synes godt om

medions Nybegynder

03. januar 2003 - 13:23 #3

SQL = "SELECT TOP 1 SubMenuId FROM submenus WHERE MainMenuId = " + Request.QueryString("SubMenuId") + " AND SubMenuId "
if dir = "op" then
SQL = SQL & " < " & oldID & " ORDER BY SubMenuId DESC"
'newID = Int(oldId) - 1
else
SQL = SQL & " > " & oldID & " ORDER BY SubMenuId"
'newID = Int(oldId) + 1
end if
Response.Write SQL
set rs = conn.Execute(SQL)
newID = rs("SubMenuId")

//>Rune

Synes godt om

pelkjaer Nybegynder

03. januar 2003 - 13:32 #4

Okay hele scriptet der rykker og/ned:

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.RecordSet")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& server.mappath("../../access/pecms.mdb")

'flyt op/ned
dir = Request.QueryString("dir")
if dir <> "" then
oldId = Request.QueryString("SubMenuId")
tempID = 0

SQL = "SELECT TOP 1 SubMenuId FROM submenus WHERE SubMenuId "
if dir = "op" then
SQL = SQL & " < " & oldID & " ORDER BY SubMenuId DESC"
'newID = Int(oldId) - 1
else
SQL = SQL & " > " & oldID & " ORDER BY SubMenuId"
'newID = Int(oldId) + 1
end if
Response.Write SQL
set rs = conn.Execute(SQL)
newID = rs("SubMenuId")

SQL = "UPDATE submenus SET subMenuId = " & tempID & " WHERE subMenuId = " & oldID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & oldID & " WHERE subMenuId = " & newID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & newID & " WHERE subMenuId = " & tempID
Conn.Execute(SQL)
end if

'Udskriv formen..
'Så er det nødvendigt at hendte min/max SubMenuId for der ikke sker fejl..:
SQL = "SELECT min(SubMenuId) as minID, max(SubMenuId) as maxID FROM submenus "
set rs = conn.Execute(SQL)
maxID = rs("maxID")
minID = rs("minID")

SQL = "SELECT SubMenuId, SubMenuTitle FROM submenus ORDER BY SubMenuId"
set rs = conn.Execute(SQL)

Response.Write "<table>"
Response.Write "<tr><td>Undermen title</td><td>Ryk op</td><td>Ryk ned</td></tr>"

do while not rs.EOF
Response.Write "<tr>"
Response.Write "<td>" & rs("SubMenuTitle") & "<td>"

if Int(rs("SubMenuId")) > minID then
Response.Write "<td><a href=""?dir=op&SubMenuId="&rs("SubMenuId")&""">ryk op</a><td>"
else
Response.Write "<td>...<td>"
end if
if Int(rs("SubMenuId")) < Int(maxID) then
Response.Write "<td><a href=""?dir=ned&SubMenuId="&rs("SubMenuId")&""">ryk ned</a><td>"
else
Response.Write "<td>...<td>"
end if
Response.Write "</tr>"

rs.MoveNext
loop
%>
</table>
<% 'Lukker for connection til databasen
Conn.Close
%>

problemet er så at de på siden når det udskrives skal sorteres efter MainMenuId som er hovedmenu punkterne, fordi man tilgår undermenu redigeringssiderne med side.asp?MainMenuId=2 osv.

Synes godt om

medions Nybegynder

03. januar 2003 - 13:34 #5

Ok, så prøv lgie det her:

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.RecordSet")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& server.mappath("../../access/pecms.mdb")

'flyt op/ned
dir = Request.QueryString("dir")
if dir <> "" then
oldId = Request.QueryString("SubMenuId")
tempID = 0

SQL = "SELECT TOP 1 SubMenuId FROM submenus WHERE SubMenuId "
if dir = "op" then
SQL = SQL & " < " & oldID & " ORDER BY SubMenuId DESC"
'newID = Int(oldId) - 1
else
SQL = SQL & " > " & oldID & " ORDER BY SubMenuId"
'newID = Int(oldId) + 1
end if
Response.Write SQL
set rs = conn.Execute(SQL)
newID = rs("SubMenuId")

SQL = "UPDATE submenus SET subMenuId = " & tempID & " WHERE subMenuId = " & oldID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & oldID & " WHERE subMenuId = " & newID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & newID & " WHERE subMenuId = " & tempID
Conn.Execute(SQL)
end if

'Udskriv formen..
'Så er det nødvendigt at hendte min/max SubMenuId for der ikke sker fejl..:
SQL = "SELECT min(SubMenuId) as minID, max(SubMenuId) as maxID FROM submenus "
set rs = conn.Execute(SQL)
maxID = rs("maxID")
minID = rs("minID")

SQL = "SELECT SubMenuId, SubMenuTitle FROM submenus WHERE MainMenuId = " + Request.QueryString("MainMenuId") + " ORDER BY SubMenuId"
set rs = conn.Execute(SQL)

Response.Write "<table>"
Response.Write "<tr><td>Undermen title</td><td>Ryk op</td><td>Ryk ned</td></tr>"

do while not rs.EOF
Response.Write "<tr>"
Response.Write "<td>" & rs("SubMenuTitle") & "<td>"

if Int(rs("SubMenuId")) > minID then
Response.Write "<td><a href=""?dir=op&SubMenuId="&rs("SubMenuId")&""">ryk op</a><td>"
else
Response.Write "<td>...<td>"
end if
if Int(rs("SubMenuId")) < Int(maxID) then
Response.Write "<td><a href=""?dir=ned&SubMenuId="&rs("SubMenuId")&""">ryk ned</a><td>"
else
Response.Write "<td>...<td>"
end if
Response.Write "</tr>"

rs.MoveNext
loop
%>
</table>
<% 'Lukker for connection til databasen
Conn.Close
%>

//>Rune

Synes godt om

medions Nybegynder

03. januar 2003 - 13:42 #6

Hmm prøv lige med dette!!

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.RecordSet")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& server.mappath("../../access/pecms.mdb")

'flyt op/ned
dir = Request.QueryString("dir")
if dir <> "" then
oldId = Request.QueryString("SubMenuId")
tempID = 0

SQL = "SELECT TOP 1 SubMenuId FROM submenus WHERE MainMenuId = " + Request.QueryString("MainMenuId") + " AND SubMenuId "
if dir = "op" then
SQL = SQL & " < " & oldID & " ORDER BY SubMenuId DESC"
'newID = Int(oldId) - 1
else
SQL = SQL & " > " & oldID & " ORDER BY SubMenuId"
'newID = Int(oldId) + 1
end if
Response.Write SQL
set rs = conn.Execute(SQL)
newID = rs("SubMenuId")

SQL = "UPDATE submenus SET subMenuId = " & tempID & " WHERE subMenuId = " & oldID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & oldID & " WHERE subMenuId = " & newID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & newID & " WHERE subMenuId = " & tempID
Conn.Execute(SQL)
end if

'Udskriv formen..
'Så er det nødvendigt at hendte min/max SubMenuId for der ikke sker fejl..:
SQL = "SELECT min(SubMenuId) as minID, max(SubMenuId) as maxID FROM submenus "
set rs = conn.Execute(SQL)
maxID = rs("maxID")
minID = rs("minID")

SQL = "SELECT SubMenuId, SubMenuTitle FROM submenus WHERE MainMenuId = " + Request.QueryString("MainMenuId") + " ORDER BY SubMenuId"
set rs = conn.Execute(SQL)

Response.Write "<table>"
Response.Write "<tr><td>Undermen title</td><td>Ryk op</td><td>Ryk ned</td></tr>"

do while not rs.EOF
Response.Write "<tr>"
Response.Write "<td>" & rs("SubMenuTitle") & "<td>"

if Int(rs("SubMenuId")) > minID then
Response.Write "<td><a href=""?dir=op&SubMenuId="&rs("SubMenuId")&""">ryk op</a><td>"
else
Response.Write "<td>...<td>"
end if
if Int(rs("SubMenuId")) < Int(maxID) then
Response.Write "<td><a href=""?dir=ned&SubMenuId="&rs("SubMenuId")&""">ryk ned</a><td>"
else
Response.Write "<td>...<td>"
end if
Response.Write "</tr>"

rs.MoveNext
loop
%>
</table>
<% 'Lukker for connection til databasen
Conn.Close
%>

//>Rune

Synes godt om

medions Nybegynder

03. januar 2003 - 13:47 #7

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.RecordSet")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& server.mappath("../../access/pecms.mdb")

'flyt op/ned
dir = Request.QueryString("dir")
if dir <> "" then
oldId = Request.QueryString("SubMenuId")
tempID = 0

SQL = "SELECT TOP 1 SubMenuId FROM submenus WHERE MainMenuId = " + Request.QueryString("MainMenuId") + " AND SubMenuId "
if dir = "op" then
SQL = SQL & " < " & oldID & " ORDER BY SubMenuId DESC"
'newID = Int(oldId) - 1
else
SQL = SQL & " > " & oldID & " ORDER BY SubMenuId"
'newID = Int(oldId) + 1
end if
Response.Write SQL
set rs = conn.Execute(SQL)
newID = rs("SubMenuId")

SQL = "UPDATE submenus SET subMenuId = " & tempID & " WHERE subMenuId = " & oldID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & oldID & " WHERE subMenuId = " & newID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & newID & " WHERE subMenuId = " & tempID
Conn.Execute(SQL)
end if

'Udskriv formen..
'Så er det nødvendigt at hendte min/max SubMenuId for der ikke sker fejl..:
SQL = "SELECT min(SubMenuId) as minID, max(SubMenuId) as maxID FROM submenus "
set rs = conn.Execute(SQL)
maxID = rs("maxID")
minID = rs("minID")

SQL = "SELECT SubMenuId, SubMenuTitle FROM submenus WHERE MainMenuId = " + Request.QueryString("MainMenuId") + " ORDER BY SubMenuId"
set rs = conn.Execute(SQL)

Response.Write "<table>"
Response.Write "<tr><td>Undermen title</td><td>Ryk op</td><td>Ryk ned</td></tr>"

do while not rs.EOF
Response.Write "<tr>"
Response.Write "<td>" & rs("SubMenuTitle") & "<td>"

if Int(rs("SubMenuId")) > minID then
Response.Write "<td><a href=""?dir=op&MainMenuId=" & Request.QueryString("MainMenuId") & "&SubMenuId="&rs("SubMenuId")&""">ryk op</a><td>"
else
Response.Write "<td>...<td>"
end if
if Int(rs("SubMenuId")) < Int(maxID) then
Response.Write "<td><a href=""?dir=ned&MainMenuId=" & Request.QueryString("MainMenuId") & "&SubMenuId="&rs("SubMenuId")&""">ryk ned</a><td>"
else
Response.Write "<td>...<td>"
end if
Response.Write "</tr>"

rs.MoveNext
loop
%>
</table>
<% 'Lukker for connection til databasen
Conn.Close
%>

//>Rune

Synes godt om

medions Nybegynder

03. januar 2003 - 13:56 #8

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.RecordSet")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& server.mappath("../../access/pecms.mdb")

'flyt op/ned
dir = Request.QueryString("dir")
if dir <> "" then
oldId = Request.QueryString("SubMenuId")
tempID = 0

SQL = "SELECT TOP 1 SubMenuId FROM submenus WHERE MainMenuId = " + Request.QueryString("MainMenuId") + " AND SubMenuId "
if dir = "op" then
SQL = SQL & " < " & oldID & " ORDER BY SubMenuId DESC"
'newID = Int(oldId) - 1
else
SQL = SQL & " > " & oldID & " ORDER BY SubMenuId"
'newID = Int(oldId) + 1
end if
Response.Write SQL
set rs = conn.Execute(SQL)
newID = rs("SubMenuId")

SQL = "UPDATE submenus SET subMenuId = " & tempID & " WHERE subMenuId = " & oldID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & oldID & " WHERE subMenuId = " & newID
Conn.Execute(SQL)
SQL = "UPDATE submenus SET subMenuId = " & newID & " WHERE subMenuId = " & tempID
Conn.Execute(SQL)
end if

'Udskriv formen..
'Så er det nødvendigt at hendte min/max SubMenuId for der ikke sker fejl..:
SQL = "SELECT min(SubMenuId) as minID, max(SubMenuId) as maxID FROM submenus WHERE MainMenuId = " + Request.QueryString("MainMenuId")
set rs = conn.Execute(SQL)
maxID = rs("maxID")
minID = rs("minID")

SQL = "SELECT SubMenuId, SubMenuTitle FROM submenus WHERE MainMenuId = " + Request.QueryString("MainMenuId") + " ORDER BY SubMenuId"
set rs = conn.Execute(SQL)

Response.Write "<table>"
Response.Write "<tr><td>Undermen title</td><td>Ryk op</td><td>Ryk ned</td></tr>"

do while not rs.EOF
Response.Write "<tr>"
Response.Write "<td>" & rs("SubMenuTitle") & "<td>"

if Int(rs("SubMenuId")) > minID then
Response.Write "<td><a href=""?dir=op&MainMenuId=" & Request.QueryString("MainMenuId") & "&SubMenuId="&rs("SubMenuId")&""">ryk op</a><td>"
else
Response.Write "<td>...<td>"
end if
if Int(rs("SubMenuId")) < Int(maxID) then
Response.Write "<td><a href=""?dir=ned&MainMenuId=" & Request.QueryString("MainMenuId") & "&SubMenuId="&rs("SubMenuId")&""">ryk ned</a><td>"
else
Response.Write "<td>...<td>"
end if
Response.Write "</tr>"

rs.MoveNext
loop
%>
</table>
<% 'Lukker for connection til databasen
Conn.Close
%>

//>Rune

Synes godt om

medions Nybegynder

03. januar 2003 - 13:58 #9

Thx 4 Poinz

//>Rune

Synes godt om

pelkjaer Nybegynder

03. januar 2003 - 14:01 #10

Tak for hjælpen :)

Synes godt om

Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Følg dette spørgsmål

Opret Preview

Programmeringssprog kurser

Kurser inden for grundlæggende programmering

Se alle Programmeringssprog kurser

Flere spørgsmål fra ASP kategorien

Titel	Indlæg	Oprettet	Seneste aktivitet
Persits.Upload Af bsn i ASP	9	17/09/202611:37	21/09/202615:15
Jeg bakser med noget Database og If Then med ASP - hvad gør jeg galt? Af Peer i ASP	10	25/06/202622:20	04/09/202602:30
Tjek alle checkbox i form Af bsn i ASP	4	18/05/202601:57	19/05/202613:33
Lost focus Af bsn i ASP	1	07/11/202415:34	01/01/202514:58
ASP classic Af bsn i ASP	8	28/10/202423:11	30/10/202422:51

Se alle spørgsmål i kategorien Opret spørgsmål

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester

Alle kategorier på Eksperten

Seneste artiklerRSS