Category Archives: Webservice

[RESOLVED] autocomplete TextBox with data from a SQL SERVER

Hi all, im new at asp. net and im making a web page with 3 dropdownlist filtering DATA, and then i display a Gridview with the data selected. But i also have some TextBoxs and i want them to autocomplete according to the filters i previously selected. Im working all of this with no code behind.

Thank all, and here the code. I Bold The TextBox so you can see it clearly :D.

<%@ Import Namespace="System.Data" %>
<%@ Page Language="VB" Debug="True" %>
<%@ import Namespace="System" %>
<%@ import Namespace="System.Data.OLEDB" %>
<%@ import Namespace="System.Data.OleDb.OleDbException" %>


<form id="form1" runat="server">


<asp:LinkButton ID="LinkButton5" runat="server" Text="Default"
    PostBackUrl="http://localhost:49724/Default.aspx"></asp:LinkButton>
<br />
<asp:LinkButton ID="LinkButton4" runat="server" Text="Editar/Actualizar/Eliminar Empleado"
    PostBackUrl="http://localhost:49724/EditEmpleado.aspx"></asp:LinkButton>
<br />
<asp:LinkButton ID="LinkButton7" runat="server" Text="Editar/Actualizar/Eliminar Producto"
    PostBackUrl="http://localhost:49724/EditProducto.aspx"></asp:LinkButton>
<br />
<asp:LinkButton ID="LinkButton8" runat="server" Text="Editar/Actualizar/Eliminar Categoria"
    PostBackUrl="http://localhost:49724/EditCategoria.aspx"></asp:LinkButton>


<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestingConnectionString %>"


    SelectCommand="SELECT [PROJID] FROM [sxb_Proyectos] ORDER BY [PROJID]">


</asp:SqlDataSource>


<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:TestingConnectionString %>"


    SelectCommand="SELECT DISTINCT Ax50_Empleados.rut, Ax50_HorasRegistradas.rut
                    FROM Ax50_Empleados
                    INNER JOIN Ax50_HorasRegistradas
                    ON Ax50_Empleados.rut= Ax50_HorasRegistradas.rut
                    where ([PROJID] = CASE WHEN @PROJID = '-1' THEN [PROJID] ELSE @PROJID END)
                    ORDER BY Ax50_Empleados.rut">


<SelectParameters>


<asp:ControlParameter ControlID="DropDownList1" Name="PROJID" PropertyName="SelectedValue"/>


</SelectParameters>


</asp:SqlDataSource>


<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:TestingConnectionString %>"


    SelectCommand="SELECT DISTINCT sxb_Producto.Producto, Ax50_HorasRegistradas.LINEPROPERTYID
                    FROM sxb_Producto
                    INNER JOIN Ax50_HorasRegistradas
                    ON sxb_Producto.Producto = Ax50_HorasRegistradas.LINEPROPERTYID
                    where (([rut] = CASE WHEN @rut=  '-1' THEN [rut] ELSE @rut END)
                    AND
                    ([PROJID] = CASE WHEN @PROJID = '-1' THEN [PROJID] ELSE @PROJID END))
                    ORDER BY sxb_Producto.Producto">


<SelectParameters>


<asp:ControlParameter ControlID="DropDownList2" Name="rut" PropertyName="SelectedValue"/>
<asp:ControlParameter ControlID="DropDownList1" Name="PROJID" PropertyName="SelectedValue"/>


</SelectParameters>


</asp:SqlDataSource>


<table>
<tr>
<td>Proyecto</td>
<td>


<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"  AppendDataBoundItems="true"
DataTextField="PROJID" DataValueField="PROJID">
<asp:ListItem Value="0"><Select an option></asp:ListItem>
<asp:ListItem Value="-1"><Select All></asp:ListItem>
                    

</asp:DropDownList>
</tr>


<tr>
<td>Rut Empleado</td>
<td>
<asp:DropDownList ID="DropDownList2" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2" AppendDataBoundItems="true" enableviewstate="false"
DataTextField="rut" DataValueField="rut">
<asp:ListItem Value="-1"><Select All></asp:ListItem>


