SCOPE_IDENITY() - Incorrect Value (Returned)

Dec 21, 2007

There are two tables
Table_1:
ProductID  (Identity Increment) ProductDescription (nvarchar)

 
Table_2:
ProductID (int)


I have a sql statement and it's like this.  This SQL is probably incorrect - but hopefully gives you an idea of what I am trying to do:

 
DECLARE @getTheNewProductID int

INSERT INTO Table_1 (ProductDescription) VALUES ('SomeValue') ; SET @getTheNewProductID = SELECT SCOPE_IDENTITY()


INSERT INTO Table_2 (ProductID) VALUES (@getTheNewProductID)
 

What I need is: when inserting into Table_1 to get the Exact New Product Id from it that occurs from the identity increment - then insert that exact same product ID into table_2
The problem is it is returning incorrect values on the scope identity. Such as values from two transaction ago.
How do you do this?
I did try using @@identity which may have stuffed things up??
 
Thanks for your help

View 1 Replies


ADVERTISEMENT

Incorrect Value Returned To Page

Apr 21, 2008

 Hi,I have a shopping cart and before each product is added to the cart it checks to see if the stock control is turned on if so then checks the quantity available.My problem is that when i run the query  select [dbStatus] from InventoryControl where [ID]=1 to view the status it is returning the incorrect bit value for dbStatus.If "dbStatus" is 0 in the database it returns 0 which is correct, but if the "dbStatus" is 1 in the db it returns -1!! If i run the query in query analyser the correct values are returned. Any ideas???Thanks in Advance 

View 2 Replies View Related

Incorrect Value Returned From Stored Procedure

Sep 26, 2006

I have an asp.net 1.1 website that uses sql server 2000 and vb.I have a bit of a dilema, when I run a stored procedure in a webpage it returns the wrong value, but if I run itin the query analyzer the correct value is returned. Dim orderHistory As nlb.OrdersDB = New nlb.OrdersDB

' Obtain Order ID from QueryString
Dim OrderID As Integer = CInt(Request.Params("ID"))

' Get the customer ID too
Dim myNewCustomerId As Integer = 0
myNewCustomerId = orderHistory.GetOrderCustomer(OrderID)



Public Function GetOrderCustomer(ByVal orderID As Integer) As Integer

' Create Instance of Connection and Command Object
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("nlbsp_OrdersCustomerID", myConnection)

' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC
Dim parameterOrderID As New SqlParameter("@order_id", SqlDbType.Int, 4)
parameterOrderID.Value = orderID
myCommand.Parameters.Add(parameterOrderID)

Dim parameterOrderCustID As New SqlParameter("@customer_id", SqlDbType.Int, 4)
parameterOrderCustID.Value = ParameterDirection.Output
myCommand.Parameters.Add(parameterOrderCustID)

'Open the connection and execute the Command
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

' Return the customer_id (obtained as out paramter of SPROC)

If parameterOrderCustID.Value <> 0 Then
Return CInt(parameterOrderCustID.Value)
Else
Return 0
End If

End Function


the stored procdure is
CREATE PROCEDURE [dbo].[nlbsp_OrdersCustomerID]
(
@order_id int,
@customer_id int OUTPUT
)
AS

/* Return the customer_id from the Orders. */
SELECT
@customer_id = customer_id

FROM
nlb_Orders

WHERE
order_id = @order_id
GO

 I know a particular order_id returns a value of 1. But when I run it in the webpage it always comes back as 2. Any ideas would be appreciated ThanksPete

View 1 Replies View Related

SUM And JOIN And Possibly GROUP BY - Incorrect Value Returned By SUM

Mar 23, 2006

It's me again :)

