Stored Procedure, Autocomplete, Ajax ... and stuff...

[EDN Admin]

Hi, Im trying to use Autocomplete with Ajax binding with SQL Server 2000 and VS2008. The SProcedure doesnt give any data or rows from table are 0 (cero). Please Help !
This is my code:
Stored Procedure:ALTER PROCEDURE spBusca_Universo_Nombre
@cualNombre varchar(150),
@resultado varchar(150) OUTPUT
AS

SET NOCOUNT ON

SELECT @resultado = nombre
FROM UNIVERSO
WHERE NOMBRE LIKE @cualNombre + %

RETURN

This is AutoComplete.cs: [WebMethod]
public string[] GetCompletionList(string prefixText, int count)
{
string connectionString = null;
SqlConnection connection;
SqlDataAdapter adapter;
SqlParameter param;
SqlParameter paramResultado;
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
string[] items = null;

AutoComplete oAC_Datos = new AutoComplete();

try
{
connectionString = "SERVER=111.22.333.444;UID=XXXX;DATABASE=WWWW; pwd=YYYY";
connection = new SqlConnection(connectionString);

connection.Open();
cmd.Connection = connection;

//Load user list
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.spBusca_Universo_Nombre";

param = new SqlParameter("@cualNombre", prefixText);
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
cmd.Parameters.Add(param).Value = prefixText + "%";

paramResultado = new SqlParameter("@resultado",SqlDbType.Text,150);
paramResultado.Direction = ParameterDirection.Output;
paramResultado.DbType = DbType.String;
cmd.Parameters.Add(paramResultado);

List<string> customers = new List<string>();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
customers.Add(sdr["nombre"].ToString());
}
}
connection.Close();
}
catch (Exception ex)
{
string sMensaje = ex.Message.ToString();
}
return items;

}

And the default.aspx is:<asp:TextBox ID="txtNombreCompleto" runat="server" Height="22px" Width="486px </asp:TextBox>
<asp1:AutoCompleteExtender
runat="server"
ID="autoComplete1"
TargetControlID="txtNombreCompleto"
ServicePath="AutoComplete.asmx"
ServiceMethod="GetCompletionList"
MinimumPrefixLength="2"
CompletionInterval="1000"
EnableCaching="true"
CompletionSetCount="20"
CompletionListCssClass="autocomplete_completionListElement"
CompletionListItemCssClass="autocomplete_listItem"
CompletionListHighlightedItemCssClass="autocomplete_highlightedListItem"
DelimiterCharacters=";, :"
ShowOnlyCurrentWordInCompletionListItem="true"
>
<Animations>
<OnShow>
<Sequence>
<OpacityAction Opacity="0" />
<HideAction Visible="true" />

<ScriptAction Script="
var behavior = $find(AutoCompleteEx);
if (!behavior._height) {
var target = behavior.get_completionList();
behavior._height = target.offsetHeight - 2;
target.style.height = 0px;
}" />

<Parallel Duration=".4
<FadeIn />
<Length PropertyKey="height" StartValue="0" EndValueScript="$find(AutoCompleteEx)._height" />
</Parallel>
</Sequence>
</OnShow>
<OnHide>
<Parallel Duration=".4
<FadeOut />
<Length PropertyKey="height" StartValueScript="$find(AutoCompleteEx)._height" EndValue="0" />
</Parallel>
</OnHide>
</Animations>
</asp1:AutoCompleteExtender>
Thanks in advance
Kikillo
Kikillo

View the full article