</asp:Dropdownlist>


</td>
</tr>


<tr>
<td>Producto</td>
<td>
<asp:DropDownList ID="DropDownList3" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource3" AppendDataBoundItems="true" enableviewstate="false"
DataValueField="Producto" DataTextField="Producto">
<asp:ListItem Value="-1"><Select All></asp:ListItem>


</asp:DropDownList>
</td>
</tr>
</table>
<br />
<hr />
<table><tr><td>Codigo Proyecto</td><td>Nombre Proyecto</td><td>Cliente</td></tr>
<tr><td>
    

</td>
<td>
    <asp:textbox runat="server" ReadOnly="True"></asp:textbox>
</td>
<td>
    <asp:textbox runat="server"></asp:textbox>
</td>


</tr>
</table>
<hr />


<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="PROJID"
    DataSourceID="SqlDataSource4"
    EmptyDataText="There are no data records to display."
    onrowectedindexchanged="GridView1_SelectedIndexChanged">
    <Columns>
        <asp:TemplateField>
        <ItemTemplate>
        <asp:LinkButton ID="Editar" Text="Editar" runat="server" CommandName="Edit"></asp:LinkButton>
        </ItemTemplate>
        <EditItemTemplate>
        <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update" AutoPostBack="True" AppendDataBoundItems="true" enableviewstate="false"
            Text="Actualizar"></asp:LinkButton>
        <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" AutoPostBack="True"
            Text="Cancelar"></asp:LinkButton>
        </EditItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="PROJID" HeaderText="PROJID"
            SortExpression="PROJID" />
        <asp:BoundField DataField="rut" HeaderText="rut"
            SortExpression="rut" />
        <asp:BoundField DataField="LINEPROPERTYID" HeaderText="LINEPROPERTYID"
            SortExpression="LINEPROPERTYID" />
        <asp:BoundField DataField="QTY" HeaderText="QTY" SortExpression="QTY" />
        <asp:BoundField DataField="TXT" HeaderText="TXT" SortExpression="TXT" />
        <asp:BoundField DataField="TRANSDATE" HeaderText="TRANSDATE"
            SortExpression="TRANSDATE" DataFormatString="{0:d}" />
        <asp:BoundField DataField="Comentario" HeaderText="Comentario"
            SortExpression="Comentario" />
    </Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource4" runat="server"
    ConnectionString="<%$ ConnectionStrings:TestingConnectionString %>"
    ProviderName="<%$ ConnectionStrings:TestingConnectionString.ProviderName %>"
    SelectCommand="SELECT [PROJID], [rut], [LINEPROPERTYID], [QTY], [TXT], [TRANSDATE], [TRANSSTATUS], [DIMENSION], [DIMENSION3_], [Comentario]
                   FROM [Ax50_HorasRegistradas]
                   WHERE
                   (([rut] = CASE WHEN @rut=  '-1' THEN [rut] ELSE @rut END)
                   AND
                   ([PROJID] = CASE WHEN @PROJID = '-1' THEN [PROJID] ELSE @PROJID END)
                   AND
                   ([LINEPROPERTYID] = CASE WHEN @Producto = '-1' THEN [LINEPROPERTYID] ELSE @Producto END))"


     UpdateCommand="UPDATE [Ax50_HorasRegistradas] set Comentario = @Comentario WHERE PROJID = @PROJID AND TRANSDATE = @TRANSDATE">


<UpdateParameters>
<asp:Parameter Name="PROJID" />
<asp:Parameter Name="TRANSDATE" />
</UpdateParameters>


<SelectParameters>
<asp:ControlParameter ControlID="DropDownList2" Name="rut" PropertyName="SelectedValue"/>
<asp:ControlParameter ControlID="DropDownList1" Name="PROJID" PropertyName="SelectedValue"/>
<asp:ControlParameter ControlID="DropDownList3" Name="Producto" PropertyName="SelectedValue"/>
</SelectParameters>


