Need to build a search stored procedure

I have a few textboxes on a page that I would like to use as a search page and have clients shown in a gridview that meet the users entry into one or more of the textboxes.

I have ClientID, LastName, FirstName, Address, and Keywords. How would I build a stored procedure to allow me to do this?

This question has been answered several times in this forum. Please search for "dynamic where"/similar keywords.|||

What is the standard way of creating a search page of this sort?

|||

This thread discusses the SQL LIKE clause and dynamic queries... should be helpful:

http://forums.asp.net/thread/1529167.aspx

|||I searched dynamic where as well as many other keywords and have had no luck in finding any results. Can you offer any additional assistance?|||

SELECT *

FROM Table

WHERE (Column1 = @.col1 OR @.col1 IS NULL)

AND (Column2 = @.col2 OR @.col2 IS NULL)

AND (Column3 = @.col3 OR @.col3 IS NULL)

...

Need to add line feeds

I am working on an "invoice" print, and it has to be on a pre-printed form, I need to print the totals at the bottom of the page and need help figuring out how to add specific amount of line feeds. I tried printing chr(13) , chr(10), conbination of both, also tried Environment.NewLine, no luck.

Using SQLRS2000, developing report with VS2003.

Anyone's suggestions would be greatly appreciated!.

Also try using vbCrLf.|||

I tried it just now, no luck yet.

I am doing it on the group footer, simply entering :

= vBCrLf

I have never had this need, not sure if this is the proper syntax?

|||

Hello,

Are you trying to use it in an expression? Can you try to wrap the vbCrLf with some dummy text just so you can see that it's working. Like this:

="Some text on line 1." + vbCrLf + "Next, line number 2."

The result should be:

Some text on line 1.

Next, line number 2.

Jarret

|||

thanks everyone! it works with text around it.

thank you so much!

Need SQL query

Hi there. U have table structure shown here:
category (Int - AutoIncrement) parent_category(Int) Title(string)
1 0
HOME PAGE
2 1
FIRST DEPTH 1
3 1
FIRST DEPTH 2
4 1
FIRST DEPTH 3
5 2
SECOND DEPTH 1
6 3
SECOND DEPTH 2
7 3
SECOND DEPTH 3
8 6
THIRD DEPTH 1
I want 1 (one) SQL query that can return me full depth for certain caregory.
In some meta-language
for category 8 it look like this:
SELECT * FROM table WHERE category = 8
WHILE parent_category <> 1
SELECT * FROM table WHERE category = [parent_category from previous
iteration]
WEND
So in that case resulting recordset would be
category (Int - AutoIncrement) parent_category(Int) Title(string)
8 6
HOME PAGE
6 3
FIRST DEPTH 1
3 1
FIRST DEPTH 2
I know that I can do this using more that one recordset, but I want all this
in one recordset.
I am using ADO, not ADO.NET
ThanksBola,
You can achieve this using user defined function, see following example.
create table tree
(category Int, parent_category Int, Title varchar(50))
go
insert into tree
select 1, 0,'HOME PAGE' union all
select 2, 1,'FIRST DEPTH 1' union all
select 3, 1,'FIRST DEPTH 2' union all
select 4, 1,'FIRST DEPTH 3' union all
select 5, 2,'SECOND DEPTH 1' union all
select 6, 3,'SECOND DEPTH 2' union all
select 7, 3,'SECOND DEPTH 3' union all
select 8, 6,'THIRD DEPTH 1'
select * from tree
go
create function fn_get_tree (@.y int)
returns
@.tb table(empid int,
supervisor int,
empname varchar(300)
)
as
begin
declare @.x table (empid int)
insert into @.x
select parent_category from tree where category = @.y
union all
select @.y
while 1=1
begin
insert into @.x
select parent_category from tree where category in (select distinct empid from @.x)
and parent_category not in(Select empid from @.x)
if @.@.rowcount = 0
break
end
insert into @.tb
select * from tree where
exists
(select * from @.x a where a.empid= tree.category)
return
end
go
--usage
select * from fn_get_tree (7)
--
- Vishal

Need some help with a query

Hello everyone, I have a table that is setup to record a page of web
form elements. The form elements are dynamically created. Each page
contains x number of questions. (x depends on many different things
There are 4 pages. Since we never know how many form elements are on
the page, the DB was design as such
GroupID int
PageID int
QuestionID int
FormElementID int
FormElementValue varchar(500)
The problem is when trying to grab a combination of forms to display in
a report I have to write a query like this (let's assume I am trying to
grab all records that have $5/10 years) On page 1, question 4, the
form element 1 is the dollar amount and the form element 2 is the
number of years.
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=5 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=10)
This works fine. My problem is when I have to grab all other
combinations. So let's say my client wants $5/10 years, $10/15 years,
$15/20 years and all other combinations as four distinct numbers. How
can I accomplish this? Currently I am doing this (which I know is poor
as it just looks wrong and takes forever to run.
SELECT GroupID FROM thistable WHERE GroupID NOT IN (
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=5 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=10)
AND GroupID NOT IN (
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=10 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=15)
AND GroupID NOT IN (
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=15 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=20)
Let me know if you have any ideas, thanks for your help in advance
(please note that I wrote the script above based on my real script, I
can't display the exact real code, but above is very close.night_day (night_day_8@.yahoo.com) writes:
> This works fine. My problem is when I have to grab all other
> combinations. So let's say my client wants $5/10 years, $10/15 years,
> $15/20 years and all other combinations as four distinct numbers. How
> can I accomplish this? Currently I am doing this (which I know is poor
> as it just looks wrong and takes forever to run.
> SELECT GroupID FROM thistable WHERE GroupID NOT IN (
> SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
> FormElementID=1 AND FormElementValue=5 AND GroupID IN (SELECT GroupID
> from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
> FormElementValue=10)
> AND GroupID NOT IN (
> SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
> FormElementID=1 AND FormElementValue=10 AND GroupID IN (SELECT GroupID
> from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
> FormElementValue=15)
> AND GroupID NOT IN (
> SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
> FormElementID=1 AND FormElementValue=15 AND GroupID IN (SELECT GroupID
> from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
> FormElementValue=20)
You should be able to sort this out, if you learn to master EXISTS/NOT
EXISTS. I show example with your first query to get you going:
SELECT t1.GroupID
FROM thistable t1
WHERE t1.PageID=1
and t1.QuestionID=4
AND t1.FormElementID=1
AND t1.FormElementValue=5
AND EXISTS (SELECT *
from thistable t2
WHERE t2.PageID=1
AND t2.Question_ID=4
AND t2.FormElementID=2
AND t2.FormElementValue=10
AND t1.GroupID = t2.GroupID)
The point here is that you may not need multiple subqueries, but could
then use OR conditions.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thank you for your response. I've spend some time converting my
original query to use EXISTS, I can now generate the same output using
a version running NOT EXISTS as my current NOT IN statements.
Unfortunately, I've run the 2 queries in query analyzer and the NOT
EXISTS statements takes 12 seconds whereas my current NOT IN statement
takes 7 seconds so I am not seeing any benefit from using NOT EXISTS.
My query looks like so
SELECT d.GroupID
FROM thistable d
WHERE d.PageID=1
and d.QuestionID=4
AND d.FormElementID=1
AND d.FormElementValue=5
and NOT EXISTS (SELECT * FROM thistable t1 WHERE t1.PageID=1 AND
t1.Question_ID=4 AND t1.FormElementID=1 AND FormElementValue = 5
AND EXISTS (SELECT * from thistable t2 WHERE t2.PageID=1 AND
t2.Question_ID=4 AND t2.FormElementID=2 AND FormElementValue = 10 AND
t1.GroupID = t2.GroupID) and d.GroupID=t1.GroupID)|||night_day (night_day_8@.yahoo.com) writes:
> Thank you for your response. I've spend some time converting my
> original query to use EXISTS, I can now generate the same output using
> a version running NOT EXISTS as my current NOT IN statements.
> Unfortunately, I've run the 2 queries in query analyzer and the NOT
> EXISTS statements takes 12 seconds whereas my current NOT IN statement
> takes 7 seconds so I am not seeing any benefit from using NOT EXISTS.
> My query looks like so
> SELECT d.GroupID
> FROM thistable d
> WHERE d.PageID=1
> and d.QuestionID=4
> AND d.FormElementID=1
> AND d.FormElementValue=5
> and NOT EXISTS (SELECT * FROM thistable t1 WHERE t1.PageID=1 AND
> t1.Question_ID=4 AND t1.FormElementID=1 AND FormElementValue = 5
> AND EXISTS (SELECT * from thistable t2 WHERE t2.PageID=1 AND
> t2.Question_ID=4 AND t2.FormElementID=2 AND FormElementValue = 10 AND
> t1.GroupID = t2.GroupID) and d.GroupID=t1.GroupID)
Eh, the query is not exactly trivial to understand. I feel quite
bewildered. It would help if you posted:
o CREATE TABLE statement for the table.
o The output of sp_helpindex for the table.
o Some indication of number of rows and distribution.
It could also be interesting to see the query plans. You can this
by surrounding the query in SET SHOWPLAN_ALL ON. (This will not execute
the query.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Need simple connect/SQL Select in aspx.vb

I have searched for what I think is a simple solution.

In my aspx.vb code page, I need to make a connection to SQL Server (my connection strings are in the web.config file) and then make a simple SQL Select to return one or 2 values.

For example, the user will select a customer on the ASP form, then I need to read a couple of default values from that user's record and display them back onto the form.

Can someone provide the sample code or direct me to this? I'm thinking that this is rather simple, but I can't seem to make it work. If I see a sample of exactly what I need to do, such as reading a value using the Northwind sample DB, then I can modify it to fit my table structure, etc.

Thank you.

Hi,

Here is a sample code that you can run on a web form which reads an Id value from a textbox and then returns the firstname and lastname of the customer.

PrivateSub Button5_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles Button5.ClickDim sqlConnectionStringAsString = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString")Dim sqlConnectionAs SqlClient.SqlConnection =New SqlClient.SqlConnection(sqlConnectionString)Dim sqlCommandAs SqlClient.SqlCommand =New SqlClient.SqlCommand("SELECT FirstName, LastName FROM Customers Where CustomerId = @.CustomerId")Dim sqlParamAs SqlClient.SqlParameter =New SqlClient.SqlParameter("@.CustomerId",CInt(txtCustomerId.Text))

sqlCommand.Parameters.Add(sqlParam)

sqlCommand.Connection = sqlConnection

sqlConnection.Open()

Dim drAs SqlClient.SqlDataReader

dr = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)

While dr.Read

txtFirstName.Text =

CType(dr.GetValue(dr.GetOrdinal("FirstName")),String)

txtLastName.Text =

CType(dr.GetValue(dr.GetOrdinal("LastName")),String)EndWhile

dr.Close()

EndSub

You can use the below line of code to get the connection string from the web.config file

System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString")

You should have placed the below configuration declaration within the configuration tags (<configuration></configuration>)

<appSettings><addkey="ConnectionString"value="..."/></appSettings>

Eralper

http://www.kodyaz.com

|||

How come my intellisense does not automatically pop-up the wording 'SqlClient...' in my dim statements. Nothing seems to be wrong with my set up as I have been working fine for months, but now that I am trying to ad-hoc querries I don't have access to these facilities..

Dim sqlConnectionAsSqlClient.SqlConnection =New SqlClient.SqlConnection(sqlConnectionString)Dim sqlCommandAsSqlClient.SqlCommand =New SqlClient.SqlCommand("SELECT FirstName, LastName FROM Customers Where CustomerId = @.CustomerId")Dim sqlParamAsSqlClient.SqlParameter =New SqlClient.SqlParameter("@.CustomerId",CInt(txtCustomerId.Text))

|||

Never mind, I just had to add

Imports System.Data

Imports System.Data.SqlClient

Need sample code for asp + sql server

Hello,
I am developing for internet.
I need a good example for ASP page, that use sql-server with some update
commands + transactions (use insert/update command + beginTrans/EndTrans,
please.
Thanks
Try a site like http://www.asp101.com/ or http://www.4guysfromrolla.com/
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Avi" <nobody@.nospam_please.com> wrote in message
news:u8BcHvD8EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am developing for internet.
> I need a good example for ASP page, that use sql-server with some update
> commands + transactions (use insert/update command + beginTrans/EndTrans,
> please.
> Thanks
>
|||Have a look at
http://www.mindsdoor.net/aSP/DBAccess.inc.html

Need sample code for asp + sql server

Hello,
I am developing for internet.
I need a good example for ASP page, that use sql-server with some update
commands + transactions (use insert/update command + beginTrans/EndTrans,
please.
Thanks
Try a site like http://www.asp101.com/ or http://www.4guysfromrolla.com/
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Avi" <nobody@.nospam_please.com> wrote in message
news:u8BcHvD8EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am developing for internet.
> I need a good example for ASP page, that use sql-server with some update
> commands + transactions (use insert/update command + beginTrans/EndTrans,
> please.
> Thanks
>
|||Have a look at
http://www.mindsdoor.net/aSP/DBAccess.inc.html

Need sample code for asp + sql server

Hello,
I am developing for internet.
I need a good example for ASP page, that use sql-server with some update
commands + transactions (use insert/update command + beginTrans/EndTrans,
please.
Thanks :)Try a site like http://www.asp101.com/ or http://www.4guysfromrolla.com/
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Avi" <nobody@.nospam_please.com> wrote in message
news:u8BcHvD8EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am developing for internet.
> I need a good example for ASP page, that use sql-server with some update
> commands + transactions (use insert/update command + beginTrans/EndTrans,
> please.
> Thanks :)
>|||Have a look at
http://www.mindsdoor.net/aSP/DBAccess.inc.html

Need sample code for asp + sql server

Hello,
I am developing for internet.
I need a good example for ASP page, that use sql-server with some update
commands + transactions (use insert/update command + beginTrans/EndTrans,
please.
Thanks Try a site like http://www.asp101.com/ or http://www.4guysfromrolla.com/
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Avi" <nobody@.nospam_please.com> wrote in message
news:u8BcHvD8EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am developing for internet.
> I need a good example for ASP page, that use sql-server with some update
> commands + transactions (use insert/update command + beginTrans/EndTrans,
> please.
> Thanks
>|||Have a look at
http://www.mindsdoor.net/aSP/DBAccess.inc.html

Need rdl help

Can you launch a sql report rdl for a menu form you default page if so I know how to launch a crystal.aspx but do not know how to launch the rdl files do what do I need to to to make this work.

You have to deploy the report to your report server. You can upload the .rdl file through Report Manager but I've always had problems with this concerning the data sources. (I'm new at this too). I usually create the report in VS and then deploy it from there.

After it is on the report server you can access it usually from this urlhttp://localhost/reportserver or you can use the ReportViewer control in your .aspx page if you are using Visual Studio 2.0. Or you can link directly to the report server from your .aspx page.

If you need more help make sure you read the tutorials on Microsoft's site to get you started.

|||

I am inheriting a web site (whose original author is currently unavailable) that runs an winform app behind the scene to send emails or faxes to certain recipients based on what users enter onto the site. The win app calls a SSRS web service to generate the text contents for email/fax. Recently I have received new requirements and need to modify the SSRS text contents but have not been able to find the related rdl files. Could the SRSS reports be deployed,without the rdl files being copied to the server? What can I do now? Thanks.

Need Page break for Every Odd Page

Hi,
I have a table with group which populated on multiple pages. If the group
ended with odd page then I need an extra page break (1 blank page).
pls get me the details on that.
Thanks & Regards
Gopi RHi,
I expecting some Suggestion but no one answer to this question. pls give
the suggestion.
Thanks & Regards
Gopi R
"Gopala Krishnan" <Gopal@.photoninfotech.com> wrote in message
news:uhB83VCYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a table with group which populated on multiple pages. If the group
> ended with odd page then I need an extra page break (1 blank page).
> pls get me the details on that.
> Thanks & Regards
> Gopi R
>
>
>

Need more table rows displayed on page

Right now I have a report with groupings and each full set takes up quite a
bit of room. So right now it only displays 10 sets per page, but I'd like to
increase this to 30. How can I make a table display more information before
making a new page?Change the page size... It is in the report properties menu item...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
news:AF670A51-C85B-43AB-89E6-10323C8D2950@.microsoft.com...
> Right now I have a report with groupings and each full set takes up quite
> a
> bit of room. So right now it only displays 10 sets per page, but I'd like
> to
> increase this to 30. How can I make a table display more information
> before
> making a new page?|||Thanks, ended up being a small fix. I'd rather there be some type of exact
number since now it can differ from page to page since some entries are
larger/smaller, but this works fine.
"Wayne Snyder" wrote:
> Change the page size... It is in the report properties menu item...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
> news:AF670A51-C85B-43AB-89E6-10323C8D2950@.microsoft.com...
> > Right now I have a report with groupings and each full set takes up quite
> > a
> > bit of room. So right now it only displays 10 sets per page, but I'd like
> > to
> > increase this to 30. How can I make a table display more information
> > before
> > making a new page?
>
>|||I have an example on www.msbicentral in the RDL Downloads section which
shows how to set a fixed number of rows/page to display... Its not very
hard...The filename is PagedTableParameters.RDL
Hope this helps
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
news:F9921495-BF23-42A9-ADB6-AB5FB3319188@.microsoft.com...
> Thanks, ended up being a small fix. I'd rather there be some type of exact
> number since now it can differ from page to page since some entries are
> larger/smaller, but this works fine.
> "Wayne Snyder" wrote:
>> Change the page size... It is in the report properties menu item...
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
>> news:AF670A51-C85B-43AB-89E6-10323C8D2950@.microsoft.com...
>> > Right now I have a report with groupings and each full set takes up
>> > quite
>> > a
>> > bit of room. So right now it only displays 10 sets per page, but I'd
>> > like
>> > to
>> > increase this to 30. How can I make a table display more information
>> > before
>> > making a new page?
>>