Freetext Box + SQL

Jan 24, 2005

I have a vb page, which is a simple front end so I can edit text from anywhere.





The person who is going to use it knows no html, and so I am trying to get freetextbox to work.





I am getting the following error:


Server Error in '/' Application.


--------------------------------------------------------------------------------





ExecuteNonQuery: CommandText property has not been initialized


Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.





Exception Details: System.InvalidOperationException: ExecuteNonQuery: CommandText property has not been initialized





Source Error:








Line 81:


Line 82: mySelectCmd.Connection.Open()


Line 83: mySelectCmd.ExecuteNonQuery()


Line 84: MyConnString.Close()


Line 85:





The pages code is below:





<%@ Page Language="VB" Debug="True" validateRequest="False"%>


<%@ Register TagPrefix="FTB" Namespace="FreeTextBoxControls" Assembly="FreeTextBox" %>


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


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


<script runat="server">


Dim MyConnString As SqlConnection


Dim mySelectCmd As SqlCommand


Dim mySelectQuery As String


dim myExecuteQuery As String





Sub Page_Load(Sender As Object, E As EventArgs)





If Not Page.IsPostBack Then


ReadMyData()


end if


end sub





Public Sub ReadMyData()


MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")





Dim dt As DataTable


Dim dr As DataRow


dt = New DataTable





dt.Columns.Add(New DataColumn("id", GetType(Integer)))


dt.Columns.Add(New DataColumn("title", GetType(String)))


dt.Columns.Add(New DataColumn("text", GetType(String)))


dt.Columns.Add(New DataColumn("section_no", GetType(String)))





mySelectQuery = "select * from content_text"


mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)


MyConnString.Open()


Dim myReader As SqlDataReader = mySelectCmd.ExecuteReader()


Try


While myReader.Read()





dr = dt.NewRow()





dr(0) = myReader("id")


dr(1) = myReader("title")


dr(2) = myReader("text")


dr(3) = myReader("section_no")





dt.Rows.Add(dr)





End While


Finally


myReader.Close()


MyConnString.Close()


End Try





Pubs.DataSource = DT


Pubs.Databind()





End Sub





Sub Pubs_Cancel(Sender As Object, E As DataGridCommandEventArgs)


Pubs.EditItemIndex = -1


ReadMyData()


End Sub





Sub Pubs_Edit(Sender As Object, E As DataGridCommandEventArgs)


Pubs.EditItemIndex = CInt(e.Item.ItemIndex)


ReadMyData()


End Sub





Sub Pubs_Update(Sender As Object, E As DataGridCommandEventArgs)





MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")








myExecuteQuery= "Update content_text Set title=@title, text=@text, section_no=@section_no where id=@id"


mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)





dim e_id as integer


dim e_title, e_text, e_section_no as string





e_Id = Pubs.DataKeys(CInt(E.Item.ItemIndex))


e_title = CType(e.Item.FindControl("e_title"), TextBox).Text


e_text = CType(e.Item.FindControl("e_text"), FreeTextBox).Text


e_section_no = CType(e.Item.FindControl("e_section_no"), TextBox).Text





mySelectCmd.Connection.Open()


mySelectCmd.ExecuteNonQuery()


MyConnString.Close()





pubs.EditItemIndex = -1





ReadMyData()





End Sub


</script>


<html>


<head>


<title>Untitled Document</title>





<style type="text/css">


<!--


.style1 {font-family: Arial, Helvetica, sans-serif; font-size:8pt}


-->


</style>


</head>


<body>


<form runat="server">


<asp:datagrid


id="Pubs"


GridLines="Both"


CssClass="style1"


DataKeyField="id"


Border="0"


CellPadding="2"


font-name="Arial"


font-size="9pt"


OnEditCommand="Pubs_Edit"


OnUpdateCommand="Pubs_Update"


OnCancelCommand="Pubs_Cancel"


Autogeneratecolumns="false"


Showfooter="true"


HeaderStyle-Font-Name="Arial"


HeaderStyle-Font-Size="8pt"


HeaderStyle-BackColor="#B7CEDF"


Font-Bold="True"