</asp:SqlDataSource>
</form>


well it didnt bold the text box because i think is pasted codes, so the lines of the textbox are 107 and 110.

thank so much

Hi,


Check below links,


http://www.aspdotnetcodes.com/AutoComplete_From_Database.aspx

http://www.developerbarn.com/net-code-samples/75-using-ajax-auto-complete-extender-database.html



You can use ajax extensions and  web services:

.aspx page:

<ajaxToolkit:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server"> </ajaxToolkit:ToolkitScriptManager>
  <div>
    <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
    <ajaxToolkit:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" MinimumPrefixLength="1"
            ServiceMethod="GetProducts" ServicePath="WebService.asmx" TargetControlID="txtSearch"> </ajaxToolkit:AutoCompleteExtender>
          </div>


WebService.cs:

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Web.Script.Services;

/// <summary>
/// Summary description for WebService
/// </summary>
[ScriptService]
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class WebService : System.Web.Services.WebService
{
    public WebService()
    {
        //Uncomment the following line if using designed components
        //InitializeComponent();
    }
    [WebMethod]
    public string[] GetProducts(string prefixText)
    {
       
            string sql = "Select rut from table Where rut like @prefixText";
            SqlDataAdapter da = new SqlDataAdapter(sql, ConfigurationManager.ConnectionStrings["TestingConnectionString"].ToString());
            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["rut"].ToString(), i);
                i++;
            }
            return items;

        }


       
    }




 

 

hi, thanks for answering but with ajax it doesnt work, y pasted the code like this. 

<%@ Import Namespace="System.Data" %>
<%@ Page Language="VB" Debug="True" %>
<%@ import Namespace="System" %>
<%@ import Namespace="System.Data.OLEDB" %>
<%@ import Namespace="System.Data.OleDb.OleDbException" %>


<script>


<ajaxToolkit:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server"> </ajaxToolkit:ToolkitScriptManager>
  <div>
    <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
    <ajaxToolkit:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" MinimumPrefixLength="1"
            ServiceMethod="GetProducts" ServicePath="WebService.asmx" TargetControlID="txtSearch"> </ajaxToolkit:AutoCompleteExtender>
          </div>


</script>






<form id="form1" runat="server">


<asp:LinkButton ID="LinkButton5" runat="server" Text="Default"
    PostBackUrl="http://localhost:49724/Default.aspx"></asp:LinkButton>
<br />
<asp:LinkButton ID="LinkButton4" runat="server" Text="Editar/Actualizar/Eliminar Empleado"
    PostBackUrl="http://localhost:49724/EditEmpleado.aspx"></asp:LinkButton>


............


and it give me an error "Etiqueta de servidor 'ajaxToolkit:ToolkitScriptManager' desconocida."

it doesnt recognize the ajaxtoolkit

Did you download and configure the ajax control toolkit with your Visual studio?

if not, you can download from here:

 http://www.asp.net/ajaxlibrary/act.ashx

http://ajaxcontroltoolkit.codeplex.com/releases/view/43475

 

 

 

 

ok i did all the steps of ajax and nothing happens,now i have this.


<%@ Import Namespace="System.Data" %>
<%@ Page Language="VB" Debug="True" %>


<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<%@ import Namespace="System" %>
<%@ import Namespace="System.Data.OLEDB" %>
<%@ import Namespace="System.Data.OleDb.OleDbException" %>
<%@ Import Namespace="AjaxControlToolkit" %>


 <ajaxToolkit:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server"></ajaxToolkit:ToolkitScriptManager>
  <div>
    <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
    <ajaxToolkit:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" MinimumPrefixLength="1"
            ServiceMethod="GetProducts" ServicePath="WebService.asmx" TargetControlID="txtSearch"> </ajaxToolkit:AutoCompleteExtender>
  </div>




<form id="form1" runat="server">


<asp:LinkButton ID="LinkButton5" runat="server" Text="Default"
    PostBackUrl="http://localhost:49724/Default.aspx"></asp:LinkButton>