So; if you read my earlier thread here (http://www.dbforums.com/showthread.php?t=1214353), you'll know that I'm trying to build stored procedures to deal with ticketing queries, and that it's all getting a bit complicated. I have, however, made a bit of progress and now have the following working:


CREATE PROCEDURE [dbo].[getAvailableTickets]
@eventId INT,
@standId INT,
@admissionDateId INT,
@concessionId INT,
@userId INT

AS

DECLARE @startyear DATETIME
DECLARE @endyear DATETIME
SELECT @startyear=CONVERT(datetime, '2006/01/01')
SELECT @endyear=CONVERT(datetime, '2006/12/31')


SELECT
[tblTickets].[id] AS ticketId,
[tblEvents].[id] AS eventId,
[tblStands].[id] AS standId,
[tblAdmissionDates].[id] AS admitDateId,
[tblEvents].[event_name],
[tblStands].[stand_name],
[tblTicketConcessions].[concession_name],
[tblMemberships].[membership_name],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblBookingMinQuantities]. AS minBookingQuantity,
[tblBookingMaxQuantities].[booking_quantity] AS maxBookingQuantity,
MIN([tblQuotas].[quota]) AS Quota,
[B]SUM([tblBasket].[ticket_quantity]) AS History,
[tblTickets].[price],
[tblTickets].[availability]

FROM [tblTickets]
LEFT JOIN [tblEvents]ON [tblEvents].[id] = [tblTickets].[event_id]
LEFT JOIN [tblStands]ON [tblStands].[id] = [tblTickets].[stand_id]
LEFT JOIN [tblBookingDates]ON [tblBookingDates].[id] = [tblTickets].
LEFT JOIN [tblTicketConcessions]ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantitiesON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
LEFT JOIN [tblAdmissionDates]ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
LEFT JOIN [tblMemberships]ON [tblMemberships].[id] = [tblTickets].[membership_id]
LEFT JOIN [tblQuotas]ON
([tblQuotas].[event_id] = [tblTickets].[event_id] OR [tblQuotas].[event_id] IS NULL) AND
([tblQuotas].[stand_id] = [tblTickets].[stand_id] OR [tblQuotas].[stand_id] IS NULL) AND
([tblQuotas].[admission_date_id] = [tblTickets].[admission_date_id] OR [tblQuotas].[admission_date_id] IS NULL) AND
([tblQuotas].[concession_id] = [tblTickets].[ticket_concession_id] OR [tblQuotas].[concession_id] IS NULL) AND
([tblQuotas].[membership_id] = [tblTickets].[membership_id] OR [tblQuotas].[membership_id] IS NULL) AND
([tblQuotas].[ticket_id] = [tblTickets].[id] OR [tblQuotas].[ticket_id] IS NULL)
[B]LEFT JOIN [tblBasket] ON [tblBasket].[ticket_id] = [tblTickets].[id]
LEFT JOIN [tblOrders] ON [tblOrders].[id] = [tblBasket].[order_id]

WHERE 1=1
AND ([tblTickets].[ticket_open] = 1)
AND (([tblEvents].[id] = @eventId OR @eventId = 0)AND ([tblEvents].[event_open] = 1))
AND (([tblStands].[id] = @standId OR @standId = 0)AND ([tblStands].[stand_open] = 1))
AND (([tblAdmissionDates].[id] = @admissionDateId OR @admissionDateId = 0)AND ([tblAdmissionDates].[date_open] = 1))
AND ([tblTicketConcessions].[id] = @concessionId OR @concessionId = 0)
AND ((getdate() BETWEEN [tblBookingDates]. AND [tblBookingDates].[booking_end_date]) OR ([tblBookingDates].[booking_start_date] IS NULL AND [tblBookingDates].[booking_end_date] IS NULL))
AND (([tblMemberships].[id] IN (SELECT [membership_id] FROM [tblUsers_Memberships] WHERE [user_id]=@userId)) OR [tblMemberships].[id] IS NULL)
[B]AND ([tblOrders].[user_id] = @userId OR @userId=0)

