Asking for some sample SQL/SP code to perform updates/deletes with joined
tables.
Example 1:
For every record that is matched on both tables A and B, update a field in
table A.
Example 2:
For every record that is matched on both tables A and B, delete record in
table A.
TIA.
~ Duane Phillips.
DELETE FROM ObPostEvent WHERE EpisodeID IN (SELECT ID FROM ObMotherEpisode WHERE ID = @Urno)
DELETE FROM ObPostEvent INNER JOIN ObMotherEpisode ON ObMotherEpisode.ID = ObPostEvent.EpisodeID WHERE ObMotherEpisode.ID = @Urno
Are the 2 statements give the same meaning? I get an error for the 2nd statement as follows :-
Server: Msg 156, Level 15, State 1, Procedure spArchiveObPostEvent, Line 78
Incorrect syntax near the keyword 'INNER'.
What is wrong in this query am getting incorrect syntax near ','.error. I am using sql server 2014
DELETE ab, b
FROM Authors AS a INNER JOIN AuthorArticle AS ab ON a.AuthID=ab.AuthID
INNER JOIN Articles AS b ON ab.ArticleID=b.ArticleID
WHERE AuthorLastName='Tom';
I need some help understanding what's happening on a join when updatingtables. Assume 2 tables with identical structures:UPDATETable1SETField1 = Table2.Field1,Field2 = Table2.Field2FROMTable2WHERETable1.Field3 = Table2.Field3AND Table1.Field4 = Table2.Field4Indexes exist on Field3 and Field4 on both tables. So why does SQLServer choose a hash join?Thanks in advance.
View 3 Replies View RelatedI am trying to do a query like this...
UPDATE g
SET g.GroupID = gp.GroupID, g.Contact1 = members.FirstName, g.BusPhone1 = members.BusPhone, g.HomePhone1 = members.HomePhone, g.Internet1 = members.Email
FROM statelst AS g INNER JOIN
grpcon AS gp ON g.GroupID = gp.GroupID INNER JOIN
members ON gp.MemberID = members.MemberID CROSS JOIN
I have my table statelst that I want to update certain columns from the values returned by a select on the grpcon table joined to the members table.I am getting an error "Incorrect syntex near 'JOIN'.
Is this possible? What I am looking for is something like:UPDATE T_SitesSET T_Sites.LastDate = T_Inspections.DateFROM T_SitesINNER JOIN T_Assets ON T_Sites.SiteID = T_Assets.AssetIDLEFT OUTER JOIN T_Insecptions ON T_Assets.AssetID = T_Inspections.AssetID-- But I need only the last inspection done on the site (including if it is null)
View 1 Replies View RelatedHi 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 RelatedI 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 RelatedWe find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign
then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033
4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators.
The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
Any ideas on the principles involved here.
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.
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
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.
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;
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.
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
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).
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
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
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 RelatedI 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
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
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
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
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?
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 RelatedI'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 RelatedI 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.
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 RelatedHi,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
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?
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
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!!
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?