Can anyone suggest whey I dont get any results with this search?
SQL reads:
SELECT SupplierName, Location, ShortDescription
FROM Query1
WHERE 'TimberSpecies' LIKE '%MMColParam%' AND 'CategoryTable' LIKE '%MMColParam2%' AND 'Location' LIKE '%MMColParam3%'
MMColParam 1 Request.Form("keywordSearch")
MMColParam2 2 Request.Form("location")
MMColParam3 3 Request.Form("category")
Mally.
I have Sql server 2005 SP2.
I enabled it for Full Text search. Substring search where i enter *word* doesn't return any row.
I have a table testtable where description has word Extinguisher.
If i run a query with *ting* it doesn't return any row.
select * from testtable where contains(description,'"*xting*"') ;
But it works if i do
select * from testtable where contains(description,'"Exting*"') ;
The Full text search document says it supports substring search.
Is it an issue with sql server 2005?Please help.
I am using Sql Server 2014 Express edition.I have a table with a varchar(max) column. I have created a full text search that use the stoplist "system". column has this struct: xxx.yyy.zzz.... where xxx, yyy, zzz... are numbers, like 123.345.123123.366456...I can have rows like that:
123.345
123.345
123.345.444
123.345.555
123.345.666
123.345.444.777
123.345.444.888
123.345.555.999
I am trying this query:
select * from Mytable where
contains(MyColumn, '123.345.')
I gues the contains would return all the rows with column contains 123.345, but this does not return all the expected rows, only one row.I have tried to replace "." with "-" but the result is the same.I have also tried with '123.345.*. In this case I have got more results, but no all the exptected rows.If I use this query:
select * from MyTable where
MyCOlumn like '123.345.%';
Hi guys
I need to create an "advanced search" which will allow the user to narrow down his results.
Ideally I'd want him/her to use the same search criteria form for each iteration, with a checkbox called "Search within results" type of thing.
Now what I was wondering if there was any existing literature on how to effectively do this. I have tried doing it just through SQL Statements but they are becoming very messy and large.
Is it possible to do this by searching the initial dataset, returning dataset #2 and then if a 3rd "search within results" is done apply the search against dataset #2 and return dataset #3 etc?
Many Thanks
John
I tried all of the other forums with no luck.
Using visual basic, c#, or c++ Forms and applications
Here is what I want to do:
I place a datagridview obj on the form and it works no problems. How do you take one of the columns and add all of the data (numeric) from all of the rows to show a total of all of the numbers in a text field obj added to the bottom of the form.
For example, I want to use it for office expenses and I have one table field named "cost" and at the bottom I want to show total cost (all rows added together).
Thanks in advance
Swynk have implemented a new search engine on this site. I have found that
whatever I search for it finds nothing. Does anyone know of a work around?
Hello! I am using this code to search the database but for some reason it's not liking the parameter I put there. Here is my code that works great with no parameters:
protected void getBusinessInfo() { DataSet ds; ds = SqlHelper.ExecuteDataset(connection, "spBusinessInfoSearch"); gvSearch.DataSource = ds; gvSearch.DataBind();
}
protected void btnSearch_Click(object sender, EventArgs e) { getBusinessInfo(txtName.Text); }
Here is what I put in the stored procedure:
ALTER PROCEDURE dbo.spBusinessInfoSearch
ASSELECT * from vwUserprofileInfo where DisplayName like '%Eric%'
RETURN
Now, when I used the same code with paramers nothing is returned.
protected void getBusinessInfo(string param1) { DataSet ds; ds = SqlHelper.ExecuteDataset(connection, "spBusinessInfoSearch", new SqlParameter("@DisplayName", param1)); gvSearch.DataSource = ds; gvSearch.DataBind();
}
protected void btnSearch_Click(object sender, EventArgs e) { getBusinessInfo(txtName.Text); }
Dear all, I have illustared with code and sample output data my requestin thsi post. I simply was expecting some results from my search "amdsocket a 32 bit cache 512 dell" that includes a logical AND for all thewords in that search.Since i assume that any word might be an item_name, item_key oritem_value, i included all in the search.Can any one tell me why i get 0 results?The sample output of my data should look like this:item_id item_nameitem_keyitem_value--------------------------------------------------1Gefore MX 440Size64 MB1Gefore MX 440Architecture64 Bit1Gefore MX 440AGP8x1Gefore MX 440ChipsetNvidia1Gefore MX 440VendorAsus2AMD 3200+ClassSocket A2AMD 3200+Speed2 GHz2AMD 3200+Architecture32 Bit2AMD 3200+Level 2 Cache512 KB2AMD 3200+VendorAMD3Dell P780Geometry17 Inch3Dell P780Screen TypeFlat3Dell P780Frequency60 Hz3Dell P780VendorDellHere is my scenario:create table item_table (item_id int identity (1,1) not null primarykey, item_name varchar (50) not null)gocreate table details_table (item_id int not null, item_key varchar(50), item_value varchar (50))goalter table details_table add foreign key (item_id) referencesitem_tablegoinsert into item_table values ('Gefore MX 440')goinsert into item_table values ('AMD 3200+')goinsert into item_table values ('Dell P780')goinsert into details_table values (1,'Size', '64 MB')goinsert into details_table values (1,'Architecture', '64 Bit')goinsert into details_table values (1,'AGP', '8x')goinsert into details_table values (1,'Chipset', 'Nvidia')goinsert into details_table values (1,'Vendor', 'Asus')goinsert into details_table values (2,'Class', 'Socket A')goinsert into details_table values (2,'Speed', '2 GHz')goinsert into details_table values (2,'Architecture', '32 Bit')goinsert into details_table values (2,'Level 2 Cache', '512 KB')goinsert into details_table values (2,'Vendor', 'AMD')goinsert into details_table values (3,'Geometry', '17 Inch')goinsert into details_table values (3,'Screen Type', 'Flat')goinsert into details_table values (3,'Frequency', '60 Hz')goinsert into details_table values (3,'Vendor', 'Dell')gocreate view all_view asselect top 100 percent i.item_id, i.item_name, d.item_key, d.item_valuefrom item_table as i left outer join details_table as don i.item_id = d.item_idorder by i.item_id, i.item_name, d.item_key, d.item_valuego-- the complete search is "amd socket a 32 bit cache 512 dell"declare @search_key1 as varchar (50)declare @search_key2 as varchar (50)declare @search_key3 as varchar (50)declare @search_key4 as varchar (50)declare @search_key5 as varchar (50)declare @search_key6 as varchar (50)set @search_key1 = 'amd'set @search_key2 = 'socket a'set @search_key3 = '32 bit'set @search_key4 = 'cache'set @search_key5 = '512'set @search_key6 = 'dell'select distinct item_idfrom all_viewwhere((item_name like '%' + @search_key1 + '%') or (item_key like '%' +@search_key1 + '%') or (item_value like '%' + @search_key1 + '%'))and((item_name like '%' + @search_key2 + '%') or (item_key like '%' +@search_key2 + '%') or (item_value like '%' + @search_key2 + '%'))and((item_name like '%' + @search_key3 + '%') or (item_key like '%' +@search_key3 + '%') or (item_value like '%' + @search_key3 + '%'))and((item_name like '%' + @search_key4 + '%') or (item_key like '%' +@search_key4 + '%') or (item_value like '%' + @search_key4 + '%'))and((item_name like '%' + @search_key5 + '%') or (item_key like '%' +@search_key5 + '%') or (item_value like '%' + @search_key5 + '%'))and((item_name like '%' + @search_key6 + '%') or (item_key like '%' +@search_key6 + '%') or (item_value like '%' + @search_key6 + '%'))go----Best regards
View 20 Replies View RelatedI need some help with a query. I have a table with inventory that Ineed to allow customer searches on. Based on their search criteria, apreference level is calculated; the higher the preference level, thehigher on the order on the search results.The hard part is when the results are supposed to be limited to amaximum number of stores and items. Let's say that they only want tosee 3 stores and a max of 5 items per store. What needs to bereturned is the 3 stores with the best Preference and the 5 best itemsat each store.Create Table Inventory( StoreId int, ItemId int, Preference int )
View 1 Replies View Relatedhi. i'm trying to order my results ascending by date except i'm getting some really weird output. my ouput resembles something like this:
oct 2
oct 3
sep 13
sep 21
sep 22
sep 30
aug 3
aug 5
aug 16
the data is stored in a date field. i use getdate when inserting the date to the database. is there a reason why the dates are showing up weird and not ordering appropriately? thanks for your help.
also, can you not search here any more? i keep getting timeout errors.
I am working with a complex Stored Procedure which I need to be able to filter and sort by various criteria. Can I please ask how I can get the data that the SP returns into place that I can apply my search criteria?
Ive tried the code below in the SQL Query Analyzer but all I get is:
"Server: Msg 197, Level 15, State 1, Line 10
EXECUTE cannot be used as a source when inserting into a table variable."
DECLARE @TempPropLeads TABLE (
LeadNumber int,
LeadSource char(20) )
INSERT @TempPropLeads (LeadNumber, LeadSource)
EXEC dbo.usp_ReportProposalFromLead NULL ,'2004-06-01', '2004-06-02', NULL
SELECT *
FROM @TempPropLeads
ORDER BY LeadNumber DESC
GO
PS there are more fields returned from the SP - just using the above as a test.
Once I have got the data filtered/sorted as needed it's going to go into a Excel CSV spreadsheet
Cheers
Hi guys,As the title shows, i would like to learn how to return ranked results, and be able to order by relevance/rank. I am currently using SQL2000, though the final site may be going live with SQL2005. The data i am searching against is in tables, not Indexed files.I have no idea where to start......Does it have somthing to do with the amount of times the keywords appear? any help would be greatRegardsDarren
View 3 Replies View RelatedI need to order my search results by relevance to the keywords entered by the user, how can i do that in sql ?
View 6 Replies View RelatedI'm busy writing a local site search engine that searches through a sql
server database and I want to know how or what is the correct sql
syntax to use in order to limit the amount of results a page loads at a
time? The idea is obviously similar to something like google where you
only see a certain amount of results first and then click at the botom
for the next eg. 10 results.
The second question is how do I, after the first page with the first
set of results that were shown, "clear" the second page of
the previous html in order to show the next set of results?
To give you an idea what my code looks like at the moment. Please don't
kill me if the code is done a bit a lame, because I'm still learning.
<%@ Page Language="C#" Debug="true" EnableSessionState="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.IO" %>
<script runat="server">
void Page_Load(Object sender , EventArgs e)
{
//strings used to get values from basicSearch.aspx
string strProvince;
string strGender;
string strHeight;
string strBodyType;
string strLooks;
string strHairColor;
string strEyeColor;
string strEthnicity;
string strHomeLanguage;
string strRelStatus;
string strRelInterest;
string strHaveChildren;
string strWantChildren;
//strings used for storing results from search
string resUserName;
string resFirstName;
string resLastName;
string resUserPhoto;
string resProvince;
string resGender;
string resAge;
string resHeight;
string resBodyType;
string resLooks;
string resHairColor;
string resEyeColor;
string resEthnicity;
string resHomeLanguage;
string resRelStatus;
string resRelInterest;
string resHaveChildren;
string resWantChildren;
string resProfileHeading;
string resTextDescription;
string resTextDescription2 = "";// used for the actual display of the value
string strQuery; // to store concattenated search strings
string strInput; // to store textfile input while being read
StreamReader objStreamReader;
strProvince = Session["sessionProvince"].ToString();
strGender = Session["sessionGender"].ToString();
strHeight = Session["sessionHeight"].ToString();
strBodyType = Session["sessionBodyType"].ToString();
strLooks = Session["sessionLooks"].ToString();
strHairColor = Session["sessionHairColor"].ToString();
strEyeColor = Session["sessionEyeColor"].ToString();
strEthnicity = Session["sessionEthnicity"].ToString();
strHomeLanguage = Session["sessionHomeLanguage"].ToString();
strRelStatus = Session["sessionRelStatus"].ToString();
strRelInterest = Session["sessionRelInterest"].ToString();
strHaveChildren = Session["sessionHaveChildren"].ToString();
strWantChildren = Session["sessionWantChildren"].ToString();
strQuery = strProvince + " " +
strGender + " " + strHeight + " " + strBodyType + " " + strLooks + " " +
strHairColor + " " + strEyeColor + " " + strEthnicity + " " +
strHomeLanguage + " " +
strRelStatus + " " + strRelInterest + " " + strHaveChildren + " "
+ strWantChildren;
SqlConnection conPubs;
string strSearch;
SqlCommand cmdSearch;
SqlDataReader dtrSearch;
conPubs = new SqlConnection(
@"Server=THALIONTHALION;Integrated Security=SSPI;Database=DateGame" );
//retrieve the results from the db
strSearch = "SELECT * FROM
client," + "FREETEXTTABLE( client, * , @searchphrase ) searchTable "
+ "WHERE [KEY] = client.userName " + "ORDER BY RANK DESC ";
cmdSearch = new SqlCommand( strSearch, conPubs );
cmdSearch.Parameters.Add( "@searchphrase", strQuery );
conPubs.Open();
dtrSearch = cmdSearch.ExecuteReader();
//start display of results
lblResults.Text = "<table
width='100%' style='border-style:solid; border-width:thin;
border-color:#E1E2DC;' cellpadding='0' cellspacing='0'>";
while ( dtrSearch.Read())
{
//values read from the returned result set
resUserName = dtrSearch[ "userName" ].ToString();
resFirstName = dtrSearch[ "firstName" ].ToString();
resLastName = dtrSearch[ "lastName" ].ToString();
resUserPhoto = dtrSearch[ "userPhoto" ].ToString();
resProvince = dtrSearch[ "province" ].ToString();
resGender = dtrSearch[ "gender" ].ToString();
resAge = dtrSearch[ "age"
].ToString();
resHeight = dtrSearch[ "height" ].ToString();
resBodyType = dtrSearch[ "bodyType" ].ToString();
resLooks = dtrSearch[ "looks" ].ToString();
resHairColor = dtrSearch[ "hairColour" ].ToString();
resEyeColor = dtrSearch[ "eyeColour" ].ToString();
resEthnicity = dtrSearch[ "ethnicity" ].ToString();
resHomeLanguage = dtrSearch[ "homeLang" ].ToString();
resRelStatus = dtrSearch[ "relationshipStatus"
].ToString();
resRelInterest = dtrSearch[ "relationPreference" ].ToString();
resHaveChildren = dtrSearch[ "haveChildren" ].ToString();
resWantChildren = dtrSearch[ "wantChildren" ].ToString();
resProfileHeading = dtrSearch[ "profileHeading" ].ToString();
resTextDescription = dtrSearch[ "textDescription" ].ToString();
// read the text file's info into a variable for display
if (
File.Exists( MapPath( "text files" +"\" +resTextDescription ) ) )
{
objStreamReader = File.OpenText( MapPath( "text files" +"\"
+resTextDescription ) );
strInput = objStreamReader.ReadLine();
while ( strInput != null)
{
resTextDescription2 += strInput;
strInput = objStreamReader.ReadLine();
}
objStreamReader.Close();
}
else
{
resTextDescription2 = "myFile.txt does not exist!";
}
//determine whether male or female in order to display correct sign
if ( resGender == "Male")
resGender = "Male_sign_1.jpg";
else
resGender = "Female_sign_1.jpg";
//determine whether 'want' and 'have' children and convert to correct
words for display
if ( resHaveChildren == "have kids" )
resHaveChildren = "Yes";
else
resHaveChildren = "No";
if ( resWantChildren == "want kids" )
resWantChildren = "Yes";
else
resWantChildren = "No";
// The writing of html to display the values calculated
lblResults.Text += "<tr><td width='16%'
bgcolor='#C7C9BE' class='text_bold'><div align='center'>" +
resUserName +
"</div><hr noshade class='hr_line'></td>";
lblResults.Text += "<td colspan='2' bgcolor='#E1E2DC'
class='text_bold'><div align='center'>" + resProfileHeading +
"</div><hr noshade class='hr_line'></td><td
colspan='2'><div align='center'
class='page_headings'>%</div></td></tr>";
lblResults.Text += "<tr><td rowspan='15' valign='top'
bgcolor='#C7C9BE'><p align='center'><img src='images/" +
resGender + "' width='20' height='22'></p>" +
"<img src='photos/" + resUserPhoto + "' width='80'
height='88'><div align='center'></div></td>";
lblResults.Text += "<td colspan='2' bgcolor='#E1E2DC'><p
class='text'><br></p><p class='text'>" +
resTextDescription2 + "</p><p
class='text_bold'> </p></td>";
lblResults.Text += "<td width='7%' rowspan='15'
valign='top'><img src='images/hotlist_1.jpg' alt='Add To
Favorites' width='34' height='32'></td>" +
"<td width='14%' rowspan='15' valign='top'><img
src='images/email_1.jpg' alt='Email this profile' width='42'
height='36'></td></tr>";
lblResults.Text += "<tr><td width='26%' bgcolor='#E1E2DC'
class='text_bold'>Location :</td><td width='37%'
bgcolor='#E1E2DC' class='text'>" + resProvince +
"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Age :</td><td bgcolor='#E1E2DC'
class='text'>" + resAge + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Height : </td><td bgcolor='#E1E2DC'
class='text'>" + resHeight + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Body Type : </td><td bgcolor='#E1E2DC'
class='text'>" + resBodyType + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Looks : </td><td bgcolor='#E1E2DC'
class='text'>"+ resLooks+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Hair Colour : </td><td bgcolor='#E1E2DC'
class='text'>"+resHairColor+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Eye Colour : </td><td bgcolor='#E1E2DC'
class='text'>"+resEyeColor+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Ethnicity : </td><td bgcolor='#E1E2DC'
class='text'>"+resEthnicity+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Home Language : </td><td
bgcolor='#E1E2DC'
class='text'>"+resHomeLanguage+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Relationship Status : </td><td
bgcolor='#E1E2DC'
class='text'>"+resRelStatus+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Realtionship Interest : </td><td
bgcolor='#E1E2DC'
class='text'>"+resRelInterest+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Have Children : </td><td
bgcolor='#E1E2DC'
class='text'>"+resHaveChildren+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Want Children : </td><td
bgcolor='#E1E2DC'
class='text'>"+resWantChildren+"</td></tr>";
lblResults.Text += "<tr><td colspan='2'
bgcolor='#E1E2DC'> </td></tr>";
lblResults.Text += "<tr><td
bgcolor='#497792'> </td><td colspan='2'
bgcolor='#678BA1'> </td><td colspan='2'
bgcolor='#678BA1'> </td></tr>";
lblResults.Text += "<tr><td> </td><td
colspan='2'> </td><td
colspan='2'> </td></tr>";
resTextDescription2 = "";
}
lblResults.Text += "</table>";
conPubs.Close();
}
void Button_Login(Object sender , ImageClickEventArgs e)
{
SqlConnection conClient;
string strSelect;
string strclientName;
SqlCommand cmdSelect;
//create a connection
conClient = new
SqlConnection( @"Server=THALIONTHALION;Integrated
Security=SSPI;database=DateGame" );
//select statement
strSelect = "Select userName
From [client] Where userName=@username and userPassword=@userpassword";
cmdSelect = new SqlCommand( strSelect, conClient );
cmdSelect.Parameters.Add( "@username", txtNickName.Text );
cmdSelect.Parameters.Add( "@userpassword", txtPword.Text );
//open a connection to db
conClient.Open();
//check to see if it already exists
strclientName = System.Convert.ToString(cmdSelect.ExecuteScalar());
if (strclientName.ToLower() == txtNickName.Text.ToString().ToLower())
{
//Store user name as session variable
Session["sessionUserName"] = txtNickName.Text.ToString();
txtNickName.Text = "";
txtPword.Text = "";
}
else
lbl_invalid_login.Text = "Invalid login!";
conClient.Close();
}
</script>
hi.
i know this is a bit of a shot in the dark but, does anybody know of a reasonably understandable way to rank XML node path results based on keyword search terms? seems like there are tons of uber-complex docs on the internet about this. i dont mind if it is a bit of a work-around, i am just looking for something that is reasonable non-complex that i can wrap my mind around. links etc apprecaited.
thanks.
I am not aware of this if it exists in MS SQL server. But I need to return results in alphabetic order ignoring "The" if it's the first word of a title...
so for example title "The Cliffhanger" would be returned along with other titles that start with letter C, but "The" also must be returned as part of the title, but just ignored while alphabetizing.
I'm sure that I'm not the first one to ever need this and don't want to re-invent the wheel, so if you have any ideas as to the best way to accomplish this, help me out.
Thanks in advance.
I have a DBTable named Vendors which includes the following columns: CompanyName (Name Here)
SBI (Yes, No)
MBE (Yes, No)
SBI (Yes, No)
WBE (Yes, No)
I'm trying to do a search on Vendors where either SBI or MBE or SBI or WBE is equal to 'yes' and then order by company name.
I'm lost on both the search and the results code.
I'd like to have a drop down for the search like this:
<form name="Search" action="vendor_results.asp" method="post">
<Input type=hidden name="validate" value=1>
Show all Vendors who are: <Select name="?????">
<OPTION value="YES">SBI
<OPTION value="YES">SBE
<OPTION value="Yes">MBE
<OPTION value="Yes">WBE
</select>
<Input type="submit" Value="Go">
</form>
Any ideas on how to make this work would be greatly appreciated.
I want to break up a set of search results into small chunks. For instance, think about how Google displays a block of ten results out of the entire set. Selecting the top 10 is dead easy with "TOP 10"; how do I select the next 10?
Obviously one option would be to select the top 20, and programmatically discard the first 10, but surely there is a better way? I am doing this for a ASP.NET 2.0 application, and if I can retrieve just what I want, I can DataBind to a Repeater, and let ASP.NET do all the hard work of displaying the data.
hi friends,
I have search in my project based on strings(surname, firstname).. but it does'nt works wen name consists of only two letters.... could somebody please help me on this.
Thanks sandeep
In the below section of code I'm connecing to a SQL database and when I use the "=" in the search it works ok, but when I try to add the ability to use wildcards by changin "=" to "LIKE" its not working
Working code:
Function GetName(ByVal first_Name As String) As System.Data.DataSet
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='FamilyInfo'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT Names.* FROM Names WHERE (Names.First_Name = @First_Name)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_first_Name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_first_Name.ParameterName = "@First_Name"
dbParam_first_Name.Value = first_Name
dbParam_first_Name.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_first_Name)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End Function
Code that I would like to work
Function GetName(ByVal first_Name As String) As System.Data.DataSet
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='FamilyInfo'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT Names.* FROM Names WHERE (Names.First_Name LIKE '%' + @First_Name '+')"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_first_Name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_first_Name.ParameterName = "@First_Name"
dbParam_first_Name.Value = first_Name
dbParam_first_Name.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_first_Name)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End Function
Any help would be greatly appreciated
Hi
Issue1:
I have a sql 2k machine. I hav enabled full text indexing on this machine. There after, i inserted and updated so many records. When i do a search using the below query:
It gaves me nothing
select *
from table1
where contains
(
col1,'"keyword"'
)
Issue 2: Another issue is when i gives multiple columns in the below query for search, it throws up a syntax error message at (
select *
from table1
where contains
(
(col1,col2,col3),'"keyword"')
)
Do sql 2k does not allow this format of query(above). But, the same is working fine with one column.
Any inputs on this is highly appreciated.
Thanks!
Santhosh
I have Sql server 2005 SP2.
I enabled it for Full Text search. Substring search where i enter *word* doesn't return any row.
I have a table testtable where description has word Extinguisher.
If i run a query with *ting* it doesn't return any row.
select * from testtable where contains(description,'"*xting*"') ;
But it works if i do
select * from testtable where contains(description,'"Exting*"') ;
The Full text search document says it supports substring search.
Is it an issue with sql server 2005?Please help.
Hi Guys,
I have two tables called table1 and table2.
table1 has search words and table2 has file names as below and want to get file names from table2 those match with all search words.
table1
----------------------
-searchword- column name
--------------------------------------------
Learn more about melons row0
--------------------------------------------
%.txt row1
-------------------------------------------
table2
------------------------------
-testname- column name
--------------------------------------------
FKOV43C6.EXE
-------------------------------------------
frusdr.txt
-------------------------------------------
FRUSDR.TXT
------------------------------------------
SPGP_FWPkg_66G.zip
------------------------------------------
readme.txt
-----------------------------------------
README.TXT
----------------------------------------
watermelon.exe
----------------------------------------
Learn more about melons read me.txt
-------------------------------------------------------
Here is the script what I have tried...............I hope some one will help to come out this loop.Thanks in advance.
===============================================================================
select * from @table2 t2 where t2.[testname] in (
SELECT tb.[testname] FROM @table1 ta
JOIN @table2 tb ON '% ' + tb.[testname] + ' %' LIKE '% ' + ta.searchword + ' %'
group by tb.[testname] having count(*) = (SELECT COUNT(*) FROM @table1)
)
===============================================================================
script to create tables
============================================================================
DECLARE @table1 TABLE (
searchword VARCHAR(255)
)
INSERT INTO @table1 (
searchword
) VALUES ( 'Learn more about melons' )
INSERT INTO @table1 (
searchword
) VALUES ( '%.txt' )
DECLARE @table2 TABLE (
testname VARCHAR(255)
)
INSERT INTO @table2 (
testname
) VALUES ( 'FKOV43C6.EXE' )
INSERT INTO @table2 (
testname
) VALUES ('frusdr.txt' )
INSERT INTO @table2 (
testname
) VALUES ('FRUSDR.TXT' )
INSERT INTO @table2 ( testname
) VALUES ( 'SPGP_FWPkg_66G.zip' )
INSERT INTO @table2 (
testname
) VALUES ( 'readme.txt' )
INSERT INTO @table2 (testname
) VALUES ('README.TXT' )
INSERT INTO @table2 (testname) VALUES (
'watermelon.exe' )
INSERT INTO @table2 (
testname
) VALUES ('Learn more about melons read me.txt' )
SELECT * FROM @table2
DECLARE @table3 TABLE (
testname VARCHAR(255)
)
INSERT INTO @table2 (
testname
) VALUES ('Melon release NOTES 321.xls' )
===================================================================================
Say I want to search for a range of account numbers but only which are active. After I set my field for A (active) this field shows in my results, I dont want it to.
In Access you can easily just uncheck that field in design view, but how do I do it in sql?
In sql server 2000 - our QA pointed out that his testing for empty strings returned 200 + rows but that when he clicked in the field there were obviously a space there. This issue came up because of the script I created to replace and earlier one that queried on empty strings instead of datalength and the earlier script always reported that it had updated x number of rows regardless of how many times it was run on the same database.
QA query based on the earlier script:
Select * from StringTable
WHERE (LongString = '' OR LongString IS NULL)
My script:
The fields are nvarchars in the newer database but older version of the database had varchars. I had created a script to replace empty strings as follows:
-- if LongString column is varchar - run varchar update else nvarchar update
If exists (Select * from sysobjects o
inner join syscolumns c on c.id = o.id
where c.name = 'LongString' and o.name = 'StringTable' and c.xtype = 167) begin
-- update varchar LongString
UPDATE StringTable
SET LongString = char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(LongString ) < 1) OR LongString IS NULL)
END
Else Begin
-- update nvarchar LongString
UPDATE StringTable
SET LongString = char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(LongString ) < 2) OR LongString IS NULL)
END
If exists (Select * from sysobjects o
inner join syscolumns c on c.id = o.id
where c.name = 'ShortString' and o.name = 'StringTable' and c.xtype = 167) begin
UPDATE StringTable
SET ShortString= char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(ShortString) < 1) OR ShortString IS NULL)
END
Else Begin
-- update nvarchar ShortString
UPDATE StringTable
SET ShortString= char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(ShortString) < 2) OR ShortString IS NULL)
END
My method for checking for datalength appears to work correctly why doesn't the QA script? I thought it might have to do with the nvarchar used in the table but I changed the column to a varchar and still has the same issue.
Thanks
Microsoft could not have made SQL Server 2005 Reporting Services any harder given the wacky way you have to add web references in Visual Studio 2005 and then when you finally get the report services working as well as the C# program compiling and seeing the
Here are the steps for you poor souls like me:
When you create you C# program Maybe its the same thing for vb rightclick on the project
and click add Web Reference
To the right of the go button you must put in a path to:
http://<server>/reportserver/ReportExecution2005.asmx?wsdl
If you used the default ReportServer settings this should work You will need to make sure you have ReportServices working first but by entering this exact URL and press the go button allows you to acess the ReportExecutionServcie object ofcourse you need to properly rename it in the text box in the lower right labled: Web Reference Name: I renamed it to ReportExecution2005
Note us must add a using <myclassname>.ReportExecution2005; which allows access to the the components and allos compiling.
ReportExecutionService
object correctly and want to use them in C# program Microsoft makes you suffer further with problems getting the path in rs.LoadReport(ReportPath,HistoryID) function
I believe I have the correct path string from trial and error when I go to my report services site
It shows me which as a guess I thought might be derived from the webpage:
http://myserver.mydomain.com/Reports/Pages/Report.aspx?ItemPath=%2fReport+Project2%2fKSConcordanceErrorReport&SelectedTabId=PropertiesTab&SelectedSubTabId=GenericPropertiesTab
displaying:
SQL Server Reporting Services
Home > Report Project2 >
KSConcordanceErrorReport
/home/Report Project2/KSConcordanceErrorReport but that failed to work. Next I tried:
The RichTextBox stored:
The item '/home/Report Project2/KSConcordanceErrorReport' cannot be found. ---> The item '/home/Report Project2/KSConcordanceErrorReport' cannot be found.
/Report Project2/KSConcordanceErrorReport
??t was all that was returned when I tried this path so I believe but am (Not Sure) this is the reportpath to be used in rs.LoadReport function.
In the browser: I believe the catalog based reportpath should be
/Report Project2/KSConcordanceErrorReport
I set up a form with 3 fields:
TextBox: ReportPath so I could play with the path I provide
RichTextBox To store the output from rs.render.
RunButton1 to call up the render code see below:
Please note when I start the program in debug mode I get an error of:
Note the following message proceeds the execution:
The Project cannot be deployed because no target server
is specified. Provide a value for the TargetServerURL
property in the property pages for this project
Any help in getting output on this render function would be appreciated
Perhaps some of my work will help others struggling with this poorly documented service. Even the three poor books I had to work with did not help much. Does Microsoft really expect people to use this?
Here the relevant code. I will assume knowledge of Forms development I provide two pieces: 1 the calling function from the button presss event: and the method called:
richTextBox1.Text=EmbededReportx.Program.GetReportXML2005(
string.Format("http://myserver.mydomain.com/ReportServer/R
eportExecution2005.asmx"),ReportPathTextBox.Text);
public static string GetReportXML2005(string
ReportingServicesURL,string ReportPath)
{
ReportExecutionService rs = new
ReportExecutionService();
//windows authentication
rs.Credentials =
System.Net.CredentialCache.DefaultCredentials;
rs.Url = ReportingServicesURL;
byte[] result = null;
ParameterValue[] parameters = new
ParameterValue[1];
parameters[0] = new ParameterValue();
parameters[0].Name="user2report";
parameters[0].Value = "%";
string encoding, mimetype, extension;
Warning[] warnings = null;
string[] streamIDs = null;
try {
rs.LoadReport(ReportPath, null);
rs.SetExecutionParameters(parameters,
"en-us");
result = rs.Render("XML", null, out extension, out encoding,
out mimetype, out warnings, out streamIDs);
}
catch (SoapException e) {
return e.Message;
}
return System.Text.Encoding.ASCII.GetString(result);
}
Any help anyone could give me regarding this would be most appreciated:
Hello,
My full-text search isn't working at all! I have a temporary table with full-text indexing enabled where files are scanned for social security numbers. If it has one, the user will see a message that it believes it's found a SSN and won't upload it. There is only ever one row in this table, as we overwrite the contents upon each upload.
I'm testing this search, and it doesn't work. The table has the following columns:
attachemtId (int) - primary key
fileContent (image) - contents of the file
fileExtension (varchar) - extension of the file (this is always either ".pdf" or ".doc")
I created a .doc file that simply says "ssn", and then run the following query:
SELECT * FROM TempAttachment
WHERE CONTAINS(fileContent,'ssn')
and nothing is returned! I tried the same thing with a .pdf file, and same results.
I'm not sure if this is related, but earlier I had this issue where I had to reset permissions for the directory. I've tried removing the full-text index and adding it again, but that didn't do anything. I also checked error logs on the server, and there were no messages. Any help would be appreciated! Thank you!
Hi, I was wondering if any SQL Server gurus out there could help me...I
have a table which contains text resources for my application. The text
resources are multi-lingual so I've read that if I add a html language
indicator meta tag e.g.<META NAME="MS.LOCALE" CONTENT="ES">and
store the text in a varbinary column with a supporting Document Type
column containing ".html" of varchar(5) then the full text index
service should be intelligent about the language word breakers it
applies when indexing the text. (I hope this is correct technique for
best multi-lingual support in a single table?)However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.DECLARE @SearchWord nvarchar(256)SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column.Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.
I'd like to get some ideas for the following:
I am writing a quick mini-application that searches for records in a database, which is easy enough. However, if the search term comes up empty, I need to return 10 records before the positon the search term would be in if it existed, and 10 records after. (Obviously the results are ordered on the search term column)
So for example, if I am searching on "Microsoft", and it doesn't exist in my table, I need to return the 10 records that come before Microsoft alphabetically, and then the 10 that come after it.
I have a SP that does this, but it is pretty messy and I'd like to see if anyone else had some ideas that might be better.
Thanks!
Hi, Could you tell me if this is possible? How do I return results using an entire column as part of the search parameter? I need to do this in the sql rather than selecting the contents and iterating through it as it would take too long.
eg.
CREATE TABLE [dbo].[tPopupKeywords](
[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[description] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
INSERT INTO dbo.tPopupKeywords(title, description)
SELECT 'check', 'desc' UNION ALL
SELECT 'for', 'desc' UNION ALL
SELECT 'keywords', 'desc'
select dbo.tpopupkeywords.title
where 'This is an example of a passed in string to check if any keywords are returned.'
LIKE '% ' + dbo.tpopupkeywords.title + ' %' --Does this bit need to do a select??
expected results.....:
check
keywords
I have created a table(T1) from select query result, that Select query is parameterised. Now I need to update the select query table(T1) based on the result every time.
Below is my Query:
ALTER PROCEDURE [dbo].[RPT_Cost_copy]
SELECT MEII.*, SIMM.U_SP2DC, UPPER(SIMM.U_C3C2) AS GRP3,sb.cost, PREV.Z1, PREV.Z3, SB.Z2, SB.Z4,SIMM.U_C3DC1 AS FAM
INTO T1
FROM
(SELECT a.meu, a.mep2, SUM(a.mest) as excst
FROM mei as A WHERE a.myar=@yr and a.mprd=@mth AND LTRIM(A.MCU) <> '' AND LTRIM(A.MRP2) <> ''
[code]....
Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g.
<META NAME="MS.LOCALE" CONTENT="ES">
and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)
However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.
DECLARE @SearchWord nvarchar(256)
SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.
SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)
I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column.
Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.
We have a table that is Full Text Search index enabled on one column.This table has 200 lakhs of rows(20000000) . ContainsTable() function is searching data with in these 200 lakhs of rows(20000000), if any new rows are inserted then the ContainsTable is not going to search in these recent inserted rows.
We observed when we try for a data to search. it is returning the rows till the rows that are inserted date is less than 30th of march 2012. but not searching in the records that are created after April month , if even the data we are searching is available .
TableFulltextItemCount is around 2.2 crores.
Then we done rebuilt the FT catalog Index. then the TableFulltextItemCount became 0.Again we run the containstable query ,but still it is not getting results.
As the no of rows are very more . so i am not able to show the actual rows from which the data is not coming.
the below query gives 2 results that are from actual base table
HTML Code:
select * from g_case_action_log where cas_details like '%235355%' and product_id = 38810
To search for the same above word using FTS,I have used the query as below
HTML Code:
SELECT Distinct top 50 cal.case_id,cal.cas_details From g_case_action_log cal (READUNCOMMITTED)inner join containstable(es.g_case_action_log, cas_details, ' ("235355" OR "<br>235355" OR "235355<br> ") ') as key_tbl on cal.log_id = key_tbl.[key] Where cal.product_id = 38810 ORDER By cal.case_id DESC
I have attached one sql script file for your ref that contains create logic and index schema properties
Why it is not returning results all the time.