GROUP BY
[tblTickets].[id],
[tblEvents].[id],
[tblStands].[id],
[tblAdmissionDates].[id],
[tblEvents].[event_name],
[tblStands].[stand_name],
[tblTicketConcessions].[concession_name],
[tblMemberships].[membership_name],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblBookingMinQuantities].[booking_quantity],
[tblBookingMaxQuantities].[booking_quantity],
[tblTickets].[price],
[tblTickets].[availability]
GO



Except... there's two problems with it. One is that it only returns tickets that you've already bought, the other is that it doens't work out correctly how many of those tickets you've bought in past orders.

This is what's in tblBasket:


idticket_idquantityorder_iddate
152321/03/2006
262321/03/2006
3144421/03/2006
4154421/03/2006
551421/03/2006


Both the orders in there are for the same user id: "1". All the tickets in there are tied to event "2".

When I run Exec dbo.getAvailableTickets 2,0,0,0,1, it tells me it's found 6 of ticket 5, 4 of ticket 6 and 12 of ticket 14. And I can't for the life of me figure out how it's calculating it. Any ideas?

And how do i get it to return all tickets regardless of whether you've bought them previously or not?

View 4 Replies View Related

Heavily Fragmented Index - Incorrect Data Returned?

Dec 1, 2007

Hi everyone,
If I have a table with some indexes on the foriegn keys and these indexes are heavily fragmented (80%+), is it normal for queries to return incorrect results?

For example if I had a table called Customer( CustID, Name) and Orders (OrderID, CustID, Product, Date).
Lets say I have a non clustered index on CustID in Orders table, and the clustered indexes are Customer.CustID and Orders.OrderID


If the non clusterd index on Orders.CustID becomes heavily fragmented and I am querying the Orders table with TSQL "SELECT * FROM Orders where CustID = @CustID" I sometimes get missing data or incorrect results. In one case all orders for a particular year were missing, but if I queried using OderID they were returned. Rebuilding the index fixed the problem.

I know the index should be rebuilt or reorganized depending on the fragmentation but if one happened to become this fragmented should it start returning incorrect data?

- Using SQL Server Express 2005

View 3 Replies View Related

Data Returned From .WriteXML Is Different Than What Is Returned In Query Analyzer.

Jun 20, 2006

I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3.   string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE  a.RegionCode = b.RegionCode"; 5.   DataSet dsMarketList = new DataSet();6.   SqlConnection sqlConn = new SqlConnection(intranetConnStr);   7.   SqlCommand cmd = new SqlCommand(sql,sqlConn);8.  sqlConn.Open();9.   SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10.   try11.   {12.   adapter.Fill(dsMarketList);
 13.  String bling = adapter.SelectCommand.CommandText;//BRG 14.   dsMarketList.DataSetName="RegionMarket"; 15.  dsMarketList.Tables[0].TableName = "MarketList"; 16.    dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to  17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18.           // from the SQL query 19.  } 20.  catch(Exception e) 21. {  22. // Handle the exception (Code not shown)

View 2 Replies View Related

Incorrect Syntax Near The Keyword 'from'. Line 1: Incorrect Syntax Near ')'.

May 27, 2008

This is the error it gives me for my code and then it calls out line 102.  Line 102 is my  buildDD(sql, ddlPernames)  When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one.  Could it not like something in my sql select statement.  thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
 
End Sub

View 2 Replies View Related

Incorrect Syntax Near The Keyword 'SELECT'.Incorrect Syntax Near The Keyword 'else'.

May 22, 2008

What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.

View 5 Replies View Related

One Row Returned But E.AffectedRows ==0?

Jan 9, 2007

Stored Procedure:CREATE PROCEDURE [dbo].[GetBanner]     @BannerPage nvarchar(50),    @MagazineID intASBEGIN    SET NOCOUNT ON;    DECLARE @BannerID int     SELECT TOP 1    @BannerID = BannerID    FROM Banners    WHERE BannerPage=@BannerPage AND MagazineID = @MagazineID    AND StartDate <= GetDate() AND EndDate >=GetDate()    ORDER BY Views    SELECT     BannerID,     BannerFileName,     AltText,     URL,     Views     FROM Banners     WHERE BannerID = @BannerID           UPDATE Banners SET Views = Views +1 WHERE BannerID = @BannerID     UPDATE BannerStats SET Views = Views + 1 WHERE BannerID = @BannerIDEND OnSelected method: protected void dsBanner_Selected(object sender, SqlDataSourceStatusEventArgs e)    {        int RecordCount = e.AffectedRows;            Response.Write(RecordCount);    }  This ALWAYS gives me 0 - despite the fact that the correct banner is pulled from the database, and updated etc.  Also, when I execute the procedure (for valid parameter values) in SSMS, I get "1 row(s) Affected".  Can anyone tell me what I need to change to get the proper value? Thanks   

View 6 Replies View Related

Name Column Returned

Sep 27, 2007

 I have the following sql:SELECT courseModuleCode FROM courses WHERE courseCode like '%' + @prefixText + '%'UNION
 SELECT courseName FROM courses WHERE courseName like '%' + @prefixText + '%'
UNIONSELECT TeacherName FROM courses WHERE TeacherName like '%' + @prefixText + '%' it returns one column, i want to name tha column as "words"how do i do it?thanks  

View 3 Replies View Related

No Record Set Returned

Sep 10, 2004

I have a table that COULD be linked to 3 other tables, but if one of the link fields is zero I get nothing back at all. Here is my query:
SELECTEvents.*, Agencies.A_NAME AS A_NAME,
(Families.F_FNAME + ' ' + Families.F_LNAME) AS F_NAME,
(Candidates.C_FNAME + ' ' + Candidates.C_LNAME) AS C_NAME
FROM Events
INNER JOIN Agencies ON Events.E_AGENCY_ID = Agencies.A_ID
INNER JOIN Families ON Events.E_FAMILY_ID = Families.F_ID
INNER JOIN Candidates ON Events.E_CANDIDATE_ID = Candidates.C_ID
WHEREE_ID = 89

Even if all the inner join id's are zero, I would at least like to get back the event information.
Could anyone help me out here, or do I have to do 4 queries?

Thanks in advance.

View 2 Replies View Related

Once Again, No Records Returned....

Dec 21, 2004

The following sproc returns no records in query analyzer, although it doesn't error out either. Last time adding the length to the end of the variable fixed this problem, but this time it's an integer type which doesn't accept a length. Any ideas?



----------------------------------------------------------------------------------------------------
CREATE PROCEDURE spUnitsbyUnitID
@unitid int
AS

SELECT
E.camid, E.camname, E.cammodel, E.unitid,
D.contactid, D.Contactfname, D.Contactlname, D.Contactphone, D.Contactcell, D.Contactcompany, D.unitid,
C.videoserverid, C.videoservermac, C.videoserveruser, C.videoserverpass, C.videoservermodel, C.videoserverip, C.unitid,
B.radioid, B.radioip, B.radiomac, B.radioessid, B.radiouser, B.radiopass, B.unitid,
A.unitid, A.unitcity, A.unitname, A.unitalias, A.unitdeploydate, A.unitpickupdate, A.unitattatchedcams, A.unitenabled

FROM tbl_units as A

INNER JOIN tbl_radios as B ON A.unitid = B.unitid
INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid
INNER JOIN tbl_contacts as D on A.unitid = D.unitid
INNER JOIN tbl_cameras as E on A.unitid = E.unitid

WHERE A.UnitID = @unitID
GO
-----------------------------------------------------------------------------------------------------

View 3 Replies View Related

Subquery Returned More Than 1 Value...

Mar 9, 2006

can anyone tell me why i get the following error when i run the below query? its a simple query. i dont understand why its throwing an error. Thanks in advance.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

update purchaseorders set orderstatusID = 4 where purchaseorderid in (
select distinct postprocesspurchaseorderID from wishlistitems where postprocesspurchaseorderID is not null
)

View 1 Replies View Related

