Searching SQL Db And Returning Results To Gridview

Jan 29, 2008


I am in the process of building a search function for an application (VS2005,, 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 populate
in 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 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>

           <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 />
    <br />
    <asp:Button ID="btnUpdateFilter" runat="server" Text="Search" width="120px"/>
    <asp:Button ID="btnReset" runat="server" Text="Reset" /></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"
      strContractType = "0"
End If

If ddlSortBy.SelectedValue = 1 Then
     strSortBy = "ContractName"
ElseIf ddlSortBy.SelectedValue = 2 Then
     strSortBy = "ContractType"
     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!!!


View 1 Replies


Want Error Message To Appear When No Database Results Were Returned In GridView, Also Other GridView Issues.

Jun 12, 2008

Hi, I am seeking a hopefully easy solution to spit back an error message when a user receives no results from a SQL server db with no results. My code looks like this What is in bold is the relevant subroutine for this problem I'm having.   Partial Class collegedb_Default Inherits System.Web.UI.Page Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] WHERE [name] like '%" & textbox1.Text & "%'" SqlDataSource1.DataBind() If (SqlDataSource1 = System.DBNull) Then no_match.Text = "Your search returned no results, try looking manually." End If End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End Sub Protected Sub reset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles reset.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End SubEnd Class  I'm probably doing this completely wrong, I'm a .net newb. Any help would be appreciated. Basically I have GridView spitting out info from a db upon Page Load, but i also have a search bar above that. The search function works, but when it returns nothing, I want an error message to be displayed. I have a label setup called "no_match" but I'm getting compiler errors. Also, next to the submit button, I also have another button (Protected sub reset) that I was hoping to be able to return all results back on the page, similar to if a user is just loading the page fresh. I'd think that my logic would be OK, by just repeating the source code from page_load, but that doens't work.The button just does nothing. One final question, unrelated. After I set this default.aspx page up, sorting by number on the bottom of gridview, ie. 1,2,3,4, etc, worked fine. But now that paging feature, as long with the sorting headers, don't work! I do notice on the status bar in the browser, that I receive an error that says, "error on page...and it referers to javascript:_doPostBack('GridView1, etc etc)...I have no clue why this happened. Any help would be appreciated, thanks! 

View 2 Replies View Related

Searching A Gridview In ASP.NET 2.0

Dec 15, 2005

Hello,How do I search through the gridview?  Would I do this at the sqldatasourcelevel?I figured that I sould search with the but how do I accessthe datatable of the sqldatasource?I am using ASP.NET 2.0 with VB.Thanks for your helpJ

View 2 Replies View Related

Mind-boggling Gridview Results! Different Results For Different Teams..

Jun 18, 2008

Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"  SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1)))  AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG],, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1.  This doesn't make sense to me at all!  For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI


GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519

MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218


AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015

ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP.  Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu  ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)*** 

View 2 Replies View Related

Returning HTML Via SQL To Bind To A Gridview

Apr 14, 2008

So I have a stored procedure that returns rows that look similer to this

<a href="~/Manage/ManageVersion.aspx?viewstate=MTgwNywzMTc0LDguOSAgICAgICA=">DPF-13</a>

Unfortunatly when it is bound to the gridview it seems to convert the value to HTML safe.

The value it prints on the page is

&lt;a href=&quot;~/Manage/ManageVersion.aspx?viewstate=MTgwNywzMTc0LDguOSAgICAgICA=&quot;&gt;DPF-13&lt;/a&gt;

Hit quote to look at the actual values

How can I fix the output to the gridview to print the actual link and the the html safe value?

Can I accomplish this in SQL or am I going to have to do it all in the c# code (which will be a less clean solution and alot more work).


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

Searching Relations And Returning Them In A Table

Feb 28, 2007

I have a table tblFriends:UserCodeOwner   FriendCode5                         545                         1235                         4785                         49054                       5123                     5478                     5478                     500490                     5490                     500500                      478500                    490500                    600600                   500As you can see I store each relation twice, i've done that because now I can create a clustered index on UserCodeOwner for faster searching.What I want is the following:I want to count how many steps it takes to get from one user to another via their relations and also via which usercode it from 5 to 123 is one step.5-123from 5 to 500 is 2 steps via either 478 or 490I want to get a result like this:5-478-500and5-490-500from 5 to 600 is 3 steps:5-478-500-6005-490-500-600Does anyone have a good start for me on how im able to return such results and how I can search through the table most effecively?

View 3 Replies View Related

Searching In Results In Sql

Nov 8, 2005

I want to search all of the employeeid #'s in the employee table for any mis-typed information.  so if they put a . in the string i want to return that employee's info.  as well as any letter a - z.  I need to do this in sql and return it to a dataset for my crystal report.  any ideas?

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

Getting Results With Stored Procedure From Single Textbox Searching Table With Multi Columns

Feb 12, 2007

I admit I am a novice to queries and stored procedures but understand stored procedures are more secure, so I am choosing to challenge myself with a stored procedure and have not been successful at all.

What I am looking for is a stored procedure which would query the single table named Hospital, which would search from a single textbox the end user would enter the text they are searching for on a Windows VB application but is dependant on a few end user selected items.

1. The end user can select a radio button either "Starts with" or "Contains"
2. The end user can select from 6 checkboxes (Hospitals, Address, City, State, Zip, Type) to limit or expand their search from the columns in the table.

