SqlDataReader Returning Incorrect Results

Oct 23, 2006

I am/have been having an issue with Data Access from a Sql Server database. I have a class that contains a method called "GetDataReader" which takes in a string for the query. Occasionally, the DataReader returned has completely different columns thus resulting in an error when trying to read the data. Below is a small section of code that actually creates the datareader, opens the connection and executes the reader.SqlCommand cmdReader = new SqlCommand();
SqlDataReader drReturn = null;
try {
// Set the connection for the command object
cmdReader.Connection = new SqlConnection(this._csbGlobal.ConnectionString);
cmdReader.Connection.Open();
// Set the command type
cmdReader.CommandType = Type;
// Set the command text
cmdReader.CommandText = Sql;
// Set the command timeout
cmdReader.CommandTimeout = _iTimeout;
if (Parameters != null) {
// Set the parameters
for (int i = 0; i < Parameters.Count; i++) {
cmdReader.Parameters.AddWithValue("@" + Parameters.GetKey(i), Parameters[i]);
}
}

// Get the return value
drReturn = cmdReader.ExecuteReader(CommandBehavior.CloseConnection);
// Dispose of the command object
cmdReader.Dispose();
cmdReader = null;
} catch (SqlException e) {
this.HandleError(e);
} catch (Exception e) {
this.HandleError(e);
}
// Return the reader
return drReturn;The data access class is created on each page and only used for that page and any usercontrols on that page. I have checked all the datareaders and they are all being closed. I have been fighting with this issue for about a month and a half now. It only seems to happen when there are a lot of people on the site.If anyone has experienced this or konws how to fix it please let me know. Thanks-Jason

View 5 Replies


ADVERTISEMENT

Transact SQL :: Insert Trigger Returning Incorrect Results

Jul 11, 2015

SQL Version:  SQL2014

PROBLEM:  The SQL insert trigger code below is returning incorrect results.  In some cases the results returned are from entirely different fields than those specified as the source field in the SET statement.  For instance the value returne for the Price_BeforeAdj field does not = 20000000?  It returns a NULL.  See code below.

OFFENDING CODE:

ALTER TRIGGER [dbo].[xcti_WIPAdjustments_I]
   ON  [dbo].[budxcWIPAdjustments]
AFTER INSERT AS
BEGIN
 SET NOCOUNT ON;
  UPDATE budxcWIPAdjustments

[Code] ....

View 11 Replies View Related

A SqlDataReader Is Returning An Int, When It Should Be Returning A Tinyint

Sep 25, 2007

I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables.  The SELECT looks like this:
 SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89
 The TreatmentStatus column is a tinyint.  When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2.  But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1.
Why?

View 5 Replies View Related

Sqldatareader Not Returning Records

Feb 13, 2007

My query is as follows:Dim CurrentDate As DateCurrentDate = "09/02/2007" MyCommand = New SqlCommand("SELECT RegisterID FROM Registers WHERE RegisterDate = @RegisterDate AND IsMorningRegister = 1", MyConn)MyCommand.Parameters.Add("@RegisterDate", Data.SqlDbType.DateTime)MyCommand.Parameters("@RegisterDate").Value = CurrentDate My DB table is called RegisterDate and is of type DateTime. The record that should be matched is: Register ID: 13 RegisterDate: 09/02/2007 09:00:00IsMorningRegister: TrueIsAfternoonRegister: False But no records are returned. Any idea why?    

View 4 Replies View Related

Need Advise. Problem With Connection Pool. Function Returning SqlDataReader.

Sep 6, 2007

Halo all
 
I have a problem with "timeout expired. Thei timeout periode elapsed prior to obtaining a connection from the pool. This may have occured because all pooled  connections were use  and max pool size was reached"
Then i explore and found out that i did not close my SqlDataReader, SqlDataAdapter, SqlCommand or my connection.
 
But i have a function that return a  SqlDataReader.
Is this function will cause a connection problem?
 
Thanks in advance
 
 
        Public Function GetDataReader(ByVal strSQL As String, ByVal DBCon As DB.DBConnection) As SqlDataReader            Dim MyCommand As SqlCommand = New SqlCommand(strSQL, DBCon.GetConnection())
            If DBCon.GetConnection().State = ConnectionState.Closed Then                DBCon.GetConnection().Open()            End If
            Dim dr As SqlDataReader = MyCommand.ExecuteReader()
            Return dr
            dr.Close()
        End Function

View 6 Replies View Related

Possible To Get 'count Of Expected Results' From SqlDataReader?

Nov 28, 2007

I'm reading a lot of data from a database using SqlDataReader. Now I'd like to report the progress to the user, but for this I need to know in advance how many items SqlDataReader will return.

Is there any way to get this 'number of expected results' from the SqlDataReader?

tia,
Sam

View 1 Replies View Related

SQL Server 2008 :: Elegant Way For Returning All Results When Subquery Returns No Results?

