MS SQL Server :: Strange Result

3 / 2=1? Strange Result In Expression...HELP!

Oct 29, 2006

Hi Folks!I have a strange thing happening; I have a field that Counts the number ofrecords and another field that shows the number of clients(Count(RecordID)=3 and NoOfClients=2) When I do a simple expression"Count(RecordID)/NoOfClients" the expected result should be 1.5. Insteadthe result I get is 1. Any ideas?Thanks!Rick

View 4 Replies View Related

Strange Result From My Creation Script

Apr 11, 2005

I have a SQL Server 200 database called sampleDB in my local server that uses windows authentication, and all objects are owned by 'dbo' user. I created a creation script using the Eneterprise Manager 2000.
Then I ran the script against a database on my hosting service provider's server, that uses sql server login and not windows authentication, and I found some stored procedures were created with 'dbo' as owner and some with 'sun21170' as owner. 'sun21170' is the user for the database of my hosting service provider's server. Why should not all stored procedures be owned by dbo, as they were in original database on my local server?

View 7 Replies View Related

Strange Stored Procedure Result

May 17, 2006

Hello all, I have the following Stored Procedure that has been working perfectly for the last year:
CODE
====================================================
DELETE FROM tblReportMainMembers
INSERT INTO tblReportMainMembers                      (emplid, userid, membership, price, approvecode, purchasedate, wpecend)SELECT   DISTINCT tblCart.emplid, tblCart.userid, tblCart.membership, tblCart.Price, tblcart.approvecode, tblCart.addedcart, tblCart.addedcart + 365FROM         tblCart INNER JOIN                      tblMemberships ON tblCart.membership = tblMemberships.idWHERE     (tblMemberships.type = 'MAIN') AND approvecode IS NOT NULL AND approvecode <> 'X44444444444'
UPDATE tblReportMainMembersSET tblReportMainMembers.fname = tblRecords.fname, tblReportMainMembers.lname = tblRecords.lname,    --tblReportMainMembers.userid = tblRecords.id,     tblReportMainMembers.address = tblRecords.home_address, tblReportMainMembers.city = tblRecords.city,    tblReportMainMembers.state = tblRecords.state, tblReportMainMembers.zip = tblRecords.zip,    tblReportMainMembers.homephone = tblRecords.home_phone, tblReportMainMembers.officephone = tblRecords.office_phone,    tblReportMainMembers.email = tblRecords.email, tblReportMainMembers.signup = tblRecords.signupFROM tblRecordsWHERE tblReportMainMembers.emplid = tblRecords.emplid
UPDATE tblReportMainMembersSET tblReportMainMembers.membership = tblMemberships.membershipFROM tblMembershipsWHERE tblReportMainMembers.membership = tblMemberships.id
UPDATE tblReportMainMembersSET tblReportMainMembers.emplid = Onecard.dbo.Accounts.CustomFROM Onecard.dbo.AccountsWHERE tblReportMainMembers.emplid = Onecard.dbo.Accounts.Account
SELECT RTRIM(emplid) AS EMPLID,        RTRIM(userid) AS USERID,        RTRIM(fname) AS FNAME,        RTRIM(lname) AS LNAME,        RTRIM(membership) AS MEMBERSHIP,       CAST(price AS varchar(12)) AS PRICE,       RTRIM(approvecode) AS APPROVECODE,       CONVERT(varchar(20), purchasedate, 101) AS PURCHASEDATE,       CONVERT(varchar(20), wpecend, 101) AS WPECEND,       RTRIM(address) AS ADDRESS,        RTRIM(city) AS CITY,       RTRIM(state) AS STATE,        RTRIM(zip) AS ZIP,       RTRIM(homephone) AS HOMEPHONE,        RTRIM(officephone) AS OFFICEPHONE,       RTRIM(email) AS EMAIL, signup AS SIGNUP FROM tblReportMainMembersWHERE fname IS NOT NULL AND lname IS NOT NULLORDER BY lname
As you can tell from the procedure, i copy some records into a report table, do some modifications, and then send the results to the browser. But all of a sudden, i'm getting timeouts on all my users.
But here is the strange part, when i take the above code and run it using Query Analyzer, it works. And then after that, my users are OK running the clients for about 1 week. And then it starts acting up again.
Everytime i run the code in Query Analyzer, i have no more problems for about a week. Weird isn't it.
Any ideas? Thanks in advance.Richard M.

