|
Hi friends. This is selvaraj.K. My requirement is When i updated data in Master table(Parent) and at the same time i inserted data in detail table(Child table).Some time the data updated in Master table but not inserted in detail table,that time we suffer a lot(So i need when the data not inserted in detail table, the master table data will be deleted automatically(i mean rollback the task).I hope u have catch my point.I have given below my stored procedure.Please find out and give assistance to me As soon as possible.
My stored procedure is
----------------------
CREATE PROCEDURE spCallerUpdation (@strHdrID VARCHAR(30),@strClaimID VARCHAR(50),@strUser VARCHAR(30),@strCode varchar(30),@strComments varchar(2000),@FollowupDate varchar(30))
AS
BEGIN
--IF @strDtlID = ''
BEGIN ----- Generate DETAIL ID
--PRINT @strDtlID
DECLARE @strDtlID VARCHAR(30)
DECLARE @maxid VARCHAR(30)
SET @maxid = CONVERT(BIGINT,(SELECT MAX(SUBSTRING(MCD_DtlID, 4 ,LEN(MCD_DtlID)-3)) FROM MARMS_Claim_DTL WHERE MCD_DtlID LIKE '%C%' AND SUBSTRING(MCD_DtlID,2,2)=RIGHT(DATEPART(YEAR,GETDATE()),2)))
--PRINT @maxid
IF @maxid IS NULL
SET @strDtlID = 'C' + RIGHT(DATEPART(YEAR,GETDATE()),2) + '0000000000001'
ELSE
BEGIN
SET @maxID = @maxID + 1
SET @strDtlID = 'C' + RIGHT(DATEPART(YEAR,GETDATE()),2) + STUFF('0000000000000',13-LEN(@maxid)+1,LEN(@maxid),@maxid)
END
DECLARE @SeqNo INT
SELECT @SeqNo = MAX(MCD_SeqNo) FROM MARMS_Claim_Dtl WHERE MCD_ClaimID = @strClaimID
IF @SeqNo IS NULL
BEGIN
SET @SeqNo = 1
END
ELSE
BEGIN
SET @SeqNo = @SeqNo + 1
END
IF @FollowupDate = ''
BEGIN
SET @FollowupDate = NULL
END
UPDATE MARMS_Claim_Hdr SET MCH_User = @strUser, MCH_LastUpdateDate = GETDATE(), MCH_SeqID = @SeqNo,
MCH_Comments = @strComments,MCH_StatusCodeID = @strCode,MCH_FollowupDate=@FollowupDate
WHERE MCH_ClaimID = @strClaimID
INSERT INTO MARMS_Claim_Dtl(MCD_DtlID, MCD_MCH_HdrID, MCD_ClaimID, MCD_Date,
MCD_User, MCD_SeqNo, MCD_Status_Code, MCD_Comments, MCD_Caller,MCD_FUDATE)
VALUES(@strDtlID, @strHdrID, @strClaimID, GETDATE(), @strUser, @SeqNo, @strCode, @strComments, @strUser,@FollowupDate)
END
END
|
|
|
|
|
Put a BEGIN TRANSACTION at the top of the procedure. Then if you detect an error you ROLLBACK TRANSACTION at the point the error occurs and exit the stored procedure. If you don't detect an error then COMMIT TRANSACTION at the end.
e.g.
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
|
|
|
|
|
Thanks Colin
By selvaraj.K
|
|
|
|
|
Hi guys:
What is the best book for learning and practice sql queries (advances queries) ?
Thanks in advance
|
|
|
|
|
SQL For Smarties[^]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Excellent book.
|
|
|
|
|
I am doing some research on data-warehousing and I was wondering if anybody has a good resource on the subject. I tried finding information using google, but that doesn't give me much that I can use.
WM.
What about weapons of mass-construction?
|
|
|
|
|
I have an awkward question, and i was wondering if any of you have ever seen behaviour like this before:
We have an ASP.net application that is currently in testing and has been for the past several weeks. During the last week, first one of the test sites and then another, started to throw SqlDateTime exceptions, citing a SqlDateTime overflow - The date must be between... etc...
The sql statement that seems to be causing this exception has not changed and is a fairly simple
SELECT X
FROM TABLE
WHERE TABLE_DATE = @MaxDate
where @MaxDate is a constant parameter that we set (this has also not changed, date is somewhat arbitrarily set to 31 Dec 2099 (in that format, so it's shouldn't be in any way ambiguous). The constant is a .net datetime variable.
Unfortunately, this date time problem seems to be occurring throughout the application. What i can't really understand is why this has suddenly changed, first on one web server and then on another.
We checked the localisation settings on the web servers and the database server and found that they were different - however making them the same hasn't fixed the problem and doesn't explain why they were working fine for the past few weeks...
Any help on this would be very much appreciated, i'm stumped and when i get in on Monday, our testers are going to be at a standstill... Unhappy people in our office...
|
|
|
|
|
how can i store picture in binary field and select it as binary to
picturebox by c#
MD_NADA
|
|
|
|
|
Store it in an image column (so ensure the column in the database your are using is of type image, or varbinary(max) in SQL Server 2005) and then you can persist it to the database you would any other column type (I am, of course, assuming that you do the sensible and secure thing and use parameters to pass changable data into your queries). The data must exist in a byte array first.
When you retrieve it from the database you'll get a byte array back. What you do with that byte array is off topic for this forum.
-- modified at 14:07 Saturday 19th August, 2006
|
|
|
|
|
FYI--The image data type is being deprecated in future versions of SQL Server.
When was the last time you poured some wine for you and your sweetie and went out on the front porch to watch the geometry frolic on the lake?--Rebecca M. Riordan, Designing Effective Database Systems
|
|
|
|
|
We know it is quite easy to fetch data from sql server to datatable with dataadaptor of ado.net. However, i want to store my query result with over 2000 records to sql server now. Is there any good approach that could store this datatable immediately to sql server?
Any idea is appreciated! Thank u in advance! ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
|
|
|
|
|
sorry to add that the format of the query result is not determined at first. waiting for ur reply!
|
|
|
|
|
I am trying to build a simple script but by Newbie scripting is showing...
The Database creates Ok but the tables are created in the master Database not in the Newly created Db as it should...
Do I need to switch to the new Db after it is created???
I fried to append the New Db name to the table but this gave me an error??? (ShowroomRegDB.ISSUESTb)
Thoughts...
CREATE DATABASE ShowroomRegDB
drop table ISSUESTb;
drop table eMailTb;
drop table ConfigTb;
drop table SuggestionTb;
CREATE TABLE ISSUESTb
(
Issueid INT identity(1,1) not null,
IssuedDate VARCHAR(50),
);
CREATE TABLE eMailTb
(
eMailid INT identity(1,1) not null,
Lastname VARCHAR(50),
Firstname VARCHAR(50),
eMailAddress VARCHAR(100),
);
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
japel wrote: Do I need to switch to the new Db after it is created???
Yes.
After the create, add : "Use database ShowroomRegDB"
|
|
|
|
|
I'm new to ADO.NET so if this question is odd, forgive me. Anyways, here the problem. I have a DataSet, that I populated with a SqlDataAdapter that I made, then I binded the DataSet to a BindingSource and hooked that up to some controls on my Form. Now if I want to add a new record to the DataSet table (there's only one in it) do I do it through the BindingSource or through the DataSet? And once the new row is added will the BindingSource be able to see it right away (so the Form can display the new record) or do I have to something to update the BindingSource? If a quick example could be provided as well that would be awesome.
Thanks for any and all help.
- Aaron
|
|
|
|
|
The AddNew method of the bindingsource control will achieve what you are looking for. You can then enter the data for the new record in the bound controls. You will need a Save button and a Cancel button. The save button should validate the data entered by the user, call the EndEdit method of the binding source, and finally call the Update method of the tableadapter to write the new record to the datasource. The Cancel button should call the CancelEdit method of the bindingsource to discard any changes made. Whether or not you need to do anything else depends on a few things in your program structure. For example, whether you are updating the dataset from the same thread as your UI. If you do not see the results of the changes immediately, you may need to call the ResetBindings method of the bindingsource.
Hope this helps.
|
|
|
|
|
Sorry about the late reply, but I haven't been near my computer for some time. Anyways, your answer worked perfectly so thank you very much.
- Aaron
|
|
|
|
|
I've got a project going here where I created my own SqlDataAdapter. The problem I'm running into is this...the SelectCommand for the adapter requires a parameter (ie. WHERE id = @id), but I have no idea how to supply this parameter to the Fill method of the adapter.
Any help on this is much apprecitated, and if you need any more info just let me know.
Thanks!
- Aaron
|
|
|
|
|
da.SelectCommand.Parameters["@id"].Value = ???
da.Fill
only two letters away from being an asset
|
|
|
|
|
That seems to have worked perfectly.
Thanks very much for the help.
- Aaron
|
|
|
|
|
What is the best way to test T-SQL stored procedures?
Do You use some special tools for this task?
Wojtek
|
|
|
|
|
I make a copy of the database to my local machine and run the stored procedure on that copy for testing. It's pretty easy and then you don't have to worry about affecting live data.
|
|
|
|
|
Could also run it within a transaction and roll it back
only two letters away from being an asset
|
|
|
|
|
Maybe, but it is safer to run it on a copy of the live database rather than the live database itself.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