Mar 25, 2015

I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).

I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.

Right now, I'm doing it this way.

DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId

[Code] .....

This works; it just seems cludgy. Is there a more elegant way to do this?

View 4 Replies View Related

SQL Command Returning Some Incorrect Records

Nov 13, 2006

I am fairly sure that I am just overlooking something, but the following command is returning some incorrect fields. 
SqlDataSource1.SelectCommand = "SELECT ITNBR, (SELECT ITDSC FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS ITDSC, SUM(MOHTQ) AS Balance, (SELECT VNDNR FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS VENDOR FROM AMFLIBT.ITEMBL WHERE VNDNR = @DDLVNDNR GROUP BY ITNBR, VENDOR"
SqlDataSource1.SelectParameters.Add("ddlvndnr", ddl1.SelectedValue)
I have more code to show, if this looks correct.  Just let me know.
Thanks in advance.

View 2 Replies View Related

DatePart Returning Incorrect Value For Weekday (dw)

Feb 18, 2008

Hi All,In ASP.Net, I am passing a date as a string formatted as yyyymmdd (e.g. 20080219) via an SqlParameter as follows:aSqlParams(0) = New SqlParameter("@DepartDate", inpDepartDate.Value)In the Stored Procedure (SP) I am using DATEPART(dw, @DepartDate).If I execute the SP from within SQL Server and enter a date, formatted as above, as tha value for the Parameter @DepartDate then DatePart returns the correct value (3).If I use the SP via ASP.Net it returns a value which is 1 less (2). When I execute the SP manually the resultant EXEC statement uses "@DepartDate = N'20080219'". Can something similar be done in ASP.Net when setting the SqlParameter? Thank you,

View 4 Replies View Related

Incorrect Results

Feb 9, 2007

I am running the following code in my stored procedure and I am not geting the results I need:


set @dbidstr = 'select @dbid = dbid from dbo.databaseoriginalsize'
execute sp_executesql @dbidstr, N'@dbid int output', @dbid output
print @dbid

'select dbid from dbo.databaseoriginalsize' returns values 1-26 but when I run it as a code shown above I only get one value which is 26, 26 times.

What am I doing wrong?

View 3 Replies View Related

Preview Data, It Gives You Incorrect Results

Apr 25, 2007

I am reposting this to a different forum, becasue I got not response...
Using VS05 SP1 Pro SQL Express€¦
Take a simple stored procedure like the following to return a specific column from a specific row in a data table ....
SELECT fld_IX_UserID
FROM UserIDs
WHERE (fld_UserID_Table_ID = @USERID_TABLE_ID)
It works fine in Store Procedure, and if you create at table adapter to reference it, that works fine as well.
Here is the issue. In the store procedure (i.e. under Server Explorer), when you execute the command to test it, it gives you the correct results. In Edit with Dataset Designer, Table Adapater, if you highlight it, right click preview data, it gives you incorrect results. In code, the table adapter gives you the correct resutls.
In every case, Preview Results for a table adapter built on a stored procedure will give you the wrong results. This is clearly a bug and can result in a log of wasted time.
Am I missing something? FYI, I realize I don't need to use the Table Adapater to execute the above stored procedure, but we are using table adapters for everything to be consistent.
Thanks,
Bob

View 3 Replies View Related

Incorrect Results From FTS On Multiple Columns

Apr 24, 2006

I'm writing a FTS query which needs to search on two different columns.

E.g. Table contains "Location" and "LocationDescription" columns.
Both columns are FT indexed.
The query also uses AND/OR operators to filter out the results.

I found the following article which gives the solution to the same problem.
Link: http://support.microsoft.com/default.aspx?scid=kb;en-us;286787
Is this problem associated with SQL Server 2005 also?

Making a third column which hold data from first two column is the
only solution or is there any other way to acheive better results?

Shailesh Patel...

View 3 Replies View Related

Sort Asc On Smalldatetime Results In Incorrect Sorting

May 15, 2007

I have a smalldatetime column in my sql2000 database table and am sorting ascending on a smalldatetime.
The problem is my results are not in desc order like I want.
Now 5/15/2007 is showing up after 5/9/2007.
I need my dates to sort from the most recent to the latest.
Any help is appreciated.

View 3 Replies View Related

Incorrect Query Results, Could Use Fresh Eyes

Jun 4, 2007

I have three tables:

Category: category id, category name, more…
Topic: topic id, topic name, category id, more…
Post: post id, post text, topic id, more…

I need help with a query to display the following:

Category name, # of topics, # of posts

Example:
Category.........................Topics.....Posts
SQL Stored Procedures.........12........562


It’s coming along but there are some problems, ASP.NET actually has 2 posts not 1. And the java totals are correct but it should be Java, 3, 10 (all in one line)
Category.....Topics...Posts
ASP.NET.........2........1
C#................1........1
Java..............1........1
Java..............1........2
Java..............1........7


