AutoCompleteExtender to show an extra item based on if statement RSS

• • Reply
  

  grinnellja

  Participant

  1221 Points

  624 Posts

  AutoCompleteExtender to show an extra item based on if statement

  Feb 24, 2012 08:26 PM|LINK

  Is it possible to show results of a search with an extra item showing if it falls in my requirements?

  I have an autocomplete extender that works fine, but I have a field in the database that only has a value in it if it is a certain type of product.

  Based on that value, I would like to show the Date of the product in the dropdown of the autocompleteitem.

  This is what I have so far.

  <WebMethod()> _
      Public Function GetProducts(ByVal prefixText As String, ByVal count As Integer) As String()
          Dim ProductSql As String = "Select DISTINCT ProductID, ProductName, Presenter FROM Product WHERE ProductName LIKE '%' + @prefixText + '%' ORDER BY ProductName ASC"
          Using sqlConn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString)
              sqlConn.Open()
              Dim myCommand As New SqlCommand(ProductSql, sqlConn)
              myCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText
              Dim myReader As SqlDataReader = myCommand.ExecuteReader()
              Dim myTable As New DataTable
              myTable.TableName = "ProductSearch"
              myTable.Load(myReader)
              sqlConn.Close()
              Dim items As String() = New String(myTable.Rows.Count - 1) {}
              Dim i As Integer = 0
              For Each dr As DataRow In myTable.Rows
                  Dim presenter As String = dr("Presenter").ToString
                  If presenter IsNot " " Or "NULL" Then
                      Dim launchdate As String = dr("LaunchDate").ToString
                  End If
                  Dim id As String = dr("ProductID").ToString()
                  Dim name As String = dr("ProductName").ToString()
                  Dim item As String = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(name, id, launchdate) <---launchdate is underlined because it is not defined....but it won't let me use FindControl here.
                  items.SetValue(item, i)
                  i += 1
              Next
              Return items
          End Using
      End Function

  autocompleteextender

• • Reply
  

  chetan.sarod...

  All-Star

  65839 Points

  11163 Posts

  Re: AutoCompleteExtender to show an extra item based on if statement

  Feb 27, 2012 02:35 AM|LINK

  Hi, Please refer this

  http://vincexu.blogspot.in/2009/01/custom-autocomplete-6-multicolumn.html

  http://thecableguysblog.blogspot.in/2010/02/ajaxget-keyvalue-from-autocomplete-with.html

  Chetan Sarode
  Senior Software Engineer,
  Approva Systems Pvt Ltd, Pune, India.
  
• • Reply
  

  grinnellja

  Participant

  1221 Points

  624 Posts

  Re: AutoCompleteExtender to show an extra item based on if statement

  Feb 27, 2012 09:22 PM|LINK

  I'm sorry but I really don't understand either one of those links. They have their Autocompleteextenders set up differently than the one I am using. I didn't write the code for it but was asked to change it a little. I just want to know how to update it to my current needs without having to change the entire code of the WebService. Thanks.

• • Reply
  

  BU XI - MSFT

  All-Star

  22367 Points

  2704 Posts

  Microsoft

  Re: AutoCompleteExtender to show an extra item based on if statement

  Feb 28, 2012 03:27 AM|LINK

  Hello

  For items variable, make an loop to check all values. Use the if statement to find your required item, and make an string concatenation to append the data string. If you'd want to add more items, just append these new items to items array.

  Please mark the replies as answers if they help or unmark if not.
  If you have any feedback about my replies, please contact msdnmg@microsoft.com.
  Microsoft One Code Framework
  • Edited by BU XI - MSFT on Feb 28, 2012 03:29 AM
