|
Maybe, but they don't do that a SQL forum. They would do that in a VB forum.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Colin Angus Mackay wrote: Maybe, but they don't do that a SQL forum. They would do that in a VB forum
You are right... precisely the reason I was only replying to Pete and not the OP.
|
|
|
|
|
may be following links helpful to u.
http://visualbasic.ittoolbox.com/documents/popular-q-and-a/viewing-a-crystal-report-using-vb-3386
http://www.freevbcode.com/ShowCode.asp?ID=4832
|
|
|
|
|
hello
I have two related table
table1
cId
cDesc
table2
Id
Name
phone
cId
These two tables are related
where cId in table 1 is primary key and cId in table2 is foreign key
If I want to delete a record in table 1 which is related to table 2
Which is faster and more accurate
should I write my stored procedure like this
if exists(select * from table2 where cID = @CID)
delete from table1 where cID = @CID
else
return 0
------------------------------------------
or
------------------------------------------
delete from table1 where cID = @CID
if @@error<>0
return 0
|
|
|
|
|
If exists is a pretty slow approach. If you really wanted to do a select statement first you should do something like this:
if (select count(cID) from table2 where cID = @CID) > 0
begin
delete from table1 where cID = @CID
end
You probably don't want to do blind deletes since you may incur some locks that you don't really need if the row isn't there.
Hope that helps.
Ben
|
|
|
|
|
I don't know where you got that idea. EXISTS internally does a TOP 1, stopping as soon as it finds a row. Your approach will require SQL Server to continue after finding a row, to count all the matching rows (this assumes cID does not have a UNIQUE index on it). EXISTS is generally the better solution.
SQL Server only exclusively locks rows that will be changed, initially. The exception is if you're using the SERIALIZABLE transaction isolation level, in which case key-range locks will be taken to prevent new rows being inserted. If a large number of rows are affected, and SQL Server is running low on memory, it may decide to escalate to page locks or even table locks, because the lock object itself requires a certain amount of memory (one page lock obviously takes much less memory than 50 row locks). A DELETE operation that doesn't find any matching rows won't take any locks in READ COMMITTED or REPEATABLE READ isolation levels.
|
|
|
|
|
I guess I was thinking of NOT EXISTS being the performance hog. I was assuming the cID has a Unique index on it.
Ben
|
|
|
|
|
Hello,
There is no SQL r any format of codes.
Because if u created Primary Key in Master table and Forgin Key in Child table is enough. If you using SQL 2000 r 2005, the primary n forgin key relation itself have that fuction of deleteing. Try it
It is better to use Dotnet's Server Explorer which give all the facilities..
Senthil S
Software Engineer
|
|
|
|
|
hi,
Is it possible to connect oracle without 'tnsname.ora' in VB.NET
I want to use tnsname.ora entry in connection string Eg.
CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=mysrv)(PORT=7001))" & _
"(CONNECT_DATA=(SERVICE_NAME=MYDB))); uid=read;pwd=read;"
|
|
|
|
|
Nope. The Oracle classes rely on the underlying Oracle client in order to work.
|
|
|
|
|
Hi i'm using a stored procedure ..
Which contain the function dbo.split(....)
When i try to use this stored procedure in the local it works fine.
when i try to use that stored procedure i get the error invalid object dbo.split
can some one please help
Thank you
Hepsi
|
|
|
|
|
This sounds to me like you've got a stored function on your system called Split, and you haven't deployed it to your live server. You need to copy it over before you can use it.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thank you
i did that its working fyn
hepsi
|
|
|
|
|
What is the difference between typed and untyped datasets.
seema
|
|
|
|
|
http://www.interviewcorner.com/Answer/Answers.aspx?QuestionId=1136&MajorCategoryId=1&MinorCategoryId=2
http://www.velocityreviews.com/forums/t94794-difference-between-typed-dataset-and-untyped-dataset.html
|
|
|
|
|
Hi i have like this one table...,
id value1 edate
100 2000 5/21/2006
100 3000 7/14/2006
100 1600 12/12/2006
100 4000 5/17/2007
in this suppose i want to see the value in the month of 11 and year 2006
i will select like this:
select top 1 from tablename where empid=100 and (datepart(yyyy,edate) < 2006 or (datepart(yyyy,edate) = 2006 and datepart(mm,edate) <= 11)) order by edate desc
so it will give the value:
100 3000 7/14/2006
same like this suppose i want to update the month value...
how to do that...,
Plz help me..,
Regards,
Magi
|
|
|
|
|
update tblname set bassalary=10001
from (Select top 1 * from tblname where empid=100
and (datepart(yyyy,edate) < 2006 or (datepart(yyyy,edate) = 2006
and datepart(mm,edate) <= 11)) order by edate desc) as th
where tblname.edate=th.edate
it will work
Magesh
|
|
|
|
|
It would be better if you replace
(datepart(yyyy,edate) < 2006 or (datepart(yyyy,edate) = 2006)
with
(datepart(yyyy,edate) <= 2006)
I Love SQL
|
|
|
|
|
Hello,
I have two tables as below
Table1 (where id is Pk key)
Id Name
1 A
2 B
Table2
Id Reasons
1 X
2 Y
1 Z
I need output like as below
ID NAME REASON1 REASON2
1 A X Z
2 B Y Null
Amit
|
|
|
|
|
If you're using SQL Server 2005, take a look at the Pivot command.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
see the following example : it may help u (run it in sql query analyser)
CREATE TABLE #T1(id int,Name varchar(10))
insert into #T1 (id,Name) values (1,'Haris')
insert into #T1 (id,Name) values (2,'Arshad')
--select * from #T1
CREATE TABLE #T2(id int,Name varchar(5))
insert into #T2 (id,Name) values (1,'X')
insert into #T2 (id,Name) values (2,'Y')
insert into #T2 (id,Name) values (1,'Z')
insert into #T2 (id,Name) values (1,'X')
SELECT Id,Emp,X,Y,Z
FROM
(
SELECT #T1.Id,#T1.Name AS Emp,#T2.Name
FROM #T1
INNER JOIN #T2 ON #T2.Id = #T1.Id
) s
PIVOT
(
COUNT(Name)
FOR Name IN (X,Y,Z)
) p
drop table #T1
drop table #T2
fasih_is_my_signature
|
|
|
|
|
Thanks but i don't want to count. My rwq is little different and i would really appreciate if you could help me in this..
I have one table
CREATE TABLE #T1(id int,Name varchar(10))
insert into #T1 (id,Name) values (1,'Haris')
insert into #T1 (id,Name) values (2,'Arshad')
CREATE TABLE #T2(id int,Reason varchar(5))
insert into #T2 (id,Reason) values (1,'X')
insert into #T2 (id,Reason) values (2,'Y')
insert into #T2 (id,Reason) values (1,'Z')
insert into #T2 (id,Reason) values (1,'X')
Now i need output like
Id Name Reason1 Reason2 Reason3 ......ReasonN
1 Haris X Z X
2 Arshad Y NULL NULL
Thanks
Amit
|
|
|
|
|
I have never done a database search but am trying to search through a microsoft Access database with multiple tables. I am using visual studio 2005, and my project is a windows form based c++. Basically the user will enter what to search for and the program will go to the database and find all relevant entries. I just have no idea how to interact with the database. Any help will be appreciated. Thank you
|
|
|
|
|
You may want to research the LIKE keyword. If you have a table, lets call it Customers and it has a field called CustomerName, you could search for a customer who's name begins with the letter A by doing this SQL Select:
SELECT CustomerName FROM Customers WHERE CustomerName LIKE 'A%'
If you wanted all customers that have an A anywhere in their name:
SELECT CustomerName FROM Customers WHERE CustomerName LIKE '%A%'
Note that this is case sensitive, you would have to search for a and A if your data is not stored in all upper case...Like this:
SELECT CustomerName FROM Customers WHERE CustomerName LIKE '%A%' OR CustomerName LIKE '%a%'
Hope this helps.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
