Update Delete Wins Conflict (Type 3) Non Convergence Issue
Jan 26, 2007
Topology:
Merge Replication (Using Web Sync anonymous pull subscriptions)
SQL 2005 SP1 all nodes
Scenarios:
I have two subscribers both have sync'd and data appears fine. Then one of the subscribers deletes a row whilst the other subscriber makes an update to the same row. The subscriber who deleted sync's first and the row has gone from the subscriber DB and the Publisher DB.
The second subscriber who still has the newly updated row in it's DB then sync's. There is a conflict of Type 3 (Update Delete Wins Conflict) which the conflict resolver states that the last subscriber to sync lost the conflict and the deleted row wins.
However this has caused problems. After sync has occurred the second subscriber still has the updated row in it's DB. The row does not exist on either the publisher or the subscriber where it was deleted. The data has become non-convergent.
I have used the Microsoft COM resolver Last Datetime wins and specified our [lud] datetime column. Now in my understanding in a type 3 situation the winning row (The deleted row) should be propogated to the publisher and subscriber and thus the data should have been removed at all nodes regardless of whether it had been updated or not.
Also to add another twist, the delete was performed before the update (Chronologically speaking) therefore I would assume the resolver should have taken the update to be the winning row (i.e. Type 4 Update Wins Delete Conflict) at least because a deleted row will have NULL in the [lud] column therefore the updated row with a NON NULL value for [lud] would be the winner.
Am I missing something here, or is the resolver not performing as described in Books On-Line
Cheers
Rab
View 5 Replies
ADVERTISEMENT
Oct 19, 2005
I'm trying to create relational database with some triggers in SQL Server 7.0, but it doesn't work as expected. Let's say that I have 'Office' database with two tables, 'Users' and 'UserRights' (userRights table should have much more rights, but that's not relevant for this problem):
CREATE TABLE [Users] (
[FS_Username] [nvarchar] (8) NOT NULL ,
[FS_Password] [nvarchar] (32) NOT NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED
(
[FS_Username]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [UserRights] (
[FS_Username] [nvarchar] (8) NOT NULL ,
[FI_UserType] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_UserType] DEFAULT (1),
[FI_AllowLogin] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_AllowLogin] DEFAULT (1),
CONSTRAINT [PK_UserRights] PRIMARY KEY NONCLUSTERED
(
[FS_Username]
) ON [PRIMARY] ,
CONSTRAINT [FK_UserRights_Users] FOREIGN KEY
(
[FS_Username]
) REFERENCES [Users] (
[FS_Username]
)
) ON [PRIMARY]
GO
Foreign Key CONSTRAIN above is created by adding both tables to the diagram and defining relationship between these two tables FS_Username field, where 'Enable relationship for INSERT and UPDATE' option is turned ON. You can easily see this if you create diagram youself and insert these two tables in it.
Next to this, I created two triggers that should handle inserting/deleting rows in UserRights table as consequence of inserting/deleting rows in Users table:
CREATE TRIGGER InsertUserRights ON Users
FOR INSERT
AS
BEGIN
INSERT INTO UserRights (FS_Username) (SELECT FS_Username FROM Inserted)
END
CREATE TRIGGER DeleteUserRights ON Users
FOR DELETE
AS
BEGIN
DELETE UserRights WHERE FS_Username IN
(SELECT FS_Username FROM Users)
END
Now, when (manually) I insert row in Users table, UserRights table gets updated accordingly. HOWEVER, when I try to delete one or more entries from Users table, I get error report. For example, if you try to execute following two commands:
Insert Into Users (FS_Username, FS_Password) VALUES ('John', 's')
Delete from Users
... first command will succede, but second one will fail with message:
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_UserRights_Users'. The conflict occurred in database 'Office', table 'UserRights', column 'FS_Username'.
The statement has been terminated.
Does anyone know how to resolve this problem without loosing constrains and triggers ? (If I turn off 'Enable relationship for INSERT and UPDATE' option for relationship, things will work fine, but than I can make inconsistent data in UserRights table).
tnx a lot,
Dejan
View 1 Replies
View Related
Mar 14, 2008
I have merge replication set with with one publisher and three subscribers (push subscription). From time to time I get the following in the conflict table:
"The same row was updated at both 'Publisher.database' and 'Subscriber.database'. The resolver chose the update from 'Publisher.database' as the winner"
This is weird because the UPDATE only happend at the given subscriber. I checked the publisher and all other subscribers and there was never an UPDATE on this table. Also, the update at the publisher was actually the record that was already present.
I'm kinda stuck here. Any help or comments are greatly appreciated.
View 1 Replies
View Related
May 9, 2006
I am running merge replication on SQL Server 2005. Included in replication are 6 Subscribers and one Publisher/Distributor. Replication compatibility level is SQL 2005 and I am using row-level tracking. Servers are synchronized in 5 minutes each (so gap between two consecutive syncs of one server is 30 minutes)
Earlier all of this was on compatibility level SQL 2000, since all servers were upgraded from 2000 to 2005. But 10 days ago I upgraded and replication compatibility to 2005. I did not initialize subscription, since I had data in place and db's ready for merge replication.
Since that 10 days ago I have column update conflict on each row change. Not just if it is changed at same time at different subscribers. For example, if I change one row today, and somebody else changes it three days later on other subscriber it counts as a conflict. He always chooses good (updated) version of data as winner, but it is quite annoying having 15000 conflicts in one table daily. And it makes harder to track other conflicts.
Does it have anything to do with row level column tracking, or it is some known bug? Or, even better, if anyone knows how to avoid it. I was thinking about changing it to column level, but then I need to reinitialize subscriptions which means sending of 9 GB data through some pretty slow lines (256 kbp/s), or few hours work to drop existing subscriptions and create new one's. Maybe I would to this if I knew that column level will help.
View 14 Replies
View Related
Aug 3, 2006
Does SQL 2005 require WINS?
View 1 Replies
View Related
Sep 4, 2001
We would like to use dns entries (CNAME) to create transparency between server infrastructure and logical servernames. So we could physically move databases onto another server and changing the dns entry without reconfiguring all client applications.
Old situation:
- Financeapplication connects to SERVERA (wins) through a .ini file.
New situation:
- Financeapplication connects to finserver.mydomain.be (CNAME)
- finserver.mydomain.be points to servera.mydomain.be (A record) in dns
- servera.mydomain.be is refering to the ip address of SERVERA
We tried to connect and got a 'get_overlapped_results' (clientside) when using a dns name in combination with named pipes. At the server we are getting this error:
Error: 17832, Severity: 18, State: 8
2001-08-24 13:10:16.64 ods Connection opened but invalid login packet(s) sent. Connection closed.
Connecting to the wins name of the server does work! Connecting to the dns name also works when using tcp sockets instead of named pipes.
Though, on some client pc's this does work with named pipes too, but we couldn't find any regularity or logic when it works or when it doesn't (service packs, mdac versions, NT/95/98/2000, ... etc).
We also noticed that integrated security works with tcp sockets when using dns names, but it doesn't when using wins names.
Does anyone have a clue?
Kurt.
View 1 Replies
View Related
May 3, 2006
Hi all,
I have developed a mobile program with sql server 2000 merge replication. It works fine in Win mobile 2003 OS, but, not working at all after I upgrade the mobile OS to Win mobile version 5.0
Does anyone have any idea at all what's going on?
Thanks a lot.
AngelaC
View 4 Replies
View Related
Mar 6, 2007
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 2 Replies
View Related
Jul 11, 2006
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 1 Replies
View Related
Aug 18, 2007
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 2 Replies
View Related
Dec 13, 2007
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 3 Replies
View Related
Mar 21, 2008
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 5 Replies
View Related
Jul 12, 2004
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 1 Replies
View Related
Jul 23, 1999
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 1 Replies
View Related
Jan 8, 1999
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 2 Replies
View Related
Feb 16, 2007
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 2 Replies
View Related
May 11, 2007
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 3 Replies
View Related
Jan 22, 2008
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 6 Replies
View Related
Jul 23, 2005
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 2 Replies
View Related
Jul 23, 2005
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 1 Replies
View Related
Oct 16, 2007
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 10 Replies
View Related
Aug 3, 2006
I'm referring to this bug, which I've seen a lot of people (including me) run into:
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=93937
Has anyone heard if MS plans to fix this soon?
Thanks
View 3 Replies
View Related
Sep 28, 2006
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 12 Replies
View Related
Nov 15, 2006
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 1 Replies
View Related
Jan 15, 2007
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 1 Replies
View Related
Jul 24, 2007
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 2 Replies
View Related
Oct 7, 2007
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 2 Replies
View Related
Oct 20, 2007
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 1 Replies
View Related
Jan 2, 2008
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 2 Replies
View Related
Nov 13, 2005
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 3 Replies
View Related
Jan 3, 2006
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 4 Replies
View Related
Feb 23, 2006
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 2 Replies
View Related
Apr 7, 2006
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 3 Replies
View Related