11 Replies - 5343 Views - Last Post: 15 August 2010 - 09:10 PM Rate Topic: -----

#1 pwtc222  Icon User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 72
  • Joined: 02-July 10

AutoComplete from database

Posted 11 August 2010 - 07:43 PM

I m having problem when implementing autocompleteextender in visual studio 2008, where when i enter my any text in my textbox,it will automaticly capture the keywords and suggest words from database..no errors found,but it didnt works when i enter any keyword inside the textbox..
This is my aspx file :
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        <Services>
        <asp:ServiceReference Path="AutoComplete.asmx" />
        </Services>
        </asp:ScriptManager>
        <div>
            <asp:TextBox runat="server" ID="myTextBox" Width="300" autocomplete="off" />
            <ajaxToolkit:AutoCompleteExtender
                runat="server"
                ID="autoComplete1"
                TargetControlID="myTextBox"
                ServicePath="AutoComplete.asmx"
                ServiceMethod="GetCompletionList"
                MinimumPrefixLength="2"
                CompletionInterval="1000"
                EnableCaching="true"
                CompletionSetCount="12" />
    </div>
    </form>
</body>
</html>




This is my asmx.cs file:
using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for AutoComplete
/// </summary>

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]

public class AutoComplete : System.Web.Services.WebService
{
    [WebMethod]
    public string[] GetCompletionList(string prefixText)
    {
        DataSet dtst = new DataSet();
        SqlConnection sqlCon = new SqlConnection(ConfigurationManager.AppSettings["%$ ConnectionStrings:C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\DATABASE1.MDFConnectionString %"]);
        string strSql = "SELECT Company_Name FROM Table1 WHERE CompanyName LIKE '" + prefixText + "%' ";
        SqlCommand sqlComd = new SqlCommand(strSql, sqlCon);
        sqlCon.Open();
        SqlDataAdapter sqlAdpt = new SqlDataAdapter();
        sqlAdpt.SelectCommand = sqlComd;
        sqlAdpt.Fill(dtst);
        string[] cntName = new string[dtst.Tables[0].Rows.Count];
        int i = 0;
        try
        {
            foreach (DataRow rdr in dtst.Tables[0].Rows)
            {
                cntName.SetValue(rdr["CompanyName"].ToString(), i);
                i++;
            }
        }
        catch { }
        finally
        {
            sqlCon.Close();
        }
        return cntName;
    }
}


Should i modify any code part?
Can i use the autocompleteextender using sqldatasource to call database?
Any ideas will be much appreciated.

Reference:http://www.c-sharpcorner.com/UploadFile/munnamax/AutocompleteExtender08062007113854AM/AutocompleteExtender.aspx

This post has been edited by pwtc222: 11 August 2010 - 08:50 PM


Is This A Good Question/Topic? 0
  • +

Replies To: AutoComplete from database

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: AutoComplete from database

Posted 11 August 2010 - 08:02 PM

First and foremost.....it's called code tags. Not many people, including me, aren't going to even take the time to read what you have until it's inside code tags, otherwise it's just too hard to read. :code:
Was This Post Helpful? 0
  • +
  • -

#3 pwtc222  Icon User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 72
  • Joined: 02-July 10

Re: AutoComplete from database

Posted 12 August 2010 - 12:37 AM

Is it possible to implement the autocomplete textbox using sqldatasource?
In every keypress,the data should be called from database via sqldatasource.
I have tried to use WebService method to call database many times and methods,but it doesnt work.
Was This Post Helpful? 0
  • +
  • -

#4 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: AutoComplete from database

Posted 12 August 2010 - 05:10 AM

I don't believe it's possible. The SQLDataSource is on the page. The AutoCompleteExtender calls a webservice.
Was This Post Helpful? 0
  • +
  • -

#5 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: AutoComplete from database

Posted 12 August 2010 - 05:13 AM

Have you tried taking that query, putting it into a console application and running it to see if it returns anything?
Was This Post Helpful? 0
  • +
  • -

#6 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: AutoComplete from database

Posted 12 August 2010 - 07:23 AM

Topics merged. Since this is all related lets keep it all in one topic.
Was This Post Helpful? 0
  • +
  • -

#7 pwtc222  Icon User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 72
  • Joined: 02-July 10

Re: AutoComplete from database

Posted 12 August 2010 - 05:40 PM

I have tried that too.But its not seems to be working.
Is my web service code above right?
Any other way to call database?
Was This Post Helpful? 0
  • +
  • -

#8 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: AutoComplete from database

Posted 12 August 2010 - 06:55 PM

You can use the sqlconnection, sqlcommand, and sqldatareader classes. Those are what I always use.
Was This Post Helpful? 0
  • +
  • -

#9 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: AutoComplete from database

Posted 13 August 2010 - 07:11 AM

View Postpwtc222, on 12 August 2010 - 04:40 PM, said:

I have tried that too.But its not seems to be working.
Is my web service code above right?

No, the signature of the WebMethod is not correct, it is missing the second parameter. It must match the following:
public string[] GetCompletionList(string prefixText, int count)

Was This Post Helpful? 1
  • +
  • -

#10 pwtc222  Icon User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 72
  • Joined: 02-July 10

Re: AutoComplete from database

Posted 15 August 2010 - 06:08 PM

Still not working..
How to test webservice page to check wheater it is functioning correctly or not when connect to database?

I run the current web service page and it shows the Hello World string,although i have removed the method and replace with the sql connection code.
Was This Post Helpful? 0
  • +
  • -

#11 pwtc222  Icon User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 72
  • Joined: 02-July 10

Re: AutoComplete from database

Posted 15 August 2010 - 07:42 PM

Hi..
I implement an autocomplete texttbox using following code and it works fine :

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="AutoComplete2._Default" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public static string[] GetCompletionList(string prefixText, int count)
{
    return "this is sample text".Split();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
        </asp:ToolkitScriptManager>
        <asp:TextBox ID="TextBox1" runat="server" ></asp:TextBox>
        <asp:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server"
            ServiceMethod="GetCompletionList"
            CompletionInterval="500"
            ServicePath="" TargetControlID="TextBox1"
            EnableCaching="true"
            CompletionSetCount="20"
            DelimiterCharacters=";, :"
            UseContextKey="True">
        </asp:AutoCompleteExtender>
        <br />
    </div>
    </form>
</body>
</html>



I want the autocomplete to read from database. And i tried used following code in above script,but it doesn't work and shows some error
in "SqlConnection oConn = new SqlConnection(connect);" part:

<script>

public string[] GetCompanyName(string prefixText, int count)
{

string sql = "Select * from Table1 Where Company_Name like '" + prefixText + "%" + "'";
string connect = ConfigurationManager.AppSettings["Conn"];
SqlConnection oConn = new SqlConnection(connect);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
da.SelectCommand.Parameters.Add(@prefixText, SqlDbType.NVarChar, 50).Value = prefixText + "%";
DataTable dt = new DataTable();
da.Fill(dt);
string[] items = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
items.SetValue(dr["Company_Name"].ToString(), i);
i++;
}
return items;
}
</script>



could anyone tell me where's the wrong?Is my code above correct?
Was This Post Helpful? 0
  • +
  • -

#12 pwtc222  Icon User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 72
  • Joined: 02-July 10

Re: AutoComplete from database

Posted 15 August 2010 - 09:10 PM

I have solved the problem.
I added following code on aspx file:

<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SqlClient" %>



And this code on cs file:
using System.Data.SqlClient;



it works fine,it reads and suggests from database.

This post has been edited by pwtc222: 15 August 2010 - 09:11 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1