<br />
.
.
.
.
.
.


thanks very much for your help :D

Did you add the webservice.cs (with the GetProducts method) file in the app_code folder ?

if you did already, please mark out the line and start debugging and see if that is calling or not.....

i have the webservice in the same folder as the aspx web page, but what is that of getProducts method? it is something that i have to add as a line, its a file?


thank you for your time.

Please see my first reply.....you can see the GetProducts method there....

public string[] GetProducts(string prefixText)
    {......}


 

hi, and thanks for answering.

i have this for the moment in the aspx file.

<%@ Import Namespace="System.Data" %>
<%@ Page Language="VB" Debug="True" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<%@ import Namespace="System" %>
<%@ import Namespace="System.Data.OLEDB" %>
<%@ import Namespace="System.Data.OleDb.OleDbException" %>
<%@ Import Namespace="AjaxControlToolkit" %>


<ajaxToolkit:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server"></ajaxToolkit:ToolkitScriptManager>
  <div>
    <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
    <ajaxToolkit:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" MinimumPrefixLength="1"
            ServiceMethod="GetProducts" ServicePath="WebService.asmx" TargetControlID="txtSearch"> </ajaxToolkit:AutoCompleteExtender>
  </div>


<form id="form1" runat="server">


<asp:LinkButton ID="LinkButton5" runat="server" Text="Default"
    PostBackUrl="http://localhost:49724/Default.aspx"></asp:LinkButton>
<br />


and this in the .cs file

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Web.Script.Services;

/// <summary>  
/// Summary description for WebService  
/// </summary>  
[ScriptService]
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class WebService : System.Web.Services.WebService
{
    public WebService(prefixText)
    {
        //Uncomment the following line if using designed components  
        //InitializeComponent();  
    }
    [WebMethod]
    public string[] GetProducts(string prefixText)
    {

        string sql = "Select rut from table Where rut like @prefixText";
        SqlDataAdapter da = new SqlDataAdapter(sql, ConfigurationManager.ConnectionStrings["TestingConnectionString"].ToString());
        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["rut"].ToString(), i);
            i++;
        }
        return items;

    }



}


and it gives me this error when i debug

Mensaje de error del analizador: Etiqueta de servidor 'ajaxToolkit:ToolkitScriptManager' desconocida.

Error de código fuente: 

Línea 7:  <%@ Import Namespace="AjaxControlToolkit" %>
Línea 8:  
Línea 9:  <ajaxToolkit:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server"></ajaxToolkit:ToolkitScriptManager>   
Línea 10:   <div>    
Línea 11:     <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox> 

thanks for your answer


hi there, i still have this error and i did all of the ajax tools stuff

Línea 9:
Línea 10:
Línea 11: <ajaxToolkit:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
Línea 12:   <div>
Línea 13:     <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>

this is my code. 


<%@ Import Namespace="AjaxControlToolkit" %>



<ajaxToolkit:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
  <div>    
    <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>    
    <ajaxToolkit:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" MinimumPrefixLength="1"     
            ServiceMethod="GetProducts" ServicePath="WebService.cs" TargetControlID="txtSearch"> </ajaxToolkit:AutoCompleteExtender>    
  </div> 
</ajaxToolkit:ToolkitScriptManager> 
<form id="form1" runat="server">



i change the code to this and i can see the page now, but the text doesnt show anything.

<%@ Import Namespace="AjaxControlToolkit" %>


<form id="form1" runat="server">


<ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
  <div>
    <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
    <AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" MinimumPrefixLength="1"
            ServiceMethod="GetProducts" ServicePath="WebService.cs" TargetControlID="txtSearch"> </AutoCompleteExtender>
  </div>
</ToolkitScriptManager>



Hello,

please read my first post carefully and see there the servicepath="Webservice.asmx"....put the code inside the webservice.cs file! 

great thanks so much!

Accessing Database from Silverlight

Hi All,