• • Reply
  

  grinnellja

  Participant

  1221 Points

  624 Posts

  Re: AutoCompleteExtender to show an extra item based on if statement

  Feb 28, 2012 03:54 PM|LINK

  I have a for loop in my code if you look at it above. I was just hoping that you might be able to shed some light on it. If I try to add the "LaunchDate" to the AutoCompleteExtender I get an error saying that it is not declared. I don't know how to take the value from the for loop outside of it to use in the AutoCompleteExtender.

  For Each dr As DataRow In myTable.Rows
                  Dim presenter As String = dr("Presenter").ToString
                  If presenter IsNot " " Or "NULL" Then
                      Dim launchdate As String = dr("LaunchDate").ToString
                  End If
                  Dim id As String = dr("ProductID").ToString()
                  Dim name As String = dr("ProductName").ToString()
                  Dim item As String =
  AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(name, id, launchdate)
  <---launchdate is underlined because it is not defined,
  but it won't let me use FindControl here.--->
                  items.SetValue(item, i)
                  i += 1
              Next

• • Reply
  

  grinnellja

  Participant

  1221 Points

  624 Posts

  Re: AutoCompleteExtender to show an extra item based on if statement

  Mar 01, 2012 02:49 PM|LINK

  I am trying to add launchdate to the CreateAutoCompleteItem line and I get an error saying 'Too many arguments to Public Shared Function CreateAutoCompleteItem(text As String, value As String) As String.'

  I have no idea where this function is so I can change it. The code for the AutoCompleteItem is in a WebMethod.

  Public Function GetProducts(ByVal prefixText As String, ByVal count As Integer) As String()
          Dim ProductSql As String = "Select DISTINCT ProductID, ProductName, LaunchDate
          FROM Product
          WHERE ProductName LIKE '%' + @prefixText + '%'
          AND LaunchDate IS NOT NULL
          ORDER BY ProductName ASC"
          Using sqlConn As New SqlConnection
  (System.Configuration.ConfigurationManager.ConnectionStrings
  ("LocalSqlServer").ConnectionString)
              sqlConn.Open()
              Dim myCommand As New SqlCommand(ProductSql, sqlConn)
              myCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText
              Dim myReader As SqlDataReader = myCommand.ExecuteReader()
              Dim myTable As New DataTable
              myTable.TableName = "ProductSearch"
              myTable.Load(myReader)
              sqlConn.Close()
              Dim items As String() = New String(myTable.Rows.Count - 1) {}
              Dim i As Integer = 0
              For Each dr As DataRow In myTable.Rows
                  Dim id As String = dr("ProductID").ToString()
                  Dim name As String = dr("ProductName").ToString()
                  Dim launchdate As String = dr("LaunchDate").ToString()
                  Dim item As String =
  AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(name, id, launchdate)
                  items.SetValue(item, i)
                  i += 1
              Next
              Return items
          End Using
      End Function

  • Edited by grinnellja on Mar 01, 2012 02:49 PM
  • Edited by grinnellja on Mar 01, 2012 04:08 PM
• • Reply
  

  grinnellja

  Participant

  1221 Points

  624 Posts

  Re: AutoCompleteExtender to show an extra item based on if statement

  Mar 01, 2012 04:48 PM|LINK

  This works, after I figure out how to format the datetime from the database into showing just the date, it will be perfect. :)

  Public Function GetProducts(ByVal prefixText As String, ByVal count As Integer) As String()
          Dim ProductSql As String = "Select DISTINCT ProductID, ProductName, LaunchDate FROM Product WHERE ProductName LIKE '%' + @prefixText + '%' AND LaunchDate IS NOT NULL ORDER BY ProductName ASC"
          Using sqlConn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString)
              sqlConn.Open()
              Dim myCommand As New SqlCommand(ProductSql, sqlConn)
              myCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText
              Dim myReader As SqlDataReader = myCommand.ExecuteReader()
              Dim myTable As New DataTable
              myTable.TableName = "ProductSearch"
              myTable.Load(myReader)
              sqlConn.Close()
              Dim items As String() = New String(myTable.Rows.Count - 1) {}
              Dim i As Integer = 0
              For Each dr As DataRow In myTable.Rows
                  Dim id As String = dr("ProductID").ToString()
                  Dim name As String = dr("ProductName").ToString()
                  Dim launchdate As String = dr("LaunchDate").ToString()
                  Dim item As String = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(name & " " & launchdate, id)
                  items.SetValue(item, i)
                  i += 1
              Next
              Return items
          End Using
      End Function

  • Marked as answer by grinnellja on Mar 01, 2012 04:48 PM