ItemStyle-Font-Name="Arial"


ItemStyle-Font-Size="8pt"


ItemStyle-VerticalAlign="top"


runat="server">


<columns>


<asp:templateColumn>


<HeaderTemplate>


<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">


<tr>


<td width="5%" align="left">Word</td>


<td width="15%" align="left">Title</td>


<td width="70%" align="left">Text</td>


<td width="10%" align="left">Section Number</td>


</tr>


</table>


</HeaderTemplate>


<ItemTemplate>


<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">


<tr>


<td width="5%" align="left"><asp:LinkButton ToolTip="Delete record" CommandName="Delete" runat="server"><img src="images/deleteicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Edit record" CommandName="Edit" runat="server"><img src="images/editicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>


<td width="15%" align="left"><asp:Label ID="title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>


<td width="70%" align="left"><asp:Label ID="text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="server" /></td>


<td width="10%" align="left"><asp:Label ID="section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>


</tr>


</table>


</ItemTemplate>


<EditItemTemplate>


<table width ="100%" border="0" cellpadding="2" cellspacing="2" class="style1">


<tr>


<td width="5%"><asp:LinkButton ToolTip="Cancel edit record" CommandName="Cancel" runat="server"><img src="images/cancelicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Update record" CommandName="Update" runat="server"><img src="images/saveicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>


<td width="20%" align="left"><asp:TextBox Width=200 CssClass="style1" ID="e_title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>


<td width="70%" align="left"><FTB:FreeTextBox ToolbarStyleConfiguration="Office2000" id="e_text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="Server" /></td>


<td width="5%" align="left"><asp:TextBox Width=50 CssClass="style1" ID="e_section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>


</tr>


</table>


</EditItemTemplate></asp:templateColumn>


</columns>


</asp:datagrid>


</form>


</body>


</html>

View 5 Replies


ADVERTISEMENT

Freetext

Nov 5, 2007

 I don't know whether this is the right place to ask this question. I didn't get the answer from sql forums may be someone here can help. I need to use freetext on two columnssomething likefreetext(column1+column2,@Search)  this will give error "Incorrect syntax near '+'."Any One know the correct syntax. Thanks 

View 1 Replies View Related

Cannot Use A CONTAINS Or FREETEXT...

Jul 9, 2007

Hello all. I'm new to this forum and of course I found it because I have a problem I can't find the answer to.

I have full-text indexing on a couple of tables in my database. Periodically, maybe once every couple of weeks, I get an error on a webpage that runs a stored procedure searching these tables. The error is as follows:

"Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Products' because it is not full-text indexed."

I'm running Classic ASP, and the stored procedures run thousands of times a day. As soon as I get the error email from the website containing the url that created the error, I immediately click on it, but by that time, the error has stopped and everything is running properly.

The timing of the error does not coorespond to any other processes. My transactionals run on the hour and the index is re-built at 12:45am, yet I just received the error again at 12:45pm (no, I checked the clocks and they are right).

I do not have replication running and I don't know where to look next.

Server:
SQL 2005 sp1
Windows 2003 Server

Any ideas would be greatly appreciated.

Russ

View 2 Replies View Related

Cannot Use A Contains On Freetext

Nov 4, 2005

I tried the following select:
select * from patientprofile where contains (BillingNotes, '572576-02')

And got the errormessage:
Server: Msg 7601, Level 16, State 2, Line 1
Cannot use a CONTAINS or FREETEXT predicate on table 'patientprofile' because it is not full-text indexed.

It shows BillingNotes as a text column

Is there a differnet way I can search this note field for records that contain that text value (572576-02)

View 3 Replies View Related

Freetext

Nov 5, 2007

Is there a way I could achieve something like this.
freetext(FirstName+LastName,@Name) FirstName and LastName are two columns in my database table.
What I am trying here is allowing the user to type their first name , last name or both. and write a search which can find there record for all the three cases.
Please Advice.

View 1 Replies View Related

Using FREETEXT With A Parameter

Aug 7, 2007