I am working on Silverlight 2 Beta 2. I ma trying to bind the data to a DataGrid from the DB using LINQ. When I search in the net all articles are talking about binding the data using WCF Service only.

Without WCF we cannot bind the Data?

 I want to design a DataGrid like below


___________________________________________________
|               |                |               Number of                               |
|               |                |___________________________________|
|Column1  |Column 2 |SubColumn1|SubColumn2|SubColumn3|
|________|________ |__________|__________|____________|
| 1          |  a          | as            | fg             | gh            |
| 2          |  b          | sd            | fg             | fgh           |
| 3          |  c          | df             | fg             | fgf           |
________________________________________________

Total     | Total of this Col | same | same...

Let me know

Thanks

Anandraj.A.

You have only 2 method to bind grid in the silver light 1). WCF and 2). Web service (asmx).
1. Create Interface for Datagrid(classes, methods)
2. Create User Control as a Label
3. You have to design a datagrid as your wish by the user control or any.
4. Place table-data into the Label-User-Control through WCF

Note: Dropdown also possible by User Control


 Hi Anandraj

it is a little tricky at best to get databinding in silverlight,  take a look at these videos should help. ado.net data services made life a little easier (but not much) seems to be the way to go.but im not sure if it works with beta, any reason you are using beta?

http://www.microsoft.com/uk/msdn/screencasts/screencast/308/adonet-data-services-a-basic-silverlight-client.aspx

Im not sure about doing it with silverlight 2 beta either?, I know the datagrid changed at some point from beta to live (i think anyway) so you may hit a problem with that.

Hi anand,

WCF is the most suitable way to bind the data in Silverlight applications. We can use LINQ TO SQL classes to create the data layer mapped over SQL Server tables. Further by creating Silverlight-enabled-WebService we can access above created LINQ classes to access the data and fetching to Silverlight application.

First create a silverlight application.

Secondly,

Now that you have created the Silverlight 3 Application, we would add a LINQ to SQL file that is *.dbml .

LinqToSQLSilverlight2.gif


Thirdly,

To communicate with LINQ to SQL we need to have a service that client can call at any time.

Add Silverlight enabled WCF Service to the Web Project.

LinqToSQLSilverlight7.gif


In the Service.svc.cs add methods as per your requirement.

I have written two methods such as:


#region [GetAllEmployees]


        [OperationContract]
        List<vw_mst_EmployeeDetail> GetAllEmployees()
        {
            using (EmployeeDBDataContext context = new EmployeeDBDataContext())
            {
                var result = from emp in context.vw_mst_EmployeeDetails
                             select emp;


                return result.ToList();
            }
        }


        #endregion


        #region [GetEmployeesByFirstName]


        [OperationContract]
        List<vw_mst_EmployeeDetail> GetEmployeesByFirstName(string firstName)
        {
            using (EmployeeDBDataContext context = new EmployeeDBDataContext())
            {
                var result = from emp in context.vw_mst_EmployeeDetails
                             where emp.FirstName.StartsWith(firstName)
                             select emp;


                return result.ToList();
            }
        }


        #endregion


Fourthly,

After writing the service build the project and get this service's reference in Silverlight 3 Project as shown in below figure.

LinqToSQLSilverlight10.gif


<div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">        #region [GetAllEmployees]</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"></div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">        [OperationContract]</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">        List<vw_mst_EmployeeDetail> GetAllEmployees()</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">        {</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">            using (EmployeeDBDataContext context = new EmployeeDBDataContext())</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">            {</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">                var result = from emp in context.vw_mst_EmployeeDetails</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">                             select emp;</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"></div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">                return result.ToList();</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">            }</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">        }</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"></div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">        #endregion</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"></div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">        #region [GetEmployeesByFirstName]</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"></div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">        [OperationContract]</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">        List<vw_mst_EmployeeDetail> GetEmployeesByFirstName(string firstName)</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">        {</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">            using (EmployeeDBDataContext context = new EmployeeDBDataContext())</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">            {</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">                var result = from emp in context.vw_mst_EmployeeDetails</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">                             where emp.FirstName.StartsWith(firstName)</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">                             select emp;</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"></div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">                return result.ToList();</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">            }</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">        }</div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"></div> <div style="position: absolute; left: -10000px; top: 926px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">        #endregion</div>