The table has 17columns (CO, PARENTCO, ADDR, POBox, CITY, State, ZIP, TEL, FAX, WEBSITE, HOSP_TYPE, OWNERSHIP, BEDS, CONTACT, TITLE, Email) but I am only allowing the end user to search by the above 6 columns and need to display the above 6 columns plus the phone, fax and website columns.

After the user clicks the search button it displays the results in a listbox and the user will then select the one they want and it displays the information in another set of textboxes.

Hopefully someone can get me pointed in the right direction! Thanks for your help!

View 12 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.

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,


View 1 Replies View Related

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);
// 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 = null;
} catch (SqlException e) {
} catch (Exception 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

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
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,
[ProjID] ASC

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


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,
[ReportName] ASC

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


CREATE TABLE [dbo].[tbl_ProjectStatus](
[ProjectStatusID] [int] IDENTITY(1,1) NOT NULL,
[ProjectStatus] [varchar](max) NULL,
[ProjectStatusID] ASC



CREATE TABLE [dbo].[tbl_ProjectManager](
[ProjectManagerID] [int] IDENTITY(1,1) NOT NULL,
[FName] [varchar](50) NULL,
[LName] [varchar](50) NULL,
[Inactive] [int] NULL,
[ProjectManagerID] ASC


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




IF @ReportName IS NULL

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

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

FROM dbo.Results
FROM dbo.Results
group BY T_ID) AND T_ID = 21405)


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


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


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,

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 =
INNER JOIN security_users AS security_users ON incident.incid_assigned_to = 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


If @dateValue IsNot @Null
Informationdate = @dateValue


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


FROM tblTest t

INNER JOIN CONTAINSTABLE(tblTest,*,'"kevin test 1"') as A ON = 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

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,

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

TableAdapter/DataTable Query Returning No Results

Sep 6, 2007

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

View 5 Replies View Related

Case Statement Returning Blank Results?

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:

(Select ind_customer.cst_key WHERE ixo_rlt_code = 'Relationship' )
THEN ind_customer.cst_ind_full_name_dn

View 7 Replies View Related

Procedure/Cursor Question About Returning Results

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
OPEN authors_cursor
FETCH NEXT FROM authors_cursor

FETCH NEXT FROM authors_cursor

Close Authors_cursor
deallocate authors_cursor

Thanks in advance

Tony Murunion

View 3 Replies View Related

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

... 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 View Related

Transact SQL :: GetDate Function Not Returning Any Results?

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

Returning Results Left Outer Join With Conditions

Sep 21, 2006

I have a SELECT Statement that I am using that is pulling from two tables.  There won't always be results in the second table so I made a LEFT OUTER JOIN.  The problem I am having is that I need to have three conditions in there:WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))The two conditions from the request table are causing the entire query to return NULL as the value.  I need help trying get a value whether or not there are any results in the request table.Here is the full select statement:SELECT (SELECT SUM(ISNULL(request.request_duration, '0')) AS Expr1
request AS request ON employee.emp_id = request.emp_id
WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))
AS dayspending
FROM employee AS employee_1 LEFT OUTER JOIN
request AS request_1 ON employee_1.emp_id = request_1.emp_id
WHERE (employee_1.emp_id = @emp_id)
GROUP BY employee_1.emp_id, employee_1.emp_begin_accrual, employee_1.emp_accrual_rate, employee_1.emp_fname, employee_1.emp_minitial,

View 2 Replies View Related

SqlDataSource And SSMSE Returning Different Results For Same SELECT Command

Oct 2, 2007

Hope you guys won't mind this rather newbie question.  I'm writing a simple blog page for my website and have created a SqlDataSource which queries the database for a list of blog post titles (from the web.Blog table) and the number of comments (from the web.BlogComments table).  The SqlDataSource control is:
<asp:SqlDataSource ID="sourceBlogArticles" ProviderName="System.Data.SqlClient" connectionString="<%$ ConnectionStrings:myDatabase %>" runat="server" SelectCommand="SELECT gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded, COUNT(gbc.blogID) AS noOfComments FROM web.Blog gb LEFT OUTER JOIN web.BlogComments gbc ON gb.blogID = gbc.blogID GROUP BY gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded ORDER BY gb.dateAdded"></asp:SqlDataSource>
This works perfectly well if each blog entry in the web.Blog table has associated comments in the web.BlogComments table.  However, if there are no comments yet defined in the web.BlogComments table for that blogID then no row is returned in ASP.Net (as checked with a GridView control or similar linked to the data source to view what I get)
 HOWEVER, I think the SELECT command IS correct: if I use the select command as a query in SQL Server Managment Studio Express, I do get the rows returned, with 0 for the number of comments which is what I would expect for that query:
blogID, title, description, tags, dateAdded, noOfComments
1, title 1, description for title 1, tag1, 2007-09-27 06:49:03.810, 32, title 2, description for title 2, tag2, 2007-09-27 06:49:37.513, 03, title 3, description for title3, tag3, 2007-10-02 18:21:30.467, 0
Can anyone help?  The result from the SSMSE query is what I want, yet when I use the very same SELECT statement in my SqlDataSource I don't get any rows returned if the BlogComment count is zero (in the above example I get only the first row).  Many thanks for any suggestions!

View 6 Replies View Related

Copyrights 2005-15, All rights reserved