View 1 Replies View Related

Strange Result When Trying To Use The IsMissing Property

Mar 7, 2007

Hello once again !

This is a very strange result I had noticed while using the IsMissing property.

I had the following Expression.

=IIF(Fields!CertainField.IsMissing = True, "N/A", Sum(Fields!.CertainField.Value))
This should just put the text "N/A" into the textfield if the specific field is missing from the dataset in the report.

I had put this in the following TextFields:-

The group's detail and the table Footer.

The result that I had gotten show an empty textfield in the group's detail but showed "N/A" in the page footer. So I know that the expression I had used is correct since it showed up in the table footer. What baffles me is that it didn't give me a "#Error" in the textfield, but an empty string (which essentially is correct, since this particular field in the dataset is missing to begin with).

The question I am asking is, does this have anything to do with the scope or is it something else, and how do I resolve this problem ?

Sincerely,
Bernard Ong

View 7 Replies View Related

Strange Result From A Simple JOIN

Sep 18, 2007

I am currently studying Transact SQL and playing around with queries from a sample database. Recently I created the following query.

USE MemtrackSQL

SELECT m1.MemberID, m1.Surname, m1.FirstName, m1.DateOfBirth

FROM tblMember m1 JOIN tblMember m2

ON m1.FirstName = m2.FirstName

WHERE m1.MemberID <> m2.MemberID

This simple query is designed to show all members with the same first name as other members. The result I got shows duplicates of existing members an inconsistent number of times even though I specified not to show duplicates with WHERE m1.MemberID <> m2.MemberID

2 Scharenguivil Rodney 1958-06-24 00:00:00.000
2 Scharenguivil Rodney 1958-06-24 00:00:00.000
2 Scharenguivil Rodney 1958-06-24 00:00:00.000
5 O'Grady Patrick 1975-09-23 00:00:00.000
7 Greenfield Lynne 1955-07-26 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000

Any help in explaining where I have gone wrong here would be greatly appreciated.

Cheers

View 3 Replies View Related

Backup And Restore Using Tape - Strange Result

Feb 24, 2000

Hi
I have two database called test1 and test2 both are of same size(both device and log). I run the following statements to take the backup of both the databases using the tape device 'tapedevice1'. dump database test1 to 'tapedevice1'
dump database test2 to 'tapedevice1'
After this I run the load statement
load database test2 from 'tapedevice1'.
It just restore the database test2 with test1 data which is added first to the tape with a warning.
Is there any restriction that in a single tape backup of more than one database is not possible.
Your valuable suggestion appreciated
Jiji

View 1 Replies View Related

Strange Result In Stored Procedure Or PROBLEM

Dec 23, 2007

this is my problem in stored procedure
1) TABLE SilokE IS MY TABLE OF THE ALL EMPLOYEE
2) I need to see only the employee than in the table v_un
3) i see all the employee
4) problem in this line ( [new date] = @mydate2,[new_shift2] = )
i see 2 rows of result from fields [new date] + ,[new_shift]
how to see only 1 field for each field

new date new_shift new date empid name
---------------------------------------------------------------
2007-12-01 99 2007-12-02 99 2568947 aaa
2007-12-01 99 2007-12-02 99 2845209 bbbb
2007-12-01 99 2007-12-02 99 4807756 ccc
2007-12-01 99 2007-12-02 99 9819590 ddd
2007-12-01 99 2007-12-02 99 10055648 eee
2007-12-01 99 2007-12-02 99 10815413 ffff
2007-12-01 99 2007-12-02 99 11070042 gggg
2007-12-01 99 2007-12-02 99 11162047 hhh
-------------------------------------------------------------------------------
i need to see only one for each field

new date new_shift id name
---------------------------------------------------------------
2007-12-01 99 2568947 aaa
2007-12-01 99 2845209 bbbb
2007-12-01 99 4807756 ccc
2007-12-01 99 9819590 ddd
2007-12-02 99 10055648 eee
2007-12-02 99 10815413 ffff
2007-12-02 99 11070042 gggg
2007-12-02 99 11162047 hhh

Code Block
DECLARE @yeara [varchar](4)
DECLARE @month1 [varchar](2)
DECLARE @day1 [varchar](2)
DECLARE @day2 [varchar](2)
DECLARE @day3 [varchar](2)
DECLARE @mydate1 [datetime]
DECLARE @mydate2 [datetime]
set @yeara ='2007'
SET @month1 ='12'
Set @day1 ='1'
Set @day2 ='2'
set @mydate1 = CONVERT([datetime] ,@day1 + '/'+ @month1 + '/' + @yearA ,103)
set @mydate2 = CONVERT([datetime] ,@day2 + '/'+ @month1 + '/' + @yearA ,103)
SELECT
SilokE
CASE
WHEN (empid IN (SELECT empid FROM SilokEWHERE (shift = 51 )))
THEN 1
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 11 )))
THEN 2
else 99
END,
[new date] = @mydate2,[new_shift] =
CASE
WHEN (empid IN (SELECT empid FROM v_un
WHERE (shift = 11 )))
THEN 1
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 12 )))
THEN 2
else 99
END,
SilokE.empid, SilokE.Fname
FROM SilokE

View 1 Replies View Related

Modifying Sysxlogins Domain Names In SQL2000, Strange Result..

Jul 20, 2005

It seens that a simple script:Update sysxlogins set name = 'AA001' + substring(name, 9, LEN(name)-8)where name like 'ILLINOIS%'Will replace the SQL2000 domain name correctly in sysxlogins:ILLINOISJonesP becomes AA001JonesPBut for some strange reason via ILLINOISJonesP can still logon viaQueryAnalyzer although he is no longer in the sysxlogins tableanymore? SQL has been stop/started, server even rebooted, yet BOTHthe new and old logins seem to both allow QA login, any thought howthe old one is getting thru SQL security?thanks in advance for any help...

View 2 Replies View Related

Strange Problem Sql 2005 Returning Different Size Result Sets From Different Machine..

Nov 5, 2007

Hi,

I have a very strange problem using SQL Server 2005

I have several machines running an application, the problem is that on all machines except one of them the size of the result set that gets returned when I execute the following query is dfferent:

Select * from custoemr where EmployeeID = 3

on three out of the four machine the size of the result set is 1000, where on the other machine the size of the result set is 250, No errors are generated..

Can someome please teel me how to preceed in resolving this issue..

thanks,

View 2 Replies View Related

Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result

Jul 20, 2005

I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg

View 4 Replies View Related

Problem Assigning SQL Task Result To A Variable - Select Count(*) Result From Oracle Connection

Dec 26, 2007

I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".

Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.

Thanks!

View 5 Replies View Related

Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?

Dec 11, 2007

Hi all,

I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:

USE AdventureWorks;

GO

IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

GO

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE

(

-- Columns returned by the function

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

)

AS

-- Returns the first name, last name, job title, and contact type for the specified contact.

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

ELSE NULL

END;

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'

END;

-- Return the information to the caller

IF @ContactID IS NOT NULL

BEGIN

INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

END;

RETURN;

END;

GO

----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.

Thanks in advance,
Scott Chang

View 1 Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

Feb 13, 2001

HI,
I ran a select * from customers where state ='va', this is the result...

(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes

I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record

Thanks for your help

Ali

View 1 Replies View Related

End Result Is Main Query Results Ordered By Nested Result

May 1, 2008

As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:

SHOWS
showID
showTitle

SHOWACCESS
showID
remoteID

VIDEOS
videoDate
showID

SQL is as follows:

SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;

I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?

thanks

View 3 Replies View Related

CASE Function Result With Result Expression Values (for IN Keyword)

Aug 2, 2007

I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.

WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )

I kept on getting errors, like

Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.

View 3 Replies View Related

Return Subquery Result For Only First Row In Result

Apr 7, 2015

I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.

declare @start smalldatetime
declare @end smalldatetime
set @start = '2015-03-22 00:00' -- this should be a Sunday
set @end = '2015-03-28 23:59' -- this should be the following Saturday

select di.dticket [Contract], di.ddate [Delivered], di.item [Fleet_No], di.descr [Description], dd.min_chg [Delivery_Chg], dd.last_invc_date [Delivery_Invoiced],

[code]....

In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.

View 6 Replies View Related

For Each, Sql Result Set

Jan 28, 2008

Hi All, I need to For Each loop tied back to a SQL result set. This is what I have so far, I know its not much but I'm stuck. Thanks soooo much!TimDim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReaderDim connectionstring As String = ConfigurationManager.ConnectionStrings("Magic").ConnectionStringconn = New SqlConnection(connectionstring)comm = New SqlCommand("Select Distinct([_Group_]) from _Smdba_._Groupdet_ Where [_InActive_:] = 0", conn)
Tryconn.Open()reader = comm.ExecuteReader()
For Each (FOR EACH SQL RECORD) 'I'm going to put some code in here.... reader.Close()Catch dberrorlabel.Text = "Error Loading Admin Panel"Finally conn.Close()End Try

View 3 Replies View Related

Sum When There Is No Result

Dec 2, 2003

hello everyone,

i have an sql statement which is like this

select f1,f2, ( sum(isNull(f3,0)) - (select sum(isNull(e1,0)) from otherTable where e2=Value) )
from Table where f3 = 4

it works great!

but there is a case it dont work it

when the subquery which is:

(select sum(isNull(e1,0)) from otherTable where e2=Value)

has no result it dont do the subtraction operation and the value is Null although the sum of F3 (which is subtracted from ) has a value

so is there any way i can give the sum value, the value of zero if there is no result in the record set.

and just for knowledge, i tried

sum(isNull(e1,0)

and

isNull(sum(e1),0)

the both cases and also it returns nothing

any one can help :)

Thank you

View 2 Replies View Related

Different Result Set

Mar 13, 2001

In 6.5 I ran a SELECT query with a specific WHERE clause. It gave me a result
set of 430 rows. But when I use the same conditions in WHERE clause in UPDATE,
it updated only 420.

What might be the reason?

Thanks,
Chan

View 1 Replies View Related

Have You Got The Same Result As I Have?

Dec 22, 2006

Hi All
Have you got the same result as I have?

select convert(varchar(15), 0x4861707079204368726973746D6173)

View 2 Replies View Related

How To Get This Result

Feb 22, 2006

HI EVERYBODY

YOU ALL REQUESTED TO HELP ME TO COMEOUT FROM PROBLEM : I HAVE A TABLE NAME PLUCK
IN WHICH DATA STORE: MONYEAR EMPCODE PDATE(ddmmyyy) JOBCODE HCD kg
022006 A01608P 01/02/2006 LA N 0
022006 A01608P 02/02/2006 LA N 0
022006 A01608P 03/02/2006 LA N 0
022006 A01608P 04/02/2006 LA N 0
022006 A01608P 05/02/2006 LH N 0
022006 A01693P 01/02/2006 PL N 21
022006 A01693P 02/02/2006 PL N 25
022006 A01693P 03/02/2006 PL N 15
022006 A01693P 04/02/2006 PL N 25
022006 A01693P 05/02/2006 LH N 0

BUT I WANT REPORT LIKE : 022006 A01608P LA N LA N LA N LA N LH N
022006 A01693P 21 N 25 N 15 N 25 N LH N

(IF JOBCODE = PL THAN INSTEAD OF JOBCODE KGPLUCK IS SHOWN..)

Pls guide how to write the SQL to achive the report...

Thanks

ASM

---sample data insert
USE NORTHWIND
CREATE TABLE PLUCK (MONYEAR CHAR(6), EMPCODE CHAR(7), PDATE SMALLDATETIME,JOBCODE CHAR(2), HCD CHAR(1), KGPLUCK INT)

INSERT INTO PLUCK VALUES('022006', 'A01608P','01/02/2006','LA','N',0);
INSERT INTO PLUCK VALUES('022006', 'A01608P','02/02/2006','LA','N',0);
INSERT INTO PLUCK VALUES('022006', 'A01608P','03/02/2006','LA','N',0);
INSERT INTO PLUCK VALUES('022006', 'A01608P','04/02/2006','LA','N',0);
INSERT INTO PLUCK VALUES('022006', 'A01608P','05/02/2006','LH','N',0);

INSERT INTO PLUCK VALUES('022006', 'A01693P','01/02/2006','PL','N',21);
INSERT INTO PLUCK VALUES('022006', 'A01693P','02/02/2006','PL','N',25);
INSERT INTO PLUCK VALUES('022006', 'A01693P','03/02/2006','PL','N',15);
INSERT INTO PLUCK VALUES('022006', 'A01693P','04/02/2006','PL','N',25);
INSERT INTO PLUCK VALUES
('022006', 'A01693P','05/02/2006','LH','N',0);