After you add the service it would create the ServiceReferences.ClientConfig file.

Now it's time to design our Silverlight UI in Blend, I have gone for a simple approach, where simple buttons and textbox would help us getting the result which would be displayed in DataGrid.


LinqToSQLSilverlight11.gif


XAML for your reference is as follows - 


<UserControl

    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 

    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

    xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 

    mc:Ignorable="d" xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data" x:Class="Mazeweb.Silverlight.MainPage"

    d:DesignWidth="640" d:DesignHeight="480">

    <Grid x:Name="LayoutRoot">

     <Grid.Background>

     <LinearGradientBrush EndPoint="0.5,1" StartPoint="0.5,0">

     <GradientStop Color="#FFF2F2F8" Offset="0.948"/>

     <GradientStop Color="#FF5B99F2" Offset="0.665"/>

     </LinearGradientBrush>

     </Grid.Background>

     <Grid.RowDefinitions>

     <RowDefinition Height="0.106*"/>

     <RowDefinition Height="0.056*"/>

     <RowDefinition Height="0.838*"/>

     </Grid.RowDefinitions>

        <TextBlock HorizontalAlignment="Center" Text="LINQ TO SQL &amp; SILVERLIGHT 3" 

             TextWrapping="Wrap" FontSize="21.333" TextDecorations="Underline" Margin="0,3,0,19" d:LayoutOverrides="Height"/>

        <StackPanel Margin="0" Height="80" VerticalAlignment="Center" Orientation="Horizontal" 

                    HorizontalAlignment="Center" Grid.Row="1">

            <Button x:Name="btnAll" Height="25" Content="Show All" Click="btnAll_Click" VerticalAlignment="Center" Margin="0,0,0,8" Width="109" />

            <Rectangle Height="25" Width="10"/>

            <TextBox x:Name="txtName" Height="25" TextWrapping="Wrap" Width="164" VerticalAlignment="Center"/>

            <Rectangle Height="25" Width="10"/>

            <Button x:Name="btnSubmit" Height="25" Content="Search" Click="btnSubmit_Click" VerticalAlignment="Center" Margin="0,0,0,8" Width="109" />

        </StackPanel>

        <data:DataGrid x:Name="dgResults" Margin="8,10,8,89" Grid.Row="2" IsReadOnly="True" 

                       AutoGenerateColumns="False">

            <data:DataGrid.Columns>

                <data:DataGridTextColumn Binding="{Binding ID}" Header="ID"></data:DataGridTextColumn>

                <data:DataGridTextColumn Binding="{Binding FirstName}" Header="First Name"></data:DataGridTextColumn>

                <data:DataGridTextColumn Binding="{Binding LastName}" Header="Last Name"></data:DataGridTextColumn>

                <data:DataGridTextColumn Binding="{Binding UserCode}" Header="EmailID"></data:DataGridTextColumn>

                <data:DataGridTextColumn Binding="{Binding DesignationName}" Header="Designation"></data:DataGridTextColumn>

                <data:DataGridTextColumn Binding="{Binding BranchName}" Header="Branch"></data:DataGridTextColumn>

            </data:DataGrid.Columns>

        </data:DataGrid>

    </Grid>

</UserControl>

<div>
</div><div>
</div><div>Finally,</div><div>
</div><div>Now we would write client side code to call the service and display the result.

