|
|
hi.
I want to select,for example, records from no 10 to 20.
I have no id column or ordered column .
i want this for paging in asp.net grid fro a table with one million records.
I wan retrive records 10 by 10
|
|
|
|
|
A common way to do this is to do nested selects. If you want records from number 10, and you want 10 of them, you select the top 20 records, then you do a select on the result set of that query, which asks for the top 10, ordered in descending order. Finally, you want to select this in ascending order, if that's the order you want.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
I’m writing the following stored procedure:
USE Employees;
GO
IF OBJECT_ID('mrmsp_RetrieveEmployeeForEditting','P') IS NOT NULL
DROP PROCEDURE mrmsp_RetrieveEmployeeForEditting;
GO
CREATE PROCEDURE mrmsp_RetrieveEmployeeForEditting
@varEmployeeID INT,
@varTitleID INT OUTPUT,
@varDeptID INT OUTPUT,
@varLName VARCHAR(25) OUTPUT,
@varFName VARCHAR(15) OUTPUT,
@varMI VARCHAR(1) OUTPUT,
@varAddrTypeID INT OUTPUT,
@varAddress1 VARCHAR(40) OUTPUT,
@varAddress2 VARCHAR(40) OUTPUT,
@varCity VARCHAR(40) OUTPUT,
@varStateID INT OUTPUT,
@varZip VARCHAR(10) OUTPUT
AS
SELECT tblEmployees.[EmployeeID], tblEmployees.[TitleID],
tblEmployees.[DeptID], tblEmployees.[LName],
tblEmployees.[FName], tblEmployees.[MI],
tblAddresses.[AddressTypeID], tblAddresses.[Address1],
tblAddresses.[Address2], tblAddresses.[City],
tblAddresses.[StateID], tblAddresses.[ZipCode]
FROM Employees.dbo.tblEmployees
INNER JOIN Employees.dbo.tblAddresses
ON Employees.dbo.tblEmployees.EmployeeID =
Employees.dbo.tblAddresses.EmployeeID
WHERE Employees.dbo.tblEmployees.EmployeeID = @varEmployeeID
AND Employees.dbo.tblEmployees.Deleted = 0
ORDER BY Employees.dbo.tblEmployees.EmployeeID
SET @varTitleID = tblEmployees.[TitleID]
SET @varDeptID = tblEmployees.[DeptID]
SET @varLName = tblEmployees.[LName]
SET @varFName = tblEmployees.[FName]
SET @varMI = tblEmployees.[MI]
SET @varAddrTypeID = tblAddresses.[AddressTypeID]
SET @varAddress1 = tblAddresses.[Address1]
SET @varAddress2 = tblAddresses.[Address2]
SET @varCity = tblAddresses.[City]
SET @varStateID = tblAddresses.[StateID]
SET @varZip = tblAddresses.[ZipCode];
When I run the check routine it says there are no errors in the T-SQL statement, but when I try to run the SQL to place the stored procedure into the database I get the following errors:
Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 28
The multi-part identifier "tblEmployees.TitleID" could not be bound.
Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 29
The multi-part identifier "tblEmployees.DeptID" could not be bound.
Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 30
The multi-part identifier "tblEmployees.LName" could not be bound.
Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 31
The multi-part identifier "tblEmployees.FName" could not be bound.
Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 32
The multi-part identifier "tblEmployees.MI" could not be bound.
Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 33
The multi-part identifier "tblAddresses.AddressTypeID" could not be bound.
Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 34
The multi-part identifier "tblAddresses.Address1" could not be bound.
Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 35
The multi-part identifier "tblAddresses.Address2" could not be bound.
Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 36
The multi-part identifier "tblAddresses.City" could not be bound.
Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 37
The multi-part identifier "tblAddresses.StateID" could not be bound.
Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 38
The multi-part identifier "tblAddresses.ZipCode" could not be bound.
I assume the SQL statement can’t bind the data from the indicated columns into the variables in the stored procedure.
What am I doing wrong?
Quecumber256
|
|
|
|
|
dbo.tblEmployees.TitleID if you use multipart identifiers, you have to supply all three parts...
|
|
|
|
|
Actually, I think the syntax is wrong (you don't want the SET business at all)
Select @varTitlID = tblEmployees.[TitleID],@varDeptID = tblEmployees.[DeptID]...
FROM Employees.dbo.tblEmployees
INNER JOIN Employees.dbo.tblAddresses
ON Employees.dbo.tblEmployees.EmployeeID =
Employees.dbo.tblAddresses.EmployeeID
WHERE Employees.dbo.tblEmployees.EmployeeID = @varEmployeeID
AND Employees.dbo.tblEmployees.Deleted = 0
The 'order by' is not needed, since the query must have only one result row to work at all.
|
|
|
|
|
Thank you. I'm going to give it a try.
Quecumber256
|
|
|
|
|
i have drpdownlist,textbox and a gridview and i have one table in my database named table1. It contains 2 columns the Lastname and FirstName, I will just add the items on my dropdown, The DropDOwn items are "Last name" and the value is lastName and the other one is the "First Name" and the value is firstName, If i select the "Last Name" on the dropdown the firstName will be entered in textbox or else the gridview will display nothing. If i select "Last Name" and for example i enter my last Name data will display.
Thanks
Bernie
|
|
|
|
|
hi
i'm trying to change the Null, Not Null constraint on a column.
do u know the query to do this??
also tell me, can the primary key of a table be changed??
i have a table in the DB with a primary key defined. i want to only remove the primary key constraint of the column and assign the primary key constraint to another column in the table.
How can i do this. plz do tell the query.
Saira
|
|
|
|
|
ALTER TABLE supplier
MODIFY column1 varchar2(100) not null
U can do both with the help of this commnad plz refer this command.
Parwej Back...............DON of Developer.......
Parwej Ahamad
g_parwez@rediffmail.com
|
|
|
|
|
|
|
You haven't given enough information for people to help. What is the database that you are using? Are you inserting using Stored Procedures or direct SQL? Which database access method are you using, e.g. Sql... for Sql Server or OleDb... for OLE DB? What code are you actually using?
BTW - rather than use a 0 for new record, try explicitly setting values using column names. In other words, try doing this instead:
INSERT INTO Table1(Field2, Field3) VALUES (@Value1, @Value2)
If Field1 (not included in the insert statement) is an autoincrement column, then it will be automatically incremented.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi All,
How to display the last Access Date and Time of the record from the table, Every time (or) every access of that record.
I need the query for that using sql server 2005.
Logu
|
|
|
|
|
If you want the last date that a record was inserted or updated then you would normally use triggers.
|
|
|
|
|
Hi All,
I have a table in which i have 100s of records. I need a query to display a single record per day ie., for the 100 days i have to display 100 days and then on the 101th day the 1st record should come.
So please help me in getting the query and i m using SQL server 2005.
Logu
|
|
|
|
|
The following SQL assumes:- You want all of the records in the past 100 days.
- You want the first record from each day older than 100 days.
- That there is an identity column named "AuditId".
- There is a datestamp column named "AuditDate".
select * from MyAudit
left outer join (
select Convert(AuditDate,112) AuditDate, Min(AuditId) MinAuditId
from MyAudit
where AuditDate < (GetDate() - 100)
group by Convert(AuditDate,112)
) A
on A.MinAuditId = MyAudit.AuditId
where (AuditDate >= (GetDate()-100) or A.MinAudit is null)
order by MyAudit.AuditId I use an inline view to find the first ID for each date older than 100 days.
Hope this helps.
Andy
|
|
|
|
|
Hi
i want to know how to do a "select distict" on multiple field.
for example suppuse we have a table with field FirstName and LastName.
and we want to select all distinct the people in this table with a Query with distinct FName and LName.
I know i can use other elegant ways (such as relationship) to do so, but i need to know that query.
thanks
|
|
|
|
|
Distinct keyword is associated with one row, not with one column. So you can't get distinct rows on the base of two columns.
Sushant Duggal.
|
|
|
|
|
Hello,
Did u try group by clause? If not then u may write in the following way to get the distinct value.
SELECT FName,LName
FROM YourTableName
group by FName,LName
Mehedi Hasan
|
|
|
|
|
I was talking about the distinct keyword.
Perhaps, thanks for the query.
Regards,
Sushant Duggal.
|
|
|
|
|
Does data repeats multiple times in fields FName and LName?
If so then you can use distinct otherwise try to group, if this doesn't works explain more your question.
|
|
|
|
|
Hi friends,
I am getting a strange error. I am working on .NET framework 1.1.
When I work on a desktop application(C#.NET) and try to connect to SQL Server 2000 through my application, everything works fine and the connection is successfully established.
But when I use the same database in web project(ASP.NET/C#), I am unable to connect to the database.
I get an error:
"SQL Server does not exists or access denied".
I have carefully checked the connection string and everything in the code if perfect.
Please help me out.
Regards,
Sushant Duggal.
|
|
|
|
|
Paste your connection string here
There may be some errors
Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
sylvester_g_m@yahoo.com
|
|
|
|
|
Hi
I am using the same connection string for two projects.
One is desktop application and other is web application.
Desktop application is working fine, database connectivity is fine whereas I am unable to connect in web application.
Still I am writing the connection string for ur reference.
user id=sa;password=XXX;data source=XXX;initial catalog=XXX
Sushant Duggal.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