I'm attempting to query the MS Index Server.  I allow the users to supply the search text and when doing that it is best to pass the text into the query via a parameter to protect against SQL Injection attacks.  However, for some reason I cannot get the FREETEXT predicate to work with a parameter.  The FREETEXT predicate works fine when the query is concatenated together.  I'd like an example of the FREETEXT predicate working with MS Index Server (Windows 2003).
 The following line works just fine: cmd.CommandText = "select doctitle, filename, vpath, rank, characterization from scope() where FREETEXT(Contents, '" + searchText + "') order by rank desc "
I'd like the following lines to work:cmd.Parameters.AddWithValue("@SearchText", searchText)
cmd.CommandText = "select doctitle, filename, vpath, rank, characterization from scope() where FREETEXT(Contents, @SearchText) order by rank desc "

So now I'm wondering if the MS Index Server doesn't support the use of a parameter in this way or if I somehow managed to get the syntax wrong.
Thoughts?

View 2 Replies View Related

Freetext In A Stored Procedure

Feb 22, 2000

I've been running Freetexts on ASP through IIS. The problem with this is that the CPU usage is very big.

I've wanted to use Stored Procedures on SQL 7.0 to produce results out of Freetext. But i've been having problems. Please help:

CREATE PROCEDURE sp_match
@asp_str varchar(50)
as
select * from employees_table where
freetext(*, @asp_str )
return

View 1 Replies View Related

LIKE Pattern Searching Using CONTAINS Or FREETEXT

May 15, 2007

Hi,



We are using FULLTEXT searching for searching the title column in SQL 2005 table.

We are aware that using CONTAINS we cannot search middle of the string like the way we do using LIKE '%text%'.



Is there any alternative way of acheiving this functionality using FULLTEXT search or any other ways of doing this keeping in mind performance.



Any feedback on this is greatly appreciated.



thanks and regards,

Krishna.

View 3 Replies View Related

Freetext / Freetexttable On Multiple Tables

Aug 23, 2007

Hi,
I realised that I am not able to do a FREETEXT search on multiple table, example:SELECT * FROM [tStaffDir], [tStaffDir_ClientExp], [tStaffDir_CoreSpecs], [tStaffDir_GlobalExp], [tStaffDir_Lang], [tStaffDir_PrevEmp], [tStaffDir_TerEdu] WHERE FREETEXT(*, @Name) ORDER BY [Name]
Can I use FREETEXTTABLE instead? How do I go about doing it?

View 2 Replies View Related

URGENT HELP PLS + FREETEXT + SQL SERVER 2005

Dec 10, 2007



Hi

I was looking at this link : http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=792720&SiteID=17&pageid=0

I am using SQL SERVER 2005.

I have noiseeng.txt and noiseenu.txt.

I have stopped all the MSSQLSERVER, MSSQLAGENT,BROWSER, EXPRESS and FULL SEARCH.

Then I removed all the numbers from the noiseeng.txt and noiseenu.txt.

I restarted the services again.

I tried to run this query


DECLARE @SearchWord nvarchar(30);

SET @SearchWord = 'MS';

SELECT ProductDescription

FROM Products

WHERE FREETEXT(ProductDescription, @SearchWord);

I can't seem to get any records out and it won't pick the two that I am actually looking for.

In the Products table, it has several rows containing:
MS1234
MS_1234
MS-1234
MS 1234
MS/1234


I am trying to retrieve the above records using just 'MS', but it just doesn't work at all.

Any suggestions, help or examples please - would be most appreciated.

Thanks

Newbie

View 4 Replies View Related

FREETEXT And CONTAINS Keywords Generate `not A Recognized Function Name`

Sep 14, 1998

I installed SQL server 7.0 beta 3 and I have been experimenting with the full text search capabilities. The catalogs appear to build correctly, but when I issue a query against the full text database, isql/w always returns with `contains` is not a recognized function name. I am confident of my query syntax, since I can execute an identical query against a database without full text search enabled and I get an error stating that the target database does not have full text searching enabled. Has anybody else encountered this? I am hoping that I am missing something obvious/simple.

View 2 Replies View Related

Sort Results By Rank Using FREETEXT On Multiple Columns

Apr 20, 2008