Following code is for Show All Button Click and displaying the result.
</div><div>
</div><div><div>        private void btnSubmit_Click(object sender, RoutedEventArgs e)</div><div>        {</div><div>            ServiceClient clientService = new ServiceClient();</div><div>            clientService.GetEmployeesByFirstNameCompleted += new EventHandler<GetEmployeesByFirstNameCompletedEventArgs>(clientService_GetEmployeesByFirstNameCompleted);</div><div>            clientService.GetEmployeesByFirstNameAsync(txtName.Text);</div><div>        }</div><div>
</div><div>        void clientService_GetEmployeesByFirstNameCompleted(object sender, GetEmployeesByFirstNameCompletedEventArgs e)</div><div>        {</div><div>            dgResults.ItemsSource = e.Result;</div><div>        }</div><div>
</div><div>        private void btnAll_Click(object sender, RoutedEventArgs e)</div><div>        {</div><div>            ServiceClient clientService = new ServiceClient();</div><div>            clientService.GetAllEmployeesCompleted += new EventHandler<GetAllEmployeesCompletedEventArgs>(clientService_GetAllEmployeesCompleted);</div><div>            clientService.GetAllEmployeesAsync();</div><div>        }</div><div>
</div><div>        void clientService_GetAllEmployeesCompleted(object sender, GetAllEmployeesCompletedEventArgs e)</div><div>        {</div><div>            dgResults.ItemsSource = e.Result;</div><div>        }</div><div>
</div><div>
</div><div>Now, Finally you can test your application successfully running.</div>
</div>


Hope this POST helps.


I wrote an article about connecting a datagrid to a silverlight project here. Perhaps it will be useful.

Hi Dear

You have three option

1: Linq to Sql server

2. WCF Service

3. Web Service

[RESOLVED] execute a method from a different server

I am not sure how to explain this but I will give this a try. Before, we had an app running on windows XP and all the reports were working fine. Now, the app is moved to windows 2008 server. There is one report in this app that uses Microsoft Interop.Excel which is used by component one. Apparently, interop.excel is not supported in windows 2008 with 64 bit running. After doing bunch of research, we have two options - either write this report again or execute this report from XP.


I want to try the second one. The interop.excel works fine in XP. A copy of this app is on this server. What would be the best way to execute this report from windows server 2008? It looks like i would have to connect to the XP server, get access to the app and finally the function that executes the report. Is this possible? 

Hi,

Why not have a webservice running on the XP machine and use that to execute the report? 

So you are suggesting to call this method (housed in XP) via web service from windows 2008 server ,right? Since i don't have a working domain in XP, could i pass in the IP address to connect to the page inside this application.

Yeah,

I'd just host a webservice on the XP machine and use it's IP - to make the call to the webservice. You can do this from a scheduled task on the Server2008 box. As long as the XP machine is capable of hosting a website then it's completely doable.

See here for simple webservices: http://www.developerfusion.com/article/3114/building-xml-web-services-using-c-and-aspnet/3/

Sorry for the late response. This project was on hold, so i couldn't try it. After this came to us again, I did the same thing as you suggested, set up a web service call and the report worked. All i had to do on the XP machine was to disable all the other sites and make the one site i needed to run the report from the default website. That way, i could get into the site using the IP address. Thanks for guiding me in the right direction.

Thanks!! & sorry again for the late response.


No worries, i'm glad you got it sorted.

:) 

Thanks!!

[RESOLVED] How to display (old values) text automatically in text box somthing like MS-Excel cell

Hello,

Is there a way in .net to display the text automatically when i type the first letter or word in a text box?

I am looking for something like MS-Excel.. In MS-Excel, if the first word is given, automatically it displays the remaining texts, only if values are given previously.

Thanks

Nick

 

you may use ajaxcontroltoolkit control (Auto Complete).

e.g: http://www.asp.net/ajax/ajaxcontroltoolkit/samples/autocomplete/autocomplete.aspx


i tried retriving values from the database but i am not getting the expected out

could you please help me?

following i smy code

Public Function Getdata1(ByVal text As String, ByVal count As Integer) As String()
        Dim dt As New DataTable
        Dim sql As String = "SELECT ID FROM dbo.products"
        dt = MPClass.FillTable(sql)


          Dim items As String() = New String(dt.Rows.Count - 1) {}
        Dim i As Integer = 0
        For Each dr As DataRow In dt.Rows
            items.SetValue(dr("ID").ToString(), i)
            i += 1
        Next
        Return items

    End Function



