Hi,
My Select query returing the one column:
Options
1
2
3
Form this Options column I have to check the Checkbox1, checkbox2, checkbox3 out of total 6 checkboxes in asp.net form. I am getting the Options column from database.
How to store that resultset.
Thanks in advance.
I am trying to find a way to lock rows of data used in a SELECT query from being read by another SELECT query.
I could do a "begin tran - select - update - rollback" sequence but was wondering if there is a cleaner way to do this??
Tried UPDLOCK, ROWLOCK, TABLOCK, HOLDLOCK in multiple variations but none seem to block the select.
I have a table which is returning inconsistent results when I queryit!In query analyzer:If I do "SELECT * FROM TABLE_NAME" I get no rows returned.If I do "SELECT COL1, COL2 FROM TABLE_NAME" I get 4 rows returned.In Enterprise manager:If I do "return all rows" I get 4 rows returned, and the SQL is listedas being "SELECT * FROM dbo.TABLE_NAME".I've tried adding the "dbo." before my table name in QA, but it seemsto make no difference.I'm using SQL Server 2000, which is apparently 8.00534.Can anyone help me, or give me ideas about what to check?Thanks,Rowland.
View 9 Replies View RelatedHi friends,
I am facing problem with SQLRowCount() function which is returning -1 when I tried to fetch records greater than 99 from my data file. if the data file contains total of 99 records then the above function is returning its second parameter value nRowCount as 99 but if dat file contains 100 records then it is returning nRowCount as -1
Please tell me whether it is a driver specic error or some thing else. I am using a ODBC driver for a database called MYOB.
Below is the piece of code that I used for fetching records.
if (SQLExecDirect(m_StmtHandle, (PUCHAR)sExecuteStatement.c_str(), SQL_NTS) != SQL_SUCCESS) return false;
if (SQLNumResultCols(m_StmtHandle,(SQLSMALLINT *) &nColumnCount) != SQL_SUCCESS) return false;
if (SQLRowCount(m_StmtHandle,&nRowCount) != SQL_SUCCESS) return false;
The string 'sExecuteStatement' in SQLExecDirect() function above is carrying a Select query that fetch 100 records.
Its strange that the function fails to return exact row count for 100 records, but it is working fine with 99 records.
I ma not using any kind of array with size of 99 or 100 in my code.
What I want to know is whether I can use SQLRowCount() with SELECT statement, If not what is the alternative.
Pleasee help me with a piece of code if you can.
Ishwar B.G
Hi friends,
I am facing problem with SQLRowCount() function which is returning -1 when I tried to fetch records greater than 99 from my data file. if the data file contains total of 99 records then the above function is returning its second parameter value nRowCount as 99 but if dat file contains 100 records then it is returning nRowCount as -1
Please tell me whether it is a driver specic error or some thing else. I am using a ODBC driver for a database called MYOB.
Below is the piece of code that I used for fetching records.
if (SQLExecDirect(m_StmtHandle, (PUCHAR)sExecuteStatement.c_str(), SQL_NTS) != SQL_SUCCESS) return false;
if (SQLNumResultCols(m_StmtHandle,(SQLSMALLINT *) &nColumnCount) != SQL_SUCCESS) return false;
if (SQLRowCount(m_StmtHandle,&nRowCount) != SQL_SUCCESS) return false;
The string 'sExecuteStatement' in SQLExecDirect() function above is carrying a Select query.
Its an unpredictable error because the function fails to return exact row count for 100 records, but it is working fine with 99 records.
I am not using any kind of array with size of 99 or 100 in my code.
What I want to know is:
1. Can I use SQLRowCount() with SELECT statement, If not what is the alternative.
2. Is this a driver specific error?.
3. Can I use SQL_DIAG_ROW_COUNT instead of SQLRowCount? If Yes, How to use it?.
Your help will be greatly appriciated if you give me a piece of code for this issue.
Hi there ;
This Problem is goin to make me crazy!
I've got a table with couple of fields, 2 of them are DateTime, in Sql Express 05 .
I used asp.net 2.0 to insert datetime values, directly, using sth like DateTime.Now.ToString() .
i use 2 selects as follows :
1)select * from X where Date1='8/9/2006 11:17:41 AM'
2)select * from X where Date2='8/9/2006 11:17:41 AM'
#1 is OK, but the second one returns a row with all columns set to NULL .
(X is the name of the table)
Thanks in advance
All- Please assist: In this SELECT statement:
1 SELECT person_id, (last + ', ' + first + ' on ' + CASE f.address_1 WHEN NULL THEN 'none' ELSE f.address_1 END) as last_first_address
2 FROM person
3
4 LEFT JOIN family f
5 ON person.family_id = f.family_id
I'm finding that the compound column last_first_address returns <null> when f.address_1 is null, even though it seems like it should return the string "none" per my CASE statement. If f.address_1 is not NULL, then it the statement works as expected. Please advise as to how I can fix the statement so that "none" is returned as the final string in last_fist_address if f.address_1 is null.
Note: I tried removing the parens. No effect on result.
Thanks!
-Kurt
I have a table:
Code:
CREATE TABLE [dbo].[XmlTable](
[XmlId] [int] IDENTITY(1,1) NOT NULL,
[XmlDocument] [xml] NOT NULL,
CONSTRAINT [PK_XmlTable] PRIMARY KEY CLUSTERED
[Code] .....
With a schema structure:
Code:
<dev:Doc xmlns:dev="http://www.w3.org/2001/XMLSchema" SchemaVersion="0.1" Settings="Testing" Title="Ordering">
<dev:Base RevisionNumber="0" Baseid="34433" />
<dev:Rev Time="2013-01-21T15:08:00">
<dev:Person Name="Me" Systemid="54654" />
</dev:Rev>
[code]....
I am trying to return the id, number, name, and location of the visitors
Something like:
Code:
RevNumber Function Id Number Visitor Location Sender
========= =========== ======== ======= ======== ======
0 A1 1 Dev01 STLRF FGY(14A)
0 A1 1 Dev02 STLRF FGY(14A)
0 A1 1 Dev03 FGRTY FGY(14A)
0 A2 1 GHFF NULL W33R
0 A2 2 UDT NULL RJ4
Here is the table insert
Code:
INSERT INTO XmlTable(XMLDocument)
SELECT * FROM OPENROWSET(
BULK 'C:Users123DesktopPractice.xml',
SINGLE_BLOB) AS x;
I Have gotten a little further, but the number is not showing for A2 and the Sender, visitor is showing null.
Code:
;WITH XMLNAMESPACES ( 'http://www.w3.org/2001/XMLSchema' as dev )
SELECT
Document.value('@Title' , 'NVARCHAR(MAX)') Title,
Functions.value('@Id', 'NVARCHAR(MAX)') Functions,
A1.value('@Number', 'INT') Number,
[code]....
Results I am getting that are not all correct:
Code:
Title Functions Number VisitorName Location Sender
======== ========= ====== =========== ======== ======
Ordering A1 1 Dev01 STLRF NULL
Ordering A1 1 Dev02 STLRF NULL
Ordering A1 1 Dev03 FGRTY NULL
Ordering A2 NULL NULL NULL NULL
Not I changed Rev to Title
I have a UDF with a select * that works fine in one region (DEV) but not another (QC). It's not returning the last 2 columns from the table in QC.I looked at the UDF and it does a fairly simple select:
select a.*
from myTable A
The table is the same in both regions and I did a sp_help on the table to ensure these 2 columns are listed. They are. Also, executing a select * in a query windows does return the final 2 columns from the table in QC. The issue resides in the QC version of the UDF only.
The UDF has already been updated to retrieve all columns by name but I'm curious why this would happen. For some reason I'd just like to know and in case it happens again.
I have a strange situation with an select. I've noticed that when I select top 100, a record is not returning from the database, but when doing top 101 the record appears on position 41.
The query is like this:
select top 100 GroupId, count(HouseId)
from House h
group by h.GroupId
order by max([DateCreated]) desc
From all discussions about top 100 vs top 101 I've noticed that everybody is saying that top 101 is using another algorithm and we can have a speed problem, but my problem is not about this. With top 100 I'm missing a record that should appear at index 41.
I am using a SQLDataSource with the following Select query. If the "spouse" values are not in the database, I get the HTML non-breaking-space character back, rather than an empty string.
SelectCommand="select applicant_id, (applicant_last_name + ', ' + applicant_first_name) as applicant_name, CONVERT(varchar(10), applicant_dob, 101) as applicant_dob, applicant_ssn, (spouse_last + ', ' + spouse_first) as spouse_name, CONVERT(varchar(10), spouse_dob, 101) as spouse_dob, spouse_ssn from applicant where applicant_last_name like '%'+@last_name+'%' order by applicant_last_name"
Here is the relevant code-behind:
TextBox tb = (TextBox)formView.FindControl("SpouseName");
tb.Text = e.Item.Cells[4].Text;
DatePicker dp = (DatePicker)formView.FindControl("SpouseDateOfBirth");
if (e.Item.Cells[5].Text.Length > 0)
{
try // this is a try/catch because nbsp is not parsed as a date
{
dp.DateValue = DateTime.Parse(e.Item.Cells[5].Text);
}
catch
{
dp.txtDate.Text = "";
}
}
tb = (TextBox)formView.FindControl("SpouseSocialSecurityNumber");
tb.Text = e.Item.Cells.Text;
Thoughts?
Todd
linking two tables together to get an end result
find below the code i have used
The first part of the query provides me with the info i need
SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp
[Code].....
Hi,
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!
SQL 2005 9.00.3402.00 (x64) As Above really when I select * OR select a single column from the view the wrong column data is returned. in SQL Management Studio when I expand the Columns of the view it reflects the old table structure not the new table structure. I can easily fix by compiling the view again but this would mean I would have to recompile all referencing views when I make a change to table structures. I've tried various DBCC Clean Buffers & drop cache with no effect. Is there a command to recompile all views & poss stored procs in a database. Any help or explanation would be appreciated GW
View 12 Replies View RelatedHiI'm not sure what the best approach for this is:I have a stored procedure which I would like to use to return severaloutput values instead of returning a recordset.CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 intOUTPUT) ASSELECT field2, field3 FROM Table WHERE field1 = @param1I would like to return @param2 as field2 and @param3 as field3How do I do this without using SELECT multiple times?THanks in advanceSam
View 6 Replies View Related
I have a script with a number of different Left Joins. When I run it in Query Analyzer the Left Join works as a normal join and suppresses some rows. However when the same code is used in a stored procedure the correct results are produced when the stored procedure is executed.
Can anyone tell me what is causing this?
SELECT USR.ROLEUSER
,PER.NAME
,EMP.PAYGROUP
,USR.ROLENAME
,OPR.EMPLID
,OPR.OPRDEFNDESC
,OPR.ACCTLOCK
,OPR.ROWSECCLASS
,EMP.JOBCODE
,JCD.DESCR
,EMP.DEPTID
,EMP.DEPTNAME
FROM PSROLEUSER USR
JOIN
PSOPRDEFN OPR
ON
USR.ROLEUSER = OPR.OPRID
LEFT OUTER JOIN
PS_PERSONAL_DATA PER
ON
OPR.EMPLID = PER.EMPLID
LEFT OUTER JOIN
PS_EMPLOYEES EMP
ON
OPR.EMPLID = EMP.EMPLID
LEFT OUTER JOIN
PS_JOBCODE_TBL JCD
ON
JCD.SETID = 'RBLTT'
AND EMP.JOBCODE = JCD.JOBCODE
AND JCD.EFFDT =
(SELECT MAX(EFFDT)
FROM PS_JOBCODE_TBL
WHERE JCD.SETID = SETID
AND JCD.JOBCODE = JOBCODE
AND EFFDT <= GETDATE())
WHERE OPR.ACCTLOCK = 0
AND USR.ROLENAME = 'RBL MANAGER'
AND EMP.PAYGROUP NOT IN ('RBA', 'RMA')
Hey Guys. I’m having a little trouble and was wondering if you could help me out. I’m trying to create a custom paging control, so I create a stored procedure that returns the appropriate records as well as the total amount of records. And that works fine. What I’m having problems with is reading the data from the second select statement within the code. Anyone have any idea on how to do this? Also.. how can I check how many tables were returned?
Here's my code. I'm trying to keep it very generic so I can send it any sql statement:public DataTable connect(string sql)
{
DataTable dt = new DataTable();
SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ToString());
SqlDataAdapter SqlCmd = new SqlDataAdapter(sql, SqlCon);
System.Data.DataSet ds = new System.Data.DataSet();
SqlCmd.Fill(ds);
dt = ds.Tables[0];
//Here's where I don't know how to access the second select statement
return dt;
} Here's my stored procedure:
ALTER PROCEDURE dbo.MyStoredProcedure
(
@Page int,
@AmountPerPage int,
@TotalRecords int output
)
AS
WITH MyTable AS
(
Select *, ROW_NUMBER() OVER(ORDER BY ID Desc) as RowNum
From Table
where Deleted <> 1
)
select * from MyTable
WHERE RowNum > (((@Page-1)*@AmountPerPage)) and RowNum < ((@Page*@AmountPerPage)+1);
Select @TotalRecords = COUNT(*)
from Table
where Deleted <> 1
RETURN
Thanks
I am writing a query and have the bulk of it already written.
I am looking at a table that contains customer orders. There is a column named Customer_Order.Status Available values for this column is R, F, H, and C.
I'd like for my query to return all lines that have the value R, F, H.
My where clause is written like this
WHERE CUSTOMER_ORDER.SITE_ID = 'XXX' AND CUSTOMER_ORDER.STATUS = ('R','H','F')
I know I'm missing something....
Here's my statement below. What I'm trying to get is joining the name column in master.sys.databases with a sub query for the database name, file location and backup start date from the MSDB database. The reason for this, if a new database has never been backed up, It should be returning as a NULL value, which is my goal. However, I'm getting multiple results for the backups.
select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,a.name,File_Location=b.physical_device_name,backup_start_date=max(backup_start_date)
from master.sys.databases a
left join(select c.database_name,backup_start_date=max(backup_start_date),b.physical_device_name
from msdb.dbo.backupmediafamily b join msdb.dbo.backupset c on c.media_set_id=c.backup_set_id
where c.type='D'
[Code] .....
I have the following query.
select top 3 dbo.oncd_incident.open_date,dbo.onca_product.description,dbo.onca_user.full_name,dbo.oncd_incident.incident_id,email, dbo.oncd_contact.first_name,dbo.oncd_contact.last_name,dbo.oncd_contact.contact_id
from dbo.oncd_incident
inner join dbo.oncd_incident_contact on dbo.oncd_incident_contact.incident_id=dbo.oncd_incident.incident_id
inner join dbo.oncd_contact on dbo.oncd_contact.contact_id=dbo.oncd_incident_contact.contact_id
inner join dbo.oncd_contact_email on dbo.oncd_contact_email.contact_id=dbo.oncd_contact.contact_id
inner join dbo.onca_user on dbo.oncd_incident.assigned_to_user_code=dbo.onca_user.user_code
inner join dbo.onca_product on dbo.onca_product.product_code=dbo.oncd_incident.product_code
where dbo.oncd_incident.incident_status_code='CLOSED'
and email is not null
and dbo.oncd_incident.open_date>DateAdd(wk,-2,getdate()) and dbo.oncd_incident.completion_date>=DateAdd(dd,-2,getdate()) and
dbo.oncd_incident.assigned_to_user_code in (select user_code from dbo.onca_user)
order by newid()
I want the query to be executed for each row returned by the sub query.If I use IN keyword it returns top 3 rows for any 3 of the users.But I want top 3 rows to be returned for each of teh user.Please help.
Hi! I have a sql query in stored procedure: SELECT Salutation + ' ' + FirstName + ' ' + LastName AS fullname
Ok, this returns a value if salutation is not null, but if the salutation is null it doesn't return any value, I was thinking if the saluation is null then I would atleast get the firstname and last name. Any help appreciated on this.
so I know how to use tsql to print a value in just the query, but when I insert a column, I want it to return the value of the ID that was just created. I don't know how to do this and send the value back to asp.net so that I can move on with that value. Can any body help me with this? this is what I have so far. thanks for the help!@title varchar(40) = null,
@intro varchar(max) = null
AS
if @title is null
begin
print 'Title Cannot Be Null'
return 0
end
if @intro is null
begin
print 'Intro Cannot Be Null'
return -1
end
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.Issue
(Title, Intro)
VALUES
(@title, @intro)
return SCOPE_IDENTITY()
Greetings!
I need to run a select statement that only returns 50 rows. How do I limit the amount of rows returned? Normally the query will return hundreds of rows but all I need is the first 50 it retrieves. I have looked in the BOL and can only find help with a block cursor not just a query.
Thanks a million
zachary
how can i get a row number in a query in ms/sql
View 7 Replies View RelatedHello everyone,
I set up a View in SQL Server 2005. The syntax checks ok, however, when I execute it, it doesn't return any data.
This is my Query:
SELECT DATEPART(hh, Time) AS Time, COUNT([Recipient-Address]) AS [CountOfRecipient-Address], ROUND(SUM([Total-bytes]) / 1048576, 2)
AS [SumOfTotal-Bytes]
FROM dbo.TrackingLog
WHERE (RIGHT([Recipient-Address], LEN([Recipient-Address]) - PATINDEX([Recipient-Address], '@')) IN
(SELECT Domains
FROM dbo.Domains))
GROUP BY DATEPART(hh, Time)
The part that I am most concerned about is the WHERE section. If I remove it, I get some data returned. If I don't, obviously I don't get anything back.
Any suggestion would be great.
Thanks
I want to get the count of rows in the table which match the status. I am writing dynamic query for it..
Create Procedure Dyn_Get_CountByStatus
(
@TableName varchar(200),
@Status int
)
as
Begin
Declare @strQuery varchar(500)
Declare @count int
set @strQuery = 'select count(*) from '+@TableName + 'where status=' + @Status
set @count =exec(@strQuery)
return @count
End
GO
This query is not working. How can get the desired result using dynamic query
Is there a way to set SQL Server 2005 Express so that I can return null values? For example, the following query will not return any values:
SELECT *
FROM tbl_form_values
where fldVALUE IS NULL;
it does return values with:
SELECT *
FROM tbl_form_values
where fldVALUE = '';
I have an Oracle background and all null values are true nulls not empty strings. I would like to be able to use the first query. Also other functions such as COALESCE work very nice with nulls. I can do the following in Oracle but not in SQL Server:
SELECT fldID, fldMID, fldFID,
COALESCE(fldVALUE, 'n/a');
FROM tbl_form_values
This will return the values in fldVALUE if they are available and n/a for all NULL values.
Any help would be appreciated.
Thanks,
E
I am having problems with a SQL query.
Table has 10 fields and I need to return them all. The three most importaint, at least for the filter I need are:
id, studentid, date, canceled.
I need to return the last max(date) grater than or equal to @dateparam which is not canceled for each studentid
I have worked out some solutions but am not happy with them. Specially woried about performance when the table grows. I am expecting in full production a table growth of about 3 million records per month.
what would be grate is if there where a way of returning a the coresponding id like in:
select studentid, max(date), related(id) as ids
from tablea
where canceled=0
group by studentid
then I could do:
Select *
from tablea inner join (select studentid, max(date), related(id) as ids
from tablea a
where canceled=0
group by studentid
) b on (a.id=b.ids)
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
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
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 RelatedThis question has been posted on the site before but I could not find any resolution....I want to return rows 11 - 20 from a query that returns 100 records without using a cursor or temp table.
The closest query I have found is a query that numbers the rows, but I can't seem to use rownumber in a between clause...
Use Pubs
SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY emp_id
Thanks,
Jim
I'm trying to write a query that will return rows within a specified range and print to a Crystal Report. When I run the query, it produces 2 row of everything. I would use the SELECT DISTINCT clause, but Crystal Reports will not let me edit the Select statement. But I can edit the FROM, WHERE and ORDER BY clauses. I think the problem is in my INNER JOINS but I'm having a problem figuring it out. Can someone please guide me in the right direction.
SELECT
DrawingVouchers."PlayerID",
DrawingVoucherNumbers."PromoID", DrawingVoucherNumbers."VoucherNumber", DrawingVoucherNumbers."IssueDate", DrawingVoucherNumbers."UserID",
CDS_PLAYER."LastName", CDS_PLAYER."FirstName",
CDS_ACCOUNT."Address1A", CDS_ACCOUNT."City1", CDS_ACCOUNT."State1", CDS_ACCOUNT."Zip1"
FROM
{ oj (("WinOasis"."dbo"."DrawingVouchers" DrawingVouchers INNER JOIN "WinOasis"."dbo"."DrawingVoucherNumbers" DrawingVoucherNumbers ON
DrawingVouchers."PlayerID" = DrawingVoucherNumbers."PlayerID")
INNER JOIN "WinOasis"."dbo"."CDS_PLAYER" CDS_PLAYER ON
DrawingVoucherNumbers."PlayerID" = CDS_PLAYER."Player_ID")
INNER JOIN "WinOasis"."dbo"."CDS_ACCOUNT" CDS_ACCOUNT ON
CDS_PLAYER."Player_ID" = CDS_ACCOUNT."Primary_ID"}
WHERE
DrawingVoucherNumbers."VoucherNumber" >= 37806 AND
DrawingVoucherNumbers."VoucherNumber" <= 37813