Overview: use category id to get count of topics then use the topic id to get the count of posts.


SELECT C.CategoryName, T.ThreadCount AS Threads, T.PostCount AS Posts
FROM Category AS C LEFT OUTER JOIN
(SELECT tt.CategoryID, PostID.PostCount, COUNT(tt.ThreadName) AS ThreadCount
FROM Thread AS tt LEFT OUTER JOIN
(SELECT ThreadID, COUNT(PostID) AS PostCount
FROM Post AS P
GROUP BY ThreadID) AS PostID
ON tt.ThreadID = PostID.ThreadID
GROUP BY tt.CategoryID, PostID.PostCount) AS T
ON C.CategoryID = T.CategoryID
WHERE (C.CategoryID = T.CategoryID)
GROUP BY C.CategoryName, T.ThreadCount, T.PostCount
ORDER BY C.CategoryName

Thanks in advance

View 6 Replies View Related

Incorrect Results With T-sql Query In SQL Server 2005

Apr 18, 2006

I'm seeing some change in behavior for a query in SQL Server 2005 (compared to behavior in SQL Server 2000).  The query is as follows:
------------
create table #projects (projectid int) insert into #projects select projectid from tblprojects where istemplate = 0 and projecttemplateid = 365

Select distinct tblProjects.ProjectID
from tblProjects WITH (NOLOCK) 
     inner join #projects on #projects.projectid = tblprojects.projectid  
     Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID   
          and tblProjects.projectID in (
               select projectid 
               from tblMilestones 
               where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
                    and enabled = 1  )
------------
This is dynamic SQL generated by the application when a user requests a report with variable parameters.  It works fine in SQL Server 2000.  It outputs 47 records which is correct. 

In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as a TOP operator and outputs just 1 record.  (Results of Showplan Text at the end of this post).

If I modify the query even the slightest bit by:
1) Changing "where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
    and enabled = 1  )"
 To " where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' ) )
    and enabled = 1  "

2)  Changing " Select distinct tblProjects.ProjectID"
 To   " Select distinct tblProjects.ProjectID+''"

3) Removing the Distinct keyword, storing into a Temp table, then performing a distinct on the temp table

4) Adding: OPTION (FORCE ORDER)

5) OR completely fixing the query (remove redundant loops, etc)

...it works fine (outputs 47 records).  It also works if I created new tables (eg. tMilestones instead of tblMilestones) and inserted about 10 records into each and ran the query referencing these new tables.

I reindexed the tables, updated stats, updated usage, ran DBCC FREEPROCCACHE, changed MaxDOP settings...nothing makes the query behave the way it does in SQL Server 2000 without modifying the query/adding the query hint.