Public Shared Function FillTable(ByVal sql As String) As DataTable
        Dim conn As SqlConnection = MPClass.GetConnection()
        Dim cmd As New SqlCommand("", conn)
        Dim da As New SqlDataAdapter("", conn)
        Dim ds As New DataSet
        da.SelectCommand.CommandText = sql
        da.Fill(ds)
        Return ds.Tables(0)
    End Function



<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:ScriptManager id="ScriptManager1" runat="server">     
        </asp:ScriptManager>
        <asp:AutoCompleteExtender
        runat="server"
        ID="AutoCompleteExtender"
        TargetControlID="TextBox1"
        ServiceMethod="Getdata1"
        MinimumPrefixLength="1"
               >
        </asp:AutoCompleteExtender>

I don't see the ServicePath attribute  for the AutoCompleteExtender.

Here's a nice beginner example

http://www.davidhayden.com/blog/dave/archive/2007/10/22/ASPNETAJAXAutoCompleteExampleUsingAutoCompleteExtenderControl.aspx

Also you can try JQuery AutoComplete....

http://docs.jquery.com/Plugins/autocomplete

And to call WebMethods from JQuery....

http://www.intstrings.com/ramivemula/articles/jquery-post-get-request-to-wcf-service-in-asp-net-mvc/

In the above resoource, you can write your database logic in the WCF service....

Thanks.

I tried the way mentioned in the link above i am not getting the expected output

I have coopied the code from the  below mentioned link and still didnt get the expected op

http://www.aspsnippets.com/Articles/ASP.Net-AJAX-Control-Toolkit-AutoCompleteExtender-without-using-Web-Services.aspx

could you please help me?

My code is given below


<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>

 

 <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

 <asp:ScriptManager id="ScriptManager1" EnablePageMethods = "true" runat="server"> </asp:ScriptManager>

 <cc1:AutoCompleteExtender ID="AutoCompleteExtender" TargetControlID="TextBox1" ServiceMethod="SearchCustomers"
  MinimumPrefixLength="2" CompletionInterval="100" EnableCaching="false"
  CompletionSetCount="10" runat="server" FirstRowSelected = "false" >
</cc1:AutoCompleteExtender>

 

 
Imports System.Data.SqlClient
Imports System.Collections.Generic

 <System.Web.Script.Services.ScriptMethod(), _
    System.Web.Services.WebMethod()> _
    Public Shared Function SearchCustomers(ByVal prefixText As String, ByVal count As Integer) As List(Of String)
        Dim conn As SqlConnection = New SqlConnection
        conn.ConnectionString = ConfigurationManager _
             .ConnectionStrings("DEV_ADD1ConnectionString").ConnectionString
        Dim cmd As SqlCommand = New SqlCommand
        cmd.CommandText = "select ProdID from dbo.produ where" & _
            " ProdID like @SearchText + '%'"
        cmd.Parameters.AddWithValue("@SearchText", prefixText)
        cmd.Connection = conn
        conn.Open()
        Dim customers As List(Of String) = New List(Of String)
        Dim sdr As SqlDataReader = cmd.ExecuteReader
        While sdr.Read
            customers.Add(sdr("ProdID").ToString)
        End While
        conn.Close()
        Return customers
    End Function

conenction string is working fine

pls help me

thanks
nick

Hi nicklibee,

Please check the following aspects:

  • The server code is being hit for theweb service?
  • Your query is being executed andreturning results?
  • Your items array is being populatedcorrectly?

    If "nothing" is that none of the above is happening, I would start checking that there are no javascript errors on the page and that your AutoComplete extender is rendering correctly (examine the page controls in a trace).

  • Please check the following link:

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

    http://www.java2s.com/Tutorial/ASP.NET/0400__Ajax/UsingtheAutoCompleteExtenderwithaWebServiceMethod.htm

    http://stackoverflow.com/questions/730937/ajax-autocompleteextender-isnt-working-web-service-works