Hello jag behöver lite hjälp när det gäller att få fram resultat, sidan ser ut på följande vis (se nedan). problmet är när jag trycker på någon bokstav så visas inget resultat, men qrygetexp, qrygetfaxes osv fungerar Vilken datatyp har fältet lname? TACK det var inte riktigt rätt men det var en god hjälp på vägenHjälp med Stored Proc. [LÖST]
vad har jag gjort för fel?
<code>
<HTML>
<HEAD>
<TITLE> Telefonkatalog - Index </TITLE>
<link REL="stylesheet" HREF="../style/style2.css" TYPE="text/css">
<style type="text/css">
<!--
BODY { BACKGROUND-ATTACHMENT: fixed ; font-family: Verdana;color:black;font-size:8pt; margin-right:0; }
DIV { font-family: Arial, Helvetica; color:black;font-size:8pt; }
TABLE { font-family: Arial, Helvetica; color:black;font-size:8pt; }
A { text-decoration:none; color:black}
A:VISITED { color:black; }
A:HOVER { text-decoration:underline; color:red; }
.Outline { font-size:10pt; line-height:10pt; color:white; cursor:hand; text-decoration:none; font-weight:bold; }
.Outline2 { font-size:8pt; line-height:8pt; color:white; cursor:hand; text-decoration:none; font-weight:bold; }
.Topic { font-size:8pt; line-height:8pt; color:white; cursor:hand; text-decoration:none; font-weight:bold; }
//-->
</style>
</HEAD>
<BODY BGCOLOR="#FFFAF0">
<TABLE Width="600" cellpadding="0">
<TR><TD>
<TABLE width="100%" border="0" cellspacing="0" cellpadding="2">
<TR BGCOLOR="#DDDDDD">
<TH style="color:black" colspan="3">
<% For A = 65 to 65 + 25
Response.Write "<A style='color:black' href='default.asp?t=i&ix=" & A & "'> " & CHR(A) & " </a>|"
next
Response.Write "<A style='color:black' href='default.asp?t=i&ix=197'> " & CHR(197) & " </a>|"
Response.Write "<A style='color:black' href='default.asp?t=i&ix=196'> " & CHR(196) & " </a>|"
Response.Write "<A style='color:black' href='default.asp?t=i&ix=214'> " & CHR(214) & " </a>" %>
</TH>
</TR>
<TR BGCOLOR="#DDDDDD">
<TH style="color:black" colspan="3">
<% Response.Write "<A style='color:black' href='default.asp?t=f&ix=qrygetexp'>Expeditioner</a> | "
' Response.Write "<A style='color:black' href='default.asp?t=f&ix=qrygetfunct'>Funktioner</a> | "
Response.Write "<A style='color:black' href='default.asp?t=f&ix=qrygetfaxes'>Faxar</a> | "
Response.Write "<A style='color:black' href='default.asp?t=f&ix=qrygetlekt'>Lektionssalar</a> | "
Response.Write "<A style='color:black' href='default.asp?t=f&ix=qrygetlarm'>Larmnummer</a>" %>
</TH>
</TR>
</TABLE>
<%
If Not IsEmpty(Request.Querystring("ix")) Then
Set cn = Server.CreateObject("ADODB.Connection")
strDSN = "telekat"
cn.Open strDSN
Set rs = Server.CreateObject("ADODB.RecordSet")
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = cn
Select Case Request.Querystring("t")
Case "i"
cmd.CommandText = "qrygetindex"
var = CHR(Request.QueryString("ix"))
Set param1 = cmd.CreateParameter
param1.type = adChar
param1.size = 100
param1.name = "var"
param1.value = var & "*"
cmd.Parameters.Append param1
Set rs = cmd.Execute(,,adCmdStoredProc)
Case "f"
cmd.CommandText = Request.Querystring("ix")
Set rs = cmd.Execute(,,adCmdStoredProc)
End Select
response.write(var)
%>
<TR><TD>
<TABLE width="100%" border="0" cellspacing="0" cellpadding="2">
<TR bgcolor="maroon" style="color:white">
<TH>Namn</TH>
<TH>Enh / Avd</TH>
<TH>Anknytning</TH>
<TH>Mobil</TH>
<TH>Psök</TH>
<TH>Mail</TH>
</TR>
<% Count = 1
DO While NOT rs.EOF %>
<TR>
<TD nowrap><%= rs("lname") %> <%= rs("fname") %></TD>
<TD nowrap bgcolor="#DDDDDD"><%= rs("avd") %> / <%= rs("uavd") %></TD>
<TD nowrap align="center"><%= rs("ankn") %></TD>
<TD nowrap bgcolor="#DDDDDD" align="center"><%= rs("mobil") %></TD>
<TD nowrap align="center"><%= rs("psok") %></TD>
<TD nowrap bgcolor="#DDDDDD">"><%= rs("mail") %></TD>
</TR>
<%
rs.MoveNext
count=count+1
if count MOD 15 = 1 Then
%>
<TR bgcolor="maroon" style="color:white">
<TH>Namn</TH>
<TH>Enh / Avd</TH>
<TH>Anknytning</TH>
<TH>Mobil</TH>
<TH>Psök</TH>
<TH>Mail</TH>
</TR>
<% End If
Loop %>
</TABLE>
</TD></TR>
<% ' Städa & Stäng
rs.Close
cn.Close
Set cmd = Nothing
Set rs = Nothing
Set cn = Nothing
End If %>
</TD></TR>
</TABLE>
</BODY>
</HTML>
</code>
------------------------------
Stored Proc.
[qrygetindex]
(
@var nchar
)
AS
Select *
FROM telekat
WHERE (((telekat.lname) Like '%@var%') And ((([telekat].[lname]) LIKE '%fax%' Or ([telekat].[lname]) LIKE '%lekt%' Or ([telekat].[lname]) LIKE '%larm%')))
Order by telekat.lname, telekat.fname;Sv: Hjälp med Stored Proc.
Du kan testa med:
<code>
<HTML>
<HEAD>
<TITLE> Telefonkatalog - Index </TITLE>
<link REL="stylesheet" HREF="../style/style2.css" TYPE="text/css">
<style type="text/css">
<!--
BODY { BACKGROUND-ATTACHMENT: fixed ; font-family: Verdana;color:black;font-size:8pt; margin-right:0; }
DIV { font-family: Arial, Helvetica; color:black;font-size:8pt; }
TABLE { font-family: Arial, Helvetica; color:black;font-size:8pt; }
A { text-decoration:none; color:black}
A:VISITED { color:black; }
A:HOVER { text-decoration:underline; color:red; }
.Outline { font-size:10pt; line-height:10pt; color:white; cursor:hand; text-decoration:none; font-weight:bold; }
.Outline2 { font-size:8pt; line-height:8pt; color:white; cursor:hand; text-decoration:none; font-weight:bold; }
.Topic { font-size:8pt; line-height:8pt; color:white; cursor:hand; text-decoration:none; font-weight:bold; }
//-->
</style>
</HEAD>
<BODY BGCOLOR="#FFFAF0">
<TABLE Width="600" cellpadding="0">
<TR><TD>
<TABLE width="100%" border="0" cellspacing="0" cellpadding="2">
<TR BGCOLOR="#DDDDDD">
<TH style="color:black" colspan="3">
<% For A = 65 to 65 + 25
Response.Write "<A style='color:black' href='default.asp?t=i&ix=" & A & "'> " & CHR(A) & " </a>|"
next
Response.Write "<A style='color:black' href='default.asp?t=i&ix=197'> " & CHR(197) & " </a>|"
Response.Write "<A style='color:black' href='default.asp?t=i&ix=196'> " & CHR(196) & " </a>|"
Response.Write "<A style='color:black' href='default.asp?t=i&ix=214'> " & CHR(214) & " </a>" %>
</TH>
</TR>
<TR BGCOLOR="#DDDDDD">
<TH style="color:black" colspan="3">
<% Response.Write "<A style='color:black' href='default.asp?t=f&ix=qrygetexp'>Expeditioner</a> | "
' Response.Write "<A style='color:black' href='default.asp?t=f&ix=qrygetfunct'>Funktioner</a> | "
Response.Write "<A style='color:black' href='default.asp?t=f&ix=qrygetfaxes'>Faxar</a> | "
Response.Write "<A style='color:black' href='default.asp?t=f&ix=qrygetlekt'>Lektionssalar</a> | "
Response.Write "<A style='color:black' href='default.asp?t=f&ix=qrygetlarm'>Larmnummer</a>" %>
</TH>
</TR>
</TABLE>
<%
Const adChar = 129
Const adParamInput = 1
Const adCmdStoredProc = 4
If Not IsEmpty(Request.Querystring("ix")) Then
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "telekat"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = cn
Select Case Request.Querystring("t")
Case "i"
cmd.CommandText = "qrygetindex"
var = CHR(Request.QueryString("ix"))
cmd.Parameters.Append cmd.CreateParameter("var", adChar, adParamInput, 100, Var & "%")
Set rs = cmd.Execute(,,adCmdStoredProc)
Case "f"
cmd.CommandText = Request.Querystring("ix")
Set rs = cmd.Execute(,,adCmdStoredProc)
End Select
response.write var
%>
<TR><TD>
<TABLE width="100%" border="0" cellspacing="0" cellpadding="2">
<TR bgcolor="maroon" style="color:white">
<TH>Namn</TH>
<TH>Enh / Avd</TH>
<TH>Anknytning</TH>
<TH>Mobil</TH>
<TH>Psök</TH>
<TH>Mail</TH>
</TR>
<% Count = 1
Do Until rs.EOF %>
<TR>
<TD nowrap><%= rs("lname") %> <%= rs("fname") %></TD>
<TD nowrap bgcolor="#DDDDDD"><%= rs("avd") %> / <%= rs("uavd") %></TD>
<TD nowrap align="center"><%= rs("ankn") %></TD>
<TD nowrap bgcolor="#DDDDDD" align="center"><%= rs("mobil") %></TD>
<TD nowrap align="center"><%= rs("psok") %></TD>
<TD nowrap bgcolor="#DDDDDD">"><%= rs("mail") %></TD>
</TR>
<%
rs.MoveNext
count=count+1
if count MOD 15 = 1 Then
%>
<TR bgcolor="maroon" style="color:white">
<TH>Namn</TH>
<TH>Enh / Avd</TH>
<TH>Anknytning</TH>
<TH>Mobil</TH>
<TH>Psök</TH>
<TH>Mail</TH>
</TR>
<% End If
Loop %>
</TABLE>
</TD></TR>
<% ' Städa & Stäng
rs.Close
Set rs = Nothing
Set cmd = Nothing
cn.Close
Set cn = Nothing
End If
%>
</TD></TR>
</TABLE>
</BODY>
</HTML>
</code>
[qrygetindex]
(
@var char(100)
)
AS
SELECT *
FROM telekat
WHERE (((telekat.lname) Like @var) And ((([telekat].[lname]) LIKE '%fax%' Or ([telekat].[lname]) LIKE '%lekt%' Or ([telekat].[lname]) LIKE '%larm%')))
Order by telekat.lname, telekat.fname;Sv: Hjälp med Stored Proc. [LÖST]