| Groups | Blog | Home |
sql server full text search : sql full text asp .net
answer to this, I'll be much appreciated. (SQl Server) I created a table for full-text search. It has an image field to store my html files and a field for extensions. However, when I retrieve data from SQL Server, I don't know how to convert the image file from the table to string so that the string becomes part of html codes. I'm using ASP .net (VB) to retrieve the files. i.e., Table: Papers (for full text search) Fields: imgFile IMAGE, chrExtension VARCHAR(4) ASP .NET How can I retrieve files in the table Papers, turn them into strings, and concatenate the strings with some other strings in my Asp .net. Thanks in advance! DN
Duy,
You're at the right place & talking to the right guy! ;-) You don't have to "convert the image file", the T-SQL FTS predicates of CONTAINS or FREETEXT does it for you. For example, if I store a MS Word document (or HTML file) as a binary file into a column defined as IMAGE and this document file contains the phrase "SQL Server" and this column is successfully FT Indexed and the FT Catalog is successfully populated, I can issue the following T-SQL FTS query to find this document without converting the binary text, for example: SELECT PKey, Image_column from Table_with_Document where CONTAINS(Image_column,'"SQL Server"') SELECT PKey, Image_column from Table_with_Document where FREETEXT(*,'"SQL Server"') Once you have the unique Primary Key (PKey), you can use the ADO.NET and ASP.NET code examples in the following KB article to retrieve the file and display it to your searcher as a MS Word document (or HTML file): 326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through ASP.NET http://support.microsoft.com/?id=326502 You might also find the following KB articles useful as well: 309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C# http://support.microsoft.com/default.aspx?scid=kb;EN-US;309158 308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with VB.NET http://support.microsoft.com/default.aspx?scid=kb;EN-US;308042 Hope this is helpful! Regards, John [quoted text, click to view] "Duy Nguyen" <tieuta@hotmail.com> wrote in message news:ethEJwm8DHA.2540@TK2MSFTNGP11.phx.gbl... > I don't know if I should post here or aspnet community. If you know an > answer to this, I'll be much appreciated. > > (SQl Server) I created a table for full-text search. It has an image field > to store my html files and a field for extensions. > However, when I retrieve data from SQL Server, I don't know how to convert > the image file from the table to string so that the string becomes part of > html codes. I'm using ASP .net (VB) to retrieve the files. > > i.e., > Table: Papers (for full text search) > Fields: imgFile IMAGE, > chrExtension VARCHAR(4) > > > ASP .NET > How can I retrieve files in the table Papers, turn them into strings, and > concatenate the strings with some other strings in my Asp .net. > > Thanks in advance! > > DN > >
To John,
Thanks! However, I have a little problem, which is about html files. For html files, I only store an html table with data and info (only small part of html page) , not a whole page. I know that I can store a whole page, but editing and changing the layout when I don't like it will be a little tedious. Therefore, I have to concatenate the html table to the codes of my layout. For example: (something like this using ASP .NET(VB)) <#include header.txt> <%= chrOtherStrings + StringHTMLtablefromSQLServerImageField %> <#include ending.txt> (Don't know if Response.BinaryWrite can help me with this?) Thanks for time looking at my problems. If you have answers, can you share? [quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message news:#82XVUn8DHA.3200@TK2MSFTNGP09.phx.gbl... > Duy, > You're at the right place & talking to the right guy! ;-) > You don't have to "convert the image file", the T-SQL FTS predicates of > CONTAINS or FREETEXT does it for you. > For example, if I store a MS Word document (or HTML file) as a binary file > into a column defined as IMAGE and this document file contains the phrase > "SQL Server" and this column is successfully FT Indexed and the FT Catalog > is successfully populated, I can issue the following T-SQL FTS query to find > this document without converting the binary text, for example: > > SELECT PKey, Image_column from Table_with_Document where > CONTAINS(Image_column,'"SQL Server"') > SELECT PKey, Image_column from Table_with_Document where FREETEXT(*,'"SQL > Server"') > > Once you have the unique Primary Key (PKey), you can use the ADO.NET and > ASP.NET code examples in the following KB article to retrieve the file and > display it to your searcher as a MS Word document (or HTML file): > 326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through > ASP.NET > http://support.microsoft.com/?id=326502 > > You might also find the following KB articles useful as well: > 309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C# > http://support.microsoft.com/default.aspx?scid=kb;EN-US;309158 > 308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with > VB.NET > http://support.microsoft.com/default.aspx?scid=kb;EN-US;308042 > > Hope this is helpful! > Regards, > John > > > > "Duy Nguyen" <tieuta@hotmail.com> wrote in message > news:ethEJwm8DHA.2540@TK2MSFTNGP11.phx.gbl... > > I don't know if I should post here or aspnet community. If you know an > > answer to this, I'll be much appreciated. > > > > (SQl Server) I created a table for full-text search. It has an image > field > > to store my html files and a field for extensions. > > However, when I retrieve data from SQL Server, I don't know how to convert > > the image file from the table to string so that the string becomes part of > > html codes. I'm using ASP .net (VB) to retrieve the files. > > > > i.e., > > Table: Papers (for full text search) > > Fields: imgFile IMAGE, > > chrExtension VARCHAR(4) > > > > > > ASP .NET > > How can I retrieve files in the table Papers, turn them into strings, and > > concatenate the strings with some other strings in my Asp .net. > > > > Thanks in advance! > > > > DN > > > > > >
To John,
I found an answer to this: Public Function ByteArrayToString(ByVal bytArray() As Byte) As String Dim UTF8 As New UTF8Encoding() Return UTF8.GetString(bytArray) End Function from http://www.dotnet247.com/247reference/msgs/26/133524.aspx Thanks, Duy Nguyen [quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message news:#82XVUn8DHA.3200@TK2MSFTNGP09.phx.gbl... > Duy, > You're at the right place & talking to the right guy! ;-) > You don't have to "convert the image file", the T-SQL FTS predicates of > CONTAINS or FREETEXT does it for you. > For example, if I store a MS Word document (or HTML file) as a binary file > into a column defined as IMAGE and this document file contains the phrase > "SQL Server" and this column is successfully FT Indexed and the FT Catalog > is successfully populated, I can issue the following T-SQL FTS query to find > this document without converting the binary text, for example: > > SELECT PKey, Image_column from Table_with_Document where > CONTAINS(Image_column,'"SQL Server"') > SELECT PKey, Image_column from Table_with_Document where FREETEXT(*,'"SQL > Server"') > > Once you have the unique Primary Key (PKey), you can use the ADO.NET and > ASP.NET code examples in the following KB article to retrieve the file and > display it to your searcher as a MS Word document (or HTML file): > 326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through > ASP.NET > http://support.microsoft.com/?id=326502 > > You might also find the following KB articles useful as well: > 309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C# > http://support.microsoft.com/default.aspx?scid=kb;EN-US;309158 > 308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with > VB.NET > http://support.microsoft.com/default.aspx?scid=kb;EN-US;308042 > > Hope this is helpful! > Regards, > John > > > > "Duy Nguyen" <tieuta@hotmail.com> wrote in message > news:ethEJwm8DHA.2540@TK2MSFTNGP11.phx.gbl... > > I don't know if I should post here or aspnet community. If you know an > > answer to this, I'll be much appreciated. > > > > (SQl Server) I created a table for full-text search. It has an image > field > > to store my html files and a field for extensions. > > However, when I retrieve data from SQL Server, I don't know how to convert > > the image file from the table to string so that the string becomes part of > > html codes. I'm using ASP .net (VB) to retrieve the files. > > > > i.e., > > Table: Papers (for full text search) > > Fields: imgFile IMAGE, > > chrExtension VARCHAR(4) > > > > > > ASP .NET > > How can I retrieve files in the table Papers, turn them into strings, and > > concatenate the strings with some other strings in my Asp .net. > > > > Thanks in advance! > > > > DN > > > > > >
You're welcome, Duy,
Yep, storing only a part of the HTML page (or even multiple parts of a MS Word document too) can add additional processing requirements that are not handled by the KB articles! I'm glad you were able to find your answer! Regards, John [quoted text, click to view] "Duy Nguyen" <tieuta@hotmail.com> wrote in message news:eeDMuYo8DHA.3404@TK2MSFTNGP09.phx.gbl... > To John, > I found an answer to this: > Public Function ByteArrayToString(ByVal bytArray() As Byte) As String > Dim UTF8 As New UTF8Encoding() > Return UTF8.GetString(bytArray) > End Function > > from http://www.dotnet247.com/247reference/msgs/26/133524.aspx > > Thanks, > > Duy Nguyen > > "John Kane" <jt-kane@comcast.net> wrote in message > news:#82XVUn8DHA.3200@TK2MSFTNGP09.phx.gbl... > > Duy, > > You're at the right place & talking to the right guy! ;-) > > You don't have to "convert the image file", the T-SQL FTS predicates of > > CONTAINS or FREETEXT does it for you. > > For example, if I store a MS Word document (or HTML file) as a binary file > > into a column defined as IMAGE and this document file contains the phrase > > "SQL Server" and this column is successfully FT Indexed and the FT Catalog > > is successfully populated, I can issue the following T-SQL FTS query to > find > > this document without converting the binary text, for example: > > > > SELECT PKey, Image_column from Table_with_Document where > > CONTAINS(Image_column,'"SQL Server"') > > SELECT PKey, Image_column from Table_with_Document where FREETEXT(*,'"SQL > > Server"') > > > > Once you have the unique Primary Key (PKey), you can use the ADO.NET and > > ASP.NET code examples in the following KB article to retrieve the file and > > display it to your searcher as a MS Word document (or HTML file): > > 326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through > > ASP.NET > > http://support.microsoft.com/?id=326502 > > > > You might also find the following KB articles useful as well: > > 309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C# > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;309158 > > 308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with > > VB.NET > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;308042 > > > > Hope this is helpful! > > Regards, > > John > > > > > > > > "Duy Nguyen" <tieuta@hotmail.com> wrote in message > > news:ethEJwm8DHA.2540@TK2MSFTNGP11.phx.gbl... > > > I don't know if I should post here or aspnet community. If you know an > > > answer to this, I'll be much appreciated. > > > > > > (SQl Server) I created a table for full-text search. It has an image > > field > > > to store my html files and a field for extensions. > > > However, when I retrieve data from SQL Server, I don't know how to > convert > > > the image file from the table to string so that the string becomes part > of > > > html codes. I'm using ASP .net (VB) to retrieve the files. > > > > > > i.e., > > > Table: Papers (for full text search) > > > Fields: imgFile IMAGE, > > > chrExtension VARCHAR(4) > > > > > > > > > ASP .NET > > > How can I retrieve files in the table Papers, turn them into strings, > and > > > concatenate the strings with some other strings in my Asp .net. > > > > > > Thanks in advance! > > > > > > DN > > > > > > > > > > > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |