Hjælp til en søge funktion i MS SQL database

hvilken linie er nr. 54 i din kode....

Du prøver at bruge strConnect, men har oprettet strConn.

objRS.open strSQL, strConnect, adOpenForwardOnly, adLockReadOnly,adCmdText

skal være:
objRS.open strSQL, strConn, adOpenForwardOnly, adLockReadOnly,adCmdText

Har rettet det men den skriver den samme fejl 8-(

linje 54 er den her
set objRS = Server.CreateObject("ADODB.Recordset")

Øhm, din connection opretter du på en side og så forsøger du at bruge den på en anden? Sådan hænger ASP ikke sammen. Hver side er en selvstændig enhed så du skal oprette connections på alle sider hvor du skal have fat i databasen. Følgende skal altså også være i soeg.asp:

Set conn = Server.CreateObject("ADODB.Connection")
strConn = "DRIVER=SQL Server;UID=Anonym;PWD=Anonym;Server=172.19.193.0;Database=BogDatabase"
conn.open strConn

Jeg har prøvet at sætte
Set conn = Server.CreateObject("ADODB.Connection")
strConn = "DRIVER=SQL Server;UID=Anonym;PWD=Anonym;Server=172.19.193.0;Database=BogDatabase"
conn.open strConn

ind i starten af soeg.asp altså lige under <body> men den skriver den samme fejl nu er det bare linje 58 selvfølgelig

Prøver lige at kopier de 2 filer i fuld længde så kan det være i for mere ud af det

Filen soeg.asp

<html>
<head>
<title></title>
</head>
<body>
<%
Set conn = Server.CreateObject("ADODB.Connection")
strConn = "DRIVER=SQL Server;UID=Anonym;PWD=Anonym;Server=172.19.193.0;Database=BogDatabase"
conn.open strConn
%>
<%
dim strSearch, intPageForm, intPageSize
dim strSearchRes

strSearch = request("itemsearch")
strSearchRes = request("searchres")

if request("pageform") = "" then
  intPageForm = 1
else
  intPageForm = cint(request("pageform"))
  if intPageForm < 1 then intPageForm = 1
end if
if request("pagesize") = "" then
  intPageSize = 3
else
  intPageSize = cint (request("pagesize"))
  if intPageSize < 1 then intPageSize = 1
end if

response.write _
  "<form action=""Default.asp"" method=""post"">" & _
  "søg: <input type=""text"" name=""itemsearch"" value""" & strSearch & """>" & _
  "<input type=""hidden"" name=""pageform"" value=""1"">" & _
  "<br>Antal pr side: <select name=""pagesize"">" & _
  "<option value=""1"">1</option>" & _
  "<option value=""2"">2</option>" & _
  "<option value=""5"">5</option>" & _
  "</select><br>" & _
  "<input type=""submit"" value=""søg""></form><br>"
if strSearchRes <> "" then
  ShowPage intPageFrom,intPageSize
else
  DoSearch strSearch
  ShowPage intPageFrom, intPageSize
end if

'Funktioner/procedurer

sub DoSearch (strS)
  dim strSQL, objRS
  strSQL = "SELECT Nummer, Titel, Forfatter, Forlag, ISBN_nummer FROM Bogtabel " & _
          "WHERE Titel LIKE '%" & strS & "%'"

set objRS = Server.CreateObject("ADODB.Recordset")
  objRS.open strSQL, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText
  if not objRS.EOF then
    strSearchRes = objRS.GetString( , , ",", ",")
else
    strSearchRes = ","
end if
objRS.Close
Set objRS = Nothing
strSearchRes = left(strSearchRes, len(strSearchRes)-1)
end sub

sub ShowPage (intFrom, intSize)
  dim arrSearchRes, i, intLast, intMax
  arrSearchRes = split(strSearchRes, ",")
if (intSize*3)+3*(intFrom-1) > ubound(arrSearchRes) then
    intLast = ubound(arrSearchRes)
else
  intLast = (intSize*3)+3*(intFrom-1)
end if
  intMax = (ubound(arrSearchRes)+1)/3
  response.write "<p>Fandt: " & intMax & " Titler.</p>"
 
  ShowButtons intFrom, intSize, intMax

  for i = 3*(intFrom-1) to intLast - 2 step 3
    response.write _
      arrSearchRes(i) & " - " & _
      arrSearchRes(i+1) & " - " & _
      arrSearchRes(i+2) & "<hr>"
  next
end sub

sub ShowButtons (intFrom, intSize, intMax)
  dim strBackButton, strForwardButton
  dim intPrev, intNext
  intPrev = intFrom - intSize
  if intPrev < 1 then intPrev=1
  intNext = intFrom + intSize
  if intNext > intMax then intNext=intMax

strBackButton = _
  "<Form action=""soeg.asp"" method=""post"">" & _
  "<input type=""hidden"" name=""searchres"" value=""" & strSearchRes & """>" & _
  "<input type=""hidden"" name=""pagesize"" value""" & intSize & """>" & _
  "<input type=""hidden"" name=""pagefrom"" value""" & intPrev & """>" & _
  "<input type=""submit"" value""<- Tilbage""></form>"
strForwardButton = _
  "<form action=""soeg.asp"" method=""post"">" & _
  "<input type=""hidden"" name""searchres"" value=""" & strSearchRes & """>" & _
  "<input type=""hidden"" name""pagesize"" value=""" & intSize & """>" & _
  "<input type=""hidden"" name""pageform"" value=""" & intNext & """>" & _
  "<input type=""hidden"" name""Frem ->""></form>"

  response.write " <table border=0><tr><td>" & strBackButton & _
    "</td><td>" & strForwardButton & "</td></tr></table>"
end sub
%>
</body>
</html>

Og default.asp

<html>
<head>
<title>SQL Server</title>
</head>
<body>
<H1><p align="Center">SKP Bogdatabase</p></H1>
<br>
<%
Set conn = Server.CreateObject("ADODB.Connection")
strConn = "DRIVER=SQL Server;UID=Anonym;PWD=Anonym;Server=172.19.193.0;Database=BogDatabase"
conn.open strConn
%>
<!-- #INCLUDE FILE="soeg.asp" -->
<Table Border="0" align="center">
<Tr>
<td width="5%"><H3><U><p align="center">Nummer</p></U></H3></td>
<td width="35%"><H3><U><p align="center">Titel</p></U></H3></td>
<td width="20%"><H3><U><p align="center">Forfatter</p></U></H3></td>
<td width="20%"><H3><U><p align="center">Forlag</p></U></H3></td>
<td width="20%"><H3><U><p align="center">ISBN nummer</p></U></H3></td>
</tr>
<tr>
<td>
<p align="center">
<FONT SIZE="3">
<B>
<%
strSQL = "SELECT Nummer FROM BogTabel"
set res = Conn.execute(strSQL)
if not res.eof then
  while not res.eof
  response.write res("nummer")& "<br>"
res.movenext
  wend
else
  response.write "EOF"
end if
%>
</B>
</FONT>
</P>
</td>
<td width="20%">
<p align="center">
<FONT SIZE="3">
<B>
<%
strSQL = "SELECT Titel FROM BogTabel"
set res = Conn.execute(strSQL)
if not res.eof then
  while not res.eof
  response.write res("Titel")& "<br>"
res.movenext
  wend
else
  response.write "EOF"
end if
%>
</B>
</FONT>
</p>
</td>
<td>
<B>
<FONT SIZE="3">
<p align="center">
<%
strSQL = "SELECT Forfatter FROM BogTabel"
set res = Conn.execute(strSQL)
if not res.eof then
  while not res.eof
  response.write res("Forfatter")& "<br>"
res.movenext
  wend
else
  response.write "EOF"
end if
%>
</p>
</FONT>
</B>
</td>
<td>
<p align="center">
<FONT SIZE="3">
<B>
<%
strSQL = "SELECT Forlag FROM BogTabel"
set res = Conn.execute(strSQL)
if not res.eof then
  while not res.eof
  response.write res("Forlag")& "<br>"
res.movenext
  wend
else
  response.write "EOF"
end if
%>
</B>
</FONT>
</p>
</td>
<td>
<p align="center">
<FONT SIZE="3">
<B>
<%
strSQL = "SELECT ISBN_nummer FROM BogTabel"
set res = Conn.execute(strSQL)
if not res.eof then
  while not res.eof
  response.write res("ISBN_nummer")& "<br>"
res.movenext
  wend
else
  response.write "EOF"
end if
%>
</B>
</FONT>
</p>
</td>
</tr>
</table>
</body>
</html>

Her deklarerer du conn som Connection og bruger efterfølgende strConn som navn:
Set conn = Server.CreateObject("ADODB.Connection")
strConn = "DRIVER=SQL Server;UID=Anonym;PWD=Anonym;Server=172.19.193.0;Database=BogDatabase"
conn.open strConn

Ret:
Set conn = Server.CreateObject("ADODB.Connection")
til:
Set strConn = Server.CreateObject("ADODB.Connection")

Hvis jeg ændre det i begge filer for jeg fejl meddelelsen

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: ''
/Default.asp, line 11

og hvis jeg kun ændre det i soeg for jeg fejl medelelsen

Error Type:
ADODB.Connection (0x800A0E79)
Operation is not allowed when the object is open.
/soeg.asp, line 9

Du skal fjerne linierne:

Set conn = Server.CreateObject("ADODB.Connection")
strConn = "DRIVER=SQL Server;UID=Anonym;PWD=Anonym;Server=172.19.193.0;Database=BogDatabase"
conn.open strConn

fra filen: default.asp

Du includere jo soeg.asp i default.asp derfor er conn allerede åben.

Derefter skal du denne linie i soeg.asp:
  objRS.open strSQL, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText
til:
  objRS.open strSQL, Conn, adOpenForwardOnly, adLockReadOnly, adCmdText

Du bruger strConn hvor du skal bruge Conn.

derefter skal du jo nok fjerne disse html-linier fra soeg.asp da den includeres i default.asp

<html>
<head>
<title></title>
</head>
<body>

og i bunden af soeg.asp fjernes disse:

</body>
</html>

Jeg kan ikke finde på mere lige nu. :-)

JO - du kan ikke bruge denne linie da det kræver at du includere adovbs.inc og det kan jeg ikke se at du gør.

  objRS.open strSQL, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText

den kan evt. laves sådan:

  objRS.open strSQL, strConn, 0, 1, &H0001

da det giver det samme.

Jeg har ændret filerne som sagt!!!
Filen soeg.asp

<%
Set strConn = Server.CreateObject("ADODB.Connection")
strConn = "DRIVER=SQL Server;UID=Anonym;PWD=Anonym;Server=172.19.193.0;Database=BogDatabase"
conn.open strconn
%>
<%
dim strSearch, intPageForm, intPageSize
dim strSearchRes

strSearch = request("itemsearch")
strSearchRes = request("searchres")

if request("pageform") = "" then
  intPageForm = 1
else
  intPageForm = cint(request("pageform"))
  if intPageForm < 1 then intPageForm = 1
end if
if request("pagesize") = "" then
  intPageSize = 3
else
  intPageSize = cint (request("pagesize"))
  if intPageSize < 1 then intPageSize = 1
end if

response.write _
  "<form action=""Default.asp"" method=""post"">" & _
  "søg: <input type=""text"" name=""itemsearch"" value""" & strSearch & """>" & _
  "<input type=""hidden"" name=""pageform"" value=""1"">" & _
  "<br>Antal pr side: <select name=""pagesize"">" & _
  "<option value=""1"">1</option>" & _
  "<option value=""2"">2</option>" & _
  "<option value=""5"">5</option>" & _
  "</select><br>" & _
  "<input type=""submit"" value=""søg""></form><br>"
if strSearchRes <> "" then
  ShowPage intPageFrom,intPageSize
else
  DoSearch strSearch
  ShowPage intPageFrom, intPageSize
end if

'Funktioner/procedurer

sub DoSearch (strS)
  dim strSQL, objRS
  strSQL = "SELECT Nummer, Titel, Forfatter, Forlag, ISBN_nummer FROM Bogtabel " & _
          "WHERE Titel LIKE '%" & strS & "%'"

set objRS = Server.CreateObject("ADODB.Recordset")
  objRS.open strSQL, Conn, 0, 1, &H0001
  if not objRS.EOF then
    strSearchRes = objRS.GetString( , , ",", ",")
else
    strSearchRes = ","
end if
objRS.Close
Set objRS = Nothing
strSearchRes = left(strSearchRes, len(strSearchRes)-1)
end sub

sub ShowPage (intFrom, intSize)
  dim arrSearchRes, i, intLast, intMax
  arrSearchRes = split(strSearchRes, ",")
if (intSize*3)+3*(intFrom-1) > ubound(arrSearchRes) then
    intLast = ubound(arrSearchRes)
else
  intLast = (intSize*3)+3*(intFrom-1)
end if
  intMax = (ubound(arrSearchRes)+1)/3
  response.write "<p>Fandt: " & intMax & " Titler.</p>"
 
  ShowButtons intFrom, intSize, intMax

  for i = 3*(intFrom-1) to intLast - 2 step 3
    response.write _
      arrSearchRes(i) & " - " & _
      arrSearchRes(i+1) & " - " & _
      arrSearchRes(i+2) & "<hr>"
  next
end sub

sub ShowButtons (intFrom, intSize, intMax)
  dim strBackButton, strForwardButton
  dim intPrev, intNext
  intPrev = intFrom - intSize
  if intPrev < 1 then intPrev=1
  intNext = intFrom + intSize
  if intNext > intMax then intNext=intMax

strBackButton = _
  "<Form action=""soeg.asp"" method=""post"">" & _
  "<input type=""hidden"" name=""searchres"" value=""" & strSearchRes & """>" & _
  "<input type=""hidden"" name=""pagesize"" value""" & intSize & """>" & _
  "<input type=""hidden"" name=""pagefrom"" value""" & intPrev & """>" & _
  "<input type=""submit"" value""<- Tilbage""></form>"
strForwardButton = _
  "<form action=""soeg.asp"" method=""post"">" & _
  "<input type=""hidden"" name""searchres"" value=""" & strSearchRes & """>" & _
  "<input type=""hidden"" name""pagesize"" value=""" & intSize & """>" & _
  "<input type=""hidden"" name""pageform"" value=""" & intNext & """>" & _
  "<input type=""hidden"" name""Frem ->""></form>"

  response.write " <table border=0><tr><td>" & strBackButton & _
    "</td><td>" & strForwardButton & "</td></tr></table>"
end sub
%>

Filen default

<html>
<head>
<title>SQL Server</title>
</head>
<body>
<H1><p align="Center">SKP Bogdatabase</p></H1>
<br>
<!-- #INCLUDE FILE="soeg.asp" -->
<Table Border="0" align="center">
<Tr>
<td width="5%"><H3><U><p align="center">Nummer</p></U></H3></td>
<td width="35%"><H3><U><p align="center">Titel</p></U></H3></td>
<td width="20%"><H3><U><p align="center">Forfatter</p></U></H3></td>
<td width="20%"><H3><U><p align="center">Forlag</p></U></H3></td>
<td width="20%"><H3><U><p align="center">ISBN nummer</p></U></H3></td>
</tr>
<tr>
<td>
<p align="center">
<FONT SIZE="3">
<B>
<%
strSQL = "SELECT Nummer FROM BogTabel"
set res = Conn.execute(strSQL)
if not res.eof then
  while not res.eof
  response.write res("nummer")& "<br>"
res.movenext
  wend
else
  response.write "EOF"
end if
%>
</B>
</FONT>
</P>
</td>
<td width="20%">
<p align="center">
<FONT SIZE="3">
<B>
<%
strSQL = "SELECT Titel FROM BogTabel"
set res = Conn.execute(strSQL)
if not res.eof then
  while not res.eof
  response.write res("Titel")& "<br>"
res.movenext
  wend
else
  response.write "EOF"
end if
%>
</B>
</FONT>
</p>
</td>
<td>
<B>
<FONT SIZE="3">
<p align="center">
<%
strSQL = "SELECT Forfatter FROM BogTabel"
set res = Conn.execute(strSQL)
if not res.eof then
  while not res.eof
  response.write res("Forfatter")& "<br>"
res.movenext
  wend
else
  response.write "EOF"
end if
%>
</p>
</FONT>
</B>
</td>
<td>
<p align="center">
<FONT SIZE="3">
<B>
<%
strSQL = "SELECT Forlag FROM BogTabel"
set res = Conn.execute(strSQL)
if not res.eof then
  while not res.eof
  response.write res("Forlag")& "<br>"
res.movenext
  wend
else
  response.write "EOF"
end if
%>
</B>
</FONT>
</p>
</td>
<td>
<p align="center">
<FONT SIZE="3">
<B>
<%
strSQL = "SELECT ISBN_nummer FROM BogTabel"
set res = Conn.execute(strSQL)
if not res.eof then
  while not res.eof
  response.write res("ISBN_nummer")& "<br>"
res.movenext
  wend
else
  response.write "EOF"
end if
%>
</B>
</FONT>
</p>
</td>
</tr>
</table>
</body>
</html>


Men for nu en ny fejl meddelelse

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: ''
/soeg.asp, line 4

Det er fordi denne linie skal ændres
Set strConn = Server.CreateObject("ADODB.Connection")
til
Set Conn = Server.CreateObject("ADODB.Connection")

Det havde jeg ikke set.

Det virkede men nu er der lige en ny fejl

Error Type:
Microsoft VBScript runtime (0x800A0009)
Subscript out of range: '-3'
/soeg.asp, line 76

Linje 73 er den her
response.write _

Er der ikke nogen der har et bud på denne fejl meddelelse???

lukket