|
izzit possible to do this?
(SELECT NAME FROM STAFF)//first statement
-
(SELECT NAME FROM HUMAN WHERE NAME LIKE 'A')//2nd statement
of coz this is not the actual statment but my staement is somewhat alike
i can generate a table of the infomation i dont need :
ABC
AAA
and i can also generate all the infomation
ABC
AAA
BBB
however what i need is :
BBB
|
|
|
|
|
the query to be used is ....
SELECT name FROM staff WHERE name NOT IN (SELECT name FROM hanuman WHERE name LIKE 'A' )
Regards
KP
|
|
|
|
|
in my previous question i asked how to get date
so now i was wondering how can i compare date?
SELECT NAME, DATE_TIME
FROM STUFF
WHERE ()<-- not sure
do i use DATE_TIME > get_date()?
or something else please help
|
|
|
|
|
Hi,
you can use the following WHERE clause,
MyDate = CONVERT(DATETIME, '2007-03-27 00:00:00', 102)
or
MyDate = getdate()
Mubashir
Every job is a self portrait of the person who did it.
|
|
|
|
|
Data type date is not available in SQL server
however DateTime is available
so if table Stuff is updated with
INSERT INTO stuff VALUES('Mr. X', '01-Jan-2007')
INSERT INTO stuff VALUES('Mr. X', getdate())
and while fetching
SELECT * FROM staff WHERE date_time = getdate()
returns no rows i.e. both rows would not be returned. because getdate() returns date & time
In this case need to take only date protion and compare or compare day, month, year separately.
Regards
KP
|
|
|
|
|
Krish - KP wrote: SELECT * FROM staff WHERE date_time = getdate()
returns no rows i.e. both rows would not be returned. because getdate() returns date & time
It returns no rows because all dates in the database occur in the past, therefore there is no match. It has nothing to do with the fact that GETDATE() returns the date and time.
If you only stored the date portion then the time portion will always be midnight. You can use that information to your benefit when writing calculation.
|
|
|
|
|
Hello
VS 2005
I have been looking at transactions to rollback if 1 of my insert or updates does not complete successfully.
Code example:
<br />
dim ts as new transactionScope()<br />
<br />
'Insert a record into table A<br />
'Insert a record into table B <br />
'update table C<br />
ts.Complete()<br />
The above does not work too well with my code. I am wondering is there a better way to manage transactions.
Many thanks for any advice,
Steve
|
|
|
|
|
hi my questions is that in the case where my datasource have no result (becasue of the parameter there are no result) i want to route it to another sql statment is it possible??
my code below:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:asdConnectionString1 %>"<br />
<br />
SelectCommand="SELECT DISTINCT AuctionSessionID, LocationID, Date_Time, AuctionDescription, Comments FROM AuctionSession WHERE (AuctionSessionID <> (SELECT AuctionSession FROM Registration WHERE (NRIC = @NRIC)))"<br />
|
|
|
|
|
neodeaths wrote: in the case where my datasource have no result (becasue of the parameter there are no result) i want to route it to another sql statment is it possible??
Yes, it is possible.
You might want to write it as a stored procedure - that way you only have one round trip to the database server and you always get a result set back (assuming, of course, that the fallback produced a result)
|
|
|
|
|
could you teach me how to do it without using the stored procedure way?
as i am asssumeing i have to put something inside my <asp:sqldatasource> tag
|
|
|
|
|
Why are you so reluctant to use stored procedures? It is a better way.
|
|
|
|
|
Hi,
I have a store procedure added to the database. I would like to try passing a value to the stored proc.
The store proc was as follow:
INSERT INTO DBName(Name, FName, LName, Salary, Active)
SELECT 'Mimic', FName, LName, SUM(Salary), 'A'
FROM DBInfo
WHERE TransactionDate=@TransDate
I would like to pass the 'Mimic' static data from the windows control and value was actually passed from the program itself to the stored proc. The store proc was actually used to insert into DBName table by specifying the Name with 'Mimic' value. My question was how do i implement passing the value from windows form textbox control to the stored proc and the data was inserted the *Name* static value into the database. Anybody can help regards on that?
Is that I have to write stored proc as following:
INSERT INTO DBName(Name, FName, LName, Salary, Active)
SELECT @Name, FName, LName, SUM(Salary), @Active
FROM DBInfo
WHERE TransactionDate=@TransDate
But the problem is that when i pass the data value of @Name & @Active, the program won't allow me to do like that. Is there any mwthods i can implement such of this functionality?
Steven
|
|
|
|
|
coolzzzz wrote: But the problem is that when i pass the data value of @Name & @Active, the program won't allow me to do like that.
Well, what did you try to do? You have not shown any .NET code, so it is difficult to see what is going wrong.
|
|
|
|
|
hi
how can rename a table in sql server 2000 (with tsql in query analyzer)?
|
|
|
|
|
from : http://p2p.wrox.com/topic.asp?TOPIC_ID=24417
Hi,
Yes, you can use sp_rename for renaming a table or column in SQL Server. The only difference is that if you want to rename a column witbin a table you need to provide proper namespace.
for e.g.
for renaming a table
EXEC sp_rename 'Old_TableName', 'New_TableName'
for renaming a column
EXEC sp_rename 'TableName.[Old_ColumnName]', 'TableName.[New_ColumnName]', 'COLUMN'
Hope this helps,
Lalit,
Life Means More...
|
|
|
|
|
Hi all,
I am not fully aware of DTS, I have created a DTS package its executing from the .Net code also, but I have been struck up with 2-things. They are
1. Here I am using a .csv file as source file, this file has to be given at runtime as user selects it.
2. In the destination table, the DTS has to check whether the same recipt number exitsting, if it exists it should fail running of the DTS. And error message must be passed to a table.
Can anybody please advice me how to do it. I would be thankfull for them pls.
Regards,
Aleem.
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
Hi all,
I have created a DTS which is running fine, now I would like to give its source file at runtime, how can I do it. Pls. help me its urgent. Thank you.
Regards,
Aleem.
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
Is there any restriction on the name of the stored procedure. I mean on the length of the sp.What is the max length that a stored procedure can have.
Mohinder Singh
|
|
|
|
|
From SQL Server Books On Line:
the NAME of a stored procedure cannot exceed 128 characters.
The size of a Transact SQL Stored procedure cannot exceed 128 MB.
|
|
|
|
|
what is different between SqlDataAdapter and SqlDataReader?.these 2 r different from dataadapter and datareader.
|
|
|
|
|
No these two are same.Dataadapter is used when u want to do insert or update or delete. And if u want the records for reading purpose then it is better to use datareader. Datareader is fast in this case. Datareader directly interacts with the Database. Dataadapter works with the dataset. And dataadapter interacts with the database. and fills the records in the dataset.Dataadapter is used only when you use dataset.And dataaset uses disconnected architecture and datareader does not.This reason makes dataset more useful.
Mohinder Singh
|
|
|
|
|
SqlDataAdapter is a version of the DataAdapter that is specific for use with Sql Server. For other databases an OleDbDataAdapter or OdbcDataAtapter would be appropriate. Both of these classes inherit from the DataAdapter class, which providesthe abstract model for both. Similarly, SqlDataReader is an implementation of (inherits from) DataReader, one which is specifically for use with SQL Server databases.
DataAdatpters are disigned to serve as the bridge between a database and the in memory representastion classes DataSet and DataTable. The provide mechanisms for populating Datasets with the data, metadata and relationships form a database, and allow one to work with this in memory data while disconnected from the database. Mechanisms fro reconnecting and updating the database are also provided.
DataReaders are designed for rapid reading of data from a database. The data is provided as a set of records which must be read in order and can only be read once without closing and restarting the datareader.
All of this information (in much more detail) is available from MSDN[^]
|
|
|
|
|
Hi,
ADO.Net implements disconnected architecture with sqlDataAdapter and Dataset,SqlDataAdapter is disconnected from datasorce it establishes connection when required once data processing is finished it disconnect from DB and data is dumped into Dataset.
SqlDataAdappter is used for all DML command by defaults it is selectcommand mode.
SqlDataReader is connection oriented it reads the records on at a time mean while it reads all records. we can use DataReader only for SelectCommand. we cann't instantiate DataReader with the help of executeReader we can
SqldataReader used mainly for populate data to the controls, when we need to gothough all the records in data
I hope this will clear u r doubt for some extend
"Winning is not our dream,It our habit"
|
|
|
|
|
This is a question about the best way to construct a query.
Each person is associated with a team, and each team is associated with an office. This is represented in three tables:
a table of users (tblUsers) that have an id (userID) and a team (userTeamID)
a table of teams (tblTeams) that have an id (teamID) and an office (teamOfficeID)
a table of offices (tblOffices) that have an id (officeID).
How can I (with a single query) retrieve a list of all of the people (in tblUsers) who are in a particular office (officeID)?
Initially I tried this (in this example, I'm finding all the people in the office with officeID of XYZ):
SELECT userID
FROM tblUsers,tblTeams,tblOffices
WHERE
officeID='XYZ'
teamOfficeID=officeID
userTeamID=teamID
But that doesn't seem to be working for me.
Any thoughts?
---Greg
|
|
|
|
|
Isn't it :
SELECT userID FROM tblUsers,tblTeams,tblOffices WHERE officeID='XYZ' AND teamOfficeID=officeID AND userTeamID=teamID
?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