Have you come across this?  Any ideas on what might be causing the "TOP" operation.  (Somewhat resembles the bug mentioned in this article: http://www.kbalertz.com/Feedback_910392.aspx - but this was apparently fixed POST-SQL Server 2000 SP4 - so has it not made it into SQL Server 2005 yet?).

I will appreciate any new insights you might have on this issue. 
Thanks much,
Smitha


P.S. Results of Showplan Text:

StmtText                      
------------------------------
SET STATISTICS PROFILE ON

(1 row(s) affected)

StmtText                                                                                                                                                                                                                                                                                                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Select distinct tblProjects.ProjectID from tblProjects WITH (NOLOCK) 
inner join #projects on #projects.projectid = tblprojects.projectid 
Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID  
and tblProjects.projectID in (
select tblMilestones.projectid from tblMilestones
where (parent = 683691 AND tblMilestones.PrimaryDate between '4/15/2006' and '4/22/2006' ) 
and tblMilestones.enabled = 1  )

(1 row(s) affected)

StmtText                                                                                                                                                                                                                                                                                                                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Stream Aggregate(DEFINE:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=ANY([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID])))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
            |    |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[ExpesiteProductionCopy].[dbo].[tblMilestones].[Enabled],0)=(1)))
            |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1014], [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) OPTIMIZED)
            |    |         |--Merge Join(Inner Join, MERGE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014])=([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014]), RESIDUAL:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] = [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014] = [Uniq1014]))
            |    |         |    |--Sort(ORDER BY:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] ASC, [Uniq1014] ASC))
            |    |         |    |    |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byPrimaryDate]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] >= '2006-04-15 00:00:00.000' AND [ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] <= '2006-04-22 00:00:00.000') ORDERED FORWARD)
            |    |         |    |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byParentID]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[Parent]=(683691)) ORDERED FORWARD)
            |    |         |--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014]=[Uniq1014]) LOOKUP ORDERED FORWARD)
            |    |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblProjects].[PK_tblProjects_1]), SEEK:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
            |--Top(TOP EXPRESSION:((1)))
                 |--Nested Loops(Inner Join)
                      |--Table Scan(OBJECT:([tempdb].[dbo].[#projects]), WHERE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[tempdb].[dbo].[#projects].[projectid]))
                      |--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)

(15 row(s) affected)

StmtText                     
-----------------------------
SET STATISTICS PROFILE OFF

(1 row(s) affected)

 

 

View 6 Replies View Related

Returning No Results

May 19, 2008

I have the following stored procedure that is returning nothing can anyone please help?
 SELECT     job_id, line_num, cust_id, cust_po_id, product_desc, form_num, revision, flat_size, new_art_reprint, order_qty, po_recieved_date, ord_ent_date,                       customer_due_date, scheduled_ship_date, act_ship_date, act_ship_qty, ship_from, ship_to, price_per_m, misc_charges, commentsFROM         tblOrderWHERE     (cust_id = @Cust_Id) AND (po_recieved_date BETWEEN @Start AND @End)
 When I input parameters I make sure my start date is before the first po_recieved_date and the end date is after it yet it is returning nothing. I also made sure that I am putting the correct @Cust_Id

View 6 Replies View Related

(SQL 2000) Incorrect Results When Using An Outer Join And A View!

Mar 29, 2008

Hi,
I have a query written in SQL 2000 which returns incorrect result. The query uses left outer join and a view. I read an issue related to this in one of microsoft bug report in this article http://support.microsoft.com/kb/321541.

However, there's a slight difference in the sympton second bullet wherein instead of a expression the query returns a fixed string for one of the column value.

Although the issue mentioned in article seems to be fixed. The later one still seems to be reproducible even with Service Pack 4. However, this issue doesn't appear in SQL Server 2005.

Here's the query to reproduce this error.



Code Snippetcreate table t1 (pk1 int not null,primary key (pk1))
create table t2 (pk1 int not null,label1 varchar(10) not null,primary key (pk1))
go
insert into t1 values (1)
insert into t2 values (2, 'XXXXX')
go
create view V as
select pk1, 'ZZZZ' as label1 from t2
go
select A.pk1 as A_pk1, B.pk1 as B_pk1, B.label1 as B_label1
from t1 as A left outer join V as B on A.pk1 = B.pk1
go

This query is similar to the one mentioned in the article except that in the SELECT clause of CREATE VIEW statement I am passing a fixed value for column "label1".

I just want to confirm that this is an issue and no fix is available for this so far.

Regards,
Naresh Rohra.

View 2 Replies View Related

Results Are Returning On Different Row Levels?

Feb 26, 2014

How do I get my data to show starting at the first row instead of skipping down?

Refer to the attachment.

Code:
CREATE PROCEDURE [dbo].[uspReportData]
-- Add the parameters for the stored procedure here
@Metric1 as varchar(50) = NULL, @Metric2 as varchar(50) = NULL, @Metric3 as varchar(50) = NULL, @Metric4 as varchar(50) = NULL,
@Metric5 as varchar(50) = NULL, @Metric6 as varchar(50) = NULL, @Metric7 as varchar(50) = NULL, @Metric8 as varchar(50) = NULL,

[code].....

View 1 Replies View Related

Stored Procedure Not Returning Results

May 15, 2007

Hi,I'm creating a stored procedure that pulls information from 4 tables based on 1 parameter. This should be very straightforward, but for some reason it doesn't work.Given below are the relevant tables:  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Project](
[ProjID] [varchar](300) NOT NULL,
[ProjType] [varchar](20) NULL,
[ProjectTitle] [varchar](max) NULL,
[ProjectDetails] [varchar](max) NULL,
[ProjectManagerID] [int] NULL,
[RequestedBy] [varchar](max) NULL,
[DateRequested] [datetime] NULL,
[DueDate] [datetime] NULL,
[ProjectStatusID] [int] NULL,
CONSTRAINT [PK__tbl_Project__0B91BA14] PRIMARY KEY CLUSTERED
(
[ProjID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID])
REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID])
GO
ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectManager]
GO
ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID])
REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID])
GO
ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus]


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


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Report](
[ReportName] [varchar](50) NOT NULL,
[ProjID] [varchar](300) NULL,
[DeptCode] [varchar](50) NULL,
[ProjType] [varchar](50) NULL,
[ProjectTitle] [varchar](500) NULL,
[ProjectDetails] [varchar](3000) NULL,
[ProjectManagerID] [int] NULL,
[RequestedBy] [varchar](50) NULL,
[DateRequested] [datetime] NULL,
[DueDate] [datetime] NULL,
[ProjectStatusID] [int] NULL,
CONSTRAINT [PK_tbl_Report] PRIMARY KEY CLUSTERED
(
[ReportName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID])
REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID])
GO
ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectManager]
GO
ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID])
REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID])
GO
ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus]


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


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ProjectStatus](
[ProjectStatusID] [int] IDENTITY(1,1) NOT NULL,
[ProjectStatus] [varchar](max) NULL,
CONSTRAINT [PK__tbl_ProjectStatu__023D5A04] PRIMARY KEY CLUSTERED
(
[ProjectStatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


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


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ProjectManager](
[ProjectManagerID] [int] IDENTITY(1,1) NOT NULL,
[FName] [varchar](50) NULL,
[LName] [varchar](50) NULL,
[Inactive] [int] NULL,
CONSTRAINT [PK__tbl_ProjectManag__7D78A4E7] PRIMARY KEY CLUSTERED
(
[ProjectManagerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

 And here is the stored procedure that I wrote (doesn't return results):  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetReportQuery]
(
@ReportName varchar(100)
)

AS

BEGIN

SET
NOCOUNT ON

IF @ReportName IS NULL
BEGIN
RETURN -1
END
ELSE
BEGIN

DECLARE @DeptCode varchar(50), @ProjID varchar(50)
SELECT @DeptCode = DeptCode FROM tbl_Report WHERE ReportName = @ReportName

SET @ProjID = @DeptCode + '-' + '%'

SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, M.FName, M.LName, P.DateRequested, P.DueDate, S.ProjectStatus
FROM tbl_Project P, tbl_ProjectManager M, tbl_ProjectStatus S
WHERE ((P.ProjID = (SELECT ProjID FROM tbl_Report WHERE ((ReportName = @ReportName))))
AND (P.ProjectDetails = (SELECT ProjectDetails FROM tbl_Report WHERE ReportName = @ReportName) OR P.ProjectDetails IS NULL)
AND (M.FName = (SELECT FName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.FName IS NULL)
AND (M.LName = (SELECT LName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.LName IS NULL)
AND (P.DateRequested = (SELECT DateRequested FROM tbl_Report WHERE ReportName = @ReportName) OR P.DateRequested IS NULL)
AND (P.DueDate = (SELECT DueDate FROM tbl_Report WHERE ReportName = @ReportName) OR P.DueDate IS NULL)
AND (S.ProjectStatus = (SELECT ProjectStatusID FROM tbl_Report WHERE ReportName = @ReportName) OR S.ProjectStatus IS NULL)
)
END

END Can someone see what's wrong? Thanks. 

View 7 Replies View Related

OpenXML Not Returning Desired Results

Aug 31, 2007

Hi all,I have a SQL job where I do the following -I check for new rows in my Table "DumpResults", every now and then and get the new rows to be inserted into table "CleanTable". I use OPENXML() to get the new data to be inserted but for some reason I don't get the right data through OPENXML() - DECLARE @intDoc INTDECLARE @xmlDoc VARCHAR(8000)IF(SELECT COUNT(*) FROM DumpResults WHERE DumpResults.C1 NOT IN (SELECT CleanTable.C1 FROM CleanTable)) > 0BEGINSET @xmlDoc = (SELECT * FROM DumpResults WHERE DumpResults.C1 NOT IN (SELECT CleanTable.C1 FROM CleanTable) FOR XML RAW) SET @xmlDoc = '<TABLE>' + @xmlDoc + '</TABLE>'
PRINT @xmlDocEXEC sp_xml_preparedocument @intDoc OUTPUT, @xmlDoc--INSERT INTO CleanTable(C1, C2, C3, C4, C5)
SELECT C1, C2, C3, C4, C5, C6 FROM OPENXML(@intDoc,'/row',1)WITH (C1 INT, C2 CHAR(3), C3 CHAR(3) , C4 FLOAT, C5 INT)EXEC sp_xml_removedocument @intDoc
END
ELSE Output that I get is - <TABLE><row C1="1" C2="AAA" C3="BBB" C4="1.000000000000000e+000" C5="2"/></TABLE>(0 row(s) affected) SO "PRINT @xmlDoc" is returning back the xml data (new results) it collected from the "DumpResults" table which isn't there in "CleanTable" but the "Select... FROM OPENXML(...)" doesn't return any result. why so? If anyone knows please reply If anyone has any better method to do it, inputs are welcome. Thanks  

View 3 Replies View Related

Searching SQL Db And Returning Results To Gridview

Jan 29, 2008

Hello....
I am in the process of building a search function for an application (VS2005, ASP.net, VB code, and AJAX functionality).The user's form has a textbox (for an input string) and two drop-downs.  The user would then click on a "Search Now" button, and the data should populatein a Gridview to the left of the search form.  I'm trying to search over an SQL database and need to know how to exactly implement what I have so far:
<div id="mcb" style="background-color:#2e4b5b; width:195px; height:410px; float:left">     <div id="ht" style="background-color:#1b2c36;padding:10px; border-bottom:solid 1px #fffdff; width:175px;float:left;">           Quick Search      </div><div style="clear:both;"></div>
<div style="padding:10px; background-color:#2e4b5b">      <div id="tfta" style="padding:5px 0px 0px 0px">                <b>Vendor Name</b>                <asp:TextBox ID="txtVendorFilter" runat="server"></asp:TextBox>                <div style="font-size:1px; width:150px; height:1px; background-color:#8FA71F"></div><br />                 <b>Contract Type:</b>                 <asp:dropdownlist ID="ddlContractTypeSearch" runat="server" Width="155px">                           <asp:ListItem Value="0">Select...</asp:ListItem>                           <asp:ListItem Value="1">Vendor Maintenance</asp:ListItem>                           <asp:ListItem Value="2">Consulting Contract</asp:ListItem>                           <asp:ListItem Value="3">Hardware License</asp:ListItem>                           <asp:ListItem Value="4">Software License</asp:ListItem>                           <asp:ListItem Value="5">Statement of Work</asp:ListItem>                           <asp:ListItem Value="6">Admin Svcs. Agreement</asp:ListItem>                           <asp:ListItem Value="7">Master Service Agreement</asp:ListItem>                  </asp:dropdownlist>
           <div style="font-size:1px; width:150px; height:1px; background-color:#8FA71F"></div><br />                   <b>Sort By:</b>                   <asp:DropDownList ID="ddlSortBy" runat="server" Width="155px">                          <asp:ListItem Value="0">Select...</asp:ListItem>                          <asp:ListItem Value="1">Contract Name</asp:ListItem>                          <asp:ListItem Value="2">Contract Type</asp:ListItem>                    </asp:DropDownList>             <div style="font-size:1px; width:150px; height:1px; background-color:#8FA71F"></div><br />        </div>    <br />    <asp:Button ID="btnUpdateFilter" runat="server" Text="Search" width="120px"/>    <asp:Button ID="btnReset" runat="server" Text="Reset" /></div>
</div>
 Here's my select statement used to initially populate the Gridview (set up using the configuration of the Gridview):SELECT [ContractID], [ContractName], [ContractType], [EffectiveDate], [TerminationDate] FROM [Contract]Here's my btnUpdateFilter codebehind (VB):
Protected Sub btnUpdateFilter_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdateFilter.Click     Dim strVendorName As String     Dim strContractType As String     Dim strSortBy As String     If txtVendorFilter IsNot "" Then           strVendorName = txtVendorFilter.Text     End If
''''''''''''''''''''''''''''If ddlContractTypeSearch.SelectedValue = 1 Then      strContractType = "1"ElseIf ddlContractTypeSearch.SelectedValue = 2 Then      strContractType = "2"ElseIf ddlContractTypeSearch.SelectedValue = 3 Then      strContractType = "3"ElseIf ddlContractTypeSearch.SelectedValue = 4 Then      strContractType = "4"ElseIf ddlContractTypeSearch.SelectedValue = 5 Then      strContractType = "5"ElseIf ddlContractTypeSearch.SelectedValue = 6 Then      strContractType = "6"ElseIf ddlContractTypeSearch.SelectedValue = 7 Then      strContractType = "7"Else      strContractType = "0"End If
''''''''''''''''''''''''''''''If ddlSortBy.SelectedValue = 1 Then     strSortBy = "ContractName"ElseIf ddlSortBy.SelectedValue = 2 Then     strSortBy = "ContractType"Else     strSortBy = "ContractName"End If
End Sub
 
My question is, "how can I form the proper SQL statement", and then implement it so that my gridview will populate using my generated SQL Statement?
Thank you for the help!!!
-S

View 1 Replies View Related

Returning The Results Of A Linq Query

Feb 29, 2008

I need some help. I have a function that used to use ADO.NET to return a dataview. Now I'm using linq, and I don't know how to make this work anymore.my original function looked like this:    public DataView getIssue()    {       //do some ADO, return the dataview       return ds.Tables["Articles"].DefaultView;    }  With this I could write Gridview1.dataSource = getIssue();I want to do the same thing with Linq, but I'm running into some trouble:Here's my function now:public DataView getIssue()    {        var query = from a in db.Articles        join i in db.Issues on a.IssueID equals i.IssueID       select a;            DataView dv = new DataView();            dv = query.asdatatable();        return query;    }OK, first off I can't use asdatatable since I am using a join, so I can't make the results of my query a dataview. I can't return the results of my query in this function, something that seemed simple to do in Linq now seems like something that may only be possible in ADO.Thanks for your time 

View 5 Replies View Related

Executing A Script And Returning The Results..

Mar 1, 2004

You know how osql can return the results of executing a .sql script to a window? Is there any way to achieve that same behavior in .NET? I am in the process of working on an application that needs to execute a .SQL script (which is easy to do) and then return the actual SQL output from the execution to a window. How do I get that output? None of the sql data objects seem to support this.

View 1 Replies View Related

NOT IN Vs. NOT EXISTS Returning Diff Results

Mar 12, 2004

select count(orgcode)
from orglevel
where orglvl = 1 and exists (select 'x' from empcomp where eecemplstatus = 'A' and eecorglvl1 = orgcode)

26 rows

select count(orgcode)
from orglevel
where orglvl = 1 and orgcode in (select distinct eecorglvl1 from empcomp where eecemplstatus = 'A')

26 rows

select count(orgcode)
from orglevel
where orglvl = 1 and not exists (select 'x' from empcomp where eecemplstatus = 'A' and eecorglvl1 = orgcode)

6 rows

select count(orgcode)
from orglevel
where orglvl = 1 and orgcode not in (select distinct eecorglvl1 from empcomp where eecemplstatus = 'A')

zero rows


Confusing the hell out of me .....

View 2 Replies View Related

Not Returning Results In Production Environment

Oct 7, 2015

I have this script that captures metrics and sizes using T-SQL. When running in development I have no issues. I can run under the context of any database and get results back. When deploying to production servers no results are returning. I'm trying to figure out what permissions are needed for this to return results.

No error message is being returned either. My access is limited for triage against Production? I need to be able to have Production DBA set the right permissions so this job can run correctly.

DECLARE @allocation_table table
(
dbname sysname,
reservedpages bigint,
usedpages bigint,
pages bigint

[Code] ....

View 1 Replies View Related

Max Datetime Returning Multiple Results

Oct 23, 2013

I have a query running without issue on around 100,000 results however i have a couple where my MAX aggregate is returning two results for a MAX(date time) and i cannot figure out why. I have paired down the query to try and work it out but still don't know.

My query now looks like this (i have remove most columns)

SELECT STATUS, RunOn, sort, T_ID
FROM dbo.Results
WHERE (RunOn IN
(SELECT MAX(RunOn)
FROM dbo.Results
group BY T_ID) AND T_ID = 21405)

ORDER BY sort

I added the T_ID = 21405 to restrict the results to the problematic results, if i set T_ID to any other test result i get one value based on the latest RunOn, but for some reason with this T_ID i get two values, the latest one and one slightly older... The date time looks ok, i just can't work out why the latest and one older result are returned !

The original query was grouping by more items but i removed those to try and work out what is going one, as mentioned for 99.9% of results it works perfectly, but for this one i don't know.

View 10 Replies View Related

Query Keeps Returning Slightly Different Results?

Feb 9, 2015

I have a query with a nested query that is used as an in line view. When I run the whole query I keep getting slightly different results each time (10000, 10002, 10001, 9999 etc.), nothing is being changed, no jobs are running on the DB to affect the tables etc.

When I run the inline view query with the nested query, or nested query on its own, it returns same number each time.

What could potentially be the cause of this?

Using SQL Server 2008 R2 Express Edition on local W7 PC

View 1 Replies View Related

Sql Query Not Returning Correct Results From Db

Feb 6, 2006

Hello.

I am new at SQL and am using SQL server express edition and im a bit stuck! I am using ASP.NET and C# in my website which is using sql database back end.

String SQLroom = "SELECT DISTINCT RoomName FROM Room INNER JOIN RoomCalendar ON Room.RoomID = RoomCalendar.RoomID WHERE Capacity = '" + reqCapacity + "' " + " AND NOT ('" + newRoomEnd + "' <= roomStartDateTime OR '" + newRoomStart + "' >= roomEndDateTime) AND (OHP = '" + ohpYesNo + "' AND AV = '" + avYesNo + "') ";

This is my SQL string... what it is trying to do is:

find the room
where the capacity is the reqcapacity entered by user
and the startdatetime and enddatetime entered by the user are not present in the table for that room capacity
and then look at whether the user requires OHP or AV facilities, which are stored in the database as either yes or no values.
The problem i am having is with the condition in the sql query... because the user may require an OHP and not AV, but then the room returned "`could" have AV facilities, as it wouldnt make a difference to them if it was there or not, basically, the yes condition has to be satisfied.

Not sure whether i should be using AND, or OR? or a combination.

Any ideas gratefully appreciated....

Sandy

View 2 Replies View Related

Query With Parameters Not Returning Right Results

Mar 19, 2008

I havea 2 part issue with a query I'm trying to run for a report.

I have an incident report that needs to show results based on dates from the week before. In addition, This report should run every Monday morning, with the exception of a Monday holiday, where in that case, the report will run the next business day. I have an idea to use a case statement, but it doesn't seem to work for me. Any suggestions?

Also,part of the query I have is pulling back the right data, as long as I don’t include parameters. When I do add values to the parameters the query includes all the dates. I need to see either/or - not both. I just want to either see dates without the param values or see only dates that I ask for in a parameter. Does that make sense?

Here’s the query (thanks!):

SELECT incident.incid_id,
incident.incid_short_desc,
incident.incid_received_date_time,
incident.incid_closed_date_time,
security_users.description,
incident.incid_assigned_to,
incident.tagged_delete_flag,
activity_result_master.result_desc,
incident_priority_master.priority_desc,
activities.result_id

FROM activities AS activities INNER JOIN
incident AS incident ON
activities.incid_id = incident.incid_id INNER JOIN
activity_result_master AS activity_result_master ON
activities.result_id =
activity_result_master.result_id
INNER JOIN security_users AS security_users ON incident.incid_assigned_to = security_users.name INNER JOIN
incident_priority_master AS incident_priority_master ON incident.priority_id = incident_priority_master.priority_id

WHERE incident.tagged_delete_flag = 'N' AND
activities.result_id = '6' AND
(incid_received_date_time >= DATEADD (d,-7,GETDATE()) AND
incid_closed_date_time <=DATEADD (d,-3,GETDATE())) OR
CONVERT(varchar,incident.incid_received_date_time,101) >= @StartDate AND CONVERT(varchar,incident.incid_closed_date_time,101) <= @EndDate

ORDER BY incident.incid_assigned_to, incident.incid_id

View 2 Replies View Related

Returning Results While The Query Is Executing

Jul 20, 2005

Hi,I wanted to know if this is possible and if so, how do Ido it. Say, I have a query "SELECT * FROM Table WHEREColumn="some_value". This executes on a very large dataset and I would like to return the results as they queryexecutes rather than wait for the whole query to execute.Basically, I want to get the results as they are preparedby the database. Any way to do this?Regards,San

View 8 Replies View Related

Returning Results Based On Dates

Oct 19, 2007



I want to write a stored procedure that takes two three paramters based on a case statement determining which values are null something like this

Select InformationDate From thisTAble

Where

If @dateValue IsNot @Null
Informationdate = @dateValue
Else

Where

In this second where cluase I want to be able to pull out all the results based on a date range and i am not sure how the syntaz would go

InfomationDate IsBettween @daterangeFrom @dateRangeTwo


Any help on this i hope i was clear...thank you!

View 7 Replies View Related

Containstable Not Returning Accurate Results

Aug 27, 2007



Does anyone lese get the same results that I do?

tblTest has an id field and a testName field....table has 2 records:
1 kevin test 1
2 kevin test 3


SELECT *

FROM tblTest t

INNER JOIN CONTAINSTABLE(tblTest,*,'"kevin test 1"') as A ON t.id = A.[KEY]

Result is both records returned, but since the search is in quotes as an exact phrase, should not just the first record be returned?

View 3 Replies View Related

Why Is This JOIN Statement Not Returning The Right Results?

Sep 19, 2007



Hi,
I have three tables:
1 Order Details
2 Product Info
3 Received Qty


The query is pretty simple however I'm getting incorrect results.

The query grabs a load of information (including a SUM statement) from the order details tbl.. this is INNER JOIN ed to the products tbl to match an ID field which grabs some extra info from the product info tbl.

all fairly straightforward and works without a problem..

However I now need to add in the received qty tbl (as the name suggests this keep a track of the qty's of items received)

I need to join this tbl to the order details tbl based on a unique ID from the orders tbl and then SUM together all of the entries in that received qty tbl for that item.

Result is I get a single record which shows the qty ordered (from the order details tbl), and the total received (from the received qty) tbl

These are held in a seperate tbl as an order can be deliveryed in several stages.



Here is my statement:

SELECT PDS_Login.Supplier_Products.ProductName, PDS_Login.Supplier_Products.ProductCode, dbo.Suppliers_POs_Details.POID,
dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, dbo.Suppliers_POs_Details.PricePerItem,
dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived,
SUM(dbo.Suppliers_POs_Details.Qty * dbo.Suppliers_POs_Details.PricePerItem) AS LineTotal, SUM(dbo.Suppliers_POs_Details_Received.Qty)
AS QtyReceived
FROM dbo.Suppliers_POs_Details INNER JOIN
PDS_Login.Supplier_Products ON dbo.Suppliers_POs_Details.ProductID = PDS_Login.Supplier_Products.ProductID LEFT OUTER JOIN
dbo.Suppliers_POs_Details_Received ON dbo.Suppliers_POs_Details.PODetailsID = dbo.Suppliers_POs_Details_Received.PODetailsID
WHERE (dbo.Suppliers_POs_Details.POID = @POID)
GROUP BY dbo.Suppliers_POs_Details_Received.Qty, PDS_Login.Supplier_Products.ProductName, PDS_Login.Supplier_Products.ProductCode,
dbo.Suppliers_POs_Details.POID, dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, dbo.Suppliers_POs_Details.PricePerItem,
dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived,
dbo.Suppliers_POs_Details_Received.Qty


The problem is, if there is more than one relating record in teh Received Qty tbl instead of that being added together as I want, two results appear...

It's obviously something simple but I can't seem to figure it out - anyone point me in the right direction?

View 3 Replies View Related







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