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).
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!!!!!!
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
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)
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
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?
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?
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
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.
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.
--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
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
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
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.
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
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..
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.
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
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.
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".
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
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.
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.
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?
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
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
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();
if (rowsAffected != 1) { // Report a problem Server.Transfer("submit_problem.htm"); } else { // Everything was OK Server.Transfer("submit_success.aspx"); } }
