I have four criterias in my .aspx page. They are "First Name, Last Name, Title, Year". I have a Book and an Author table. The Author table would contain all the author's information and the Book table contains all the books information such as title, publisher, subject, and so on. So here's what I'm trying to do.
I want to write a transaction statement that will query the four criterias above if the criteria textbox is not black. So for example, when the user click the submit button, all the four criteria fields are filled except Year. That means the query would search the Author and Book tables for "First Name, Last Name, Title" but not "Year" for any potential matches. I also wanted to use "Like" instead of "=" for a wider search.
Actually I'm trying to create a store procedure that will accept those four criterias and search the tables based on those criterias.
i am working for a Library Project. in that project i want to search Book by putting book content. in book content there could be single quatation e.g William's. when ever i put single quatation my string type is terminated and my query is not executed successfully. select Book_ID,Title,Auther from book_details where Contents like '%xyz%' here xyz is my object name. if i put William's in xyz it does not work. plz tell me how to solve this problem. Jasim... New Delhi, INDIA
dear friend, the problem i am finding is moreover a concatenation problem what i did is that have stored procedure which is accepting any character and i have to show all the records where this character is existing it is like this only ******************************** name1 like '% @str %' but then it is not treating the str as a variable and treating it as a string so if you have any solution please let me know early please give the answer with example waiting for reply ashish bhatnagar
I am working on SQL Server in VB 2008. I have a table 'Records' having 8 columns. I have a search page where I can choose 5 different parameters to search as 'Category' , 'Name' , 'Date' etc.
I can successfully search with a single criteria selected either Category Name Or Date. But I want to create a single SQL command that can search my 'Records' table for either two or all the parameters depending on the selections made by the user.
I am trying to set up a query that will allow the user to input a string, and the search will match ANY word in that string. Currently, I have it configured so that the search will only match the exact string that the user inputs. I have google searched for the answer, but no luck yet. Any ideas?
DECLARE @ID UNIQUEIDENTIFIER, @SOMEDATE DATE SELECT * FROM myTable WHERE ID = @ID AND DATEFIELD=@SOMEDATE I want to pass values to @ID and @SOMEDATE, such that it meets the WHERE criteria for all values in the respective fields.
What parameter value should I pass such that all values are selected? In the actual SP, I have uniqueidentifier, varchar and date parameters.
where content like '%EditLiveJava%' or content like '% Sys__%' ESCAPE '_' or content like '%<div class="row"/>%' or content like '%<a href="" title=""%' or content like '%cmsprod%' or content like '%Error processing inline link%' or content like '%see log for stack trace%'
I output the content field if the search is true but would like to also output which specific 'like' has been found.
Can I do this in the one pass or do I have to read the database separately for each condition?
I have a store procedure that search by Firstname and Lastname. I want it search by either both (Firstname and Lastname) or any of them. For example if only FirstName passes to it shows all the record with that Fistname. Currently I have to pass both Firstname and Lastname to my store proc to get the result.
This is my stor proc:
USE [CustomerPortal] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[CSA_Search_Customer_By_Name]
I am trying to create a query that will show me who is phoning who in anorganisation from available Telephone Billing information. I am creating aMSAccess 2000 database with a few few tables, two of which are:TableMembers: (containg fields Refs, DateCreated, MembershipNo,OfficeLocation ...NB: Refs has a Primary Key - No Duplicates)TablePeople: (containing fields: Refs, Name, Addr, TelHome, TelWork,TelMobile & TelFax)TableTelBills: (containing fields: Refs, TelNo, DateCalled, Duration,TelType)I am trying to create a query that will use a simple searching criteria eg.,Like "*" [Enter the Tel No or part Tel No to search:] & "*"to search all the Tel fields in the TablePeople and TableTelBills (TelHome,TelWork, TelMobile, TelFax and TelNo) but am running in difficulties.I start by creating a query and adding the tables TablePeople andTableTelBills and TableMembers.I use the Refs from the Table Members as a base criteria but do not know howto create criteria that will search all Tel fields at once!I would appreciate any and all help people!Jan
I'm trying to figure out why I am not getting any result set back from a search that includes non-alphanumeric or non-printable characters. For instance, if I have a table with a 20 character name column with names with beginning ranges from A-Z, why doesn't the following return any rows: select * from table where name < CHAR(126). In the ASCII character set, 126 is a tilde (~) which is numerically above the alphanumeric ranges 1-9,a-z, and A-Z. Shouldn't all records that sort lower in the character range be included in the result set ?
I'm assuming this has something to do with the default collation sequence being used which somehow does not include characters outside the alphanumeric range. Any ideas ?
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?
Hi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen
SELECT * FROM TableA A JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID WHERE B.SomeParamColumn = @SomeParam
SELECT * FROM TableA A JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam
Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?
hi! I'm just wondering does anyone know how to create an sql command that can search word by word? what i mean is like I have a product with name 'harry potter broom'. I want an sql command where if i type only 'harry broom' this 'harry potter broom' product will show up. Does anyone have any idea? Here's my sql comand: (I'm using asp.net vb script do develop this system) ------------------------------------------------------------------------- query = "select distinct * from product where " & _ "(pname like '%" & keyword & "%' or " & _ "pdesc like '%" & keyword & "%' ) and " & _ "(price >= " & price1 & " and price <= " & price2 & _ ") and status <> 'out of stock' order by price asc" ------------------------------------------------------------------------- Thank you.
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
Our clients want to be able to do full text search with a single letter. (Is the name Newton, Nathan, Nick?, Is the ID N1, N2...). Doing a single character full text search on a table work 25 out of 26 times. The letter that doesn't work is 'n'. the WHERE clause CONTAINS(full_text_field, ' "n*" ') returns all rows, even rows that have no 'n' in them anywhere. Adding a second letter after the "n" works as expected.
Here is an example
create table TestFullTextSearch ( Id int not null, AllText nvarchar(400) ) create unique index test_tfts on TestFullTextSearch(Id); create fulltext catalog ftcat_tfts;
I have a scenario of where the standard Full-Text search identifies keywords but Semantic Search does not recognize them as keywords. I'm hoping to understand why Semantic Search might not recognize them. The context this is being used in medical terminology and the specific key words I noticed missing right off the bat were medications.
For instance, if I put the following string into a FT indexed table
'J9355 - Trastuzumab (Herceptin)' AND 'J9355 - Trastuzumab emtansine'
The Semantic Search recognized 'Herceptin' and 'Emtansine' but not 'Trastuzumab'
Nor in
'J8999 - Everolimus (Afinitor)'
It did not recognize 'Afinitor' as a keyword.
In all cases the Base of Full-Text did find those keywords and were identifiable using the dmvsys.dm_fts_index_keywords_by_document.It does show the index as having completed.
why certain words might not be picked up while others would be? Could it be a language/dictionary issue? I am using English and accent insensitive settings?
would you use sql server "full text search" feature as your site index? from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on... so i thought create site search table, with the columns: id, text, url and to write every thing to this table. but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server... what do you think?
I have installed the Adobe iFilter 11 64 bit and set the path to the bin folder. I still cannot find any text from the pdf files. I suspect I am missing something trivial because I don't find much when I Bing for this so it must not be a common problem.Here is the code.
--Adobe iFilter 11 64 bit is installed --The Path variable is set to the bin folder for the Adobe iFilter. --SQL Developer version 64 bit on both Windows 7 and Windows 8. USE master; GO DROP DATABASE FileTableStudy; GO CREATE DATABASE FileTableStudy ON PRIMARY
I have a table that contains words that will be used to search another table where FullText index has been created on searchable columns. I'm basically trying to run something like this:
SELECT t1.col1, t2.col3 FROM tbl1 t1, tbl2 t2 WHERE CONTAINS (t1.col1, t2.col1)
I know this won't work but is there a way to join these two tables so the words (t2.col1) can be passed as search conditions? There is no common key on both tables so normal join won't work. I'm trying to find a way to pass the search words from one table to another.
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:
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. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:
Description: An OLE DB Error has occured. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".
.........
Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".
This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:
private bool BuildPackage()
{
// Create the package object
oPackage = new Package();
// Create connections for the Foxpro and SQL Server data
MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);
Can anyone tell me how to search an SQL database for a given key word in a textbox? I basically have a database that has a qualifications column and this column needs to be searched for the data given in the textbox. Which is the best method to search for the data? Is it a simple SQL query or an XML based search engine type? Can anyone give any suggestions regarding this? If XML is efficient then how do I use it to query my database, as I'm pretty new in XML based searching.Thanks
hi there, i am doing a school project and i need to have this search engine that will search the data that i have stored inside the database and display the results out can anyone help? thanks