title: Jquery Auto Complete + Asp.net / c# / SQL not working
body: <p>Not sure if this is a 'too many working parts' type situation but I have searched the net high and low and cant solve my issue.</p>
  
  <p>I've got to a point now that it my text box that attempts to 'auto complete' tries but gives an error message (However when the code is ran separately in 'test application' it works fine) however the error message should says 'Error', so I wondered if you guys / gals here maybe able to help.</p>
  
  <p>Also suggestions on good learning / training sites to learn Jquery / SQL / Asp.net would be a great help...</p>
  
  <p>I did try using AJAX auto complete but it failed to work so switched to JQuery (not sure if this was a good idea or not) so my code can be found below:</p>
  
  <p>Webform2.aspx </p>
  
  <pre><code>&lt;%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="SicknessDBNewSite.WebForm2" %&gt;
  &lt;%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %&gt;
  &lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;
  
  
  
  
  &lt;html xmlns="http://www.w3.org/1999/xhtml"&gt;
  &lt;head id="Head1" runat="server"&gt;
      &lt;link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/&gt;
      &lt;script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"&gt;&lt;/script&gt;
      &lt;script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js"&gt;&lt;/script&gt;  
      &lt;script type="text/javascript"&gt;
          $(document).ready(function () {
              SearchText();
          });
          function SearchText() {
              $(".autosuggest").autocomplete({
                  source: function (request, response) {
                      $.ajax({
                          type: "POST",
                          contentType: "application/json; charset=utf-8",
                          url: "Default.aspx/GetAutoCompleteData",
                          data: "{'username':'" + document.getElementById('txtSearch').value + "'}",
                          dataType: "json",
                          success: function (data) {
                              response(data.d);
                          },
                          error: function (result) {
                              alert("Error");
                          }
                      });
                  }
              });
          }
      &lt;/script&gt;
  
      &lt;title&gt;&lt;/title&gt;
      &lt;style type="text/css"&gt;
          .style1
          {
              width: 72%;
          }
          .style2
          {
              width: 443px;
          }
          .style3
          {
              text-align: center;
              text-decoration: underline;
          }
          .style4
          {
              width: 443px;
              height: 65px;
          }
          .style5
          {
              height: 65px;
          }
          .style6
          {
              text-align: center;
          }
          .style7
          {
              width: 443px;
              height: 59px;
          }
          .style8
          {
              height: 59px;
          }
          #txtSearch
          {
              width: 500px;
          }
      &lt;/style&gt;
  &lt;/head&gt;
  &lt;body&gt;
  
  
      &lt;form id="form1" runat="server"&gt;
      &lt;div style="text-align: center"&gt;
          &lt;h1 class="style3"&gt;
              Test Form Submission
          &lt;/h1&gt;
  
          &lt;table class="style1" align="center"&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      Name&lt;/td&gt;
                  &lt;td&gt;
  
                      &lt;input type="text" id="txtSearch" class="autosuggest" /&gt;
  
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      Grade&lt;/td&gt;
                  &lt;td&gt;
                      &lt;asp:TextBox ID="txtgrade" runat="server" Width="500px"&gt;&lt;/asp:TextBox&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      Depot&lt;/td&gt;
                  &lt;td&gt;
                      &lt;asp:TextBox ID="txtdepot" runat="server" Width="500px"&gt;&lt;/asp:TextBox&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      Nature of Illness?&lt;/td&gt;
                  &lt;td&gt;
                      &lt;asp:TextBox ID="txtillness" runat="server" Width="500px"&gt;&lt;/asp:TextBox&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      Appointment made to see doctor (Yes or No)?&lt;/td&gt;
                  &lt;td&gt;
                      &lt;asp:RadioButton ID="RadioAppointmentYes" runat="server" Text="Yes" GroupName="Appointment" /&gt;
                      &lt;asp:RadioButton ID="RadioAppointmentNo" runat="server" Text="No" GroupName="Appointment" /&gt;
                      &lt;asp:TextBox ID="txtAppointment" runat="server" Visible="False"&gt;&lt;/asp:TextBox&gt;
  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      Medication Taken (Yes or No)?&lt;/td&gt;
                  &lt;td class="style6"&gt;
                      &lt;asp:RadioButton ID="RadioMedicationYes" runat="server" Text="Yes" GroupName="Medication" /&gt;
                      &lt;asp:RadioButton ID="RadioMedicationNo" runat="server" Text="No" GroupName="Medication" /&gt;
                      &lt;asp:TextBox ID="txtMedication" runat="server" Visible="False"&gt;&lt;/asp:TextBox&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      Expected length of absence?&lt;/td&gt;
                  &lt;td&gt;
                      &lt;asp:TextBox ID="txtabsence" runat="server" Width="500px"&gt;&lt;/asp:TextBox&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      If not contacted by staff member, who contacted on their behalf?&lt;/td&gt;
                  &lt;td&gt;
                      &lt;asp:TextBox ID="txtcontactby" runat="server" Width="500px"&gt;&lt;/asp:TextBox&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      Likely resumption date?&lt;/td&gt;
                  &lt;td&gt;
                      &lt;asp:TextBox ID="txtresumedate" runat="server" Width="500px"&gt;&lt;/asp:TextBox&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      Contact telephone number&lt;/td&gt;
                  &lt;td&gt;
                      &lt;asp:TextBox ID="txtcontactnum" runat="server" Width="500px"&gt;&lt;/asp:TextBox&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      Latest update date&lt;/td&gt;
                  &lt;td&gt;
                      &lt;asp:TextBox ID="txtupdate" runat="server" Width="500px"&gt;&lt;/asp:TextBox&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style4"&gt;
                      Details&lt;/td&gt;
                  &lt;td class="style5"&gt;
                      &lt;asp:TextBox ID="txtdetails" TextMode="MultiLine" runat="server" Width="500px" 
                          Height="50px" style="text-align: right"&gt;&lt;/asp:TextBox&gt;
                      &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      Resumption Date&lt;/td&gt;
                  &lt;td&gt;
                      &lt;asp:TextBox ID="txtresumeddate" runat="server" Width="500px"&gt;&lt;/asp:TextBox&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style7"&gt;
                      Detail&lt;/td&gt;
                  &lt;td class="style8"&gt;
                      &lt;asp:TextBox ID="txtdetail" TextMode="MultiLine" runat="server" Width="500px" Height="50px"&gt;&lt;/asp:TextBox&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      Is the person still on medication at the time of resumption?&lt;/td&gt;
                  &lt;td&gt;
                      &lt;asp:TextBox ID="txtmedonreturn" runat="server" Width="500px"&gt;&lt;/asp:TextBox&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      Controller / Planner&amp;#39;s name&lt;/td&gt;
                  &lt;td&gt;
                      &lt;asp:TextBox ID="txtreporter" runat="server" Width="500px"&gt;&lt;/asp:TextBox&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      Date of contact&lt;/td&gt;
                  &lt;td&gt;
                      &lt;asp:TextBox ID="txtdateofcontact" runat="server" Width="500px"&gt;&lt;/asp:TextBox&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      Date and time of next shift&lt;/td&gt;
                  &lt;td&gt;
                      &lt;asp:TextBox ID="txtdatetimeshift" runat="server" Width="500px"&gt;&lt;/asp:TextBox&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td class="style2"&gt;
                      &amp;nbsp;&lt;/td&gt;
                  &lt;td&gt;
                      &lt;asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Submit" Width="100px" /&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;/table&gt;
                  &lt;/div&gt;
      &lt;a href="http://skynet/webform1.aspx"&gt;
       &lt;asp:Label ID="Label1" runat="server" ForeColor="Green" Visible="False" /&gt;&lt;/asp:Label&gt;
       &lt;/a&gt;
      &lt;asp:SqlDataSource ID="SqlDataSource1" runat="server" 
          ConnectionString="&lt;%$ ConnectionStrings:ConnectionStringTestFormSubmission %&gt;" 
  
          SelectCommand="SELECT id, Name, Grade, Depot FROM Crewnames WHERE Name LIKE %da%"&gt;&lt;/asp:SqlDataSource&gt;
      &lt;asp:SqlDataSource ID="SqlDataSource2" runat="server" 
          ConnectionString="&lt;%$ ConnectionStrings:CrewNameString %&gt;" 
          SelectCommand="SELECT * FROM [Crewnames]"&gt;&lt;/asp:SqlDataSource&gt;
      &lt;/form&gt;
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>Code Behind:</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Web;
  using System.Web.UI;
  using System.Web.UI.WebControls;
  using System.Data.SqlClient;
  using System.Configuration;
  using System.Web.Services;
  using System.Web.Script.Services;
  
  namespace SicknessDBNewSite
  {
      public partial class WebForm2 : System.Web.UI.Page
      {
          SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringTestFormSubmission"].ConnectionString);
  
          protected void Page_Load(object sender, EventArgs e)
          {
              con.Open();
              txtdateofcontact.Text = DateTime.Now.ToShortDateString();
              txtresumedate.Text = DateTime.Today.AddDays(+1).ToShortDateString();
          }
  
          [WebMethod]
  
          public static List&lt;string&gt; GetAutoCompleteData(string username)
          {
              List&lt;string&gt; result = new List&lt;string&gt;();
              using (SqlConnection con = new SqlConnection("Data Source=G00069-PC\\SQLEXPRESS;Initial Catalog=SicknessDatabase;Persist Security Info=True;User ID=USERNAME;Password=PASSWORD"))
              {
                  using (SqlCommand cmd = new SqlCommand("SELECT * FROM [Crewnames] WHERE Name LIKE '%'+@SearchText+'%'", con))
                  {
                      con.Open();
                      cmd.Parameters.AddWithValue("@SearchText", username);
                      SqlDataReader dr = cmd.ExecuteReader();
                      while (dr.Read())
                      {
                          result.Add(dr["Name"].ToString());
                      }
                      return result;
                  }
              }
          }
          protected void Button1_Click(object sender, EventArgs e)
          {
  
              if (RadioAppointmentYes.Checked)
              {
                  txtAppointment.Text = "Yes";
              }
              else if (RadioAppointmentNo.Checked)
              {
                  txtAppointment.Text = "No";
              }
  
              if (RadioMedicationYes.Checked)
              {
                  txtMedication.Text = "Yes";
              }
              else if (RadioMedicationNo.Checked)
              {
                  txtMedication.Text = "No";
              }
              SqlCommand cmd = new SqlCommand("insert into sicknesstest values('"+txtname.Text+"','"+txtgrade.Text+"','"+txtdepot.Text+"','"+txtillness.Text+"','"+txtAppointment.Text+"','"+txtMedication.Text+"','"+txtabsence.Text+"','"+txtcontactby.Text+"','"+txtupdate.Text+"','"+txtdetails.Text+"','"+txtresumedate.Text+"','"+txtdetail.Text+"','"+txtmedonreturn.Text+"','"+txtreporter.Text+"','"+txtdateofcontact.Text+"','"+txtresumeddate.Text+"')", con);
              cmd.ExecuteNonQuery();
              con.Close();
              Label1.Visible = true;
              Label1.Text = "Your Data has been stored successfully! Click here to view";
              txtname.Text = "";
              txtgrade.Text = "";
              RadioAppointmentYes.Checked = false;
              RadioAppointmentNo.Checked = false;
              RadioMedicationYes.Checked = false;
              RadioMedicationNo.Checked = false;
              txtAppointment.Text = "";
              txtMedication.Text = "";
              txtdepot.Text = "";
              txtillness.Text = "";
              txtabsence.Text = "";
              txtcontactnum.Text = "";
              txtcontactby.Text = "";
              txtupdate.Text = "";
              txtdetails.Text = "";
              txtresumedate.Text = "";
              txtdetail.Text = "";
              txtmedonreturn.Text = "";
              txtreporter.Text = "";
              txtdateofcontact.Text = "";
              txtresumeddate.Text = "";
              txtdatetimeshift.Text = "";
          }
  
  
      }
  
  }       
  </code></pre>
  
  <p>`
  Web Config</p>
  
  <pre><code>`&lt;?xml version="1.0"?&gt;
  &lt;!--
    For more information on how to configure your ASP.NET application, please visit
    http://go.microsoft.com/fwlink/?LinkId=169433
    --&gt;
  &lt;configuration&gt;
  
  
    &lt;connectionStrings&gt;
      &lt;add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
        providerName="System.Data.SqlClient" /&gt;
      &lt;add name="SicknessDatabaseConnectionString" connectionString="Data Source=G00069-PC\sqlexpress;Initial Catalog=SicknessDatabase;Persist Security Info=True;User ID=USERNAME;Password=PASSWORD"
        providerName="System.Data.SqlClient" /&gt;
      &lt;add name="ConnectionStringTestFormSubmission" connectionString="Data Source=G00069-PC\SQLEXPRESS;Initial Catalog=SicknessDatabase;Persist Security Info=True;User ID=USERNAME;Password=PASSWORD"
        providerName="System.Data.SqlClient" /&gt;
      &lt;add name="ProductionConnectionStringNames" connectionString="Data Source=saturn\gbrfdev;Initial Catalog=Production;Integrated Security=True"
        providerName="System.Data.SqlClient" /&gt;
      &lt;add name="ProductionEntitiesNames" connectionString="metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl;provider=System.Data.SqlClient;provider connection string=&amp;quot;data source=saturn\gbrfdev;initial catalog=Production;integrated security=True;multipleactiveresultsets=True;App=EntityFramework&amp;quot;"
  
      &lt;authentication mode="Forms"&gt;
        &lt;forms loginUrl="~/Account/Login.aspx" timeout="2880" /&gt;
      &lt;/authentication&gt;
      &lt;membership&gt;
        &lt;providers&gt;
          &lt;clear /&gt;
          &lt;add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" /&gt;
        &lt;/providers&gt;
      &lt;/membership&gt;
      &lt;profile&gt;
        &lt;providers&gt;
          &lt;clear /&gt;
          &lt;add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/" /&gt;
        &lt;/providers&gt;
      &lt;/profile&gt;
      &lt;roleManager enabled="false"&gt;
        &lt;providers&gt;
          &lt;clear /&gt;
          &lt;add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="ApplicationServices" applicationName="/" /&gt;
          &lt;add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/" /&gt;
        &lt;/providers&gt;
      &lt;/roleManager&gt;
      &lt;pages&gt;
          &lt;controls&gt;
          &lt;add tagPrefix="ajaxToolkit" assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" /&gt;
        &lt;/controls&gt;
      &lt;/pages&gt;
    &lt;/system.web&gt;
    &lt;system.webServer&gt;
      &lt;modules runAllManagedModulesForAllRequests="true" /&gt;
    &lt;/system.webServer&gt;
  &lt;/configuration&gt;`  
  </code></pre>