Hi,I am using MS SQL server 2005 and wondering how to sort my results by rank using FREETEXT on multiple columns.  Is there a way to do this? My two colums are:title and description
 
can anyone give any code snippets?

View 1 Replies View Related

FREETEXT FILTER ON FREETEXTTABLE USING COLUMN LIST FOR REFINED SEARCH

Sep 12, 2007

I am trying to do a freetext filter with mutiple columns using a column list, but I can't get the syntax down for multiple column list. First, am I am going about this the right way...Do I need to be doing both? Second why doesn't mutiple columns work. I can't find any good samples online. What I am trying to accomplish is a refined search stored procedure that uses the freetext to do the search refinement. Any help would be appreciated.

select

b.rank,

a.ProductID,

a.ProductName,

a.Sequence,

a.ProductImage,

a.ItemID,

a.ItemName,

a.ManufacturerItemCode,

a.ItemImage,

a.ItemSourceID,

a.PackageID,

a.BrandID,

a.BrandName,

a.ManufacturerID,

a.ManufacturerName,

a.ProductCategoryID,

a.CategoryID,

a.CategoryName,

d.CustomerGroupName,

isnull(h.PackageDescription,a.ItemPKG) as PKG,

case g.StockStatus

when 1 then 'Yes'

when 0 then 'No'

else ''

end as StockStatus,

isnull(g.StandardUnitPrice,a.ListPrice) as Price,

isnull(j.SupplierAbbreviation,a.ManufacturerAbbreviation) as ItemSource

from

dbo.vw_mcProductItem a

inner join freetexttable(dbo.vw_mcProductItem, (ProductName,ItemName,ManufacturerItemCode,ItemPKG,BrandName,ManufacturerName,ManufacturerAbbreviation,CategoryName), @SearchWord) as b ON a.ItemID = b.[KEY]

inner join [dbo].[mcCustomerGroupItem] c on c.ItemID = a.ItemID

inner join [dbo].[mcCustomerGroup] d on d.CustomerGroupID = c.CustomerGroupID

inner join [dbo].[mcCustomerGroupCustomer] e on e.CustomerGroupID = d.CustomerGroupID

inner join [dbo].[mcCustomerUser] f on f.CustomerID = e.CustomerID

left outer join [dbo].[mcSupplierItem] g on g.ItemID = a.ItemID

left outer join [dbo].[mcPackage] h on h.PackageID = g.SellingPackageID

left outer join [dbo].[mcItemSource] i on i.ItemSourceId = a.ItemSourceId

left outer join [dbo].[mcSupplier] j on j.SupplierID = g.SupplierID

where

d.CustomerGroupID = @CustomerGroupID

and f.UserID = @UserID

and FREETEXT(BrandName,ManufacturerName,CategoryName, @SearchWord)

View 1 Replies View Related

Efficiency Of A 'SELECT TOP' Style GROUP BY Query: FREETEXT Vs. FREETEXTTABLE

Aug 11, 2007

Hi,

Please have a look at the following two queries, the purpose of which is to find which ten users (represented by 'Username') have created the most records which contain the term 'foo':


SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options

WHERE FREETEXT(*, 'foo')

GROUP BY Username

ORDER BY [Count] DESC




SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options

JOIN FREETEXTTABLE (Options, *, 'foo', 500) ct

ON OptionID = ct.[KEY]

GROUP BY Username

ORDER BY [Count] DESC






They both produce the same result set. However, I am wondering which is more performant. At first glance, it would seem the first one would be. It doesn't involve a JOIN and should, therefore, be more efficient.

But this depends on how the FREETEXT expression is evaluated. My concern is that internally, SQL Server would generate an entire recordset based on 'WHERE FREETEXT(*, 'foo')', which could be thousands of records, and only then restrict this to the TOP 10 by COUNT.

If this does happen, then it would be better to join to a FREETEXTTABLE, where I can at least restrict the result set using the 'top_n_by_rank' parameter (which is set as '500' in this case, as this seems a good balance of performance against the likely number of duplicates I will get in my FREETEXTTABLE results).


So... I am worrying about this unnecessarily? Should I just use the simpler first version?

Any thoughts appreciated.

Thanks

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved