Help on creating a user function.

When I declare a cursor,I use a variable to replace the sql statement:
DECLARE rs CURSOR LOCAL FAST_FORWARD FOR
@.sqlPlan
But it is not true.Who can correct for me.

Another question is :
How to execute a sql statement state by a variable "@.sqlPlan" and
insert the result to a table "@.FeatRequestStatus"?

I am a new hand of sql programming.Thank you very much for your helpWhen I use:
insert @.FeatRequestStatus
exec @.sqlPlan
It says "execute can be used as a source when insert into a table viarable"
"Kevin" <hua@.lucent.com> wrote in message
news:dc2mgs$16f@.netnews.proxy.lucent.com...
> When I declare a cursor,I use a variable to replace the sql statement:
> DECLARE rs CURSOR LOCAL FAST_FORWARD FOR
> @.sqlPlan
> But it is not true.Who can correct for me.
> Another question is :
> How to execute a sql statement state by a variable "@.sqlPlan" and
> insert the result to a table "@.FeatRequestStatus"?
> I am a new hand of sql programming.Thank you very much for your help|||Kevin (hua@.lucent.com) writes:
> When I declare a cursor,I use a variable to replace the sql statement:
> DECLARE rs CURSOR LOCAL FAST_FORWARD FOR
> @.sqlPlan
> But it is not true.Who can correct for me.

You need to say:

EXEC ('DECLARE rs CURSOR GLOBAL FAST_FORWARD ' + @.sqlPlan)

Note that I changed LOCAL to GLOBAL here. This is necessary, since the
cursor is accessed from a different scope than it is created.

> Another question is :
> How to execute a sql statement state by a variable "@.sqlPlan" and
> insert the result to a table "@.FeatRequestStatus"?

INSERT EXEC does not work with table variables, as you have experienced.
Use a temp table instead.

And if @.sqlPlan is an SQL statement, the syntax is

EXEC(@.sqlPlan)

The syntax you had on your other post:

EXEC @.sqlPlan

means "execute the stored procedure of which the name is in @.sqlPlan".

> I am a new hand of sql programming.Thank you very much for your help

In such case, I should maybe point out, that cursors is something
to be used sparingly. There are situations where cursors can be
motivated, but they often come with a price of severly reduced
performance. Work set-based if you can.

Dynamic SQL is not really anything for beginners - it's definitely an
advanced feature. Dynamic SQL makes things a lot more complex, and
avoid if you can. I have a longer article on dynamic SQL on my web
site that you could find useful:
http://www.sommarskog.se/dynamic_sql.html

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Help Needed on Next() function of Crystal Reports

Can any of you help me by providing an Alternate Code Snippet in
reportingsvcs(SSRS) for Crystal Report Next() function.
Thanks in Advance.
Regards,
Nivasp74My CR knowledge is really rusty now, but I guess Next in CR is the
opposite of Previous in RS?
There isn't a Next function as such in RS. You'll need to do a bit of
lateral thinking.
If you are affecting formatting or showing or hiding rows/cells based
on the next value, try and switch your logic so you can use previous
instead.
If that's not possible in your scenario, try incorporating the next
value onto each row from the dataset, i.e.
Select a.id, a.value, b.id, b.value
From MyTable A
Left Join MyTable B On B.Id = (A.Id + 1)
A good piece of advice when switching from CR to RS; try not to think
how you would do it in CR first - this will only cause you frustration!
RS is quite a different product in the way it works, as soon as you can
break the habit of thinking in Crystal terms, you'll find RS get's
easier.
--
Regards
Chris
nivasp wrote:
> Can any of you help me by providing an Alternate Code Snippet in
> reportingsvcs(SSRS) for Crystal Report Next() function.
> Thanks in Advance.
> Regards,
> Nivasp74|||Not sure if you will find this useful for your situation or not, but I am in
the process of converting our reports from Crystal Reports to SQL Server
Reporting Services and I came up with the following alternative for the NEXT
function in our situation.
Many of our reports consist of multiple groups with subtotals and grand
totals in the group footers. On some of the CR reports, we have a
conditional page break which forces a page break only if the NEXT group value
is equal to the current value. For example: {@.Bank_Name/Bank_HEADER} = Next
({@.Bank_Name/Bank_HEADER})
This prevents a group footer from appearing on a single page by itself.
My SSRS solution is to place all the group footer information in the
innermost group footer, but within their own individual table rows with the
innermost group footer's Page break at End property checked. Then I define
the table row's hidden property to an expression which evaluates the row
number of the group with the row count of the group, such as
=(RowNumber("table1_Group2")<>CountRows("table1_Group2")).
My Group 1 and Report Footers row hidden properties have the following
respective expressions:
=(RowNumber("table1_Group1")<>CountRows("table1_Group1"))
=(RowNumber("table1")<>CountRows("table1"))
As for the aggregate functions in each of the table rows, I apply the same
scope values to achieve the Group 2, Group 1, and report totals as in:
=Count(Fields!Formula_Acct_Num.Value, "table1_Group2")
=Count(Fields!Formula_Acct_Num.Value, "table1_Group1")
=Count(Fields!Formula_Acct_Num.Value, "table1")
= Sum(Fields!Formula_Balance.Value, "table1_Group2")
= Sum(Fields!Formula_Balance.Value, "table1_Group1")
= Sum(Fields!Formula_Balance.Value, "table1")
Enjoy!
"Chris McGuigan" wrote:
> My CR knowledge is really rusty now, but I guess Next in CR is the
> opposite of Previous in RS?
> There isn't a Next function as such in RS. You'll need to do a bit of
> lateral thinking.
> If you are affecting formatting or showing or hiding rows/cells based
> on the next value, try and switch your logic so you can use previous
> instead.
> If that's not possible in your scenario, try incorporating the next
> value onto each row from the dataset, i.e.
> Select a.id, a.value, b.id, b.value
> From MyTable A
> Left Join MyTable B On B.Id = (A.Id + 1)
> A good piece of advice when switching from CR to RS; try not to think
> how you would do it in CR first - this will only cause you frustration!
> RS is quite a different product in the way it works, as soon as you can
> break the habit of thinking in Crystal terms, you'll find RS get's
> easier.
> --
> Regards
> Chris
>
> nivasp wrote:
> > Can any of you help me by providing an Alternate Code Snippet in
> > reportingsvcs(SSRS) for Crystal Report Next() function.
> >
> > Thanks in Advance.
> > Regards,
> > Nivasp74
>

help needed in writing a recursive function

Guys, I have a table with following data
Table Name :TblRecursive
Sample records:
ID, Name, ParentID
1, A, 0
2, B, 1
3, C, 2
4, D, 2
5, E, 1
Using the above data I just want to generate a result as below
A
A\B
A\B\C
A\B\D
A\E
Can you help in writing a query for this?
Regards
PradeepLook at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
In SQL 2005 you can use a common table expression with a recursive member.
ML
http://milambda.blogspot.com/|||http://vyaskn.tripod.com/hierarchie...r_databases.htm
Madhivanan|||Thanks for your response ML.
As I told earlier .. I want to generate the string as A\B and so on. But
your UDF gives me 3 columns.
Also I don't want to provide any input to the function also. I want it to
just start from the beginning and go on till the end. Any idea on how to
achieve this?
Regards
Pradeep
"ML" wrote:

> Look at this example:
> http://milambda.blogspot.com/2005/0...or-monkeys.html
> In SQL 2005 you can use a common table expression with a recursive member.
>
> ML
> --
> http://milambda.blogspot.com/|||Concatenating values into a single string really should be done on the
cilent, as it has no relational value - it's pure presentation.
There are a few ways to do it on the server if absolutely necesary:
declare @.result varchar(8000)
set @.result = N''
select @.result = <column> + '/' + @.result
from <function>
order by <level?>
ML
http://milambda.blogspot.com/|||Check whether this sample helps you!
--Table creation
Create table tblEmployeeInfo
(
EmpId int primary key,
EmpName varchar(30),
MgrId int
)
--Insert test data into it
Insert into tblEmployeeInfo values(1, 'Director', null)
Go
Insert into tblEmployeeInfo values(2, 'Joint Director', 1)
Go
Insert into tblEmployeeInfo values(3, 'Secretary', 2)
Go
Insert into tblEmployeeInfo values(4, 'Joint Secr.,', 3)
Go
Insert into tblEmployeeInfo values(5, 'Legal Advisor', 1)
Go
-- UDF for your requirement
Create function GetEmpPath ( @.pEmpid int ) Returns varchar(8000)
As
Begin
Declare @.intMgrid int
Declare @.strEname varchar(500)
Declare @.strPath varchar(500)
Select @.intMgrid = mgrid, @.strEname = empname From tblEmployeeInfo where
EmpId = @.pEmpid
If (@.intMgrid is null)
Begin
Set @.strPath = @.strEname
End
Else
Set @.strPath = dbo.GetEmpPath(@.intMgrid) + '' + @.strEname
return @.strPath
End
Go
--Test the code
Select dbo.GetEmpPath(empid) as Hierarchy, empname as 'Employee Name' from
tblEmployeeInfo
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
"SqlBeginner" wrote:
> Thanks for your response ML.
> As I told earlier .. I want to generate the string as A\B and so on. But
> your UDF gives me 3 columns.
> Also I don't want to provide any input to the function also. I want it to
> just start from the beginning and go on till the end. Any idea on how to
> achieve this?
> Regards
> Pradeep
>
> "ML" wrote:
>|||This is what I was looking for. Thanks a lot Vadivel. It really helped me.
Regards
Pradeep
"Vadivel" wrote:
> Check whether this sample helps you!
> --Table creation
> Create table tblEmployeeInfo
> (
> EmpId int primary key,
> EmpName varchar(30),
> MgrId int
> )
> --Insert test data into it
> Insert into tblEmployeeInfo values(1, 'Director', null)
> Go
> Insert into tblEmployeeInfo values(2, 'Joint Director', 1)
> Go
> Insert into tblEmployeeInfo values(3, 'Secretary', 2)
> Go
> Insert into tblEmployeeInfo values(4, 'Joint Secr.,', 3)
> Go
> Insert into tblEmployeeInfo values(5, 'Legal Advisor', 1)
> Go
> -- UDF for your requirement
> Create function GetEmpPath ( @.pEmpid int ) Returns varchar(8000)
> As
> Begin
> Declare @.intMgrid int
> Declare @.strEname varchar(500)
> Declare @.strPath varchar(500)
> Select @.intMgrid = mgrid, @.strEname = empname From tblEmployeeInfo wher
e
> EmpId = @.pEmpid
> If (@.intMgrid is null)
> Begin
> Set @.strPath = @.strEname
> End
> Else
> Set @.strPath = dbo.GetEmpPath(@.intMgrid) + '' + @.strEname
> return @.strPath
> End
> Go
> --Test the code
> Select dbo.GetEmpPath(empid) as Hierarchy, empname as 'Employee Name' from
> tblEmployeeInfo
> Hope this helps!
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "SqlBeginner" wrote:
>|||Take into acount that in SQL 2000 the maximum recursion level is 32. And
quite frankly, recursion is not necesary here.
In my other post there's a solution that does not use a rexursive function.
Another example can also be found here:
http://milambda.blogspot.com/2005/0...s-as-array.html
ML
http://milambda.blogspot.com/

Help Needed in writing a function to help daily nightmare with Debugging.

I debug SPS on a daily basis and I use SQL profiler to help me trace where the problem is.
Once I have established which SP is the main problem I need to debug the line of code.

What I do is Cut and Paste the SQL Profiler details and populate all the parameters,sometimes that can be 30 and more..

Now what i thought is to write an SP or Function where I pass :

SP name and Parameters that profiler genererates
and returns me Declare Statements and Set Statements with parameters filled.

EG

Profiler Returns
Customer_INSERT,20,'JO',BLOGG','5 LONDON ROAD'

I would call my new SP =PopulateSPParams and cut and paste the profiler's string
PopulateSPParams 'Customer_Insert,20,'JO',BLOGG','5 LONDON ROAD'

this will RETURN THE FOLLOWING THAT WILL IMMENSILY HELP MY DAILY PROGRAMMING.

DECLARE
@.CustomerID int,
@.CustomerName varchar(50),
@.CustomerSurname varchar(50),
@.CustomerAddress varchar(100)

SET @.CustomerID =1
SET @.CustomerName='JO'
SET @.CustomerSurname='BLOGG'
SET @.CustomerAddress='5 London Road'

Can you help in writing something that generates and populate parameters?

Check this out. It only handles three parms, but it certainly could be made to handle more:

create procedure buildParms
(
@.procName nvarchar(128),
@.parm1 varchar(max),
@.parm2 varchar(max),
@.parm3 varchar(max)
) as
begin
set nocount on

select 'DECLARE @.retval int'
union all
select ' ,' + parameters.name + ' ' + types.name +
case
when types.name like 'n%char' then '(' + cast(parameters.max_length /2 as varchar(10)) + ')'
when types.name like '%char' then '(' +cast(parameters.max_length as varchar(10)) + ')'
when types.name like '%binary' then '(' +cast(parameters.max_length as varchar(10)) + ')'
when types.name = 'numeric' then '(' + cast(parameters.precision as varchar(10)) + ',' + cast(parameters.scale as varchar(10)) + ')'
else '' end
from sys.parameters
join sys.types
on types.user_type_id = parameters.user_type_id
where object_name(object_id) = @.procName
union all
select ' '
union all

select 'SET ' + parameters.name + ' = ' +
case when types.name like '%char' then '''' else '' end +
case parameters.parameter_id --to add more parms, add to this list
when 1 then @.parm1
when 2 then @.parm2
when 3 then @.parm3
end +
case when types.name like '%char' then '''' else '' end
from sys.parameters
join sys.types
on types.user_type_id = parameters.user_type_id
where object_name(object_id) = @.procName

union all
select ' '
union all
select 'EXECUTE @.retval = ' + @.procName
union all
select ' ' + parameters.name + ' = ' + parameters.name
+ case when parameters.is_output = 1 then ' OUTPUT ' else '' end +
case when parameter_id <> (select max(parameter_id) from sys.parameters where object_name(object_id) = @.procName)
then ',' else '' end
from sys.parameters
join sys.types
on types.user_type_id = parameters.user_type_id
where object_name(object_id) = @.procName
end
go

--test with this proc

alter procedure test$paramTest
(
@.value1 int,
@.value2 nvarchar(10) output,
@.value3 numeric (6,2)
) as
select @.value1, @.value2, @.value3
go

Execute this

buildParms 'test$paramTest','1','x','200'

returns:

DECLARE @.retval int
,@.value1 int
,@.value2 nvarchar(10)
,@.value3 numeric(6,2)

SET @.value1 = 1
SET @.value2 = 'x'
SET @.value3 = 200

EXECUTE @.retval = test$paramTest
@.value1 = @.value1,
@.value2 = @.value2 OUTPUT ,
@.value3 = @.value3

|||

Fantastic.Sorry for late reply.I could not access Computer.

Let me check.Wll be back to you.

I was thinking of passing a delimited string so that it could handle all sorts of params.

Very very grateful!!!!!!!!!

|||

Thanks

This works like a dream.I beleive this only works on sql server 2005

To make it work on sql 2000 I will change the varchar(max) to varchar(8000) But still get errors on all the sys stuff.

Any more suggestions?

very very grateful for your help

|||

Try this on 2000. I used the information_schema views this time. I thought of doing this the first time, but thought it would be too much bother (actually it was about the same:)

alter procedure buildParms
(
@.procName nvarchar(128),
@.parm1 varchar(8000),
@.parm2 varchar(8000),
@.parm3 varchar(8000)
) as
begin
set nocount on

select 'DECLARE @.retval int'
union all
select ' ,' + PARAMETER_NAME + ' ' + data_type +
case
when data_type like 'n%char' then '(' + cast(CHARACTER_MAXIMUM_LENGTH /2 as varchar(10)) + ')'
when data_type like '%char' then '(' +cast(CHARACTER_MAXIMUM_LENGTH as varchar(10)) + ')'
when data_type like '%binary' then '(' +cast(CHARACTER_MAXIMUM_LENGTH as varchar(10)) + ')'
when data_type = 'numeric' then '(' + cast(NUMERIC_PRECISION as varchar(10)) + ',' + cast(NUMERIC_SCALE as varchar(10)) + ')'
else '' end
from information_schema.parameters
where SPECIFIC_NAME = @.procName
union all
select ' '

union all

select 'SET ' + PARAMETER_NAME + ' = ' +
case when data_type like '%char' then '''' else '' end +
case ordinal_position --to add more parms, add to this list
when 1 then @.parm1
when 2 then @.parm2
when 3 then @.parm3
end +
case when data_type like '%char' then '''' else '' end
from information_schema.parameters
where SPECIFIC_NAME = @.procName

union all
select ' '
union all
select 'EXECUTE @.retval = ' + @.procName
union all
select ' ' + PARAMETER_NAME + ' = ' + PARAMETER_NAME
+ case when PARAMETER_MODE = 'INOUT' then ' OUTPUT ' else '' end +
case when ordinal_position <> (select max(ordinal_position) from information_schema.parameters where SPECIFIC_NAME = @.procName)
then ',' else '' end

from information_schema.parameters
where SPECIFIC_NAME = @.procName

end
go

|||

What can I say?

You made my day ,

Thanks a lot I had started to convert it to 2000 but I had got stuck on the join between systypes and Information_schema.parameters i could not find the equivalent to join on.

thanks a lot again

Help needed for datediff function for SQL query

Hi Experts,
I am working on SSRS 2005, and I am facing a problem in counting the
no of days.
My database has many fields but here I am using only two fields
They are Placement_Date and Discharge_Date
If child is not descharged then Discharge_Date field is empty.

I am writing below query to count the number of days but is is not
working it is showing the error
"The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value."

select case
when convert(datetime,Discharge_Date,103) = '' then
datediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())
else
datediff(day,CONVERT(datetime,Placement_Date,
103),CONVERT(datetime,Discharge_Date,103))
end NoOfDays
from Placement_Details
So please tell me where I am wrong?

Any help will be appriciated.
Regards
DineshWithout having your table structure and sample data, it is a bit difficult
to troubleshoot, but a few notes that can help you:

- Based on the style 103 that you use for the CONVERT function, seems you
are converting date stored as string in format "dd/mm/yyyy" to a datetime
type. The error that you get indicates that the date string cannot be
converted, because the day, month, or year portion is out of the allowed
range. You can easily simulate the error if you run something like this:
SELECT CONVERT(datetime, '23/13/2007', 103). The month cannot be 13 so it
fails with the error you get. You can use the LEFT, RIGHT and SUBSTRING
functions to extract the day, month, and year portion of both columns
(Placement_Date and Discharge_Date) and check for invalid values, then clean
your data. For year the down side value is 1753 (that is the lower limit for
datetime data type).

- It is always best to keep date values in columns of datetime data type.
That way you do not have to worry about the format and can benefit of using
all datetime functions with no need to convert.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Dinesh (dinesht15@.gmail.com) writes:

Quote:

Originally Posted by

I am working on SSRS 2005, and I am facing a problem in counting the
no of days.
My database has many fields but here I am using only two fields
They are Placement_Date and Discharge_Date
If child is not descharged then Discharge_Date field is empty.
>
I am writing below query to count the number of days but is is not
working it is showing the error
"The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value."
>
select case
when convert(datetime,Discharge_Date,103) = '' then

This does not really make sense. A datetime value cannot be the
empty string. By the default the empty string will convert to the
datetime value 1900-01-01 00:00:00.000, but I don't think that is
what you want.

Assuming a reasonably designed database, the test would be

WHEN Discharge_Date IS NULL THEN

Then again, since you seem to store dates in character values, this
may not be a reasonably designed database. :-)

Anyway, the problem appears to be that you have junk in your character
columns. As Plamen said, you should use the datetime data type to store
your dates instead.

--
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|||Dinesh (dinesht15@.gmail.com) writes:

Quote:

Originally Posted by

I am working on SSRS 2005, and I am facing a problem in counting the
no of days.
My database has many fields but here I am using only two fields
They are Placement_Date and Discharge_Date
If child is not descharged then Discharge_Date field is empty.
>
I am writing below query to count the number of days but is is not
working it is showing the error
"The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value."
>
select case
when convert(datetime,Discharge_Date,103) = '' then
datediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())
else
datediff(day,CONVERT(datetime,Placement_Date,
103),CONVERT(datetime,Discharge_Date,103))
end NoOfDays
from Placement_Details
So please tell me where I am wrong?

Oh, by the way, this SELECT should give you the rows with bad dates:

SET DATEFORMAT DMY
go
SELECT Discharge_Date, Placement_Date
FROM Placement_Details
WHERE isdate(Discharge_Date) = 0 OR
isdate(Placement_Date) = 0

--
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

help me....

hi everyone

i got 2 questions plz

1- how to use the datadiff function ....plz write the code

2- is there a Trim function or statment that cuts the unneeded spaces before and after the word

thanx ...

Hi there,
1. http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_5vxi.asp?frame=true
samples imcluded :-)
2. What do you mean by unneeded ? You can trail characters, by using the function which I wrote some time ago:
CREATE FUNCTION dbo.fn_removetrailingchars
(
@.strValue VARCHAR(200),
@.TrailingChar VARCHAR(200),
@.RemoveLeading BIT
)
--Coded by Jens Suessmeyer, 2005 Available on http://www.sqlserver2005.de
RETURNS VARCHAR(200)
AS
BEGIN

DECLARE @.intCount int
SET @.intCount = 0

WHILE @.intCount <= LEN(@.strValue)
BEGIN
SET @.intCount = @.intCount +1
IF SUBSTRING(@.strValue, @.intCount, 1) NOT LIKE @.TrailingChar
BREAK
ELSE
CONTINUE
END
IF @.RemoveLeading = 1
SET @.strValue =
REVERSE(dbo.fn_removetrailingchars(REVERSE(RIGHT(@.strValue,
LEN(@.strValue) - @.intCount +1 )),@.TrailingChar,0))
ELSE
SET @.strValue = RIGHT(@.strValue, LEN(@.strValue) - @.intCount +1
)

RETURN @.strValue
END

Could be called by: Select dbo.fn_removetrailingchars (SomeColumn,CHAR(32),1) FROM Sometable

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

thanx for answering

but what i mean is a function or starment Called trim which removes the spaces ....and i think it should be used in the insertion

for an example lets say wa have a string " hi " i want to remove the spaces before and after hi

so how can i use it ?

|||Hi,

if you don't want to use my function, simply use LTRIM(RTRIM(Expression))

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Help me write a Search function please

Hi all,

I'm very new to ASP.NET stuffs, I'm trying to write a Search function for my website... I have two text boxes, one if called "SongTitle" and the other is "Artist"... Now I need to populate the GridView to display the result, based on the input of the textbox... So if only the "SongTitle" have input, it will search for the Song Titles on the database... if the Artist is searched, then it will return the artist... If both text boxes have value in them, then it need to check for both fields in the database and return the correct item...

For the "Artist", I have 2 columns in the Database (originalArtist and performer), so for the Artist select statement, it need to check both columns on the table, if any of them match then it will return the item.

Any help would be greatly appreciated,

Thank you all,

Kenny.

Try something like this:

SELECTFROM YourTableWHERE (@.SongTitleISNULL OR SongTitle = @.SongTitle)AND (@.ArtistISNULL OR (originalArtist = @.ArtistOR Performer = @.Artist ))

|||

ndinakar,

Thank you very much, here is what I put for my SqlDataSource, but it doesn't appear to work... Nothing returned when I try to search, using just the artist, the song, or both for the input:

<asp:SqlDataSource ID="DSResults" runat="server" ConnectionString="<%$ ConnectionStrings:notesnhacConnectionString1 %>"
SelectCommand="SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER] FROM [t_music] WHERE (@.MUSIC_TITLE IS NULL OR [MUSIC_TITLE2] LIKE '%' + @.MUSIC_TITLE + '%') AND (@.MUSIC_ARTIST IS NULL OR [MUSIC_ORIGINAL_SINGER] LIKE '%' + @.MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @.MUSIC_ARTIST + '%')">
<SelectParameters>
<asp:QueryStringParameter Name="MUSIC_TITLE" QueryStringField="title" Type="String" />
<asp:QueryStringParameter Name="MUSIC_ARTIST" QueryStringField="artist" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

|||

If you run thre query in your query analyzer with some values for the parameters does it work as expected? If not, post some sample data from your table and the query you used so we can better understand why its not working.

|||

Here is what I put on the Query window of SQL Manager Studio Express:

DECLARE @.MUSIC_TITLE NVARCHAR(100)
DECLARE @.MUSIC_ARTIST NVARCHAR(100)

SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]
FROM [t_music]
WHERE (@.MUSIC_TITLE IS NULL OR [MUSIC_TITLE] LIKE '%' + @.MUSIC_TITLE + '%') AND (@.MUSIC_ARTIST IS NULL OR [MUSIC_ORIGINAL_SINGER] LIKE '%' + @.MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @.MUSIC_ARTIST + '%')

SET @.MUSIC_TITLE = 'Everytime'
SET @.MUSIC_ARTIST = 'Cascada'

I've tried to use just @.MUSIC_TITLE, or just @.MUSIC_ARTIST, and both... each time it return ALL the records in the table... But if I execute the same select statement in VS 2005, it returns nothing... In the table, there is a song called "Everytime We Touch" performed by Cascada.

|||

Are you setting the values after running the SELECT? post some sample data from your table so I can test it on my machine..