View 2 Replies View Related

Result Row

Mar 12, 2008

Dear All,Can we do multiple result row as a single row while selection ..?Request you to provide the SQL if we can achieve this...For Eg,Table Name = Employeeid - EmployeeName - addresstype - address1 - address2 - City1 - AAA - permanent - 231 - First Street - XYA2 - AAA - Temporary - 343 - Second Street - XYAExpecting Result as a Single row as below,id - EmployeeName - Address1 - Address21 - AAA - 231/First Street/XYA - 343/Second Street/XYAThanks in advanceRgds,Ganapathi sundaram.G

View 1 Replies View Related

How To Merge Result In One Row

Nov 28, 2006

Hello,Is there a way to merge the result of a query into one row?For example, let say I want the a list of name of people of 20 year soldMy result will look like that: NAME1 john2 Mike3 Craig I would like to have the result in one row: NAME1 John, Mike, CraigIs there a way to do that?Thanks

View 3 Replies View Related

Search Result .......need Some Help

Dec 21, 2006

Dear Experts
Here is my code:
SqlConnectioncn = new SqlConnection(ConfigurationManager.ConnectionStrings["SimvipConnectionString"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("SP_SEARCH", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@TypeOfSim", Request.QueryString["NID"]));
cmd.Parameters.Add(new SqlParameter("@CategoryID", Request.QueryString["CID"]));
cmd.Parameters.Add(new SqlParameter("@Price", Request.QueryString["L"]));
cmd.Parameters.Add(new SqlParameter("@Price2", Request.QueryString["S"]));
cmd.Parameters.Add(new SqlParameter("@Title", Request.QueryString["keyword"]));
cmd.Connection.Open();
cmd.ExecuteNonQuery();
SqlDataAdapter sqlAdapter1 = new SqlDataAdapter();
sqlAdapter1.SelectCommand = cmd;
DataSet ds = new DataSet();
sqlAdapter1.Fill(ds, "ds");

CollectionPager1.DataSource = ds.Tables["ds"].DefaultView;
CollectionPager1.BindToControl = DataList1;
DataList1.DataSource = CollectionPager1.DataSourcePaged;
}
My problem is when there is no result(no item in database), the DataList1 will display the text "some text will display here" instead of empty page.
In FormView I know the option <EmptyDataTemplate>. But in the DataList I don't know how to do this. I am a beginner.
Can anybody show me how....Thanks a lots

View 1 Replies View Related

Problem In Getting Result

Jun 20, 2007

I have an account field which has datatype string. I want to only get those values which are in between 0-199.
I used
select sum(t.a_trans_amt) Credit from a_account a, a_transaction t
where a.a_account_num between '0' and '199'and
t.a_account_id=a.a_account_id
and t.a_debit_credit_ind='C'but this query also including those values which have starting 3 digit in between 0-199.
I don't know how to fix this problem . Can anybody help me on this issue.
Thanks in Advance.

View 3 Replies View Related

Need Help On Getting Result From Row To Column

Jan 17, 2008

Hi
I need help to build the below data to my requirement
i have table having the following data's

NoofLive
Action
Sold
Ratio
EffDt

10-49
43
0
0
1/1/2008

50-99
62
0
0
1/1/2008

100-199
73
0
0
1/1/2008

200-299
17
1
0.059
1/1/2008

300-499
25
0
0
1/1/2008

500-999
21
0
0
1/1/2008

1000+
45
0
0
1/1/2008

100-199
11
1
0.091
2/1/2008

1000+
1
0
0
2/1/2008

100-199
17
0
0
3/1/2008

500-999
2
0
0
3/1/2008
EffDt = (Jan-08,Feb-08,Mar-08)
I want creat result as follows from the above data's

Jan-08
Feb-08
Mar-08

Eligible Lives
Quoted
Sold
Close Ratio
Quoted
Sold
Close Ratio
Quoted
Sold
Close Ratio

10 - 49
43
0
0
0
0
0
0
0
0

50 - 99
62
0
0
0
0
0
0
0
0

100 - 199
73
0
0
11
0
0
0
0
0

200 - 299
17
1
0.059
0
0
0
17
0
0

300 - 499
25
0
0
0
0
0
0
0
0

500 - 999
21
0
0
0
0
0
0
0
0

1000+
45
0
0
1
0
0
2
0
0
Please anybody can help build a query to generate the above result.
Thanks in advance
sthanika

View 1 Replies View Related

How To Get Sum() Result In Query?

Mar 15, 2008

mail_delivery_master----------------------ml_id ml_from_mail_id ml_to_mail_id ml_user_id ml_subject ml_content ml_file_attached ml_no_of_file_attached ----------- ---------------------------------------- -------------- ------------ --------------------------------------------------------------------------------------------------------------------------------1 aa bb cc dd ee 2 joe@hotmail.com tt@gmail.com, JOHNYJP Forum answer somebody answer 0 1 3 joe@hotmail.com mailme@hotmail.com, JOHNYJP Forum answer somebody answer -1 0 4 joe@hotmail.com mailme@hotmail.com,janani@ajsquare.net, JOHNYJP Forum answer somebody answer 0 0 5 joe@hotmail.com janani@ajsquare.net, JOHNYJP Forum answer somebody answer 0 0 6 joe@hotmail.com dff@gg.com, JOHNYJP Forum answer somebody answer 0 0 7 joe@hotmail.com tt@gmail.com,janani@ajsquare.net, JOHNYJP Forum answer somebody answer 0 0 8 Durai@gamial.com yogesh@gamail.com durai test test 0 0 9 janani@ajsquare.net devi@ajsquare.net janu test hi 0 0 10 janani@ajsquare.net devi@ajsquare.net JANANI test Miss u -1 2 mail_attachementsml_id ml_file_name ml_file_format ml_file_size ml_file_select_from ----------- ---------------------------- -------------- ------------ ------------------------------------------ 1 chocolate .jpg 114528 F: empchocolate.pjg 2 Tiger Caspian Blue.jpg .jpg 1114407 F:imageTiger Caspian Blue.jpg 3 Autumn.jpg .jpg 66287 F:imageAutumn.jpg 4 Ascent.jpg .jpg 63244 F:imageAscent.jpg 5 Azul.jpg .jpg 61365 F:imageAzul.jpg 6 daisy.jpg .jpg 8197 F:imagedaisy.jpg 7 Stonehenge.jpg .jpg 59600 F:imageStonehenge.jpg i want the result :ml_id , ml_from_mail_id, ml_to_mail_id from mail_delivery_master ,,,, and sum(ml_file_size) for that mail from mail_attachements give me the code solution regards samuel chandradoss

View 2 Replies View Related

No Result In WHEREANDClause,why???

May 18, 2008

Hi y'all, I have a problem in my query I think maybe you guys could help.I have this query:SELECT dbo.BOS_GL_Workplace.WpszState, Emp2.szName, Emp1.szName AS szEmpName, Emp1.szEmployeeId, dbo.BOS_PI_Division.szName AS Divisi, dbo.BOS_PI_Team.szDescription,dbo.BOS_SD_Route.szRouteId, dbo.BOS_SD_Route.szDescription, dbo.BOS_SD_Route.szScheduleId, dbo.BOS_SD_RouteItem.szCustId, dbo.BOS_SD_Route.szOpUserId,dbo.BOS_AR_Customer.szCustId ,dbo.BOS_AR_Customer.szName, dbo.BOS_AR_Customer.CustszAddress_1 AS SendAddres, dbo.BOS_AR_Customer.szTaxId, dbo.BOS_AR_Customer.CustszAddress_1 AS CollectAddress, dbo.BOS_AR_Customer.CustszCity, dbo.BOS_AR_Customer.CustszZipCode, dbo.BOS_AR_Customer.szStatus, dbo.BOS_AR_CustSales.szPaymentTermId, dbo.BOS_AR_CustSales.decCreditLimit,BOS_AR_Customer.dtmStart,BOS_AR_Customer.dtmLastUpdated FROM dbo.BOS_PI_Employee Emp1 LEFT OUTER JOIN BOS_PI_Employee Emp2 on Emp1.szSupervisorID = Emp2.szEmployeeID Left outer join dbo.BOS_GL_Workplace ON Emp1.szWorkplaceId = dbo.BOS_GL_Workplace.szWorkplaceId LEFT OUTER JOIN dbo.BOS_PI_Division ON Emp1.szDivisionId = dbo.BOS_PI_Division.szDivisionId LEFT OUTER JOIN dbo.BOS_PI_Team ON Emp1.szTeamId = dbo.BOS_PI_Team.szTeamId LEFT OUTER JOIN BOS_SD_Route on Emp1.szEmployeeId = BOS_SD_route.szOPuserId LEFT OUTER JOIN BOS_SD_RouteItem on BOS_SD_Route.szRouteId = BOS_SD_RouteItem.szRouteID LEFT OUTER JOIN BOS_AR_Customer on BOS_SD_RouteItem.szCustID = BOS_AR_Customer.szCUstID LEFT OUTER JOIN BOS_AR_CustSales on BOS_AR_Customer.szCustId = BOS_AR_CustSales.szCustID --order by BOS_PI_Employee_1.szNameWhen I query it's working fine the results is the way as I expected. But when I add where--and--clause like this :WHERE Emp2.szName >= 'JACK ANDREWS' AND Emp2.szName <='RICK COLLINS'it's only showing the header and no result at all, why ??? can you guys help me please, I appreciate any kinds of help. Thanks. FYI: I use SQL Server 2000 Best Regards.

View 3 Replies View Related

Unspected Result

Jul 10, 2004

Hi Ive just started with asp.net and this question is maby stupid but I cant find the error.

Problem: I am trying to make a loginpage with the username and password stored in a database. The page do not generates any error then runnin BUT it is not possible to log in. The sql-procedure always return -1 even if the username do exist.

--------------------------------------------
Function MyAuthentication(strUsername As String, strPassword As String) As Integer

Dim myConn As SQLConnection
Dim myCmd As SQLCommand
Dim myReturn As SQLParameter
Dim intResult As Integer
Dim conn As String

conn = ConfigurationSettings.AppSettings("Generala")
myConn = New SQLConnection(conn)

myCmd = New SQLCommand("MyAuthentication",myConn)
myCmd.CommandType = CommandType.StoredProcedure

myReturn = myCmd.Parameters.Add("RETURN_VALUE",SqlDbType.Int)
myReturn.Direction = ParameterDirection.ReturnValue

myCmd.Parameters.Add("@username",strUsername)
myCmd.Parameters.Add("@password",strPassword)

myConn.Open()
myCmd.ExecuteNonQuery()
intResult = myCmd.Parameters( "RETURN_VALUE" ).Value
myConn.Close()

If intResult < 0 Then
If intResult = -1 Then
lblMessage.Visible = true
lblMessage.Text = "Username Not Registered!<br><br>"
Else
lblMessage.Visible = true
lblMessage.Text = "Invalid Password!<br><br>"
End If
End If

Return intResult
End Function

-----------------------
SQL Procedure
----------------------

CREATE PROCEDURE MyAuthentication (@username As Char( 25 ), @password As Char( 25 )) As

DECLARE @actualPassword Char( 25 )

SELECT @actualPassword=Password FROM [User] WHERE user=@username

IF @actualPassword IS NOT NULL
IF @password = @actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1
GO

-----------------------

Someone that can help me with and find the error?

View 4 Replies View Related

Retrieve A Particular Row From A Result Set

Aug 21, 2004

Hi all,

Is it possible to retrieve a particular row from a result set? For eg if my query returns 5 rows and i want to just retrieve the 3rd row from the result....is it possible? If yes...can someone tell me the syntax for it....would appreciate the gr8 help...

Thanks,
SQL Novice

View 1 Replies View Related

Result Set Into Array

Aug 22, 2004

Hi All,

How can I read a query result set (which are of type VARCHAR) into an array? i.e the result set comprises of just one column and 5 rows and i want to save these into an array to easily extract each row whenever i want to. Hope I have conveyed my idea clearly.

Gayathri

View 1 Replies View Related

Grouping Result

Jan 25, 2005

Here's my query:

SELECT col_1, col_2, col_3, col_4 FROM my_table
WHERE (col_1 = @col_1) AND (col_2 = @col_2)
ORDER BY col_1

I want my result rows to have an uniqe value in col_3. How can I exclude rows (but one) that have the same value in col_3?

Thanx

/sf

View 3 Replies View Related

Strange Result - Minus Result -1