Different No. Of Rows Returned In SEM Vs QA

Nov 19, 2004

Hi,

Any idea why when I run a SELECT stament in Query anaylser it returns 45 rows. But when I create the exact same SQL as a view in Enterprise manager it only returns 44 rows?

Thanks,

Alph

View 3 Replies View Related

Subquery Returned More Than 1 Value

Dec 7, 2011

I am getting the below error while trying to run sql query

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

but if in the same below query if i put the parameter in upto 10 then its working fine......if the parameter is 11 or above then the query is giving the above error.

SELECT substring(sc.container_id,0,14) container_id,
imc.item_class,
sh.ship_to, sh.shipment_id,
cust.ADDRESS1,
sh.carrier,

[code]...

View 3 Replies View Related

Exclude If Returned Value Is 0

Jan 27, 2012

I am running a query that works just fine however, I would like it to exclude value that are equal to zero.

Basically my query looks at the commission that clients pay over a number of periods.

So it goes like this

T.Client_Code as Client
,SUM(CASE t.Transaction_Date WHEN DATEADD(day, DATEDIFF(day, 1, GETDATE()),0)THEN (ABS (t.transaction_commission) /((fx.Exchange_Bid + fx.Exchange_Ask)/2 )) ELSE 0 END)as Commission_Day

FROM TABLE T
JOINING FX TABLE

WHERE
fx.Currency = 'USD'

And T.Salesman_Name in ('X''Y'Z)

Group BY
T.Client_Code

It works perfectly fine however, we dont transact with our clients everyday so therefore this list will return all of our clients in the database and many will have generate zero commission. I want to keep the query along those lines I just need to insert something that says "ONLY SHOW WHEN Commission is not ZERO.

View 2 Replies View Related

Subquery Returned More Than 1 Value?

Mar 25, 2015

i am trying to run Select query to get "application name" and "role" for each user in the DB problem is that each user has more than one App that he is useing and for each App he can have more than one role

My code is:

Code:
select top 100
username,
(Select b.name
from application b
inner join userroleapplication c
on b.applicationID = c.applicationID ) Application,
(select name from role d, userroleapplication gr
where gr.roleID = d.roleID and gr.userID = a.userID) role
from users a
where username in
(
'username'
)

i know where is the problem but i cant figure how to fix this

What i need as a output

Username | Application | Role

"user1" | "App1" | "Role1"
"user1" | "App1" | "Role2"
"user1" | "App2" | "Role1"
"user1" | "App3" | "Role1"
"user2" | "App1" | "Role1"
"user2" | "App3" | "Role1"
etc.

View 2 Replies View Related

Subquery Returned More Than 1 Value?

Apr 15, 2008

Hi

I have two tables and I need write a query that include subquery that return more than 1 value,Can you give me a idea how to write the query

The table with data is like this,one file maybe belong to many group.

Filename FilegroupID

file1 1
file2 2
file1 3
file1 4
file3 1
file2 5


I need write a query to get the result like this

file1 1,3,4
file2 2,5
file3 1

I try to write a query with subquery but I get error message

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"

I use SQL Server 2005.but I need the subquery return data like "1,3,4".

Please help me.

Thanks

Mark

View 7 Replies View Related

Why Are Nulls Not Being Returned?

Apr 29, 2008

Select * from table where field <> 'This'


Why does that not return nulls? 'This' does not equal null. Why does sql server work this way?

View 2 Replies View Related

Subquery Returned More Than 1 Value

Dec 18, 2013

I have a simple update query that looks like this:

Update Table1 set midpointDate = (select dateadd(day,(datediff(day,startDate,endDate)/2),starteDate) from Table1) where Table1.RowID = Table1.RowID

I am receiving the following error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."Yes, it is all contained in the same table.

View 5 Replies View Related

How I Will Come To Know The No. Of Rows Returned?

Jun 12, 2007

I am using SQLExecute() in C++ code.
My query is "select * from Revoked_users"?
How I will come to know the no. of rows returned by SQLExecute() ?

View 4 Replies View Related

Subquery Returned More Than 1 Value

Jan 26, 2008

Okay,
I want to fill a table in one database with some info from an identical table from another database. When I run this query, I get the following error:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."

But the point of the subquery was to in fact return more than one value.

View 2 Replies View Related

Null Out A Returned Value

Jan 29, 2008

Anyone know how I can null-out the returned values of the PrjMgr field? I have bolded the field in question.
Here is the code:


select AgencyId
,ControlNumber
,ISNULL(LLA_AUDITS.PrjMgr,Projects.PrjMgr) as PrjMgr,ISNULL(CASE LEN(datepart(mm, PeriodFinish)) WHEN 1 THEN '0' + CAST(datepart(mm, PeriodFinish) as varchar(2)) ELSE CAST(datepart(mm, PeriodFinish) as varchar(2)) END ,'')As month
,ISNULL(CAST(datepart(dd, PeriodFinish) as varchar), '') As day
,ISNULL(CAST(right(datepart(yy, PeriodFinish),2) as varchar),'') As year
,ISNULL(CAST(left(datepart(yy, PeriodFinish),2) as varchar),'') As century
,custName
from LLA_Audits Left Outer Join Projects
on controlnumber = prjcode Left Outer Join Customers on Agencyid = CustCode
where AgencyId is not null
and not(LLA_AUDITS.PrjMgr is null and Projects.PrjMgr is null)
and left(controlnumber,2)<>'72'
and left(controlnumber,2) <>'13'
and (CPAFirmId IN ('0','')or CPAFirmId is null)
order by ControlNumber

View 8 Replies View Related

No Rows Returned; Why?

Apr 7, 2008

The code below returns 0 rows. The statement is intended as generic statement that would return all records between a particular start and end date and on each date only between a specific start and end time. It works perfectly if the end date is greater than the start date.
Unfortunately, if the start and end dates are equal (i.e, return all records on that one date and only between the specified start and end times) then no records are returned.
BTW, it is also looking for matching datetimes in two tables and there really is matching data in the two tables for that particular date and times.
Can anybody help resolve this?

SELECT DISTINCT t1.* FROM [DbName].[SchemaName].[TableName1] AS t1,

[DbName].[SchemaName].[TableName2] AS t2

WHERE t1.[DateTime] BETWEEN CAST('2006-11-22' AS DATETIME) AND CAST('2006-11-22' AS DATETIME)

AND t2.[DateTime] BETWEEN CAST('2006-11-22' AS DATETIME) AND CAST('2006-11-22' AS DATETIME) AND

CONVERT(DATETIME,CONVERT(varchar, t1.[DateTime],114)) BETWEEN

CONVERT(DATETIME, '10:20:00') AND CONVERT(DATETIME, '12:19:59') AND

CONVERT(DATETIME,CONVERT(varchar, t2.[DateTime],114)) BETWEEN

CONVERT(DATETIME, '10:20:00') AND CONVERT(DATETIME, '12:19:59') AND

t1.[DateTime] = t2.[DateTime]

ORDER BY t1.[DateTime];

View 11 Replies View Related

Subquery Returned More Than 1 Value

Aug 28, 2007

Okay, this is what I have as my store procedure:

DECLARE @parentPage varchar(250)

-- Insert statements for procedure here

SET @parentPage = (SELECT [frntPage] FROM [OLissue], [Outlook] WHERE ([Outlook].[issueID] = @OLissueID AND [Outlook].[issueID] = [OLissue].[issueID] AND [OLissue].[frntPage] IS NOT NULL AND [Outlook].[parent] IS NOT NULL))


IF @parentPage IS NOT NULL


IF exists (SELECT [Outlook].[ID], [Outlook].[title], [Outlook].[name], [Outlook].[issueID] FROM [OLissue], [Outlook] WHERE ([Outlook].[issueID] = @OLissueID AND [Outlook].[issueID] = [OLissue].[issueID]))
SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)

ELSE


SELECT DISTINCT NULL ID, 'No parent page yet' title FROM [Outlook]

And I kept getting this error when testing in SQL Management Studio.


Msg 512, Level 16, State 1, Procedure parentPage, Line 19

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

View 1 Replies View Related

No Records Returned

Jun 12, 2007

I have a simple report with a group header, details and footer. How do I display a message to inform users when no data is returned when they enter a parameter which returns no records - at the moment my report returns a blank screen, it would be more useful if I could output the following

'Parameter Name' does not exist.

View 1 Replies View Related

Get Returned Value From Proc

May 9, 2008



I have a stored procedure that is going to be called from my ssis job. I create a package and it as an 'Execute SQL Task' within it. My stored procedure will return a 1 or 0. If 1 is returned then the package needs to continue processing. If
0 is returned then the job stops running.

How can I get the value that is being returned from the stored procedure and either continue or stop the job depending on what is returned?

View 19 Replies View Related

Subquery Returned More Than 1 Value....

Apr 9, 2008

I'm having problem Inserting data from 1table into another table. I'm getting the error:


Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I get the reason why I'm having this error message but I don't know how to fix code... Help please? Thanks

Here's an example of what I'm trying to do...

table1 has:
ID | Name | countryID
1 | Joe | 101
2 | Ben | 102
3 | Tom | 102



Now I wrote this code to insert some of the data from table 1 into table2...

insert into Table2(CompName, ID) values('Information',(select ID from table1 where countryid=102))

Please Help! Thanks...

View 5 Replies View Related

No Rows Returned

Oct 15, 2007



I have a qry/dataset in a report. If the qry returns no data the report will
simply show a the headers/footers and no data in the detail section. However, I need to display all zeros if the detail section ie '0', if no data is returned. How can I do that?
I have SSRS 2005.

View 1 Replies View Related

Using Returned Values From One SP In Another

Jun 20, 2006

Hi all,

I have a SP that needs to return a record set based upon the values of calling another SP. The 2nd SP uses the output parameter and works fine. However, the resulting Insert doesnt see the value. I cannot for the life of me work out why not!

My other question is how do I stop the imbedded procedures from outputing a result? When the procedure is run in analyser it produces three sets of data. The first two being the outputs for the two SPs.

Any help would be much appreciated!

Code as follows:

CREATE PROCEDURE [dbo].[trl_GetOpenDurations] AS

--- CALLS the sp trl_CallOpenDuration with the following params
---1 min minute value - int
---2 max minute value - int
---3 return value - int - interval in minutes

set nocount on

declare @min int --- less than 1 min
declare @hour int --- more than 1 min, less than 1 hour

create table #t ([Type] VARCHAR(10) ,[Value] int)

EXECUTE trl_callOpenDuration 0,1,@min
insert into #t ([Type],[Value]) values ('<1Min', @min)

EXECUTE trl_callOpenDuration 1,60,@hour
insert into #t ([Type],[Value]) values ('<1Hour',@hour)
---get data from table
Select * from #t
GO


RESULTS:

<1Min NULL
<1Hour NULL

View 1 Replies View Related

Incorrect Syntax Near '0'. Where Is The 0 At???

Jul 26, 2006

