|
Here is the code:
cn = null;
cn = new SqlConnection(cnStr);
SqlCommand cmdSet = new SqlCommand("SELECT * FROM MonthSet", cn);
SqlDataAdapter daSet = new SqlDataAdapter(cmdSet);
SqlCommand cmdHeader = new SqlCommand("SELECT * FROM MonthSetHeader", cn);
SqlDataAdapter daHeader = new SqlDataAdapter(cmdHeader);
SqlTransaction trnMonthData = null;
cn.Open();
trnMonthData = cn.BeginTransaction();
cmdSet.Transaction = trnMonthData;
daSet.UpdateCommand = cmdSet;
cmdHeader.Transaction = trnMonthData;
daHeader.UpdateCommand = cmdHeader;
try
{
daSet.Update(monthSet, "MonthSet");
daHeader.Update(header, "MonthSetHeader");
trnMonthData.Commit();
}
catch(Exception ex)
{
trnMonthData.Rollback();
return ex.Message;
}
finally
{
cn.Close();
}
return "";
I have played with the code to check the following"
1) Both datasets have changes
2) No exception returned
3) If I do only 1 update in the transaction, I can return the number of rows affected - correct.
4) This applies to both datasets
5) Rollback is not called
However, no changes are made to the database.
If I do the updates individually, using the same datasets, without using a transaction, the changes are made to the database.
Nigel
|
|
|
|
|
Any Oracle expert in here can guide us through the InstantClient steps in details?? I tried everything possible and I just gave up 
ps. Please, do not link me to a website, I need your own experiences in this matter!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
hi
i'm using vb.net 2008 and database sql server 2005 ,, my connection string is as :
Data Source=ServerName; Initial Catalog=DataBaseName ;User ID=sa;Password=1111"
it's ok and works as good ,,,
but when i test exe project on client pc ,, an error with the connectoin string ,
this problem has happend with me before with sql server 2000 and i solved by add
Network=DBMSSOCN;
to the connection string ,
but with sql server 2005 it still doesn't work ,,
what's is the correct connection string to allow the client coneects the server
thanks
jooooo
|
|
|
|
|
kindman_nb wrote: an error with the connectoin string ,
What was the error ? Take a look at Connection strings[^]
|
|
|
|
|
I am using MSSQL and I have a value named NUG(4'') in a field named Sz in the table tbl_Sz.
But when i use the query -> Select count(*) from tbl_Sz where Sz='NUG(4'')', it gives the count zero, which should be one in this case.
Is the count is zero due to NUG(4'') has two Apostrophes ? Because I think sql ignores the string after the Apostrophe or something like that?
If it is so then how to solve this problem ?
|
|
|
|
|
Hi.
Single quotes must be escaped (with single quotes) in queries:
Select count(*) from tbl_Sz where Sz='NUG(4'''')'
Kjetil
|
|
|
|
|
Hi can any body help me or give me some tips to designing database for multilingual websites.
thanks
|
|
|
|
|
Hi guys,
I am trying to figure out how to solve this simple select statement, but somehow I am unable to get it all together. I simplified my problem which is like this. I have a unlimited number of persons and teams. Each person can be a member of one or more teams. I would like to figure out which person is a member of all teams. To make it easier for you to follow here is a SQL snippet to create sample tables and data
create table #person
(
PersonId varchar(2),
Firstname nvarchar(50)
)
create table #member
(
PersonId varchar(2),
TeamId varchar(2)
)
create table #team
(
TeamId varchar(2),
TeamName nvarchar(50)
)
insert into #person values ('P1', 'Wayne');
insert into #person values ('P2', 'Rick');
insert into #person values ('P3', 'Bob');
insert into #team values ('T1', 'Team 1');
insert into #team values ('T2', 'Team 2');
insert into #team values ('T3', 'Team 3');
insert into #member values ('P1', 'T1');
insert into #member values ('P2', 'T1');
insert into #member values ('P2', 'T2');
insert into #member values ('P2', 'T3');
insert into #member values ('P3', 'T3');
drop table #member
drop table #person
drop table #team
The only person in all teams is Rick.
Thanks for any kind of help on this matter.
Cheers!
Byteloser
P.S: By the way I am using SQL Server 2005
|
|
|
|
|
SELECT PersonId, Firstname
FROM #person
WHERE
PersonId IN (
SELECT PersonId
FROM #member
GROUP BY PersonId
HAVING COUNT(*) = (SELECT COUNT(*) FROM #team)
)
|
|
|
|
|
Hi,
Thanks for the reply. I came up with the same idea last night. Hope this works in my real work szenario, because I have a high data volume.
Cheers!
Stephan
|
|
|
|
|
Hi,
In my table, my date is look like '2007-10-01 08:01:14.543'. But I need '2007-10-01' only. How to use convert function to get this format? Pls help. tnx in adv.
Balasubramanian K.
|
|
|
|
|
You have here some examples:
PRINT GETDATE()
--|OUTPUT:
--|Jul 4 2008 9:45AM
PRINT CONVERT(NVARCHAR, GETDATE(), 121)
--|OUTPUT:
--|2008-07-04 09:45:45.477
PRINT CONVERT(NVARCHAR(10), GETDATE(), 121)
--|OUTPUT:
--|2008-07-04
PRINT CONVERT(NVARCHAR, GETDATE(), 112)
--|OUTPUT:
--|20080704
You can find mode details here: http://msdn.microsoft.com/en-us/library/aa226054.aspx[^]
|
|
|
|
|
Hi,
I have 3 tables : Products, Orders and OrderDetails.
The Products table has a ProductCode field
The Orders table has an AccountNumber, DeliveryDate field and an ID field
The OrderDetails has a foreign key that points to the ID field in Orders
The OrderDetails has a foreign key that points to the ProductCode in Products
I need to get a list of all ProductCodes
For each ProductCode I need to get the most recent Orders.DeliveryDate and OrderDetails.Quantity where Order.AccountNumber equals a given AccountNumber
For example assume each table contains the following records:
Products Table
ProductCode
A1
B2
C3
D4
E5
OrderDetails Table
ID ProductCode Quantity
1 A1 1
1 B2 2
1 D4 4
2 C3 1
2 E5 2
Orders Table
ID AccountNumber DeliveryDate
1 1234 03/07/2008
2 6789 26/06/2008
Assume I want information for AccountNumber 1234. The results I need are as follows:
ProductCode DeliveryDate Quantity
A1 03/07/2008 1
B2 03/07/2008 2
C3 NULL NULL
D4 03/07/2008 4
E5 NULL NULL
Assume I want information for AccountNumber 6789. The results I need are as follows:
ProductCode DeliveryDate Quantity
A1 NULL NULL
B2 NULL NULL
C3 26/06/2008 1
D4 NULL NULL
E5 26/06/2008 2
I know that I could get a datatable with a list of products and then write some code to loop through it and lookup Orders and OrderDetails to get the other two fields but I'd rather get all the information that I need in one call.
Any ideas how to go about writing the SQL Select statement?
Thanks,
dlarkin77
|
|
|
|
|
dlarkin77 wrote: go about writing the SQL Select statement?
Have you tried one? Try a query and if it doesn't work, ask for help.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
First result for accountnumber 1234
SELECT OD.ProductCode, O.DeliveryDate, CASE WHEN O.DeliveryDate IS NULL THEN NULL ELSE OD.Quantity END AS quantity<br />
FROM dbo.Products P INNER JOIN<br />
dbo.OrderDetails OD ON P.ProductCode = OD.ProductCode LEFT OUTER JOIN<br />
dbo.Orders O ON OD.ID = O.ID AND O.AccountNumber = 1234
Second result for accountnumber 6789
<br />
SELECT OD.ProductCode, O.DeliveryDate, CASE WHEN O.DeliveryDate IS NULL THEN NULL ELSE OD.Quantity END AS quantity<br />
FROM dbo.Products P INNER JOIN<br />
dbo.OrderDetails OD ON P.ProductCode = OD.ProductCode LEFT OUTER JOIN<br />
dbo.Orders O ON OD.ID = O.ID AND O.AccountNumber = 6789
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi to all,
Hope I'm posting this in the right discussion group.
I wonder if anyone can tell me if VS 2008 brought in support for rich text formatting or HTML in reports of Reporting Services?
I did see some reports on the possibility but would like to know for sure.
Hope you can help.
Kind regards
Andries
|
|
|
|
|
hi all,
we know getdate() function returns current date. I want get the current time.
Can any one help me...
Thanks in Advance
Senthil S
Senthil.S
Software Engineer
|
|
|
|
|
SELECT RIGHT(CONVERT(varchar(23), GETDATE(), 8),11)
|
|
|
|
|
select convert(varchar(15),getdate(),108)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
i have a column its values must started with 1 and incrementing by 1
this column repeatly looses his rows
so i can not use identity here
so i tried to raise a trigger to do the job
but i have some probelems with the following statments
create trigger dbo.insertNum
on testIcreTrigger --table name
for insert
AS
begin
declare @insVal int --value to insert
declare @MaxVal int
select @MaxVal= Max(Num) from hitTheatre
select @insVal=@MaxVal+1
insert into testIcreTrigger (Num)
values (@insVal)
End
Go
|
|
|
|
|
Have you tried
SET @MaxVal= (select Max(Num) from hitTheatre)
SET @insVal= (@MaxVal+1)
Just guessing..
|
|
|
|
|
Another solution is to run DBCC CHECKIDENT('[table name]', RESEED, 0) when you flush the table and just use an identity.
|
|
|
|
|
You should replace *for insert* with *INSTEAD OF INSERT*.
When FOR is the only keyword specified, AFTER is used by default.
CREATE TRIGGER dbo.insertNum
ON testIcreTrigger
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO testIcreTrigger (Num)
SELECT MAX(Num)+1 FROM hitTheatre
END
|
|
|
|
|
Quick question... i'm feeling a bit thick today...
Does...
Begin Tran
stored procedure 1
stored procedure 2
stored procedure 3
rollback
...work? ie are the contents of stored procedures also rolled back?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
