Returning Multiple Results From A Cursor Into A Table
Sep 7, 2007
The problem I'm having is that I need a cursor to return multiple results into 1 .NET System.Data.DataTable, and I don't know if it's possible. Actually, the real problem I'm having is that whoever designed this database did a poor job and if it weren't for one small thing I could do this all with aggregates...
But more specifically, this is what I need to accomplish. I have written a cursor that I know works - when I run it in MS SQL Query Analyzer, I get the results I want, but it's returned in multiple tables. I am maintaining a .NET application with some pretty lousy performance and I'm trying to boost it up. I need to take the cursor's results, stuff them into a DataTable, and then return that table to my web application to render.
In my C# code, I am using a SqlDataAdapter to execute my query. This is what I'd like to do...
/*assume query is my working cursor, InvoiceDBConnection is not null, and dt is an empty, non-null DataTable*/
SqlDataAdapter sda = new SqlDataAdapter(query, InvoiceDBConnection);
sda.Fill(dt);
... but this doesn't work, and it makes sense, I guess...since the cursor returns multiple "tables" (not sure if that's actually how the data is returned).
So is there any way to accomplish what I want to do? Or do I have to do it the crappy way it's done now by running my aggregate query and then using a really time-consuming for loop on my results to make the necessary adjustment?
Any help is greatly appreciated
View 5 Replies
ADVERTISEMENT
Jun 8, 2006
I'm working on a procedure that needs to cycle through the records of some raw data and combine the the current record with the datetime field of the prior record. I have been able to write a script to do that with cursors and variables but my problem is it returns each record separately. How do I go about getting the procedure to return all the records as one set of data?
To see what I mean, the following script for the Pubs DB returns each pass through the data as a seperate query. Since I can't do a select *, what approach should I take? If you want the actual script, I would be happy to provide it.
DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
WHILE @@FETCH_STATUS = 0
begin
FETCH NEXT FROM authors_cursor
end
Close Authors_cursor
deallocate authors_cursor
Thanks in advance
Tony Murunion
View 3 Replies
View Related
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
Oct 7, 2004
I want to send 1 email with all clientname records which the cursor gets for me.
My code however is sending 1 email for 1 record i.e clientname got from db. What's wrong? please help.
I ano table to understand here about the while if right.
thanks.
+++++++++++++++++++++++++++++++++++++++++
CREATE PROCEDURE test1
AS
declare @clientName varchar(1000)
declare myCursor CURSOR STATIC
for
select client_name
from clients
-------------------------
-- now prepare and send out the e-mails
declare @ToEmail varchar(255)
declare @FromEmail varchar(255)
declare @Subject varchar(255)
declare @Body varchar(2000)
declare @UserID numeric(38)
declare @UserName varchar(255)
declare @SMTPServer varchar(100)
set @SMTPServer = 'test.testserver.com'
-- loop for each record
open myCursor
fetch next from myCursor
into @clientName
--loop now:
while (@@fetch_status=0)
begin -- while(@@fetch_status=0)
-- check if valid "To" e-mail address was found
if ((@clientName is null) or (ltrim(@clientName) = ''))
begin
--should not come here anytime ideally
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = 'was emailed to wrong person'
set @Body = 'the client name got is : '+ @clientName + 'client is null or empty'
end --if
else
begin
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = '-testing'
set @Body =
'this will send
ClientName:'+ @clientName
end --end else
-- send the e-mail
--exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
--fetch next from myCursor into @clientName
fetch next from myCursor
into @clientName
end --while(@@fetch_status=0)
exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
close myCursor
deallocate myCursor
GO
View 1 Replies
View Related
Aug 20, 2013
Is it possible to return the results of a stored procedure into either a CTE or temp table?
In other words, is it possible to do this:
with someCTE as (
exec someStoredProc
)
or this:
exec someStoredProc into #tempTable
???
View 9 Replies
View Related
Jul 24, 2015
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
View 3 Replies
View Related
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
Jul 23, 2005
Hi,I'm am looking for a little help. I need to create a SQL view whichjoins a few tables, and I need to return an average for a particularcolumn where a second duplicate ID exists...Heres an example of how the results could be returned...ID | Name | Order No. | Value---+------+-----------+---------5 | test | 1234 | 35 | test2| 1234 | 45 | test3| 1234 | 35 | void | 1235 | 55 | void2| 1235 | 65 | void3| 1235 | 55 | void4| 1235 | 7ID is my main join which joins the tablesName is a unique nameOrder No is the same for the different names, I only need to return onerow with this order no, and the first name (the rest are irrelevant)Value is the field which I wish to return as an average of all 3, 4 orhowever many rows is returned and share the same order no. This iswhere I get totally lost as I am pretty new to SQL. Can anyone provideany help on how I would go about limiting this query to the uniqueorder no's and returning the average of the value field, and I can takeit from there with my own tables.Thanks for your helpstr8
View 3 Replies
View Related
Jan 8, 2007
Hi everyone. I am updating a table with aggregate results for multiplecolumns. Below is an example of how I approached this. It works finebut is pretty slow. Anyone have an idea how to increase performance.Thanks for any help.UPDATE #MyTableSET HireDate=(Select Min(Case When Code = 'OHDATE' then DateChangedelse null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),TerminationDate=(select Max(Case When Type = 'N' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),ReHireDate=(select MAX(Case When Code = 'HIRE' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and #MyTable.HRRef=HREH.HRRef )
View 2 Replies
View Related
Apr 6, 2008
Using SQL Server 2005 Express:
I'd like to know how to do a SELECT Query using the following tables:
Miles Table:
Date/Car/Miles/MilesTypeID
===============
(some date)/Ford/20/1
(some date)Ford/20/2
(some date)Chevy/30/1
(some date)Toyota/50/3
(some date)Ford/30/3
Miles Type Table
MilesTypeID/MilesType
=================
1/City
2/Highway
3/Off-Road
I'd like the results to be like this:
Date/Car/City Miles/Highway Miles/Off-Road Miles
=====================================
(date)-Ford-20-0-0
(date)-Chevy-0-20-0
(date)-Ford-20-0-0
(date)-Toyota-0-0-50
(date)-Ford-0-0-30
Anyone? Thanks in advance!
View 3 Replies
View Related
Sep 21, 2015
If I have a stored procedure that returns 15 tables, how do I distinguish the tables to assign variables to each table in c#?
View 6 Replies
View Related
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
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
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
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Apr 28, 2004
Is it possible to create a cursor out of the results of a stored procedure.
OR
Is it possible to get just one value.
EXEC sp_columns @table_name = 'tablename', @column_name='columnname'
--I want the type_name value
View 3 Replies
View Related
Sep 6, 2007
Hello,
I have a query that works in query analyzer; it looks that a certain date is between the start and end date of a certain value. I also have a status field, which can be null, but if provided, provides the appropriate status to filter by.
Again, the query works in QA, but not in the application. I test in SQL by using start date = '1/1/1900', end date = '12/31/9999', and status = null. Results are returned. But, not when the results are done through code. In code, I set the begin date to new DateTime(1900, 1, 1), the end date to DateTime.MaxValue, and the status to a null string. But, no results are returning. Why isn't that mapping over correctly? In the function, it has the two dates as Nullable(Of DateTime), which I provide a date, and the string is getting passed Nothing.
Any ideas? Can't post any code on this one...
Thanks.
View 5 Replies
View Related
Sep 19, 2014
I have a query that pulls a list of organizations. I'm trying to use a CASE statement to add a column of names based on the relationship of the individual to that organization. It is possible for two people at the organization to both have that relationship to the organization. Currently, when I pull my results, I get all the individual's names for all the organizations, but I'm also getting a row where there is no name, something like:
Company1 ''
Company1 'Individual1'
Company1 'Individual2'
Some of the organizations, they have no one with the relationship, but for all the organizations that do have someone in the position I want to not see the blank row. My case statement is:
CASE WHEN EXISTS
(Select ind_customer.cst_key WHERE ixo_rlt_code = 'Relationship' )
THEN ind_customer.cst_ind_full_name_dn
ELSE ''
END
AS 'FULL NAME'
View 7 Replies
View Related
May 27, 2015
I am having a problem with the GETDATE().
WHERE TableName.ColumnNamne = Getdate()
The above SQL function does not return any results whereas the below SQL code returns results. Am I doing anything wrong?
WHERE SalesOrder.New_ActualShipmentDate >= GETDATE()
View 35 Replies
View Related
Aug 7, 2007
Need a little help here.
I have a set of product ids fed in as a delimited string and for each I need to extract the top 1 record from another query based on the id.
I need the results as one table.
Here is my code.
___________________________________
SET NOCOUNT ON
DECLARE @IdsString VARCHAR(255), @Id int
SELECT @IdsString = '918|808|1214|89|995|300|526|1207'
DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@IdsString) as s
OPEN GetData
FETCH NEXT FROM GetData
INTO @Id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @Id
FETCH NEXT FROM GetData
INTO @Id
END
CLOSE GetData
DEALLOCATE GetData
_____________________________________
Do I need to create a temp table and do an 'Insert Into(Select...' with each cusor result or is there a better way?
Any help would be much appreciated.
NB Database was not designed and the client will not tolerate any changes to structure of the tables :eek:
Regards
Shaun McGuile
View 14 Replies
View Related
