I a trying to build a better architecute for full text search.
How is it possible?
Can I put the FTS database in a seperate box?if I can how?
Like Server A..main server.
Server B...........just contains full text database files.....can I do this?Does this improve speed?_
No it is a functionality and you cannot have the details in a database, you need to install FTS as a component.sqlI think the problem is how the tabel in the datebase is setup.
How shall a tabel look like so it can store html?Since HTML is string data you should be able to use one of the "char" datatypes to store HTML. If you will need to store more than 8000 characters then you should probably using the text datatype.
Terri
Hi,
For some reason I can't use the edit, update or insert features on my remote shared server, so I am looking to create a web page that has text boxes on it, that I can enter data into, that will be saved into my database.
This is opposed to entering the data directly into the database itself. I want to be able to use a webpage, for simply adding new data, and saving it so that the new data updates and saves over the top of the old data.
What are the steps involved in doing this?
Any example code for just one text box would be appreciated, I could then extend it to suit my needs. Tia.
As I understand you want to get data in text box in you website and then update or insert to the database table. Here I wrote a very simple sample in C#:
protected void Button1_Click(object sender, EventArgs e)
{
string connectionString = @."Data Source=Confute\SQL2000;Initial Catalog=tempdb;Integrated Security=SSPI;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Connect to the database then retrieve the schema information.
SqlCommand cmd = new SqlCommand("sp_UpdateMytable", connection);
connection.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@.id", txtBox_ID.Text);
cmd.Parameters.Add("@.name", txtBox_Name.Text);
int i = cmd.ExecuteNonQuery();
}
And the storedprocedure sp_UpdateMytable will update a table t1(id int, name varchar(30)) in this way:
create proc sp_UpdateMyTable @.id int,@.name varchar(30)
as
if exists (select * from t1 whereid=@.id)
update t1 setname=@.namewhere id= @.id
else insert into t1 select @.id,@.name
go
|||
Thanks Lori_Jay,
I actually resolved the issue and did switch the answered tag on this thread. I am sure your solution would work, however mine was a more simple issue, actually I will mention it here for other poor souls who struggle with the same issue I did.
Basically, I did not have a Primary Key set in my table of my database. Firstly, I was taught that although its a good idea to have a PK, it's not absolutely necessary. Because I had one table, with one column and one row, Idecided not to have one.
If you don't have one, then in Visual Studio 2005, you cannot access the Advanced SQL options, which are INSERT, UPDATE & DELETE, this seems to be a major fault if you ask me because there is ZERO error reporting and ZERO documentation about it.
I got lucky when I did a Google for it (after a week of endless suffering) to find one site in the entire world, written in Russian (which I had to translate very poorily), which stated that you need a PK. I quickly added a PK to my table and it worked instantly.
Perhaps the powers that be whom monitor these forums, can look into this and document it so that others are spared the same distress.
Regards.
Can anyone please help me in solving this.
Thanks a lot for your help and time.
PavelPavel (p.golobokov@.ausbulk.com.au) writes:
> I have SQL 2000 and need to retrieve fairly large amout of data (~
> 50.000 characters) in XML format and then insert it into the field of
> the text type.
> As 'FOR XML' can't be used with either local variables, INSERT INTO or
> SELECT INTO this makes "XML support" quite useless in many aspects.
You can try:
INSERT tbl (xmlcol)
SELECT * FROM OPENQUERY(LOCALSVR, 'SELECT ... FOR XML')
Where LOCALSVR has been created as
EXEC sp_addlinkedserver
@.server = 'LOCALVR',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'LocalServer'
That is, you use the deprecated OLE DB over ODBC provider. This works
so far that you get XML back. However, you may find that the text
has been broken into many rows. (If you would use SQLOLEDB, the real
SQL Server provider, you get a blob back.)
If this does not work out, you will have a find a client to pick up the
XML and send it back.
In SQL 2005, the XML support is considerably enhanced, and you should
be able to do this without weird workarounds.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
I am unable to get FTS working where the column to be searched is type varchar(MAX) or Text. I can get this to work if my column to be indexed is some statically assigned array size such as varchar(1000).
For instance this works, and will return all applicable results.
CREATE TABLE [dbo].[TestHtml](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PageText] [varchar](1000) NOT NULL,
CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED
SELECT * FROM TestHTML WHERE Contains(PageText, @.searchterm);
And this does not. It returns zero results what so ever.
CREATE TABLE [dbo].[TestHtml](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PageText] [varchar](MAX) NOT NULL,
CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED
SELECT * FROM TestHTML WHERE Contains(PageText, @.searchterm);
Could someone please tell me what I need to do to enable FTS on varchar(MAX) or Text columns?
Did you create a fulltext index on the column and made it language specific / language neutral ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
i need to code for retrieving single data from sqldatasource control. i need the full set of coding
connecting
retrieving data in text box
editing data to the table
updating data to the table
deleting data to the table
i want to fetch "single field data"
any one who know the code please post reply or send it to my mail senthilonline_foryou@.rediffmail.comI suggest that you use a stored procedure like this (assuming ID is an identity index column, with a data row NAME VARCHAR(50) in Table Fred)
CREATE PROCEDURE dbo.uspGetFred
(
@.ID INT,
@.NAME VARCHAR(50) OUTPUT
)
SET NOCOUNT ON
SELECT NAME FROM Fred WHERE ID = @.ID
GO
The VB code below retrieves NAME into aName fpor a given value of iID
CONST DBCONNECT As String = "Your Connect String"
Dim sName As String = ""
Dim xSqlConnection As SqlConnection = New SqlConnection(DBCONNECT )
Dim xSqlCommand As SqlCommand = New SqlCommand("uspGetFred", xSqlConnection)
Try
xSqlCommand.CommandType = CommandType.StoredProcedure
xSqlCommand.Parameters.Add("@.ID", SqlDbType.Int)
xSqlCommand.Parameters("@.ID").Value = CType(iId, Integer)
xSqlCommand.Parameters.Add("@.NAME", SqlDbType.VarChar, 50)
xSqlCommand.Parameters("@.NAME").Direction = ParameterDirection.Output
xSqlCommand.Connection.Open()
xSqlCommand.ExecuteNonQuery()
sName = xSqlCommand.Parameters("@.NAME").Value
Catch ex As Exception
' Handle your error here!
Finally
xSqlCommand.Connection.Close()
xSqlCommand.Dispose()
xSqlConnection.Dispose()
End Try
HTH!
What is the best way to parse large amounts of formatted text datainto a table so that it can be retrieved with as much formattingretained as possible - particularly paragraphs? Will eachparagraph need to be inserted into its own row to be retrieved as aparagraph?
Wrap each paragraph in an HTML <p> paragraph goes here </p> section. They can sit together just like this
<p>1</p><p>2</p><p>3</p>
|||
Thanks, Bryan.
To follow up, I am having trouble retrieving the text data in aparagraph format. For example, I input<p>1</p><p>2</p><p>3</p>into a text field in the database and dropped the table onto a page inVisual Web Developer. The data format looked exactly like it wasinput into the db (i.e.,<p>1</p><p>2</p><p>3</p>) with theparagraph tags showing but no actual paragraphs. Is there a trickto getting the paragraph formatting to show up properly in the controlon my web page?
It depends on what kind of control you are trying to show the value in. Some controls render output to the page as HTML. You have to understand what really happens when an ASPX page is rendered. Browsers dont know what asp.net is. They dont care, they never see it. This is why you need an asp.net server and IIS. The engine on the server downrenders your .net code to HTML in a way the browser can understand. If you view the source of one of your pages, you'll notice all you have is a bunch of HTML and javascript.
The example below points this out by using a label (which just throws values into the HTML stream). And a textbox (which renders literal output)
<asp:LabelID="label1"runat="server"></asp:Label>
<asp:TextBoxID="textbox1"runat="server"/>
ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Loadlabel1.Text ="<p>1</p><p>2</p><p>3</p>"
textbox1.Text ="<p>1</p><p>2</p><p>3</p>"
EndSub
|||Bryan - thanks for the input. But I am a little baffled sinceall three controls on my page are ASP (and not simply html) and all arerunat="server" (which I understand to mean that the server converts theasp to html for the browser). But only the ASP label has theparagraph formatting. Since I don't expect to be using the labelcontrol as a primary means of displaying the extensive text data fromthe db, is it simply a matter of setting control properties on othercontrols (like the gridview dataformatstring property) to get them tohandle the <p> tags? Or am I simply limited in my choice ofcontrols that can do this job?
Not all controls will render ...for lack of a better term...renderable HTML from their data / text property. Inside gridviews for example, you'll probably want to use Bound labels, or literal controls. I think you may be missing something conceptually here. Explain to me what type of gridview you are rendering, maybe I can shed some light on the subject - and give you an example of how you'd do what you're trying to do.
|||The project I am considering involves providing searchcapability on numerous, various documents (mostly magazine articles andresearch papers) each of which is several pages long with faily complexformatting (if I retain the original look). The product needs to beavailable online and on CDs. The actual search results willprobably contain document titles or abstracts that can be selected sothat the full article will be displayed - probablly a gridview orsimilar control for the brief results list with some type of detailsview counterpart for the selected text content display. My choiceof controls may need to include Windows forms controls as well as webpage controls.
At this early stage I am wondering if Ishould put the formatted documents into the database as varbinary datathat can be searched and retrieved maintaining the formatting. Or, just put the text data into the db with simple paragraph formattingthat will give a uniform look to the search output. I have done abit ofdatabase work involving simple data searching, etc., but never withextensive, complex text fields such as this where the formatting in theoutput is important.
Thanks for the insights so far and for any additional help you can provide.
|||
Ok, I think you've done a pretty good job thinking through this so far, but I'll offer up a little advice to you. First of all, probably the best / most accepted web format for complex documents is PDF. There are numerous .NET utilities to encode input into a PDF, including converters that could take word documents, excel spreadsheets, star office...just about anything you can think of - and turn it into a PDF for your purposes. Now, my advice would be to NOT put all this information in the database. To truly allow a full text search - you are going to cause yourself a huge amount of work, and headache.
Store a path to the PDF in your database, so you know where it's at, and render a title, maybe document iinformation like creation time, last modified, owner, short description...etc.
Now the magic comes in. Tie into the MS Indexing service. It can allow a really slick full text search, and there is a connector that allows the indexing service to search inside pdfs. So all the text inside the PDF itself will be indexed, and included in the search criteria. What's returned from a call to the indexing service is a location to the document (your aspx form). My advice is to break it out like this
Site > Document List > Document Information Page with a link to the document (derived from the path in your database)
When the MS indexing service returns, it will return the location of your Document Information Page (as a link) or a list of them depending on how many results were returned. Clicking it will take you directly to the pertitant information.
Here is a great article on accomplishing this.
http://aspnet.4guysfromrolla.com/articles/033005-1.aspx
|||Thanks again Bryan. I have been looking at that article and will try that approach.
Followupquestion on the previous discussion on controls. Are you aware ofany web controls that render html besides the label? I wouldthink the textbox would have a property that would support htmlrendering but can't find any.
|||A textbox will not. You'll need an HTML based textbox that can support it. Much like the one you use to post comments on this site. There are several out there. the FCK Editor is the one I usually use, but there's also FreeTextBox, which is the other big free one.
An ASP:Literal control gives you options to render HTML formated output, transform it, or passthrough. That may be a great display control for you, but .NET has no controls that are directly editable, that support HTML nativly.
|||Bryan. Thanks for all your help in sorting this out. Good to have some options as I move forward with this project.
I am particularly interested in keeping a linebreak between paragraphs and the only way I can think to do that is to put each paragraph in its own row. But I want some input before I undertake that substantial task.
Thanks for any help provided.
You can use char(10) which is the hard break in SQL.
eg.
Code Snippet
Print 'Microsoft' + char(10) + 'Website'
The Output is like below:
Code Snippet
select 'Microsoft' + char(10) + 'Website'
|||Hi Vidhya. Thanks for your advice.I am trying to understand how to insert the data into a table and retrieve to a control on a VB form. In Visual Basic Express I tried including the char(10) with the data by pasting it into my table like this:
Paragraph 1 text here. + char(10) + Paragraph 2 text here. + char(10) + Paragraph 3 text here.
But all I get for output in my datagrid control is the literal string as one block of text.
I use a similar technique with html tags (
) if I am outputing to a webcontrol and it seems to work okay if the control renders html (ie.,
Paragraph 1 text here
Paragraph 2 text here
Paragraph 3 text here
). But this doesn't work for Windows forms unless I am using a webbrowser control and html controls (which I don't want to do).So I opened up SQL Server Management Studio Express and created a database with a table (text) and column (paragraph) and ran these scripts:
INSERT INTO Text (Paragraph)
VALUES ('Paragraph 1 text here.' + char(10) + 'Paragraph 2 text here.' + char(10) + 'Paragraph 3 text here.')
SELECT *
FROM Text
select 'Microsoft' + char(10) + 'Website'
From Text
But my output is still a single line of text without paragraph formatting.
When I run the code you provided (Print 'Microsoft' + char(10) + 'Website'), that works perfectly. But I don't know how to insert and retrieve the formatted text data in my db.
I am relatively new to SQL, so I realize that I am missing something simple in my implementation of your advice. Any suggestions on how to apply this concept is greatly appreciated.|||To Insert into table jus insert as usual
insert into <tablename> values('col1','col2')
To select from the table with char(10) you can use
select col1,char(10),col2 from <tablename>
Run the above query in text format(ctrl+t)
|||Thanks for the follow-up. I have had some success (I think). Here is what I have tried:
CREATE TABLE Paragraph
(ID int Primary Key IDENTITY(1,1) NOT NULL,
FormattedText nvarchar(500) NOT NULL)
INSERT INTO Paragraph (FormattedText)
VALUES ('Paragraph 1 text here.' + char(10) + 'Paragraph 2 text here.' + char(10) + 'Paragraph 3 text here.')
select FormattedText from Paragraph
If I have the "Results to Text" button clicked, the output looks great, like this:
Paragraph 1 text here.
Paragraph 2 text here.
Paragraph 3 text here.
If I output with "Results to Grid" clicked, it loses the format and looks like this:
Paragraph 1 text here. Paragraph 2 text here. Paragraph 3 text here.
So I think I am making progress. I am getting the data into my table ok. Getting it out in the proper paragraph format is the challenge, which is easy in Management Studio.
But when I use the same table in Visual Basic and output the row of data to a datagrid, it comes out like this, without the paragraph formatting:
Paragraph 1 text here. Paragraph 2 text here. Paragraph 3 text here.
So my problem now is how to get it to display properly on my Windows Form. Any thoughts on this, or do I need to switch to the VB forum?|||Im not sure abt VB. But i think there is a command "Break" in VB. Pls check
|||I will look into it further. There is a break property for creating menus, but I am not sure if it can be applied to datagrids or other controls for displaying formatted data.
Thanks again for your helpl.
