Datasource Won't Update On Delete
hi,
I have a dataadaptor which i use to fill a dataset. I can do this no problems. But if I delete a row from the dataset and update the adaptor again, nothing gets changed in my source database. Does anyone know why this is?
here's my code for my dataadaptor:
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("select * from tblExtRef", conn);
adapter.SelectCommand.Connection = conn;
SqlParameter parm;
SqlCommand cmd;
cmd = new SqlCommand("DELETE FROM tblextref WHERE extrefid = @extrefid",conn);
parm = cmd.Parameters.Add("@extrefid", SqlDbType.Int , 40, "extrefid");
parm.SourceColumn = "ExtRefid";
//parm.SourceVersion = DataRowVersion.Original;
//parm.Direction = ParameterDirection.InputOutput;
//SourceVersion = DataRowVersion.Original;
adapter.DeleteCommand = cmd;
View Complete Forum Thread with Replies
Related Forum Messages:
-
Does Anybody Know Where I Can Learn How To Add, Update, Delete Using SQL Datasource Control?
-
SqlDataSource Insert, Update Delete Options Are Dimmed In Configure DataSource Control
-
VS2005 - Using Detailsview To Update, Insert And Delete Rows From SQL 2005 Database. Delete And Insert Work But Update Does Not - No Errors Returned
-
DataSource Out Of Order After Update
- Batch Script To Create ODBC And Update Registry Datasource Key - Help!
- How Can You View The Connection String Of An Existing Datasource? How Do You Delete Existing Dataset?
- Turning Off Impersonation On A SQL Server Datasource, Connecting To Datasource Using Reporting Services Credentials
- Use Sybase Odbc Datasource Or Oledb Datasource,run No Error,but 0 Row Data Output,why? Help Me!!!
- Delete And Update
- Update And Delete
-
Problems With UPDATE And DELETE
-
Update Or Delete With Insert?
-
Cascading Update And Delete
- Update / Delete With Joins
- Prevent DELETE And/or UPDATE
- Update, Delete, Insert
- Use Insert Update Delete In DTS
- Cascade Delete And Update In SQL 7.0
- Cascade Delete/update
- Limiting UPDATE And DELETE
- (Update Or Delete) Which Is Faster.
- Update = Delete && Insert?
- Delete/insert Vs Update
- Update And Delete Stmt.
-
How To Enable Update, Delete In Gridview
-
Can't Update, Insert, Or Delete Rows
-
Update And Delete Statement Issues
-
Best Practices For Insert/Update/Delete
-
Update And Delete Rule - Cascade
-
GridView Wont Delete Or Update
-
DELETE And UPDATE Trigger Question
-
UPDATE/DELETE Bug In SqlDataSource - Will Microsoft Ever Fix This?
-
SQL: UPDATE, DELETE Current Record Only
-
Insert, Select, Update And Delete
-
SqlDataSource: Insert, Update And Delete Not Available
-
SQL Update/Insert/Delete Problem
-
Grid View-can't Update Or Delete
- Create One Trigger For Both Update And Delete
- Insert/ Update/ Delete Slowness.
- TRIGGER FOR INSERT,UPDATE,DELETE
- Triggers (insert Update Delete)
- Trigger For Delete Or Specific Update
- What Is Faster?! Update Or Delete And Insert
- SSIS Delete/Insert/Update
- Insert/Update And Delete Change
- Can Not Update Or Delete Records From ODBC...
- Need To Update Table To Delete Rows
- Update/Delete/Add Comparing 2 Tables
Does Anybody Know Where I Can Learn How To Add, Update, Delete Using SQL Datasource Control?
does anybody know where I can learn how to add, update, delete using SQL datasource control? I have a couple of books that shows me how to SELECT but nothing shows me how to update, add new record or delete with the SQL data source control. Even this website doesn't have any tutorial. Does any body know where I can read up on how to use all features of this control? Or where that have a couple of examples on how to use it?
View Replies !
VS2005 - Using Detailsview To Update, Insert And Delete Rows From SQL 2005 Database. Delete And Insert Work But Update Does Not - No Errors Returned
Using VS 2005 DetailsView to insert, delete, and update rows in SQL 2005 database. insert and delete work but update does not. I recieve no errors and the detailsView comes back unchanged (as well as table row is unchanged). I am trying to use as little code behind as possible. However I do have ItemUpdating routines that seem to work (i.e Checking table for new login duplicates and encrypting passwords). The following is the source code generated by VS2005:<%@ Page Language="VB" AutoEventWireup="false" CodeFile="frmDbRegionMgrNew.aspx.vb" Inherits="frmDbRegionMgrNew" Title="Region Manager DB Update" Theme="detailsVeiwTheme" %><%@ Import Namespace="System.Data" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"><title>Untitled Page</title> </head><body><form id="form1" runat="server"><div><asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Style="z-index: 101; left: 14px; position: absolute; top: 10px" Width="199px">Data Base Maintenance </asp:Label><asp:HyperLink ID="HyperLink1" runat="server" BackColor="ActiveBorder" BorderColor="ActiveBorder"BorderStyle="Outset" Font-Bold="True" Font-Size="X-Small" ForeColor="#004000"Height="31px" NavigateUrl="DataBaseMaint.aspx" Style="z-index: 133; left: 524px;position: absolute; top: 7px" Width="96px">DB Main Menu</asp:HyperLink><br /><br /><br /><table style="width: 654px"><tr><td style="width: 120px"><asp:Label ID="Label2" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Text="Regional Manager" Width="189px"></asp:Label></td><td style="width: 100px"></td><td style="width: 203px"></td></tr><tr><td style="width: 120px" valign="top"><asp:Label ID="Label3" runat="server" Font-Bold="True" Font-Size="Small" ForeColor="#004000"Style="z-index: 128; left: 2px; position: absolute; top: 115px" Width="128px">Select Greenhouse -></asp:Label></td><td style="width: 100px" valign="top"><asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"DataTextField="Name" DataValueField="GrnHseID"></asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>"SelectCommand="SELECT [GrnHseID], [Name] FROM [Greenhouse]"></asp:SqlDataSource></td><td style="width: 203px"> <asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"DataKeyNames="RegionMgrID" DataSourceID="SqlDataSource2" Height="50px" Width="125px"><Fields><asp:TemplateField HeaderText="RegionMgrID" InsertVisible="False" SortExpression="RegionMgrID"><EditItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Eval("RegionMgrID") %>'></asp:Label></EditItemTemplate><ItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Bind("RegionMgrID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:TemplateField HeaderText="GrnHseID" SortExpression="GrnHseID"><EditItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Eval("GrnHseID") %>'></asp:Label></EditItemTemplate><InsertItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:TextBox></InsertItemTemplate><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:BoundField DataField="DispLvl" HeaderText="DispLvl" SortExpression="DispLvl" /><asp:BoundField DataField="FName" HeaderText="FName" SortExpression="FName" /><asp:BoundField DataField="Minit" HeaderText="Minit" SortExpression="Minit" /><asp:BoundField DataField="LName" HeaderText="LName" SortExpression="LName" /><asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /><asp:BoundField DataField="Ext" HeaderText="Ext" SortExpression="Ext" /><asp:BoundField DataField="Cell" HeaderText="Cell" SortExpression="Cell" /><asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" /><asp:BoundField DataField="Login" HeaderText="Login" SortExpression="Login" /><asp:BoundField DataField="Password" HeaderText="Password" SortExpression="Password" /><asp:BoundField DataField="AccessLvl" HeaderText="AccessLvl" SortExpression="AccessLvl" /><asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" /></Fields></asp:DetailsView><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>" DeleteCommand="DELETE FROM [RegionMgr] WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"InsertCommand="INSERT INTO [RegionMgr] ([GrnHseID], [DispLvl], [FName], [Minit], [LName], [Phone], [Ext], [Cell], , [Login], [Password], [AccessLvl]) VALUES (@GrnHseID, @DispLvl, @FName, @Minit, @LName, @Phone, @Ext, @Cell, @Email, @Login, @Password, @AccessLvl)"OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [RegionMgr] WHERE ([GrnHseID] = @GrnHseID)"UpdateCommand="UPDATE [RegionMgr] SET [GrnHseID] = @GrnHseID, [DispLvl] = @DispLvl, [FName] = @FName, [Minit] = @Minit, [LName] = @LName, [Phone] = @Phone, [Ext] = @Ext, [Cell] = @Cell, = @Email, [Login] = @Login, [Password] = @Password, [AccessLvl] = @AccessLvl WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"><DeleteParameters><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></DeleteParameters><UpdateParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></UpdateParameters><SelectParameters><asp:ControlParameter ControlID="DropDownList1" Name="GrnHseID" PropertyName="SelectedValue"Type="Int32" /></SelectParameters><InsertParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /></InsertParameters></asp:SqlDataSource> </td></tr><tr><td style="width: 120px"></td><td style="width: 100px"></td><td style="width: 203px"></td></tr></table></div></form></body></html>Appreciate any and all help!Dave
View Replies !
DataSource Out Of Order After Update
I have a SQLDataAdapter object that fills a DataSet that fills a grid that users are allowed to edit. When the user clicks to update the grid, I re-populate the DataSet with the grid's values and execute the SQLDataAdapter's update procedure using the DataSet. This updates the SQL tables fine, but immediately after the update I fill the DataSet and the rows are not in order. For example, row 1 is a field for users to enter an item # and the table is ordered by the item # column in the SQLDataAdapter's select command, after they enter it and click update it updates the table; but after that it needs to refresh and fill the grid with the updated table information ordered by the item # column. The problem is that it refreshes the grid, but the rows are not ordered by the item # column, they are in the order they were before the update. If I close the page and enter it again the grid pulls up with the updated information properly ordered. I have tried clearing all the grid, dataset tables, and even disposing of the dataset and rebuilding it.....none work. I have done this same code in other apps with no problem. Does anyone have any ideas that I can try. Thank you, ACFalcon
View Replies !
Batch Script To Create ODBC And Update Registry Datasource Key - Help!
I have create a batch file, that creates an ODBC then updates the application datasource key in the registry to the new system dsn name. The problem is that the new DSN doesn't work when i try and connect the application...but if i had manually created the odbc source the app connects as expected... i have checked the registry and there is no difference between the two dsns created...but the application throws IM002[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified after i have also changed the applications registry key to point to the new database. Now the interesting things to note are that when i re-create the odbc to the old server and database (sql 2000) by batch file it works fine. The new ODBC is linking to a sql 2005 database, but it is still using the 2000 drivers and when i manually created it, it worked also. Another interesting thing is that if i go into the odbc dsn and click configure, go through and test the connection it works fine... after i close this i then retry the application and it opens correctly... I need this to be automated with no manual intervention, as this will be added to a large group of users login scripts! Any help greatly appreciated. My batch file code is as below: ODBCCONF.exe CONFIGSYSDSN "SQL Server" "DSN=RMS Live 2005 | SERVER=d-db99sql2005| Trusted_Connection=Yes | Database=RMS-Livedb"
View Replies !
How Can You View The Connection String Of An Existing Datasource? How Do You Delete Existing Dataset?
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
View Replies !
Turning Off Impersonation On A SQL Server Datasource, Connecting To Datasource Using Reporting Services Credentials
Dear SQL Server Reporting Services Support Guru, How do we configure Reporting Services such that when it attempts to connect to a datasource (e.g. Microsoft SQL Server datasource), it connects as the service account under which it is running as opposed to the calling user? From our research we were thinking we could try one of two things: 1) change the credential type on the datasource itself so that it does not use Windows Integrated Security and instead connects as itself or 2) change the <identity impersonate="true"> flag in the web.config of the ReportServer web service to false. A high level description of our architecture is that we have a client application with an web browser control that displays a report in the ReportViewer web control on the server. The report uses a SQL Server datasource to connect to our own SQL Server database hosted on the same server as Reporting Services but we want it to turn off impersonation and have Reporting Services connect as itself, not as the calling user. We do not want to grant users in Production direct access to our SQL Server database; so instead we wanted to configure Reporting Services such that it resolves and connects to the datasource using its own credentials. A related question...when does the ReportServer web service get called? For both retrieval of the .rdl files and connecting to the datasource for dataset retrieval? What exactly does that <identity impersonate="true"> flag affect? Any help you can provide would be very much appreciated. Thanks much! -canuck81
View Replies !
Delete And Update
Hi All,I have a Access front-end,the tables are from SQL server 2000 andlinked via ODBC using DSN.I have a Main table and several related tables.The Main table has aOne -to- Many relationship with the related tables.When I try to delete a record from the front-end,I get a error msg "the table is locked,you and another user are attempting to delete/update the same record".I am the single user and I am at a loss tounderstand what the problem is.I have to explain that I never had a problem when i was using Accessas the backend too.Is it because of linking?Any suggestions?Roy
View Replies !
Update And Delete
I need to update rows in database A from data in database B and delete from database B if a match is found but leave in databse B if no match found. Is there a way do do this with the OLe DB command?
View Replies !
Problems With UPDATE And DELETE
I have two buttons on my page, one which will update content the other which will delete content from my slq database. I get this error "CS1501: No overload for method 'updateBlockContent' takes '2' arguments" when I click on my update button. I get this error "CS1501: No overload for method 'deleteBlockContent' takes '2' arguments" when I click on my delete button. This is my method for updating - UPDATE [CMS] SET [BlockName] = @BlockName, [BlockContent] = @BlockContent WHERE (([BlockID] = @Original_BlockID)) This is my C# code behind the update button:protected void btnUpdateBlock_Click(object sender, EventArgs e) {CMSTableAdapter ta = new CMSTableAdapter(); try { ta.updateBlockContent(txtBlockName.Text, txtBlockContent.Text);lblFeedback.Text = "Block Updated.";txtBlockName.Text = txtBlockContent.Text = ""; } catch (Exception ex) { lblFeedback.Text = ex.ToString(); } finally {lblFeedback.Visible = true; ta.Dispose(); } } This is my method for deleting - DELETE FROM [CMS] WHERE (([BlockID] = @Original_BlockID)) This is my C# code behind the delete button:protected void btnDeleteBlock_Click(object sender, EventArgs e) {CMSTableAdapter ta = new CMSTableAdapter(); try { ta.deleteBlockContent(txtBlockName.Text, txtBlockContent.Text);lblFeedback.Text = "Block Deleted.";txtBlockName.Text = txtBlockContent.Text = ""; } catch (Exception ex) { lblFeedback.Text = ex.ToString(); } finally {lblFeedback.Visible = true; ta.Dispose(); } } Can somebody please explain why this is happening and how to remedy this problem. Thanks, Chris.
View Replies !
Update Or Delete With Insert?
Hi All, I have this project I'm working on it's Product Content Management System rewrite. I got to the point of updating the Product By Sku and not sure if I should use UPDATE statement or I should DELETE sections assosiated with the ProductContentID and then re-insert them again? I'm not sure which is more afficient? I can really do both and it's really not that complicated, the only problem I see with DELETE then INSERT is the ProductContentSectionId in the Sections table is going to grow very fast and I'm a bit concerned about it. We use SQL Server 2000 and we have about 4 bound tables where we keep the data. The one I'm talking about is the sections table where we keep the actual types of product content like a BoxShot, Description, Key Features and so on... Thank you in advance! Tatyana Hughes
View Replies !
Cascading Update And Delete
If we want to maintain the data in relationships. There are two ways to do it. 1. Auto (Like Cascading Update And Delete) 2. Manually (Like In Stored Procedures) I read an intresting article http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419 In this article Imar has choosen the second way (Manually). And when I talk to Imar. He said, "Cascading deletes would have worked equally well in this situation. However, I personally don't like them too much. I am much rather in control, enabling me to delete what I want and when I want it. I could, for example, keep certain data for "time travelling scenarios" (e.g. the state things were in some time ago) or I might want to keep it for other purposes." Can any one help me to choose the better one. Waiting for helpful replies.
View Replies !
Update / Delete With Joins
Asking for some sample SQL/SP code to perform updates/deletes with joinedtables.Example 1:For every record that is matched on both tables A and B, update a field intable A.Example 2:For every record that is matched on both tables A and B, delete record intable A.TIA.~ Duane Phillips.
View Replies !
Prevent DELETE And/or UPDATE
Hi all!Are there any other way than using rights or Triggers to prevent aDELETE or an UPDATE on a specific column.The "problem" with rights is that they dont apply to all DB-usersThe "problem" with triggers is that they generate lots of extraSQL-codeI would like a solution something like below. If there are anyprimitives like this or other more neat solutions I would be glad toknowCREATE TABLE some_table NO DELETE/* ^^^^^^^^^*/(some_column SOME_TYPE NO UPDATE/* ^^^^^^^^^*/)For clarity, here is a trigger that currently solves the problemCREATE TRIGGER check_updateable_columns ON some_tableFOR UPDATEASIF UPDATE(some_column)RAISERROR(...)GOorCREATE TRIGGER delete_not_allowed ON some_tableINSTEAD OF DELETEASRAISERROR(...)GO
View Replies !
Update, Delete, Insert
I am trying to update a SQL database with data from a Wang system. The Wang data is dumped to a txt file. I then import it into an update table in SQL via Access. Some of the data is new and some of the data is updated records. At this point I have been trying to create a script to update and add data to a table via the query tool in SQL Then delete data from the update table. I was able to get the UPDATE and DELETE to work but I have not figured out how to insert new records at the same time? Can I use an IF statement? I will apreciate any help or sample code, Thanks. UPDATE MemberList Set Name = NameUpd, Address1 = Address1Upd, Address2 = Address2Upd, City = CityUpd, State = StateUpd, ZipCode = ZipCodeUpd, MemberStatus = MemberstatusUpd FROM MemberList, MemberListUpd WHERE MemberList.MemberNumber = MemberListUpd.MemberNumberUpd INSERT ? DELETE MemberListUpd
View Replies !
Use Insert Update Delete In DTS
I have an Access table from which I have to get data periodically in 30 minutes everyday to an SQL server table. both have the same data structure. I want to get all the new records as insert, modified records as update and deleted records in source table that still exists in target table to be deleted. I have tried searching on the net. They all explain about using the insert, update and delete statements in DDQ. I understand that but how do i change the related constant in the ActiveX script for eg: Main = DTSTransformstat_UpdateQuery for update. I saw somewhere an eg. where i use a select case according to some column in the table that has to be changed to insert or update but i dont want to add a new column for this. some solution that will compare the source table and target table with sql queries using exists. but i cant identify target and source in a query if i use the "Execute SQL task". Can someone help.
View Replies !
Cascade Delete And Update In SQL 7.0
Is it possible to cascade update and delete while DRI remains there ? That is without deleteing refrential integrity. If anyone have example of cascade delete and Update using Pubs or Northwind Example , i'll be really obliged.
View Replies !
Limiting UPDATE And DELETE
I know how to do this in MySQL; but I was hoping there was a way to do it in MS SQL. I want to be able to limit the number of rows an UPDATE or DELETE will effect regardless of the WHERE clause. I want to do this as a stop gap in the event that there is a logical error in the WHERE clause that would make it effect more rows than is humanly intended. If I write a complex query that I know should drill down to only affecting one row, I just want to lock it in before I run it on database and take the risk of damaging some data. In MySQL you would just do something like this: Code: DELETE FROM table_name WHERE ... LIMIT 1; The only thing close to LIMIT I could come up with for MS SQL was TOP but it seems to only work for SELECT. Any ideas would be greatly appreciated. I ask this because if you screw up; manually saving 1, 2 or even 5 rows is a lot easier than having to rescue a whole table of data (even if it on a development server).
View Replies !
Update = Delete && Insert?
I vaguely recall reading an article that I can no longer find that an update statement is executed as a combination of a Delete and an Insert by SQL server. Does anyone know if this a still a true statement in SQL Server 2005? Thanks, -shl
View Replies !
Delete/insert Vs Update
Im trying to keep a mirror image of some data Im getting from Quickbooks. As the records are inserted into the database I need to check if a record exists and either update or insert a new one. it seems easier just to delete using the tnxid and reinsert vs updating my question is if I go begin INSERT INTO QBInvoicesQue(100s of feilds) end begin delete from QBInvoices where txnid = @TxnID end and there is not matching txnid to delete from will it cause any problems? before going to the insert statement? begin INSERT INTO QBInvoices(100s of feilds) end
View Replies !
Update And Delete Stmt.
Hi, I have two tables: 1. RubricReport 2. RubricReportDetail How can I code this step in my stored procedure: If @ReportID is NULL, insert a row into RubricReport table, and set @ReportID=@@IDENTITY; otherwise, update table RubricReport for columns LastUpdate and LastUpdateBy, and delete table RubricReportDetail where ReportID=@ReportID. Table RubricReport has columns ReportID, County,Dsitrict, DataYears, LastUpdate and LastUpdateBy Table RubricReportDetail has columns ReportID, IndicatorID, LocalPerf Kindly help me. Thanks in advance
View Replies !
How To Enable Update, Delete In Gridview
Hi all,Happy New Year!I've just install VS .NET 2005 and try to play with Gridviewwhen I configure the datasource for Gridview and click "Advance" in order to enable Update, Delete Select etc...the checkBox is not selectableCan someone pls show me how?Thanks in advance.
View Replies !
Can't Update, Insert, Or Delete Rows
I have recently started an ASP.Net application and am having some issues updating, inserting and deleting rows. When I started working with it, I was getting errors because it could not find any update command. Eventually, I figured out how to automatically generate the commands, by configuring my SQLDataSource control and clicking the "advanced" button. Right now though, I have generated the commands, but I still can not insert, update or delete rows. When I attempt to update anything, I recieve an error that says "The data types text and nvarchar are incompatible in the equal to operator." Nowhere in my table do I have any rows that use the datatype "nvarchar", only "text" and "int". I tried switching all of my text columns to "nvarchar(500)", which did not help. I am led to believe that the auto generated SQL procedures are trying to do something behind the scenes that is making my database act up, because even when I delete rows, I get the same exception, so the datatypes cannot be messed up there, because all that the datasource is doing is deleting rows, therefore there is no need to worry about data types. I only get the error when I check the "Use optimistic concurrency" box. When I do not use optimistic concurrency, I can delete, insert, and update rows... but nothing happens. There are no errors, but nothing is deleted, updated or inserted either. Upon postback, nothing has changed. I may upload a copy of the exact exception page, if someone thinks that it may help. Here is the update command that was generated: UPDATE [Record Information] SET [Speed] = @Speed, [Recording Company] = @Recording_Company, [Year] = @Year, [Artist] = @Artist, [Side 1 Track Title] = @Side_1_Track_Title, [Side 1 Track Duration] = @Side_1_Track_Duration, [Side 2 Track Title] = @Side_2_Track_Title, [Side 2 Track Duration] = @Side_2_Track_Duration, [Sleeve Description] = @Sleeve_Description WHERE [Record Database ID] = @original_Record_Database_ID Apparently no stored procedures exist for any of these operations, and I am unsure why. The "Record Database ID" is my identity column, and is the only field that is (and is supposed to be) uneditable.
View Replies !
Update And Delete Statement Issues
I'm working on a address book where customers can add, edit and delete address book entries. For the life of me I can't figure out what I'm messing up with the Update and Delete statements of this feature. Can someone please help me.Here's my code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:kalistadbConnectionString %>" DeleteCommand="DELETE FROM [Address] WHERE [AddID] = @original_AddID AND [AddNick] = @original_AddNick AND [AddFN] = @original_AddFN AND [AddLN] = @original_AddLN AND [AddCompany] = @original_AddCompany AND [AddAddress] = @original_AddAddress AND [AddCity] = @original_AddCity AND [AddProv_State] = @original_AddProv_State AND [AddPostal_Zip] = @original_AddPostal_Zip AND [AddCountry] = @original_AddCountry AND [AddPhone] = @original_AddPhone AND [CustID] = @original_CustID" InsertCommand="INSERT INTO [Address] ([AddNick], [AddFN], [AddLN], [AddCompany], [AddAddress], [AddCity], [AddProv_State], [AddPostal_Zip], [AddCountry], [AddPhone], [CustID]) VALUES (@AddNick, @AddFN, @AddLN, @AddCompany, @AddAddress, @AddCity, @AddProv_State, @AddPostal_Zip, @AddCountry, @AddPhone, @CustID)" OldValuesParameterFormatString="original_{0}" onselecting="SqlDataSource1_Selecting" OnInserting="SqlDataSource1_Inserting" SelectCommand="SELECT * FROM [Address] WHERE ([CustID] = @CustID)" UpdateCommand="UPDATE [Address] SET [AddNick] = @AddNick, [AddFN] = @AddFN, [AddLN] = @AddLN, [AddCompany] = @AddCompany, [AddAddress] = @AddAddress, [AddCity] = @AddCity, [AddProv_State] = @AddProv_State, [AddPostal_Zip] = @AddPostal_Zip, [AddCountry] = @AddCountry, [AddPhone] = @AddPhone, [CustID] = @CustID WHERE [AddID] = @original_AddID AND [AddNick] = @original_AddNick AND [AddFN] = @original_AddFN AND [AddLN] = @original_AddLN AND [AddCompany] = @original_AddCompany AND [AddAddress] = @original_AddAddress AND [AddCity] = @original_AddCity AND [AddProv_State] = @original_AddProv_State AND [AddPostal_Zip] = @original_AddPostal_Zip AND [AddCountry] = @original_AddCountry AND [AddPhone] = @original_AddPhone AND [CustID] = @original_CustID"> <SelectParameters> <asp:Parameter Name="CustID" /> </SelectParameters> <DeleteParameters> <asp:Parameter Name="original_AddID" Type="Int64" /> <asp:Parameter Name="original_AddNick" Type="String" /> <asp:Parameter Name="original_AddFN" Type="String" /> <asp:Parameter Name="original_AddLN" Type="String" /> <asp:Parameter Name="original_AddCompany" Type="String" /> <asp:Parameter Name="original_AddAddress" Type="String" /> <asp:Parameter Name="original_AddCity" Type="String" /> <asp:Parameter Name="original_AddProv_State" Type="String" /> <asp:Parameter Name="original_AddPostal_Zip" Type="String" /> <asp:Parameter Name="original_AddCountry" Type="String" /> <asp:Parameter Name="original_AddPhone" Type="String" /> <asp:Parameter Name="original_CustID" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="AddNick" Type="String" /> <asp:Parameter Name="AddFN" Type="String" /> <asp:Parameter Name="AddLN" Type="String" /> <asp:Parameter Name="AddCompany" Type="String" /> <asp:Parameter Name="AddAddress" Type="String" /> <asp:Parameter Name="AddCity" Type="String" /> <asp:Parameter Name="AddProv_State" Type="String" /> <asp:Parameter Name="AddPostal_Zip" Type="String" /> <asp:Parameter Name="AddCountry" Type="String" /> <asp:Parameter Name="AddPhone" Type="String" /> <asp:Parameter Name="CustID" /> <asp:Parameter Name="original_AddID" Type="Int64" /> <asp:Parameter Name="original_AddNick" Type="String" /> <asp:Parameter Name="original_AddFN" Type="String" /> <asp:Parameter Name="original_AddLN" Type="String" /> <asp:Parameter Name="original_AddCompany" Type="String" /> <asp:Parameter Name="original_AddAddress" Type="String" /> <asp:Parameter Name="original_AddCity" Type="String" /> <asp:Parameter Name="original_AddProv_State" Type="String" /> <asp:Parameter Name="original_AddPostal_Zip" Type="String" /> <asp:Parameter Name="original_AddCountry" Type="String" /> <asp:Parameter Name="original_AddPhone" Type="String" /> <asp:Parameter Name="original_CustID" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="AddNick" Type="String" /> <asp:Parameter Name="AddFN" Type="String" /> <asp:Parameter Name="AddLN" Type="String" /> <asp:Parameter Name="AddCompany" Type="String" /> <asp:Parameter Name="AddAddress" Type="String" /> <asp:Parameter Name="AddCity" Type="String" /> <asp:Parameter Name="AddProv_State" Type="String" /> <asp:Parameter Name="AddPostal_Zip" Type="String" /> <asp:Parameter Name="AddCountry" Type="String" /> <asp:Parameter Name="AddPhone" Type="String" /> <asp:Parameter Name="CustID" /> </InsertParameters> </asp:SqlDataSource>
View Replies !
Best Practices For Insert/Update/Delete
for now, doing a small school project, i find doing SPs for Insert useful, like checking for existing data and not inserting, that might not be the best method, i had advice from here i can use unique constraints instead, then what about update and delete? SPs also? the pros make SPs for everything? currently use dynamically generated SQL from SqlDataSources. for Update / delete. some delete are SPs too...
View Replies !
Update And Delete Rule - Cascade
Hi, I have a database which saves data about bus links. I want to provide a information to passenger about price of their journay. The price depends on three factors: starting busstop, ending busstop and type of ticket (full, part - for students and old people, ...). So I created a table with three foreign key constraints (two for busstops and one for type). When the busstop is deleted or type of ticket I want all data connected with it to be deleted automatically. I wanted to use cascade deleting. But I receive a following exception: Introducing FOREIGN KEY constraint 'FK_TicketPrices_BusStops1' on table 'TicketPrices' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. How can I achieve my task? Why should it cause cycles or multiple cascade paths?
View Replies !
GridView Wont Delete Or Update
I have had this problem before but it turned out to be dodgy SQL created by the wizard. Doesn't seem to be the case this time.The following does a postback but makes no changes. 1 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ehlConnectionString %>" 2 DeleteCommand="DELETE FROM [tblSubRegions] WHERE [SubRegionID] = ?" 3 InsertCommand="INSERT INTO [tblSubRegions] ([SubRegionID], [RegionID], [SubRegionName]) VALUES (?, ?, ?)" 4 ProviderName="<%$ ConnectionStrings:ehlConnectionString.ProviderName %>" 5 SelectCommand="SELECT tblSubRegions.SubRegionID, tblSubRegions.RegionID, tblSubRegions.SubRegionName, tblRegions.RegionName FROM (tblSubRegions INNER JOIN tblRegions ON tblSubRegions.RegionID = tblRegions.RegionID) WHERE (tblSubRegions.RegionID = ?) ORDER BY tblSubRegions.SubRegionName" 6 UpdateCommand="UPDATE [tblSubRegions] SET [RegionID] = ?, [SubRegionName] = ? WHERE [SubRegionID] = ?"> 7 8 <DeleteParameters> 9 <asp:Parameter Name="SubRegionID" Type="Int32" /> 10 </DeleteParameters> 11 12 <UpdateParameters> 13 <asp:Parameter Name="RegionID" Type="Int32" /> 14 <asp:Parameter Name="SubRegionName" Type="String" /> 15 <asp:Parameter Name="SubRegionID" Type="Int32" /> 16 </UpdateParameters> 17 18 <SelectParameters> 19 <asp:ControlParameter ControlID="dropRegions" Name="RegionID" PropertyName="SelectedValue" Type="Int32" /> 20 </SelectParameters> 21 22 <InsertParameters> 23 <asp:Parameter Name="SubRegionID" Type="Int32" /> 24 <asp:Parameter Name="RegionID" Type="Int32" /> 25 <asp:Parameter Name="SubRegionName" Type="String" /> 26 </InsertParameters> 27 28 </asp:SqlDataSource> 29 30 31 32 <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ehlConnectionString %>" 33 ProviderName="<%$ ConnectionStrings:ehlConnectionString.ProviderName %>" 34 SelectCommand="SELECT [RegionID], [RegionName] FROM [tblRegions]"> 35 36 </asp:SqlDataSource> 37 38 39 40 <asp:DropDownList id="dropStates" runat="server" OnSelectedIndexChanged="dropStates_SelectedIndexChanged" AutoPostBack="True"> 41 </asp:DropDownList> 42 43 <asp:DropDownList id="dropRegions" runat="server" OnSelectedIndexChanged="dropRegions_SelectedIndexChanged" AutoPostBack="True"> 44 </asp:DropDownList> 45 46 47 48 <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" 49 AutoGenerateColumns="False" EnableViewState=false Width="100%" DataSourceID="SqlDataSource1"> 50 <Columns> 51 <asp:TemplateField HeaderText="SubRegionName" SortExpression="SubRegionName"> 52 <EditItemTemplate> 53 <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" 54 DataTextField="RegionName" DataValueField="RegionID" SelectedValue='<%# Bind("RegionID") %>'> 55 </asp:DropDownList> 56 </EditItemTemplate> 57 <ItemTemplate> 58 <asp:Label ID="Label1" runat="server" Text='<%# Bind("SubRegionName") %>'></asp:Label> 59 </ItemTemplate> 60 </asp:TemplateField> 61 <asp:BoundField DataField="RegionName" HeaderText="RegionName" SortExpression="RegionName" /> 62 <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" /> 63 </Columns> 64 </asp:GridView> Thanks in advance. Shaun
View Replies !
DELETE And UPDATE Trigger Question
HelloI have a Trigger on a table. Here is the code ALTER TRIGGER [dbo].[OnOrderDelete] ON [dbo].[orders] AFTER DELETE,UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @ids int; SELECT @ids =( SELECT id from DELETED); DELETE FROM files WHERE OrderId = @ids; ENDActually the UPDATE event handler is not wanted here, but why when I leave him I have a following behaviour:When orders table is updated, the "SELECT @ids =( SELECT id from DELETED); DELETE FROM files WHERE OrderId = @ids;" part is executed, and the program recognizes DELETED as UPDATED! (Like " SELECT @ids =( SELECT id from UPDATED) ") Is this right? And how can I part UPDATED and DELETED ?ThanksArtashes
View Replies !
SQL: UPDATE, DELETE Current Record Only
Well, I really messed up. Instead of changing the name of a current company record in a table I changed ALL the company names in the table. Me.CustomerDataSource.SelectCommand = "UPDATE tbl_customers SET company = '" & companyTextBox.Text & "'" So, I need to insert a WHERE clause to fix this. My problem is that I've been searching everywhere for this simple command structure and cannot find anything that specifically addresses a simple way to reference the current record. I tried...Me.CustomerDataSource.SelectCommand = "UPDATE tbl_customers SET company = '" & companyTextBox.Text & "' WHERE recno = @recno" But I get the error: Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@recno". Can anyone provide this simple query clause?
View Replies !
Insert, Select, Update And Delete
I've got four pages with in the first page a insert, in the second a select, in the thirth a update and in the fourth a delete statement. First the values of a textbox will be inserted in the database, then the values will be shown in labels and than it is possible to edit or delete the values inserted. Every inserted item belonging to each other has one ID. The follwing values has a second ID etc. How can I make that possible?? I think that I should pass the ID's between the pages so I'm sure that I edit or delete the values that I want. So insert value 1 in page 1, show with select value 1 in page 2, edit or delete value 1 in page 3 and 4. Maybe I didn't explain it good enough for you, please tell me then!! Thanks!!
View Replies !
SqlDataSource: Insert, Update And Delete Not Available
Hello Guys, I’m trying to create a sqlDataSource using the wizard, I can get the select statement, the here clause and the order by clause but when I click on the Advanced button the options to generate the insert, update and delete statements are not available. Does anyone know what am I doing wrong?
View Replies !
SQL Update/Insert/Delete Problem
Hi,I just upgraded my ASP.NET 2.0 BETA 2 environment to the final release of ASP.NET 2.0 VWD.Once the update was finished, I could open my website without any problems..... Now, I noticed that in the final release, some modifications have been included in the Membership Stored Procedure and other stored procedures. So I created a new database (SQL Express) and added my data again.After re-creating my SQLDataSources, I tryed to enable the Editing and Deleting option in VWD and once I run my web application, it seems when selecting editing and then update, it doesn't work anymore....This is my code : <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IMMOASPNETDBConnectionString %>" DeleteCommand="DELETE FROM aspnet_test WHERE (testID = @Original_testID)" SelectCommand="SELECT BuyID, BuyNL, BuyFR, Lastupdated FROM aspnet_Buy" UpdateCommand="UPDATE aspnet_Buy SET BuyNL = @BuyNL, BuyFR = @BuyFR WHERE (BuyNL = @original_BuyID)"> <DeleteParameters> <asp:Parameter Name="Original_testID" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="BuyNL" /> <asp:Parameter Name="BuyFR" /> <asp:Parameter Name="original_BuyID" /> </UpdateParameters> </asp:SqlDataSource> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" DataKeyNames="BuyID" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:BoundField DataField="BuyID" HeaderText="BuyID" InsertVisible="False" ReadOnly="True" SortExpression="BuyID" /> <asp:BoundField DataField="BuyNL" HeaderText="BuyNL" SortExpression="BuyNL" /> <asp:BoundField DataField="BuyFR" HeaderText="BuyFR" SortExpression="BuyFR" /> <asp:BoundField DataField="Lastupdated" HeaderText="Lastupdated" SortExpression="Lastupdated" /> </Columns> </asp:GridView>Can someone help me with this ? What is wrong with the Update command ?Thanks to all,Bart
View Replies !
Grid View-can't Update Or Delete
I put a grid view on a web form ,when I run it -the SELECT, EDIT works the UPDATE,DELETE makes an error although I use the sama data,I added the error : Anyone can help? Server Error in '/CrystalReportsWebSite1' Application. The data types text and nvarchar are incompatible in the equal to operator. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: The data types text and nvarchar are incompatible in the equal to operator.Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [SqlException (0x80131904): The data types text and nvarchar are incompatible in the equal to operator.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +915 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +179 System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1140
View Replies !
Create One Trigger For Both Update And Delete
hi,CAn i have one trigger for both Update and DeleteDelete Trigger---------------------create Trigger [tr_delete_user_log]on [dbo].[user_log] for deleteasbegininsert into z_user_log select * from deletedendTrigger Update---------------------CREATE Trigger [tr_update_user_log]on [dbo].[user_log] for updateasbegininsert into z_user_log select * from deletedendCan i have one trigger instead of these Triggers ..
View Replies !
Insert/ Update/ Delete Slowness.
SQL2K sp4 Howdy all. I opened a 200 mb. file in Query Analyzer that is full of Inserts/ Updates/ and Deletes. I tried just to parse it, and killed it after 18 hours. There is no blocking. All of the appropriate indexes exist. I even removed them and retried JIC. The box is plenty powerful for this task. Does anyone have any ideas? I've tried several times with no luck. At the top of the file is SET IMPLICIT_TRANSACTIONS ON and then every 10,000 statements is COMMIT WORK. I've tried adjusting the number of commits to a lower number with no luck. This works fine on smaller files (3 - 20 mb).
View Replies !
TRIGGER FOR INSERT,UPDATE,DELETE
I HAVE TWO TABLES IN THE DATABSE AND THE SECOND TALE SI FOR AUDITING. I WANT CREATE THE TRIGGER ON FIRST TABLE SO THAT I CAN PUT THE STATUS LIKE INSERT,UPDATE OR DELETE IN THE STATUS COLUMN IN SECOND TABLE. CAN SOMEBODY HELP IN WRITING THAT TRIGGER..? HOW CAN I DETERMINE WAETHER THE RECORD IS BEEN INSERTED OR UPDATED OR DELETED. DO I HAVE TO WRITE A SEPERATE TRIGGER FOR EACH ACTIVITY..OR I CAN WRITE IT IN THE SINGLE TRIGGER..? PLEASE SUGGEST ME..ITS URGENT. THANKS IN ADVANCE HARISH
View Replies !
Triggers (insert Update Delete)
hi, is there a difference when I combine all 3 above triggers in one code vs putting code in 3 different triggers. for exmple, create trigger tr_name for insert, update, delete as ... code vs writting codein 3 separate triggers Thanks AAA
View Replies !
Trigger For Delete Or Specific Update
I have a table where I want to prevent user from deleting or setting a flag on a field to "y" with a database trigger (sql 2000). I understand the trigger for just one (stopping the delete, or stopping the field being changed to "y"). Should I have 2 seperate triggers or would there be a way to handle both.
View Replies !
SSIS Delete/Insert/Update
I am still learning SSIS. What is the best way to do delete/insert/update in a database in SSIS. I am looking to achieve something similar to what was there in a Data Driven Query in SQL Server 2000. I would like to delete/insert/update the destination table based on the condition that my lookuptable returns For ex If "the user in my source table has changed the status" Then Update the user row in the destination table Else skip the row If "the user in my source table has delete flag =1 " Then Delete the user in the destination table Else skip the row If "the user in my source table is new(has a new ID) " Then Insert the user in the destination table Else skip the row Any suggestions .... Cheers Siaj
View Replies !
Insert/Update And Delete Change
hi, I have a table which contained 5 columns and with 2 primary keys Col 1 | Col 2 | Col 3 | Col 4 | Col 5 | ab | 1 | abc | null | null ab | 2 | def | null | null Col 1 and Col 2 both are primary keys. How do I update Col2 from 1 to 2 and from 2 to 1 in a single transaction statement and commit it? Thanks
View Replies !
Can Not Update Or Delete Records From ODBC...
If I create a new table in my database I can not update or delete any records when I'm connected with ODBC. I can access all the other tables in the database just fine with ODBC, it's only when I create a new table or copy and existing table that this happens. Is there some setting I need to change, if so how do I change it? Thanks, TH
View Replies !
Update/Delete/Add Comparing 2 Tables
Hi, Im pretty new to SQL Server, but was able to create a dsn conenction to our dbf database, and then use sql script to import the table into sql server using this script below: use cameodata; /* Drop stock Table */ IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'stock' AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' ) BEGIN DROP TABLE stock END /* Import stock Table */ select * into stock from openrowset ('msdasql','dsn=stock', 'select * from stock') So basically everytime i run this script, it deletes the table in sql server and then imports the latest copy. The problem is that this table is huge and it takes a while. What Id rather have is a script that i could run that would compare the dsn connected table with the table currently on sql server and if there are any updates then update sql server, if there are items on the sql server that are not the the latest copy, then delete that record on the sql server table and also if there are new items on the lastest copy then add the new record to the sql server table. Is this possible? thanks, Anthony.
View Replies !
|
TRACKER
