Best Practice: Procedures: (Insert And Update) OR JUST (Save)
Aug 18, 2007
I have a Product Table.
And now I have to create its Stored Procedures.
I am asking the best practice regarding the methods Insert And Update.
There are two options.
1. Create separate 2 procedures like InsertProduct and UpdateProduct.
2. Create just 1 procedure like ModifyProduct. In which programmatically check that either the record is present or not. If present then update and if not then insert. Just like Imar has done in his article http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419
Can any one explain the better one.
Waiting for helpful replies.
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419
a
View 3 Replies
ADVERTISEMENT
Feb 9, 2007
Hi,
My scenario:
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
Thanks in advance for your help.
Regards,
$wapnil
View 4 Replies
View Related
May 25, 2006
I have a database schema that has an Address table used to store addresses for different entities such as Customers and Employees. I want to reuse the same Address record between different Customers and Employees without duplicating any address information. I'm not sure what the best approach might be.
Should have I have seperate stored procedures on the Address table that update and insert new addresses, where each Address record remains immutable once created? (So the update stored procedure actually creates a new Address record if the data changes). These stored procedures would then be invoked by business logic and used in tandem with stored procedures that act on Customers and Employees to ensure that no address records are duplicated.
Or should I create a view on a Customer joined with Address, and similarily with Employee and Address, and have stored procedures that act on these views and ensure that no Address records are duplicated. Should I use instead of triggers to override the behavior of insert and update on the view to achieve these?
I'm rather lost as to what direction I should take. Any help would be much appreciated, thanks!
View 1 Replies
View Related
Apr 3, 2015
Is there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?
View 4 Replies
View Related
Jan 16, 2008
Hi,
I have 3 tables: authors, companies, and countries.
I have a stored procedure defined as:
PROC addAuthor(AuthorName, CompanyID, CountryID)
INSERT INTO authors (author_name, comp_id, country_id) VALUES( authorName, CompanyID, CountryID)
END PROC
....So the ID of the company and the country are the parameters. This makes it easy since I can just do a direct INSERT statement into the authors table.
Would it be better practice if the parameters asked for the name (not the ID's) of the company and country instead? This way we do not have to memorize the ID's for everything. So for example
PROC addAuthor(AuthorName, CompanyName, CountryName)
SELECT COUNT(*) FROM companies INTO v_numRows WHERE companies.company_name = CompanyName
IF v_numRows = 0
-- error
END IF
SELECT COUNT(*) FROM countries INTO v_numRows WHERE countries.country_name = CountryName
IF v_numRows = 0
-- error
END IF
END PROC
yeah, I might have the SQL syntax wrong up there (not entirely sure how it works for variables) but the gist of it is that I will need to include validation code within the stored procedure.
What do you guys think of this? Which method do you prefer? Is there a significant performance decrease with using the second method?
Thanks for any insights
View 8 Replies
View Related
Nov 23, 2007
Hello all,
I'm looking for a best practice.
Let's say you have a report that contains out of 3 queries.
How are you going to create the report and why?
1. Just use "select * from table where p1= value" in your report
2. Save the query in your db as a view and use the view in your report
3. Create a procedure that contains all 3 queries. And use the procedure with some parameters in your report?
Kr
Karel.
View 5 Replies
View Related
Mar 27, 2008
What is the best way to do this:
I have a table (TableA) say below with 6 colunms
CPubID --------CCTable ---------CCField--------------- DWTable----- DWField
101 XYZ Type NULL NULL
102 XYZ ClaimAssocType NULL NULL
103 XYZ ID NULL NULL
104 XYZ ID NULL NULL
105 XYZ PublicID NULL NULL
106 XYZ PublicID NULL NULL
107 XYZ PublicID NULL NULL
109 XYZ PublicID NULL NULL
201 XYZ PublicID NULL NULL
301 XYZ PublicID NULL NULL
and a Table (TableB) with three Colunm
CPubID --------DWTable ---------DWField---------------
101 T1 F1
102 T1 F2
103 T3 F1
104 T1 F3
105 XT1 F4
106 T1 F5
107 T2 F8
109 T1 F9
201 T1 G1
301 T2 G2
001 T1 F1
002 T1 F2
903 T3 F1
904 T1 F3
905 XT1 F4
706 T1 F5
307 T2 F8
409 T1 F9
801 T1 G1
301 T2 G2
I want to move data of TableB to TableA depending upon the CPubID and TableA should finally look as below
CPubID --------CCTable ---------CCField--------------- DWTable----- DWField
101 XYZ Type T1 F1
102 XYZ ClaimAssocType T1 F2
103 XYZ ID T3 F1
104 XYZ ID T1 F3
105 XYZ PublicID XT1 F4
106 XYZ PublicID T1 F5
107 XYZ PublicID T2 F8
109 XYZ PublicID T1 F9
201 XYZ PublicID T1 G1
301 XYZ PublicID T2 G2
It needs to update the TableA with respective data in Table B
View 3 Replies
View Related
May 5, 2008
Please advice on the best way to this
Create Table TEST
(
[ColID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SubColID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Table1] [varchar](55) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Field1] [varchar](55) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Key1] [int] NULL,
[Key2] [int] NULL
)
INSERT INTO TEST (ColID, SubColID, Table1, Field1, Key1, Key2)
VALUES ('101', '1001', 'Payment', 'Amount', '','')
INSERT INTO TEST (ColID, SubColID, Table1, Field1, Key1, Key2)
VALUES ('101', '1001', 'Payment', 'ID', '','')
INSERT INTO TEST (ColID, SubColID, Table1, Field1, Key1, Key2)
VALUES ('101', '1001', 'Payment', 'CheckPublicID', '121','1221')
INSERT INTO TEST (ColID, SubColID, Table1, Field1, Key1, Key2)
VALUES ('101', '1002', 'Payment', 'Amount', '','')
INSERT INTO TEST (ColID, SubColID, Table1, Field1, Key1, Key2)
VALUES ('101', '1002', 'Payment', 'ID', '','')
INSERT INTO TEST (ColID, SubColID, Table1, Field1, Key1, Key2)
VALUES ('101', '1002', 'Payment', 'CheckPublicID', '131','1321')
select * from test
101 1001 Payment Amount 0 0
101 1001 Payment ID 0 0
101 1001 Payment CheckPublicID 121 1221
101 1002 Payment Amount 0 0
101 1002 Payment ID 0 0
101 1002 Payment CheckPublicID 131 1321
I want to replace the 0's and populate with respective keys as below
101 1001 Payment Amount 121 1221
101 1001 Payment ID 121 1221
101 1001 Payment CheckPublicID 121 1221
101 1002 Payment Amount 131 1321
101 1002 Payment ID 131 1321
101 1002 Payment CheckPublicID 131 1321
View 6 Replies
View Related
May 16, 2000
Hi,
I was trying to save the Stored Procedures; however, SQL Server would not save it and gave me the "Error 259: (SQL Server) Ad-hoc updates to system catalogs not enabled. System Administrator must reconfigure system to allow this."
Does anyone know what to do with this error? I went search the error in the Microsoft TechNet Website and did not get any resolution.
Many Thanks in advance!
Choco
View 2 Replies
View Related
Sep 7, 2007
In SQL Server 2000 when we use to create a new procedure it was saved in Management Consule, but when I create a new procedure and wants to save it SQL Server 2005 (Management Console) ... it saved like a query on my local drive ... I am confused what to do ... if i save it on local drive then how can i call it when required.
Thanks in Advance ...
View 9 Replies
View Related
Jul 14, 2007
Hi Guys
I have visual web developer and sqlexpress 2005 installed on my windows XP pro.
I am creating stored procedures through VWD and works fine for me. However today I realize I do not know how to create stored procedures through sqlexpress server managment.
When I try it it wants to save it as file. And if I do that I am not able to see them until manually open each .sql file.
so, could you enlighten me little please.
thanks
Cemal
View 2 Replies
View Related
Aug 7, 2007
I have several places where I need to get the id (primary key) of a resource, inserting a row if the resource does not exist (i.e. an artificial key to be used as an FK for another table). I should probably change this varchar key lookup to use a hash index, but that is beside the point.
So the table is essentially like:
CREATE TABLE MyLookup(id int identity primary key nonclustered, mykey varchar(256));
CREATE CLUSTERED INDEX mylookup_cidx_mykey ON MyLookup(mykey);
I see two main approaches for how I can do my get-id-with-insert-if-needed.
(Approach 1)
DECLARE @id INT;
SELECT @id = id FROM MyLookup WHERE mykey = 'some key value';
IF (@id is null)
BEGIN
INSERT MyLookup ('some key value');
SET @id = SCOPE_IDENTITY();
END
(Approach 2)
DECLARE @id INT;
INSERT MyLookup SELECT 'some key value' WHERE NOT EXISTS (SELECT id FROM MyLookup WHERE mykey = 'some key value');
IF (@@ROWCOUNT = 0)
SELECT @id = id FROM MyLookup WHERE mykey = 'some key value';
ELSE
SET @id = SCOPE_IDENTITY();
From some quick tests in profiler, approach 2 seems to be a bit faster and have lower resource utilization. But I'm not sure if it maybe takes some more aggressive locks even in the unnecessary case where the mykey row value of 'some key value' already exists. Approach 2 also looks cleaner to me, but I don't mind a bit of extra code if it gives me better scalability through less lock contention.
Any tip on what is considered the best practice for a conditional insert like this, or a tip on how to get detailed lock info for a query? The lock info for profiler was all greek to me, it just had a hex value with each lock acquired/released, so I have no idea what it was telling me. Is my only solution to just run exhaustive tests and look at the perf numbers from the black box?
View 1 Replies
View Related
Jan 9, 2004
I would like to have a stored procedure executed once a week via a DTS package. The data I would like inserted into Table_2, which is the table where the DTS is being executed on, comes from a weekly dump from Oracle into a Table_1 via another DTS package.
I would like to only import data since the last import so I was thinking of my logic to be like this:
INSERT INTO Table_2
(Field1, Field2, ... , FieldN)
VALUES (SELECT Field1, Field2, ... , FieldN FROM Table_1 WHERE ThisDate > MAX(Table_2.ThatDate))
Does this make sense? Or do you all suggest a different mannger of accomplishing this?
View 8 Replies
View Related
Oct 17, 2000
Hi
I have 2 Database ( INFORMIX AND SQL-SERVER), When the INFORMIX fall only work SQL SERVER, but when INFORMIX go back, I want to insert in the INFORMIX only register that were inserting in the SQL SERVER while the INFORMIX was out. For it I want DTS for export register, How I can get only this registers ?
thank you in advance
View 2 Replies
View Related
Aug 20, 2001
Hello -
I am looking for a way to allow users to create and edit their own stored procedures, saved under DBO (not under their own schema).
Any help would be greatly appreciated!
Paul
View 2 Replies
View Related
Jan 4, 2007
Hi,
using custom tasks. My properties got saved properly the first time the task is added to the workspace. After that, changing values in the CustomUI updates the properties, but the package shows, that It doesn't need to get saved. So my properties don't get changed in the package XML. Moving my custom task on the workspace after changing properties, let the package go into "save me" state and my propertiy values get saved to XML.
So now, Im searching for a method or property I have to call or set to show the package it needs to get saved after closing my CustomUI.
Any hint?
Thanks
PS: When I change the property value in the task properties grid (lower right). The package gets notified about the need to get saved.
View 11 Replies
View Related
May 13, 2008
Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
View 5 Replies
View Related
Apr 16, 2015
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE
View 9 Replies
View Related
Nov 14, 2007
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
View 6 Replies
View Related
Feb 11, 2006
Hello:I didn't find any documentation that notes save point names are casesensitive, but I guess they are...Stored Proc to reproduce:/* START CODE SNIPPET */If Exists (Select * From sysobjects Where Type = 'P' and Name ='TestSaveTran')Drop Procedure dbo.TestSaveTranGoCreate Procedure dbo.TestSaveTranAsBeginDeclare@tranCount int--Transaction HandlingSelect @tranCount = @@TRANCOUNTIf (@tranCount=0)Begin Tran localtranElseSave Tran localtranBegin Try--Simulate Error While ProcessingRAISERROR('Something bad happened', 16, 1)/*If this proc started transaction then commit it,otherwise return and let caller handle transaction*/IF (@tranCount=0)Commit Tran localtranEnd TryBegin Catch--Rollback to save pointRollback Tran LOCALTRAN --<< NOTE case change--Log Error--Reraise ErrorEnd CatchEndGo--Execute Stored ProcExec dbo.TestSaveTran/*Should receive the following message:Cannot roll back LOCALTRAN. No transaction or savepoint of that namewas found.*//* END CODE SNIPPET */What is really strange, if there is a transaction open, then no erroris thrown. So if you execute as so:/* START CODE SNIPPET */Begin Tran--Execute Stored ProcExec dbo.TestSaveTran/* END CODE SNIPPET */There is no "Cannot roll back LOCALTRAN...." message.Questions:1-)Can someone confirm save point names are case sensitve and this isnot happening because of a server setting?2-)Is this a logic error that I am not seeing in the example codeabove?We have changed our code to store the save point name in a variable,which will hopefully mitigate this "problem".Thx.
View 4 Replies
View Related
Oct 23, 2014
I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.
This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.
View 1 Replies
View Related
Feb 15, 2008
Hello
I've to write an trigger for the following action
When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz
all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated
statut_tiers to 1
and date_cloture to the same date as entered
the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture
thank you for your help
I've never done a trigger before
View 14 Replies
View Related
Jul 23, 2005
Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.
View 4 Replies
View Related
Feb 20, 2008
Hi,
I wonder if anyone knows any good web tutorials or books where I can learn how to make update using sprocs (in ASP.NET and MSSQL environment).
Thanks
View 6 Replies
View Related
Jun 26, 2007
I am new to sql sever management studio express, but a long time query analyzer user. This is a very basic question.
I want to change the default directory in sql server management studio express so that when I go to save a query, it is already pointed to the correct one. Where do I change that?
Thanks,
Nanci
View 2 Replies
View Related
Jan 9, 2008
This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.
View 1 Replies
View Related
Apr 28, 2007
Hi,I'm trying to insert some values into 2 tables using stored
procedures (which should be pretty straight forward) but I'm running
into problems.Given below are my 2 sp that I'm using: USE [DBName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddToProject] (@DeptCode varchar(20), @ProjectTitle varchar(300), @ProjectDetails varchar(3000), @ProjectManagerID int, @RequestedBy varchar(100), @DateRequested datetime, @DueDate datetime, @ProjectStatusID int)
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @Dept varchar(50), @ProjID varchar(50)
SET @Dept = REPLACE(CONVERT(char,@DeptCode),'.','')
EXEC ('INSERT INTO dbo.tbl_ProjectNumber' + @Dept + '(DeptCode) VALUES(' + @Dept + ')')
EXEC GetProjID @Dept, @ProjID OUTPUT
INSERT INTO dbo.tbl_Project (ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusID)
VALUES (@ProjID, @ProjectTitle, @ProjectDetails, @ProjectManagerID, @RequestedBy, @DateRequested, @DueDate, @ProjectStatusID);
COMMIT TRANSACTION
---------------------------------------------------------------------
USE [DBName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetProjID] (@DeptCode varchar(20), @ProjID varchar(50) OUTPUT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ProjectNumber varchar(100), @Table varchar(100)
SET @ProjectNumber = 'ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','')
SET @Table = 'dbo.tbl_ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','')
EXEC ( 'SELECT @ProjID = MAX('+@ProjectNumber + ') FROM ' + @Table )
SET @ProjID = @ProjID + '-' + @DeptCode
END When I run the AddToProject sp using the following values: USE [DBName]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[AddToProject]
@DeptCode = N'BAT',
@ProjectTitle = N'Some Title',
@ProjectDetails = N'Some Details',
@ProjectManagerID = 3,
@RequestedBy = N'Me',
@DateRequested = N'04/27/2007',
@DueDate = N'05/04/2007',
@ProjectStatusID = 4
SELECT'Return Value' = @return_value
GO
I get the following errors: Msg 128, Level 15, State 1, Line 1The name "BAT" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.Msg 137, Level 15, State 1, Line 1Must declare the scalar variable "@ProjID".Msg 515, Level 16, State 2, Procedure AddToProject, Line 17Cannot insert the value NULL into column 'ProjID', table 'NYCEDC.dbo.tbl_Project'; column does not allow nulls. INSERT fails. If I run just the GetProjID sp, I get the following error: Must declare the scalar variable "@ProjID". -------------------------------------What I'm trying to do is, get a bunch of values from the aspx page, insert the DeptCode into the field DeptCode of the table 'tbl_ProjectNumber'+DeptCode so that the IDENTITY value field 'ProjectNumber'+DeptCode can increment by 1. After 'ProjectNumber'+DeptCode has incremented, I want to run the GetProjID sp so that I can insert ProjID into the 'tbl_Project' table along with the other values taken from the aspx page. ProjID is the primary key for the 'tbl_Project' table so it needs to be added along with the other values (other they obviously can't be added). I'm running the AddToProject as a Transaction to make sure that if more than 1 person is adding a project at the same time, the project numbers and other information don't get messed up. Any help would be appreciated.Thanks.
View 13 Replies
View Related
Nov 25, 2007
Hi,I need to make an insert into two tables connected to each other via foreign and primary key. The point is - I cannot use stored procedures or triggers. That tables looks like this: Candidate(ID, Name, Surname, Class) and Candidate_Exam(ID, date). ID in Candidate_Exam is a foreign key. When you insert a new record into Candidate, another one need to be inserted into Candidate_Exam with the same ID. Can anyone help? It is supposed to be an easy assigment so maybe I just have a bad approach.Regards,N.
View 3 Replies
View Related
Mar 1, 2007
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
View 2 Replies
View Related
Aug 25, 2005
My company works with a fairly large database that needs to be kept live.
My problem is that we need a method of updating the database without messing the data, or crashing the website.
I am sure this is a common problem with a number of solutions. Could
anyone please direct me to a good article on the best practices for
updating databases like this?
View 9 Replies
View Related
Apr 28, 2007
Hi,I'm trying to insert some values into 2 tables using stored procedures (which should be pretty straight forward) but I'm running into problems.Given below are my 2 sp that I'm using:
View 5 Replies
View Related
Feb 24, 2006
I grouped everything together so you see it all. I'm not getting any errors but nothing is happening. I had this working and then I converted to Stored Procedures and now it's not.
CREATE PROCEDURE UpdateCartItem(@itemQuantity int,@cartItemID varchar)ASUPDATE CartItems Set pounds=@itemQuantityWHERE cartItemID=@cartItemIDGO
<asp:Button CssClass="scEdit" ID="btnEdit" Runat="server" Text="Update" CommandName="Update"></asp:Button>
Sub dlstShoppingCart_UpdateCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs) Dim connStr As SqlConnection Dim cmdUpdateCartItem As SqlCommand Dim UpdateCartItem Dim strCartItemID As String Dim txtQuantity As TextBox strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex) txtQuantity = e.Item.FindControl("txtQuantity") connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdUpdateCartItem = New SqlCommand(UpdateCartItem, connStr) cmdUpdateCartItem.CommandType = CommandType.StoredProcedure cmdUpdateCartItem.Parameters.Add("@cartItemID", strCartItemID) cmdUpdateCartItem.Parameters.Add("@itemQuantity", txtQuantity.Text) connStr.Open() cmdUpdateCartItem.ExecuteNonQuery() connStr.Close() dlstShoppingCart.EditItemIndex = -1 BindDataList() End Sub
____________________________________________________________
CREATE PROCEDURE DeleteCartItem(@orderID Float(8),@itemID nVarChar(50))ASDELETEFROM CartItemsWHERE orderID = @orderID AND itemID = @itemIDGO
<asp:Button CssClass="scEdit" ID="btnRemove" Runat="server" Text="Remove" CommandName="Delete"></asp:Button>
Sub dlstShoppingCart_DeleteCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs) Dim connStr As SqlConnection Dim cmdDeleteCartItem As SqlCommand Dim DeleteCartItem Dim strCartItemID strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex) connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdDeleteCartItem = New SqlCommand(DeleteCartItem, connStr) cmdDeleteCartItem.CommandType = CommandType.StoredProcedure cmdDeleteCartItem.Parameters.Add("@cartItemID", strCartItemID) connStr.Open() cmdDeleteCartItem.ExecuteNonQuery() connStr.Close() dlstShoppingCart.EditItemIndex = -1 BindDataList() End Sub
View 2 Replies
View Related
Sep 14, 2007
hi
i need to insert the list of ipaddress using stored procedures.
the user will give the from and to range of IP ADDRESS.i've to insert all the possible ip address between those values.
how to do this..
View 3 Replies
View Related