I am not sure how to fix this??
Imports System.Data
Imports System.Web.UI.WebControls.FormView
Imports System.Data.SqlClient
Partial Class Assignment
Inherits System.Web.UI.Page
Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateButton.Click
Dim empID As Integer
Dim nbrID As Integer
Dim equid As Integer
Dim UpdateQuery
Dim empequipid As SqlCommand
Dim mySQLConnection As String = System.Configuration.ConfigurationManager.ConnectionStrings("DBConnectionString").ToString
Dim myConnection As New SqlConnection(mySQLConnection)
myConnection.Open()
empID = (Session("UserEmployeeID"))
equid = (Session("UserEquipID"))
If CType(empID, Integer) = Nothing Then
empID = (DropDownList1.SelectedValue)
'Response.Write("Insert: " & empID)
'Response.End()
Me.SqlDataSource1.InsertCommand = "INSERT INTO [EMPLOYEES_EQUIP] ([EquipID], [EmployeeID]) VALUES " & equid & " " & empID & ""
Me.SqlDataSource1.InsertCommand = SqlDataSourceCommandType.Text
Me.SqlDataSource1.Insert()
Else
empequipid = New SqlCommand("Select EmpEquipID From EMPLOYEES_EQUIP Where EmployeeID = " & empID & " AND EquipID = " & equid & " ", myConnection)
empequipid.CommandType = CommandType.Text
nbrID = empequipid.ExecuteScalar
'Response.Write("Select EmpEquipID: " & nbrID)
'Response.End()
empID = (DropDownList1.SelectedValue)
'Response.Write("<br>Update EmployeeID: " & empID)
'Response.End()
Me.SqlDataSource1.UpdateCommand = "UPDATE EMPLOYEES_EQUIP SET EmployeeID = " & empID & " WHERE EmpEquipID = " & nbrID & " "
Me.SqlDataSource1.UpdateCommand = SqlDataSourceCommandType.Text
Me.SqlDataSource1.Update()  <---Right here is displaying "Incorrect syntax near '0'."

'Added to output the Update Query
UpdateQuery = "UPDATE EMPLOYEES_EQUIP SET EmployeeID = " & empID & " WHERE EmpEquipID = " & nbrID & " "
'Response.Write(UpdateQuery)
'Response.End()
End If
'Updating the session to new EmployeeID
Session("UserEployeeID") = (DropDownList1.SelectedValue)
'Response.Write("<br>UserEmployeeID Session: " & Session("UserEmployeeID"))
'Response.End()
'-- Turn On Employee Information Table
EmpInfo.Visible = True
'-- Hides the Employee Name's Drop Down List table.
maintable.Visible = False
End Sub
End Class

View 2 Replies View Related

Incorrect SQL Syntax Near......

Aug 11, 2006

Why am I getting this error. 
 
[SqlException (0x80131904): Incorrect syntax near '.123'.]   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857418   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735030   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135   WebApplication10.Edit.dgrdEdit_UpdateCommand(Object source, DataGridCommandEventArgs e) in C:Inventoryedit.aspx.vb:186   System.Web.UI.WebControls.DataGrid.OnUpdateCommand(DataGridCommandEventArgs e) +105   System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) +471   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35   System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) +117   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35   System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115   System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +163   System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +174   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

View 1 Replies View Related

Incorrect Syntax Near ';'

Sep 8, 2006

Hi,I am trying to test a login form and I get this error message and can't find out why. Istarted out with the Login control, but since I have to try it on the ISP's server, I can't use the SQL Server Managment Studio's integrated authentication. So, I converted the login control to a template and assigned a handler for the login button:protected void LoginButton_Click(object sender, EventArgs e){String usrname = lpLogin.UserName.ToString();                 //lpLogin is the <ASP:Login ...>String conString = "Data Source=mylocalserver\SQLEXPRESS;Initial Catalog=LPRU;Integrated Security=True";String selQuery = "SELECT [Password], [FirstName], [LastName] FROM [lpUserInfo] WHERE ([UserID] ='" + usrname + "';";SqlConnection con = new SqlConnection(conString);SqlCommand cmd = new SqlCommand(selQuery, con);con.Open();SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); //  <--- it says "syntax error near ';' " on this line, I tried it without CommandBehaviorwhile (rdr.Read()){Label1.Text= rdr.GetString(0) + rdr.GetString(1);  // for testing purposes, trying to print out first name and last name}rdr.Close();con.Close();}Is there a way of using SQLServer 2000, used by my ISP, and take advantage of .net 2.0's login control, roles, membership, ...? By just using a connection string?  

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved