can someone show me some sample code on how to create a store procedure tha accept mulitple parameters (lname, fname, id, etc) to seearch records in a database .
thanks all
i have tried the following :
select*from user_tbl, claim_tbl
where user_tbl.temp_claim_tbl_seq = claim_tbl.temp_claim_seqand user_tbl.lname like ' ' /*@lname*/or user_tbl.fname like 'dan' /*@fname*/
does not work correctly ...............display duplicates rows .........not sure why
Dim Search As String Search = Request.QueryString("Search")
Dim strConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("Myconn")) Dim strSearch As String strSearch = "SELECT id,brand,catg,foto,ref,descrabrv FROM products WHERE CONTAINS(ref,@search) ORDER BY ref ASC" strConn.Open() Dim cmd As New SqlCommand(strSearch, strConn) cmd.Parameters.Add("@search", Search)
Dim adatp As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim daSet As New DataSet adatp.Fill(daSet) rstDG.DataSource = daSet rstDG.DataBind() strConn.Close()
The main thing ..this is a result page I got this error :
Prepared statement '(@search nvarchar(4000))SELECT id,brand,catg,foto,ref,descrabrv ' expects parameter @search, which was not supplied
I've created a search page which looks at and indexed sqlServer table called tblPage and looks for page_Name. For some reason it shows no results. Do you know of any good links I can look at which address this issue.
<%@ Page %><%@ import Namespace="system.Data" %><%@ import Namespace="system.data.SqlClient" %><script runat="server"> Sub Button_Click( a As Object, e As EventArgs ) Dim conPubs AS SqlConnection Dim strSearch As String Dim cmdSearch As SqlCommand Dim dtrSearch As SqlDataReader conPubs = New SqlConnection( "Server=*;UID=*;PWD=*; Database=*") strSearch = "SELECT RANK, Page_Name FROM tblPage, " & _ "FREETEXTTABLE( tblPage, Page_Name, @searchphrase ) searchTable " & _ "WHERE [KEY] = tblPage.Page_ID " & _ "ORDER BY RANK DESC " cmdSearch = New SqlCommand (strSearch, conPubs) cmdSearch.Parameters.Add ("@searchphrase", txtSearchphrase.Text) conPubs.Open dtrSearch = cmdSearch.ExecuteReader() lblResults.Text = "<table border=1 cellspacing=0 cellpadding=4>" While dtrSearch.Read lblresults.text &= "<tr>" lblResults.Text &= "<td align=right> " lblResults.Text &= ShowRank( dtrSearch("RANK")) lblResults.Text &= "</td>" lblResults.Text &= "<td>" &dtrSearch("Title") & "</td>" lblResults.Text &= "</tr>" End While lblResults.Text &= "</table>" conPubs.Close End Sub Function ShowRank ( intRank As Integer ) As String Dim intCounter As Integer Dim strRank As String strRank = "<font color=green>" For intCounter = 1 to intRank / 10 strRank &= "*" Next strRank &= "</font>" Return strRank End Function </script><html><head></head><body> <form runat="server"> <h2>Search Titles: </h2> <p> <asp:TextBox id="txtSearchPhrase" runat="server" Columns="50"></asp:TextBox> </p> <p> <asp:Button id="Button" onclick="Button_Click" runat="server" Text="Search"></asp:Button> </p> <p> <asp:Label id="lblResults" runat="server" EnableViewState="False"></asp:Label> </p> </form></body></html> Many thanks
Hello all, I am trying to build a simple search page using SQL Server 2005, Visual Web Developer in C#.I have enabled Full-Text Search on my DB and created the catalogs and indexes, but I am lost on the next step to build a search form that calls the data based on the users input in a textbox. Any suggestions? Thanks!Travis
This question probably applies to Visual Web Developer but I don't know if it belongs in this Forum or that, sorry if you have to move it. Hi there kind person reading this I've created a stored procedure in SQL2000 that selects records where (name = @name) AND (site = @site). It works as I have tested it with Enterprise Manager. I have also created a page with VWD that has a drop-down list populated with the site records and a text box. I have put an SQL data source on the page that is configured to select from the stored procedure. However, I can't set the @name and @site variables to collect from the ddl and txtbox through the wizard! I've tried selecting a Control as the source for @site but neither the ddl or txtbox appear in the control dropdown list. So I want to crack it with handwritten code. Does anyone know of a post or article that can help me link the ddl and txtbox to a search button, which calls the stored procedure with the chosen variables allowing me to put the returned data into a gridview? I've been through a great walkthrough for VWD (Student Activities) but it doesn't seem to apply to the latest version - or there's something wrong with my (seems fine though) instalation. Thanks if you can help, I owe you a
have over million rows in the our table and we are looking forward to increase the speed of our query .Any ideas?set ANSI_NULLS OFF set QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[mainSearch] @startRowIndex int, @maximumRows int, @rowCount int out, @countedRow int, @QUERY nvarchar(400) AS SELECT _ID,_NAME,_TYPE,_CREATEDATE,ESTATETYPE,ESTATEDISPLAYPRICE,ESTATEDISPLAYPRICECURRENCY,ESTATEDISTRICT,ESTATECITY,ESTATEROOMCOUNT,NUMBEROFPICTURES FROM ( SELECT ROW_NUMBER() OVER (ORDER BY _CREATEDATE DESC) AS ROWRANK,* FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@QUERY) as KEY_TBL ON ADTBL._ID = KEY_TBL.[KEY] Where (_DELETIONSTATUS=0)
) AS RANKEDADDS
WHERE ROWRANK > @startRowIndex AND ROWRANK <= @startRowIndex + @maximumRows -1
if(@countedRow < 1) SET @rowCount = ( SELECT COUNT(_ID) FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@QUERY) as KEY_TBL ON ADTBL._ID = KEY_TBL.[KEY] Where _DELETIONSTATUS=0 ) else SET @rowCount = @countedRow
I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
Hello I have a project that uses a large number of MS Data access pages created in Access 2003 and runs on MS SQL2005.
When I am on lets say my client, (first page in a series) data access page and I have completed the fields in the (DAP), I am directing my users to the next step of the registration process by means of a hyperlink to another Data access page in the same web but in a linked or sometimes different table.
I need to pass data entered /created on the first page to the next page and populate the next page with some data from the first page / table. (like staying on the client name and ID when i go to the next page)
I also need the first data access page to open and display a blank or new record. Not an existing record. I will also be looking to creata a drop down box as a record selector.
Any pointers in the right direction would be appreciated. I am some what new to data access pages so a walk through would be nice but anything you got is welcome. Thanks Peter€¦
I can't find any explanation why is it string1 and string 2 of the footer section of my report displayed separately from the expression3 which is aligned on it and the rest of the object on the second page.
The expected design is that all Footer items should be displayed together of whether it is placed on the first page or on the last page.
As a workaround of this, I converted string 1 into an expression (Added = and enclosed the string with double quote).. As a result, all of the items in the Footer section are now placed together on the last page of the report.
I also remember one of the issue I encountered before where the Footer items where placed together on the first page and still have space at the bottom of the page, but then expression 6 is forced to display (alone) on the last page of my report.
I can't find any discussion related to this, I wish somebody could give me an idea why RS behaved like this.
Fit an intere table in same page without page break for save the excel export.
My table has a Group for order my dates.
I need to have the intere table in the same page, i don't care about blank space at the end of the page.
I can't use the page break beacuse i need an excel export in a unique sheet.. I have tested.. every page break..you'll have a different sheet in your excel export
Hello, If I have a report that includes a page header, the report viewer will render the report at the full width, but if I hide the page header and show only the body it will use the absolute width of the report.
I have a 7" wide report, with .75" borders set on each side.
Interactive & Print size are set to 8.5x11 Changing these sizes has no effect on the behavior of the viewer, which appears to ignore them.
When I view this report in the local viewer the contents fill the window if there is no page header. If I enable the page header then the report is drawn at 8.5" wide, leaving a _big_ white border on the right side. Any comments or workarounds that anyone knows? I know the local viewer is not a standard configuration (at least it seems) but it is what we need to use.Thanks,//Andrew
How can I print a field that is in the dataset on each page? I added a textbox in the Page Header and use =Fields!ProjectName.value in the value property. I got an error "Fields cannot used in page header and footer."
How can I have the table header shows on each page? Currently if the data goes to the second page, there is no table header.
When i view the Report from SSRS Report preview Tab it's working fine, But when i deploy that and try to view in the IE I am seeing the Body background color in between the image and page border of the page footer how to solve that?
Hi All, In my SSRS report. I have a report which has only one page. In preview it is showing as only 1 page but when I am printing the report. I am getting two printouts with the second page as a blank.. Please help me in printing the page that contains report. Intially I used a Page header, at that it used to print the blank page with a header only. Now as I removed the header it is printing the page without header i.e Blank Page.. So please help me in prinitng a single page that has the report. It is urgent,..
I have a report with multiple tables. I need to show each tables in different pages. When there is no data for tables/tables , it is coming with the next table which has data. I have given "Add a page break after" option in the tablix but still the tables are coming together when no data available. How can I show it in different page?
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
I have a report with tablix. when tablix returns no rows Footer is coming all the way up . How to display the footer at the bottom of the page all time.
In SRSS 2005 (SP2) my page header seems to take up the same amount of space on the 1st page it would take if it were to print; I have PRINT ON FIRST PAGE set to false - the header doesn't print - it just leaves the same amount of space. How do you get the report to ignore that. I do have a report header built into the body of my report. I have tested this by increasing the size of my page header and it does move the report up or down on the 1st page by that amount.
I'm hoping that someone can shed some light on this for me... I'm using SSRS SP2 and I have a basic report using a single list object which, at the moment, should print only two pages based on the results of the underlying query. When previewing the report, it shows the two pages. But when the report is printed or exported, there is a blank page before each report page.
What I've done so far: 1) Verified the properties of the list object and made sure that 'Insert a page break before this list' is not checked
2) Ensured that there are no hidden objects that could be causing this behavior 3) Ensured that the report size + margins are within the boudaries of an standard 8.5x11 paper size
Any suggestions on something that would solve this issue?
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 am running a DBCC SHRINKFILE on "FILE1" of a database (it has fileid = 1)...intent is to remove 70GB of file space:
DBCC SHRINKFILE (N'FILE1' , 400000).
For the SPID that's doing the shrink, In activity monitor you can see:
Waittype: PAGEIOLATCH_EX on resource: 9:3:15411328 (the DB is dbid=9)
But why does it need a page from fileid=3? Are there page dependencies between files that prevent moving a page within a given file? Does it need that fileid3 page to come along?
Its just sitting there in the SUSPENDED state for the last hour....I am going to leave it another 5 hours or so before cancelling.
The dm_exec_requests has an estimated percent complete at 83% and holding....not sure if I can believe that.
I have a problem which I am really struggling with. I have a report that normally spreads over three pages. We have a list setup so that for each customer it picks up fees and expenses and displays it in sections. We have a page break after FEES so that expenses start on a new page. FEES and expenses are tables.
We need to have a page footer set for the bottom of the report but to appear on the first page only per customer. I.e. we do not want it showing on the EXPENSE pages. We have played with the IIF command but can not seem to print htis per customer only by using global variables.
=IIf(Globals!PageNumber=1,"(Please note 30 day invoice period)
The example above however only prints on the first page of the entire report. So if three customers appear then this will only appear for the first customer and I have a feeling I may not be able to link this to list.
If I can not use a page break and enter this into the list then I have a problem with the text field moving up and down depending on the size of the FEE section. For example if 5 fees are brought back then the text field will be lower then a customer that has only 2 fees.
I after a solution that can either allow my page fotter to appear every time a new customer appears on a page and hiddern for the sub sheets. Or the ability to allow my text field to start at a fixed position on my report. Has anyone had a similar issue.
Hi - I am pretty new to Reporting Services. I need to create a report where a single result row from the Select Statement populates an entire page of data. The regular grid or Matrix reports don't fit this need. Is there a simple way to do this?
Is it possible to force the content of the page footer to always display at the bottom of a report? I have a report that shows customer orders. It has its interactive height set to 5 inches. There is one customer order per report page. Most of the reports don't require 5 vertical inches (but some do).
How can I make sure that the stuff in the page footer (date, page number, etc.) always gets rendered in the same position for each report? Currently, the page footer follows the last detail line so the footer information sometimes is only midway down the report page.
