My query is as follows:
Dim CurrentDate As Date
CurrentDate = "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:
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?
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
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
I'm writing a page that will return data from my database to the user based on their search paramaters, over several pages with 20 matching records showing per page, and a next button on the bottom. Similar to the format of any search engine. However, I'd like to write this into the query, and I'm not sure how I would go about doing so. For example: "SELECT TOP 20 to 40 * FROM Northwind" Hopefully this makes sense. Is there any way of doing this? Thanks in advance,Russ
I have a SQL question which I suspect is very easy to answer but can't seem to find one for.
I have a table which contains about 500 records. i would like to display these records on a web page, but paginated, showing only 20 records per page. I have in the past returned a recordset containing all the records and paginated programmatically in ASP. In this instance I would like to be able to pass an upper and lower bound into my stored proc and return only those records I want to display. So on page 4 I would want to display only records 61-80. I can pass in the upper and lower bound to the SP as parameters but is there some T-SQL i can use to return this range of records only.
So, the SP would for example accept the parameters
@Upperbound = 80 @Lowerbound = 61
and would return a recordset of 20 records being the 61st thru 80th record from the queried table.
Thanks for the help Guy
PS. I asked someone at work and they suggested using OFFSET and LIMIT. It seems to me as if these are only available in PostgreSQL or MySQL and not T-SQL. I guess I am looking for an equivalent I can use with SQL Server.
if @persontype<>"" begin set @cond= @cond+"and persontype="+@persontype end if @name<>"" begin set @cond= @cond+"and (charindex("+@name+",x_firstname)>0 or charindex("+@name+",x_lastname)>0 or charindex("+@name+",x_email)>0) " end if @activeaccount<>-1 begin set @cond= @cond+'and activeaccount='+@activeaccount end if @adddatetime<>"" begin set @cond= @cond+'and adddatetime='+@adddatetime end if @country<>"" begin set @cond= @cond+'and x_country='+@country end print @cond exec( " select * from users where 1=1 "+@cond) GO
I have a view that will return say 5000 records when I do a simpleselect query on that view like.select *from vw_test_viewHow can I set up my query to only return a certain # of records, saythe first 300?Here is what is going on, we have a large amount of data that returnsin a view and we need to work with all of it eventually, However wewant to do it in chunks. So my thoughts were as follows:1. To run a query to return X amount of the total data for us to workwith.2. Update these records with a flag in a table that the vw_test_viewfilters out.3. The next time I run the query to pull data from the view it willskip the records that I have already looked at (because of step 2) andpull the next X amount of records.Thanks in advance,Mike
I'm trying to write a select statement that will return only "billable" records. A record is billable if: - it is the only record for a Case (Case is an FKey ID column in my records table) OR - the last billable record for Case X has a DateTime > 24 hours before the record in question.
Getting records for the first condition is easy:
SELECT ID FROM Records r LEFT OUTER JOIN Records r2 ON r2.[Case] = r.[Case] WHERE r2.[Case] IS NULL
It's the second part I'm having trouble with.
UNION SELECT ID FROM Records JOIN ? .... WHERE DATEDIFF(hh, r.DateTime, r2.DateTime) > 24 ??
I have to assume there can be any number of records for a case.
Create Table #Request ( [requestid] int , [customername] Varchar(30) , [age] int , [sex] char(1) , [address] Varchar(30) , [status] int );
Insert Into #request Values('2342','Jack','23','M','Texas','0'); Insert Into #request Values('223452','Tom','45','M','Ohio','1'); Insert Into #request Values('22353','Bobby','23','M','Austin','0'); Insert Into #request Values('22362','Guck','23','M','Austin','0'); Insert Into #request Values('22392','Luck','23','M','Austin','1'); Insert Into #request Values('22362','Buck','23','M','Austin','0'); Insert Into #request Values('2564392','Jim','23','M','Austin','1'); Insert Into #request Values('2342','Jasm','23','M','Austin','0'); Insert Into #request Values('2765492','Chuck','23','M','Austin','1');
How can i return 30% random requestid's from this table?
I'm building a db to collect equip fault data in SQL 2005 and need to modify my query to be able to select/display "ALL" records. I'm currently using a sp to assign a shift to each record and then have a query to select all records by shift. I'd like to add an option to the query to be able to select ALL records, regardless of shift. I've included the sp & query I am currently using. Any help would be appreciated.
Thanks
ALTER PROCEDURE [dbo].[p_dtu_Store_Line_Fault_Data] -- Add the parameters for the stored procedure here @AssetID int, @Timestamp datetime, @FaultCode int, @State int AS BEGIN SET NOCOUNT ON; IF @State = 3 BEGIN INSERT LineFaultData (FaultCode, AssetID, StartTime, Duration, Shift) VALUES (@FaultCode, @AssetID, @Timestamp, 0, CASE WHEN DATEPART(hh,@Timestamp) BETWEEN 7 AND 14 THEN 'DAYS' WHEN DATEPART(hh,@Timestamp) BETWEEN 15 AND 22 THEN 'AFTERNOONS' ELSE 'NIGHTS' END) END
IF @State <> 3 BEGIN DECLARE @Count int SET @Count = (SELECT Count(*) FROM LineFaultData WHERE AssetID = @AssetID AND Duration = 0) IF @Count <> 0 BEGIN DECLARE @StartTime datetime SET @StartTime = (SELECT Top 1 StartTime FROM LineFaultData WHERE AssetID = @AssetID and Duration = 0) UPDATE LineFaultData SET Duration = DateDiff(s,@StartTime, @Timestamp) WHERE AssetID = @AssetID and Duration = 0 and StartTime = @StartTime
END END
END
SELECT TOP (1000) dbo.LineFaultDescription.Station, dbo.LineFaultData.StartTime, dbo.LineFaultData.Duration, dbo.LineFaultDescription.FaultDescription, dbo.LineFaultDescription.FaultCategory, dbo.LineFaultData.Shift FROM dbo.LineFaultDescription INNER JOIN dbo.LineFaultData ON dbo.LineFaultDescription.FaultCode = dbo.LineFaultData.FaultCode AND dbo.LineFaultDescription.AssetID = dbo.LineFaultData.AssetID and (StartTime < '{@End Date}' and StartTime > '{@Start Date}')
WHERE (dbo.LineFaultData.AssetID = {Asset_ID}) AND (dbo.LineFaultData.Shift = '{@Shift}') ORDER BY dbo.LineFaultData.StartTime DESC
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.
Hi I have a table called DiaryDate2 and a query called DiaryDateOver8 and want to have a sql query to return the records from the table that are not in the query based on two linked fields username and Diarydate1 The SQL I have written however returns all records SelectCommand="SELECT DiaryDate2.Username, DiaryDate2.DiaryDate1 FROM DiaryDate2 WHERE(DiaryDate2.Username = ?)AND NOT EXISTS (SELECT 1 from DiaryDateover8 where DiaryDateover8.Username=DiaryDate2.Username AND DiaryDateover8.DiaryDate1=DiaryDate2.DiaryDate1)ORDER BY DiaryDate2.DiaryDate1"> Please could someone help Many thanks Colin
Hi guys, I need to know if there is a way to select a range of records from a database. Kind of like using SELECT TOP 1000, but I need to be able to specify which records to return. So I imagine it would look like this:
SELECT TOP 2000-5000 * FROM customers WHERE groupid=2 ORDER BY FirstName DESC
Where this statement would return only records 2000 to 5000 of the returned results.
tblPerson - holds basic person data. tblPersonHistorical - holds a dated snapshot of the fkPersonId, fkInstitutionId, and fkDepartmentId tblWebUsers - holds login data specific to a web account, but not every person will have a web account
I want to allow my admins to search for users (persons) with web accounts. They need to be able to search by tblPerson.FirstName, tblPerson.LastName, tblInstitutions.Institution, and tblDepartments.Department. The only way a Person record is joined an Institution or Department record is through many -> many junction table tblPersonHistorical.
People place orders and make decisions in our system. Because people can change institutions and departments, we need an historical snapshot of where they worked at the time they placed an order or made a decision. Of course that means some folks will have multiple historical records. That all works fine.
So when an admin user wants to search for webusers, I only want to return data, if possible, from he most recent/current historical records. This is where I am getting bogged down. When I search for a specific webuser I simply do a TOP 1 and ORDER BY DateCreated DESC. That returns only the current historical record for that person/webuser.
But what if I want to return many different webusers, and only want the TOP 1 historical for each returned?
Straight TOP by itself won't do it. GROUP BY by itself won't do it.
This is a follow on from one of my earlier threads where I was trying to return one specific record. In this case I am trying to return multiple records for the same person ID and within a number of days range. Snapshot of my date with same PersonID:
And from this I want to check if one record's leaving date of the record is within 7 days of another record's arrival date. I also want to return the record that had a leaving date within 7 days of the next arrival date.I understand that if I self join on personID with the data above I will get 9 rows, for each row I will get 3 matches, I am using INNER JOIN to join to the same table but with a different alias so I assume this is the self join I should be using.
But then how do I process this? I would want to say for record 1 check the leaving date is within 7 days of arrival date of any other record matching that PersonId but not ArrivalID, and return both records.From my snapahot of code I would eventually want to return:
But can't seem to get this using a self join query like this:
select a.PersonID, a.Leaving_date,a.Arrival_Date,a.arrivalID from arrivals a INNER JOIN arrivals b ON b.personID = a.personID WHERE a.arrivalid != b.arrivalid and DATEDIFF(DD, b.[Leaving_date], a.[Arrival_Date]) <=7 and DATEDIFF(DD, b.[Leaving_date], a.[Arrival_Date]) >=0
I have data that looks like below (columns are Timestamp, Offered, Answered and Delay). I'm looking to exclude returning records that have a value for Delay that are within the top 10% of values of that column. Are there any 2005 tricks where this can be accomplished in a simple statement?
I'm trying to fully utilize parameters on a report.
I have 3 parameters, a start date, end date, and a cause.
The start and the end date are working as expected. What I would like is if a user fails to select a cause (queried from a lookup dataset) I would like to return all records, not filtering by cause. I'm using parameters handed to the dataset to do this and the report will not allow me not to select a value.
Should I be using Filter, Report Parameters, or some other means to solve this?
Any advice would be appreciated as I don't want to "KLUDGE" up the system with a bunch of reports each with varying levels of criteria.
I am trying to return the number of records found by the query but keep seeing -1 in label1. This query should return many records. sub findcustomers(sender as object,e as eventargs) dim connection1 as sqlconnection=new sqlconnection(...) dim q1 as string="select * from tblcustomers where store='65'" dim command1 as sqlcommand=new sqlcommand(q1,connection1) dim a as integer command1.connection.open() a=command1.executenonquery() label1.text=a.tostring() command1.connection.close() end sub What am I doing wrong?
How to return only non matching left join records. Currently I am doing a traffic management database to learn sql.
I am checking for all parishes with no associated drivers. Currently I only have 2 of such.
The regular left join
select parish.name, driver.fname from parish left join driver on driver.parish=parish.name
Returns the all the names of the parishes and the first name of the associated drive, followed by the matches, however the two parishes with no matches have null for the first name.
Hi,I have a sql server database with 1.7 million records in a table, withabout 30 fieldsWhen I run select * from tablename it can take over 5 minutes.How can I get this time down or is it normal?ThanksJerry
Our users use Report Builder for writing ad-hoc queries. They have run into a problem where Report Builder is returning only distinct records is the entities unique identifier is not added to the report.
Example:
TransID Type TransQuantity
1 CS 4 2 CS 4 3 CP 2 4 CN 3
Adding the above fields to the report builder canvas will return 4 rows. However, if I were to add only Type and TransQuantity to the canvas as such, Report Builder will only return 3 rows:
Type TransQuantity
CS 4 CP 2 CN 3
Is there any way to have Report Builder return the actual number of records? Our users often will export the results from Report Builder into Excel to slice and dice the data, and with the functionality the Report Builder has right now, incorrect values will be certain.
ALTER PROCEDURE [dbo].[SelectQueryAddressPostCodeSearchOnly]
(
@postcode nvarchar(50)
)
AS
SET NOCOUNT ON;
SELECT
Cust_Address.Cust_Address_ID,
Cust_Address.Cust_Address_1,
Cust_Address.Cust_Address_2,
Cust_Address.Cust_Address_Post_Code,
Cust_Address.Cust_Post_Town,
Post_Town.Post_Town_ID,
Post_Town.Post_Town_Name,
Post_Town.Post_Town_Priority
FROM Cust_Address
INNER JOIN Post_Town ON Cust_Address.Cust_Post_Town = Post_Town.Post_Town_ID
WHERE Cust_Address.Cust_Post_Town LIKE @postcode
The value for @postcode being passed for testing is "%SA%". I would expect this to return all records which have "SA" in the Post Code field. In the test database I am using there are several (Mainly SA43 0EZ). However the SP in fact returns no matching records at all.
Am I not understanding something here, or is there something to do with space in post code field that is causing a problem ? I have other SPs that are behaving just fine.
I'm running into an issue where if a report retrieves 0 records, I run into an error. My report has 2 queries-- the main query to retrieve Account information and the other to return Contact information based on the contact id(s) returned by the Account query. The reason needing two separate queries is that the contact and account tables are on different databases.
Now here is my account query: select contact_id, username, password from account. My contact query looks like this: select name, address, .... from contact where contact_id = @contact_id.
In my report parameters, I define contact_id like this: ******************************************* name = contact_id data type = integer prompt = [blanked out] hidden = checked internal = checked multi-value = unchecked allow null value = checked allow blank value = unchecked
available values: from query: dataset = account value field = contact_id
label field = contact_id
default values: from query: dataset = account value field = contact_id **********************************************
Now, when I run my report and knowing there are no records, I get the following error: "The 'contact_id' parameter is missing a value". Any ideas on how to solve this issue? Thanks.
Hi all,I am using the following stored procedure in SQL 7 to return a range of records.CREATE PROCEDURE spRetMyTable@maxRows int, @lastRecord int, @SortPhrase varchar(122)ASbeginDECLARE @stSql VARCHAR(255) SET ROWCOUNT @maxRowsSET @stSql = 'SELECT * FROM (SELECT TOP 10 colPrimary, col1, col2, col3 FROM (SELECT TOP ' + CONVERT(VARCHAR, @lastRecord) + 'colPrimary, col1, col2, col3 FROM [MyTable] ORDER BY [colPrimary] ASC) AS tbl1 ORDER BY [colPrimary] DESC) AS tbl2 ORDER by [colPrimary] ASC'exec(@stSql)set rowcount 0endTo execute the above procedure I am issuing the command:execute spRetMyTable @maxRows 10, @lastRecord 1500The column [colPrimary] is the primary field in the table.I will be using the selected records in a gridView with paging (3 records per page). So if the next page is selected I would require to take out record 1491 to record 1494 and Add record 1501 to record 1503 in the datatable subject to the availability of records in the table MyTable.How do I provide sorting by col1, col2 or col3? It could be single column or multiple column sorting as chosen by the user during runtime.Is there any performance overhead in the above method in the first place?Could it be improved by any alternative process?I need YOUR ASSISTANCE to cater to the above issues in a feasible way.Thanks in advance.
Hi, and thanks in advance. I have VWD 2005 Express and SQL 2005 Express running. I have a SqlDastasource wired to the stored procedure. When I only include one Control parameter I get results from my Stored procedure, when I inclube both Control Parameters I get no results. I manually remove the second control parameter from the sqldatasource by deleting... <asp:ControlParameter ControlID="ddlSClosed" DefaultValue="" Name="SClosed" PropertyName="SelectedValue" Type="String" /> I have one Radio Group and one dropdownlist box that supplies the parameters. The dropdownlist parameter is Null or "" when the page is first loaded. Below is my SQLDatasource and Stored procedure. I am new to Stored Procedures, so be gentle. Any help would be appreciated!
ALTER PROCEDURE [dbo].[spDisplayServiceOrders] ( @SDate_Entered SmallDateTime, @SClosed nvarchar(50)= NULL ) AS If @SClosed IS NULL BEGIN
SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID and SDate_Entered >= @SDate_Entered Order by SDate_Entered DESC END ELSE BEGIN
SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID and SDate_Entered >= @SDate_Entered and SClosed = @SClosed Order by SDate_Entered DESC
I've created a sproc in SQL2000 that returns a dataset from a temp table & the number of records it's returning as an output parameter, although I can't seem to retrieve the value it's returning in asp.net although I get the dataset ok.
This is my sproc create procedure return_data_and_value @return int output as set nocount on ... ... select * from #Table select @return = count(*) from #Table drop table #Table go
This is asp.net code
Dim nRecords as Int32 Dim cmd As SqlCommand = New SqlCommand("return_data_and_value", conn) cmd.CommandType = CommandType.StoredProcedure
Dim prm As SqlParameter = New SqlParameter("@return", SqlDbType.Int) prm.Direction = ParameterDirection.Output cmd.Parameters.Add(prm)
create table a (id int, name varchar(10)); create table b(id int, sal int); insert into a values(1,'John'),(1,'ken'),(2,'paul'); insert into b values(1,400),(1,500);
select * from a cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal 1John500 1ken500 2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
BEGIN DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' ) END BEGIN INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22' END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