|||

sorry. you need to use OR instead of AND in your WHERE clause.

|||

As you can see I have the SET @.MUSIC_TITLE = 'Everytime' in the previous post... Here are some sample data:

MUSIC_TITLE MUSIC_ORIGINAL_SINGER MUSIC_PERFORMER

Everytime We Touch Cascada Cascada

Dancing Queen ABBA Purity

Heaven Bryan Adam

Hotel California Eagles Eagles

Thank you very much for your help,

Kenny.

|||

Changed the "AND" to "OR" have helped, but I have some problem:

1. If I don't enter anything for the "Artist" or in the "Title" box (meaning only of the box have input), no records returned even if I type part of the name of the song/artist, or even the full name of the song/artist.

2. For example if I have two songs that have some similiar words, i.e. "Hotel", in the Title box I typed "Hotel" and in the Artist box, I typed "Eagles", which should return 1 record only that contain both Hotel & Eagles in the record... But instead it returns all the songs with "Hotel" in it.

Thanks again,

Kenny.

|||

I think we had it right the first time itself. AND should work.

|||

Dinakar,

You are right, the "AND" should work... The problem is that it only works if both the Title and Artist text box have values in them... Leaving one or the other blank does not return any results even if they should be... Any help?

Thank you,

Kenny.

|||

HEre's a sample I set up. I was able to get both records when I used "hotel" for music_title and left the second field blank.

Declare @.ttable (col1int identity, MUSIC_TITLEvarchar(50), MUSIC_ORIGINAL_SINGERvarchar(50), MUSIC_PERFORMERvarchar(50))insert into @.tvalues ('Everytime We Touch','Cascada','Cascada')insert into @.tvalues ('Dancing Queen','ABBA','Purity')insert into @.tvalues ('hotel','Bryan Adam',null)insert into @.tvalues ('Hotel California','Eagles','Cascada')DECLARE @.MUSIC_TITLENVARCHAR(100)DECLARE @.MUSIC_ARTISTNVARCHAR(100)SET @.MUSIC_TITLE ='hotel'SET @.MUSIC_ARTIST =nullSELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]FROM @.t--where ([MUSIC_ORIGINAL_SINGER] LIKE '%' + @.MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @.MUSIC_ARTIST + '%')WHERE (@.MUSIC_TITLEISNULL OR [MUSIC_TITLE]LIKE'%' + @.MUSIC_TITLE +'%')AND (@.MUSIC_ARTISTISNULL OR ([MUSIC_ORIGINAL_SINGER]LIKE'%' + @.MUSIC_ARTIST +'%'OR [MUSIC_PERFORMER]LIKE'%' + @.MUSIC_ARTIST +'%'))

|||

I tried the code above in SQL Manager Studio and they seems to work as expected... but somehow it doesn't work with my SqlDataSource! I don't know what else I have to do...

Thank you very, very much for your help Dinakar.

Kenny.

|||

I think that it doesn't work with SqlDataSource because the empty string (when nothing was entered in either one of the textbox) doesn't mean null in ADO.NET... Can anyone help me convert this to a code-behind, or help me convert that empty string to null? I would love to put this in code-behind file but like I said in my first post, I'm very new to ASP.NET.

Thanks,

Kenny.

|||

Unfortunately I dont do any .NET code so I cant help you there. Perhaps you can create a new post and someone might help you there. Posts with 0 replies have a better chance of being "looked at" than the ones with 12 replies.

HELP me in this datediff() function....

Hi, I am facing problem rite now.. I want to calculate the date different minutes between 23:00:00 and 01:00:00.

My code :

datediff(Minute,'01:00:00','23:00:00')

The result is 1320 minutes. (22 hours)... But, the result that I want is 120 minutes (2 hours)...

Can anybody help ?

Thanks in advance...

You have to try this: datediff(Minute,'23:00:00','01:00:00')|||

suigion:

Hi, I am facing problem rite now.. I want to calculate the date different minutes between 23:00:00 and 01:00:00.

My code :

datediff(Minute,'01:00:00','23:00:00')

The result is 1320 minutes. (22 hours)... But, the result that I want is 120 minutes (2 hours)...

Can anybody help ?

Thanks in advance...

Try the link below for how to get correct hours from the SQL Server DateDiff function. Hope this helps.

http://www.stanford.edu/~bsuter/sql-datecomputations.html