How To Search A Database By Paramaters
May 24, 2006
Hi,
Does anyone know how i go about writing a search query using paramaters, ive been using this as my template:
Select Distinct Recipedata.Name, Ingredients.Ingredient, Directions.Directions
from Recipedata,Ingredients,Directions
Where Ingredients.Ingredient Like '%Chicken%' and
RecipeData.RecipeID = Ingredients.RecipeID and Ingredients.RecipeID = Directions.RecipeID;
Problem is now that i want to use paramters instead of hard coded values as this is for a user. How do i go about this? This database has a VB2k5 front end for the desktop and PDA(and no the target PDA is not web enabled so web servcies arent available to me).
Thanks Much
View 6 Replies
ADVERTISEMENT
Aug 14, 2006
I'm having trouble using paramaters in a full-text search with FormsOf(INFLECTIONAL:DECLARE @SearchWord nvarchar(4000)
SET @SearchWord = 'tax'
SELECT listing_id, RANK, name, address, city, zip, heading, phone
FROM listings a,
FREETEXTTABLE(listings, *, 'FormsOf(INFLECTIONAL, @SearchWord')
WHERE [KEY] = a.listing_id
ORDER BY RANK DESC, name
This returns no results. It treats the paramater as a NULL value. But this...
SELECT listing_id, RANK, name, address, city, zip, heading, phone
FROM listings a,
FREETEXTTABLE(listings, *, 'FormsOf(INFLECTIONAL, tax')
WHERE [KEY] = a.listing_id
ORDER BY RANK DESC, name
returns over 500 results. I've been banging my head against this for 2+hours. Google has been no help as every example shows the result hard-coded in like the second example.PLEASE HELP!!!!!!
????????
View 4 Replies
View Related
May 10, 2007
I need to setup some asp security databases and I have seen several sets of instructions. Some say "Navigate to C:WINDOWSMicrosoft.NETFrameworkv2.0.50727" and some say at the "asp command box". I do know that the directory & file exists on my machine. I have run aspnet_regsql.exe to configure my local server. I now need to run it to configure my web host SQL Server 2005 and that requires some paramaters like "aspnet_regsql.exe -S [DB Server Name] -U [DB login] -P [Password] -A all -d [Database name]". I am old and started before windows. I know DOS. But obviously not well enough.
How do you "Navigate to C:WINDOWSMicrosoft.NETFrameworkv2.0.50727"?
If I say "CDWINDOWS", it works.
When I am at the "C:WINDOWS>" prompt and say "CD Microsoft.NET" it says "invalid directory"
When I am at the "C:>" prompt and say "CDWINDOWSMicrosoft.NET" it says "invalid directory"
When I am at the "C:>" prompt and say "CDWINDOWSMicros~1" it says "invalid directory"
Thanks for your help. John Brown
View 1 Replies
View Related
Dec 18, 2007
Hi, Just wondering if I could make sure that "hackers" don't tamper with my querystring - which is a parameter for a SQL query. i.e... Dim ListOfValues as string = request.querystring("listOfValues")
'Output would be this: 324234,5445,554654,45632,SQL command : Delete From table where product_id IN (@ListOfValues)
cmd.paramaters.addwithvalue("@ListOfValues", ListOfValues )
How can I validate it so that hackers can't add any characters other than 'numbers' and ' , ' to the sql parameter? I have tried to tryparse the 'ListOfValues ' as an integer - by replacing "," with "" but an integer overflow occured. Any questions or ideas? Thanks
View 6 Replies
View Related
Jan 17, 2007
I'm writing a search query in TSQL for MS SQL. Here is my code:
SELECT a.*, b.username, c.categoryname FROM
video a
inner join
user b
on
a.userid = b.userid
inner join
video_category c
on
a.categoryId = c.categoryid
where
a.videotitle like '%@SearchText%'
OR
a.description like '%@SearchText%'
This returns no results, but wheN I replace the '%@SearchText%' with my search text inplace of the @SearchText it returns retuls. I have a feelign I am passing the params to the LIKE clause incorrectly. Any suggestions?
Thanks,
-- shawn
View 5 Replies
View Related
Dec 21, 2007
I want to do some error checking on the parameters found in a SQLDataSource before I run the insert. The problem is these are ControlParameters and I want to do this dynamically so I can't just call the Control.Text property and grab its value. So how can I get access to what the ControlParameter evaluates to?
Secondly, is there a way to access what the update parameters evaluate to in order to check them before they're inserted - if so how do I get access to these?
Here's an example of one of the data sources i'm using:<asp:SqlDataSource ID="sqlContact" runat="server" ConnectionString="<%$ ConnectionStrings:strConn %>"
SelectCommand="SELECT [ContactID], [FirstName], [LastName], , [Address], [Phone], [Grade], [Contacted], [ListServe] FROM [Contact]"
UpdateCommand="UPDATE Contact SET FirstName = @FirstName, LastName = @LastName, Email = @Email, Address = @Address, Phone = @Phone, Grade = @Grade WHERE ContactID = @ContactID"
DeleteCommand="DELETE FROM [Contact] WHERE ContactID = @ContactID"
InsertCommand="INSERT INTO [Contact] ([FirstName],[LastName],,[Address],[Phone],[Grade],[Contacted],[ListServe]) VALUES (@FirstName,@LastName,@Email,@Address,@Phone,@Grade,0,0)">
<UpdateParameters>
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="Address" Type="String" />
<asp:Parameter Name="Phone" Type="String" />
<asp:Parameter Name="Grade" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:ControlParameter Name="FirstName" Type="String" ControlID="txtContactFirst" PropertyName="Text" />
<asp:ControlParameter Name="LastName" Type="String" ControlID="txtContactLast" PropertyName="Text" />
<asp:ControlParameter Name="Email" Type="String" ControlID="txtContactEmail" PropertyName="Text" />
<asp:ControlParameter Name="Address" Type="String" ControlID="txtContactAddress" PropertyName="Text" />
<asp:ControlParameter Name="Phone" Type="String" ControlID="txtContactPhone" PropertyName="Text" />
<asp:ControlParameter Name="Grade" Type="String" ControlID="ddlContactGrade" PropertyName="SelectedValue" />
</InsertParameters>
</asp:SqlDataSource>
An Event is fired when I click add on a button which looks similar to this:
//Add btnAddContact_Clickprotected void btnAddContact_Click(object sender, EventArgs e)
{
InsertRow(sqlContact);
}The InsertRow() function is then what i'm using to evaluate the values... So how can get the values those controlparameters actually are in order to evaluate them before I actually insert. Or is there a better way to do it?
View 4 Replies
View Related
Mar 1, 2007
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
View 5 Replies
View Related
Nov 22, 2007
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
View 6 Replies
View Related
Aug 9, 2006
I'm just wonder if this is a bug in MS Search or am I doing something wrong.
I have a query below
declare @search_clause varchar(255)
set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'
select * from results
where contains(finding,@search_clause)
I don't get the correct result at all.
If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"
then i get the correct result.
It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?
Anyone know?
Thanks
View 3 Replies
View Related
Jul 19, 2006
Hi All!I have a Table with name "Mytable" and fields as below: Tittle Date Category
View 5 Replies
View Related
Aug 2, 2007
Hi
I would like to know whether any tools are there to search in a database. Ex. i am using sql server2005 and in my db, more than 1000 tables r there.
i want to search for a perticular column. This search should be on tables, sps, functions, triggers.....etc.
If anybody aware of any tool for this or any code in dotnet to develop such tool, pls let me know.
Regards
Sanjay
View 3 Replies
View Related
Jun 19, 2004
I design table which has column "keywords" for keep keywords seperated by comma (,). If user pass one keyword, how can I know that the keyword user select is exactly match with any keyword in field. I mean I just want SQL return only rows that user's keyword is exactly match with at least one keyword in "keywords" column.
Ex.
PK1 Data1 Data2 keywords
1 1 1 apple, orange
2 2 2 mange, orange
3 3 3 mange, apple
If user select keyword 'orange', SQL must return only row 1 and 2.
View 5 Replies
View Related
Sep 6, 2006
--Author:=> Kapil Choudhary Jaipur (India)
--Motive:- Search A Text value In A DataBase With The Table Name.
create procedure [search_value]
@fstr nvarchar(128)
with encryption
as
set nocount on
declare @rc int --Counter Variable For The Cursor 1.
declare @rcc int --Counter Variable For The Cursor 2.
declare @tc nvarchar(128)--TABLE_CATALOG.
declare @ts nvarchar(128)--TABLE_SCHEMA.
declare @tn nvarchar(128)--TABLE_NAME.
declare @tt nvarchar(128)--TABLE_TYPE.
declare @Ctn nvarchar(128)--Column Table Name.
declare @Cts nvarchar(128)--Column Table Schema.
declare @Ccn nvarchar(128)--Column Column Name.
declare @Cdt nvarchar(128)--Column Data Type.
declare @currow1 int--Total Row Count For The Cursor 1.
declare @currow2 int--Total Row Count For The Cursor 2.
declare @str nvarchar(128) --Dynamically Created Query String.
declare @strresult nvarchar(128)--Value Stored From Temp Table.
declare @findwhat nvarchar(128) -- String To Be Searched.
set @rc=1
create table #temp (colval nvarchar(128))
create table #myresult(Table_Name nvarchar(128),Column_Name nvarchar(128),Column_Value nvarchar(128))
declare mycur1 cursor static for
select TABLE_CATALOG,TABLE_SCHEMA ,TABLE_NAME,TABLE_TYPE
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
open mycur1
set @currow1=@@cursor_rows
--print 'table rows = '+str(@currow1)
while @rc<=@currow1
begin
fetch next from mycur1 into @tc,@ts,@tn,@tt
declare mycolcur cursor static for
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE
from information_schema.columns
where TABLE_CATALOG=@tc and TABLE_SCHEMA=@ts and TABLE_NAME=@tn and data_type in('char','varchar','nchar','nvarchar')
open mycolcur
set @findwhat='wa'--Your Search Value Here
set @rcc=1
set @currow2=@@cursor_rows
--print 'table col = '+str(@currow2)
--print 'Table name'+' '+'owener'+' '+'Column Name'
--print '-----------------------------------------------'
while @rcc<=@currow2
begin
fetch next from mycolcur into @Cts,@Ctn,@Ccn,@Cdt
-- print @ctn+' '+@cts+' '+@ccn
set @rcc=@rcc+1
set @str='select '+quotename(@ccn) + ' from ' +quotename(@cts)+'.'+ quotename(@ctn) + ' where ' + quotename(@ccn) +' like '+char(39)+@findwhat+char(39)
set @str='insert into #temp(colval) '+@str
--print @str
exec (@str)
if exists(select * from #temp)
begin
select @strresult= colval from #temp
--print 'colval =========>>>>> ' + @strresult
delete from #temp
insert into #myresult(Table_Name,Column_Name,Column_Value) values(@ctn,@ccn,@strresult)
end
end
close mycolcur
deallocate mycolcur
set @rc=@rc+1
end
--print @@cursor_rows
select * from #myresult
close mycur1
deallocate mycur1
drop table #temp
drop table #myresult
set nocount off
Go
View 1 Replies
View Related
Jul 1, 2007
Do someone know good tutorial on web with examples for search data from SQL database?
View 2 Replies
View Related
Jan 22, 2008
well i am using vb.net and would like to know how to search a specific table in the databasefor egif table1 exists in database then drop table1end if ...something like that
View 2 Replies
View Related
Jun 22, 2005
Hi guys, Now I upload the PDF file and store it in the database as binary. But now I need to search the text in the PDF file. Is there any way I can search the content in the database ? Anyone has any idea about it ?Thanks!Regards,Sam
View 9 Replies
View Related
Mar 22, 2006
I'm trying to make this piece of code work, I get all of it except how I get the data from the database, what I wish to do is. On page load I wish to take the value of a querystring in the page URL called ProductID and search a database to see if it exists in a specifical table and column, if it exists I wish to make a button not visible. the code I am using is:
#############
protected void Page_Load(object sender, EventArgs e)
{
// Get the querystring value
String inQueryString = Request.QueryString["ProductID"];
// Get the data from the database, using the above value
DataTable data = ??
The datasource I want to connect to is SqlDataSource1, and the table I want to search is “Reviews�. I want to search in the column “ProductID� and search for the ProductID from the URL query string above.
// Did we find a product in the database?
if (data != null &&
data.Rows.count > 0)
{
// Code to display the product's information
// We found a product, so we need to hide a button
Button5.Visible = false;
}
}
##########################
Please can somebody fill in the missing bit for me so i know how it should be, i've searched the quickstart tutorial on the menu above and I still don't understand it, as it doesn't show anything like what I want to do, and I have looked at numerous websites about the matter. If you don't have time that is okay, but If somebody does have the time to show me what I need, I would be very greatfull.
Regards
Daniel coates
View 1 Replies
View Related
Mar 8, 2006
Hello
I have a Database which contains like 1000 Tables.
I am not the designer of that DB.So I need to in which table and which column
that string exists. IS there a DBWIDE String search possible?
Thanks and Regards
View 8 Replies
View Related
Feb 15, 2007
Is there any speicial considerations when you prepare the maintanance Plan for the Search Database.
View 3 Replies
View Related
Mar 19, 2008
Hi all.
I have a huge problem that ive been sitting with for awhile.
I have a web page with 4 dropdownlist boxes on it
Gender:
Race:
Field:
Location:
Now a user can search a SQL db from selecting values from these 4 dropdownlist boxes which are equal to values in the db obviously.
when they have done that and pressed submit a Gridview is populated with the people corresponding details and the user is able to view each row seperately.
Now the problem i am having is that when i havent used all 4 selections for eg
Gender: null (no value selected)
Race: Black
Field: Accounting
Location: Los Angeles
then no information is returned from the db in the gridview.
im using a sqldatasource to populate the gridview and here is the query string that i am using :
Code:
sql
SELECT [title], [gender], [initials], [name], [surname], [birthdate], [postaladdress], [suburb], [city], [zipcode], [criminalrecord], [drivers], [maritalstatus], [dependants], [citizenship], [province], [contactref], [hometel], [cell], [jobtitle], [relocate], [emmigrate], [email], [worktel], [enddate], [startdate], [FIELD], [education], [company], [positionheld], [jobdescription], [contactperson], [contacttel], [startdate2], [contactperson3], [jobdescription3], [positionheld3], [company3], [enddate3], [startdate3], [contacttel3], [other] FROM [cvinformation] WHERE (([race] = CASE WHEN @race IS NOT NULL THEN @race ELSE [race] END) AND ([province] = CASE WHEN @province IS NOT NULL THEN @province ELSE [province] END) AND ([education] = CASE WHEN @education IS NOT NULL THEN @education ELSE [education] END))
what i want to do is whether the user doesnt choose any selection and leaves the choice null that ALL the information in the table of the db be shown and even if they only choose 2 values and leave the others null then it still brings back the information from the criteria chosen..
Is this possible.?
View 8 Replies
View Related
Jul 9, 2004
Hi folks,
Whts up........??? M back, after a long gap.
I have come across with a major issue. And u know wht th issue is.........???
It is about th DATABASE SEARCH TOOL. I have a database of around 30 tables. Now I wud like to have aa search engine on my .asp page. There will be a text box on th page and one submit button. After typing some text in th text area n submitting th page, my package sud check tht perticular text in all th COLUMNS of all th TABLES, n whrevr it gets a match (exactly same, or by speech recognition), it sud through th links on th next page.
Nw i wud like u guys to take this problem, at th earliest n come up with a up to th mark solution.
Thnkx
View 12 Replies
View Related
Aug 26, 2004
Hi,
I have a sql server database with 250 tables in it, i wanted to find one particular value from one of the tables in the database. How to find that appreciate your help..
Thanks
View 1 Replies
View Related
Nov 24, 2006
Hi I was wondering if anyone can help me out here. I want to search a word in the database which has more than 80 tables is it possible to search all the tables at once for that word.
I am using SQL2005.
Thanks in advance.
View 2 Replies
View Related
Oct 8, 2007
Yep, I know this will have been asked before (because I've seen it), but I can't for the life of me find a thread!
So I've inherited this database and I need to search the whole darn thing for a single word, returning something lovely, like the table or column the thing resides in.
Basically it's a code in one of 100+ code tables, all with inconsistant, unclear naming - YAY!
FYI, I'm using SQL Server 200 and the word I'm looking for is "Unpaid".
Thanks guys, girls and gurus
View 5 Replies
View Related
Nov 4, 2007
Hi,
Can any one say how to search a particular column in a database so that the tables containing the column are listed
View 3 Replies
View Related
Jul 20, 2005
Hi,I would like to use keywords inside a record to search for - like asearchengine.How can I design the table? Right now I would store data in Memo-Column in aformat like ";apple;banana;lemon;". I would use a INSTR(ring)-function - butI wonder if the performance is still OK then - even when I index the field.Are there maybe even better ways?Thanx in advance.Greg
View 4 Replies
View Related
Mar 28, 2008
Hi, i want user to type a string in textbox, and i will search, if textbox.text exists in my SQL database table or not. Below is the code i'm using :---------------
string txtname = (txtEmpName.Text).Trim();
sQuery = "select * from tbUser where UserName='_username'";
sQuery = sQuery.Replace("_username", txtname);
SqlConnection objConnection = new SqlConnection(source);
objDataAdapter = new SqlDataAdapter(sQuery,objConnection);
// DataTable dtTable = new DataTable();
if (objConnection.State == ConnectionState.Closed)
objConnection.Open();
objDataSet = new DataSet();
objDataAdapter.Fill(objDataSet);
// objDataAdapter.Fill(dtTable);
or i want to search if "txtname" exists in "tbUser" table.
Thanks & Regards.
Manish S. Anand
View 9 Replies
View Related
Mar 14, 2008
Hii
I want to search for a keyword in the database.
The database has approximately 30 tables and the amount of data in tables is very large. Most of the tables hold 25000 rows. The search procedure for searching a keyword that i want to use is as below. When i executed the stored proc it took 20 minutes. What i want to know is full-text search a better option than this or is there any other way out.
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
EXEC SearchAllTables 'FileName'
View 7 Replies
View Related
Aug 17, 2007
Hi guys,
I'm trying to find some attributes within a database I'm working with. I'm working with SQL Server 2000, and I'm trying to find table which have a certain attribute in them. for example, If I want to see tables from my database which have an attribute called 'home_address', what would the query be? Or is there an inbuilt function in SQL Server 2000 which allows me to search this?
Thank you!
View 4 Replies
View Related
Oct 19, 2006
Hi all:
what i am trying to do is search one column name in whole database. i used to achieve this by using Query Analyzer .
Can anyone tell me does this function available in SQL 2005?
Thanks
Nick
View 9 Replies
View Related
Jun 28, 2006
Hi,I would like to have a full-text search from a sql server database in my aspx page. I have tried to do it by creating a Dataset with a DataTable where there is a query like:
SELECT title, yearFROM ProjectsWHERE title like N'%@Search%'
Problem is with the like operator - the visual studio does not reconize the @Search as a parameter, as it reconnizes it in the query below (which works fine by the way):
SELECT Projects.*, theme AS Expr1FROM ProjectsWHERE (theme = @theme)
Can anybody help me how to deal with this problem, which is the best way to have a full text search?
Cheers, ArtoV
View 1 Replies
View Related
Nov 28, 2006
hello..i need your help to provide me the idea and code how to view the data from database based on search criteria from user's input?the user's may fill up textbox and choose a criteria from dropdownlist...i am so no idea how to code it since i am fairly new in asp.net
View 5 Replies
View Related
Apr 11, 2007
Hi I have created a simple application base on the video based on the 'SQL Server 2005 Express for Beginners' video No. 8.It is working great but I would like to add another feature; if an emial address (in my case userName) is entered that already exists in the database I would like to display an message say you have already subscrided.How do I search the database for a duplicate email address? Please keep it simple I am still finding my feet with aspx and c#.Cheers protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
SqlDataSource profilesDataSource = new SqlDataSource();
profilesDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["profilesConnectionString1"].ToString();
profilesDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
profilesDataSource.InsertCommand = "INSERT INTO staffprofiles (userName, fullName, nickName, favouriteSaying, memorableMoment, song, farewell) VALUES (@userName, @fullName, @nickName, @favouriteSaying, @memorableMoment, @song, @farewell)";
profilesDataSource.InsertParameters.Add("userName", userNameTextBox.Text);
profilesDataSource.InsertParameters.Add("fullName", fullNameTextBox.Text);
profilesDataSource.InsertParameters.Add("nickName", nickNameTextBox.Text);
profilesDataSource.InsertParameters.Add("favouriteSaying", favSayingTextBox.Text);
profilesDataSource.InsertParameters.Add("memorableMoment", memMomentTextBox.Text);
profilesDataSource.InsertParameters.Add("song", songTextBox.Text);
profilesDataSource.InsertParameters.Add("farewell", farewellTextBox.Text);
int rowsAffected = 0;
try
{
rowsAffected = profilesDataSource.Insert();
}
catch (Exception ex)
{
// Report a problem
Server.Transfer("submit_problem.htm");
}
finally
{
profilesDataSource = null;
}
if (rowsAffected != 1)
{
// Report a problem
Server.Transfer("submit_problem.htm");
}
else
{
// Everything was OK
Server.Transfer("submit_success.aspx");
}
}
View 3 Replies
View Related
