I've got an issue that when I update a record in the gridview it works fine. When I click the delete link to remove the record from the database, I get the following error, "System.FormatException: Input string was not in a correct format". Part of the Stack Trace refers to "String oldValuesParameterFormatString". This parameter is in my SqlDataSource. It was dynamically created when I originally created the SqlDataSource with VWD 2005 Express Edition. The delete function will work if I remove "OldValuesParameterFormatString="original_{0}" ProviderName="System.Data.SqlClient", and any reference to "original_" in the DeleteCommand the SqlDataSource. But if I do, the update function doesn't work. Anyway, here's the SqlDataSource: Any help would be greatly appreciated!!!!!
DeleteCommand="DELETE FROM [houses] WHERE [intID] = @original_intID AND [street] = @original_street AND [city] = @original_city AND [state] = @original_state AND [zip] = @original_zip AND [status] = @original_status AND [pDate] = @original_pDate AND [sPrice] = @original_sPrice AND [asPrice] = @original_asPrice AND [actSalePrice] = @original_actSalePrice AND [cToDate] = @original_cToDate AND [rehabBudget] = @original_rehabBudget AND [tDay] = @original_tDay AND [eDate] = @original_eDate AND [loDate] = @original_loDate AND [rsDate] = @original_rsDate AND [flooringDate] = @original_flooringDate AND [estCompDate] = @original_estCompDate AND [actCompDate] = @original_actCompDate AND [coe] = @original_coe AND [lDate] = @original_lDate AND [credits] = @original_credits AND [agent] = @original_agent AND [insComplete] = @original_insComplete AND [cEscrowDate] = @original_cEscrowDate AND [bidValue] = @original_bidValue AND [thomGuideNumber] = @original_thomGuideNumber AND [locksmith] = @original_locksmith AND [notes] = @original_notes AND [hoa] = @original_hoa"
This is killing me. I've searched the forums for hours and can't find the answer. My SQLDataSource is working fine except when I want to delete. I've allowed the delete function to be shown on the gridview. This is my SQLDataSource: <asp:SqlDataSource ID="IndexDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:IndexConnectionString %>" SelectCommand="SELECT * FROM [Index] WHERE (Type LIKE '%' + @SearchText2 + '%') OR (Product LIKE '%' + @SearchText2 + '%') OR (Version LIKE '%' + @SearchText2 + '%') OR (Binder LIKE '%' + @SearchText2 + '%') OR (Language LIKE '%' + @SearchText2 + '%') OR (CDName LIKE '%' + @SearchText2 + '%') OR (Details LIKE '%' + @SearchText2 + '%') OR (ISOLink LIKE '%' + @SearchText2 + '%')" DeleteCommand="DELETE FROM [Index] WHERE [ID] = @original_ID" UpdateCommand="UPDATE [Index] SET Type = @Type, Product = @Product , Version = @Version, Binder = @Binder, Language = @Language, CDName = @CDName, Details = @Details, ISOLink = @ISOLink WHERE ID = @ID"> <SelectParameters> <asp:ControlParameter Name="SearchText2" Type="String" ControlID="SearchText2" PropertyName="Text" ConvertEmptyStringToNull="False" /> </SelectParameters> <DeleteParameters> <asp:Parameter Name="original_ID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Type" /> <asp:Parameter Name="Product" /> <asp:Parameter Name="Version" /> <asp:Parameter Name="Binder" /> <asp:Parameter Name="Language" /> <asp:Parameter Name="CDName" /> <asp:Parameter Name="Details" /> <asp:Parameter Name="ISOLink" /> </UpdateParameters>
</asp:SqlDataSource> It doesn't give me an error if I click delete but it doesn't delete the record. I've tried changing the DeleteParameter to <asp:Parameter Name="ID" Type="Int32" /> but it gives me the error "Must declare the scaler variable of '@ID'"... I saw in this post http://forums.asp.net/p/1077738/1587043.aspx#1587043 that the answer was that "The variable you have declared in the definition of the proc is different from the variable you are using in the WHERE clause." when they are both the same. Thanks for any help.-Brandan
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.
Hi, I have a table (Table1) with an autoincrement column (sql server 2005) and in my asp .net code (c#) the user can delete a row (record) in gridview (by clicking on delete link). The problem is that every time if the user adds a new record in gridview the new row has a wrong number. What I mean is: Table1 has two columns: ID and Category ID Category1 test12 test23 test3 -> delete this row Now insert a new row: ID Category1 test12 test24 test3 -> ID is 4, but it should be 3 Could anyone help me please? Thank you in advance! Best regardKlaus
Hi guys! I have a simple windows form where a gridview is being populated by a database. I have checked the ability to update, insert delete. And everything is fine but when i want to delete a row it throws an exeption. The exeptions says: "Must declare the scalar variable "@id"."I haven't changed the delete, update and insert commands. They are as fallowsDeleteCommand="DELETE FROM [recepti] WHERE [id] = @id"InsertCommand="INSERT INTO [recepti] ([ime], [recepta], [snimka], [snimka2], [tip]) VALUES (@ime, @recepta, @snimka, @snimka2, @tip)" SelectCommand="SELECT * FROM [recepti]"UpdateCommand="UPDATE [recepti] SET [ime] = @ime, [recepta] = @recepta, [snimka] = @snimka, [snimka2] = @snimka2, [tip] = @tip WHERE [id] = @id" The id column is auto generated, unique, also it's a primery key.So any idea where is the problem?
The function that is supposed to delete a row, is not working. The function is called, and the windows is refreshed, but the row is not deleted.Can anyone see anything wrong with this code:public static void DeleteBlog(int original_BlogID) { string insertCommand = "DELETE FROM Blog WHERE BlogID = @BlogID"; SqlConnection myConnection = new SqlConnection(Blog.ConnectionString); SqlCommand command = new SqlCommand(insertCommand, myConnection); command.Parameters.Add(new SqlParameter("@BlogID", original_BlogID)); myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close();}
I've not been able to test this yet in full VS2005 (MS is screwed up with orders for some reason. Has anyone heard this one. All orders for less than quantity=5 were being rejected). So, instead of waiting for it I installed VWD Express. Now, the project had been built in VWD express beta 2, so no big change there.Several gridview controls in the project had <Delete> enabled. Just <Delete> mind you. Nothing else for command buttons. They all worked fine with delete queries that use gridview.selectedvalue as the parameter. Every single one stopped working since I've converted from beta 2 to RTM!Here's what I determined. The delete will only work if the row in the grid is selected first!. Otherwise <selectedvalue> is null when you click on <Delete>.Is this a bug or what?I'd be happy to supply more details. I am certain others will report this, but I have yet to find a post here or in any blogs out there that reproduce this problem in the RTM of .NET 2.0
I have a SQLDataSource that gets it's data from a SQL 2000 database table. I have configured it to generate the Update/Delete commands, which look correct. I then have a GridView that is using this SqlDataSource to show the data with "Edit" & "Delete" buttons (the default ones from the GridView). My problem is that while all commands (Edit, Delete) work on my local server, they do NOT work on my live server. In my connection string, I specifiy a username and password like this:
<add name="Project_Management.My.MySettings.WebReportsConnectionString" connectionString="Data Source=Karlweb;Initial Catalog=WebReport;Persist Security Info=True;User ID=VbUser;Password=VbUser" providerName="System.Data.SqlClient" /> I have access to change the permissions on my production server, so I gave this "VbUser" every allow permission I could find and still I could not Edit or Delete records. What I mean by they "do not work" is that when I click the Edit button, the GridView switches to edit mode, but when I click Update, the changes are not written. When I click the Delete button, the page just refreshes and the record is not deleted. As this is working on my local server, I think the problem lies in some permission or configuration of the server. Does anyone have any suggestions of where I could look or what to change? Thank you!
I 'm having trouble with the delete function on Gridview - the update works great but I keep getting the error below when I try to delete DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Order_Details_Products'. The conflict occurred in database 'Northwind', table 'Order Details', column 'ProductID'.The statement has been terminated. I using the products table in Northwind to learn this stuff.
Hello, i currently have a gridview that is populated with data from a SQLServer datasource. I have put an output mask in the select statement, so the date and time attributes are displayed in the format i prefer them to be in. SELECT PatientNo, ConsultantName, HospitalName, CONVERT (varchar, Date, 101), CONVERT (varchar, Time, 8) FROM [Appointment]; However when i click the 'edit' link for a record in the gridview, i am unable to edit the date/time attributes and when i click update to confirm any changes to the other attributes, the values in the date/time attributes are emptied. How can i solve this update problem. I'm guessing i need to configure my SQL UPDATE statement, but bit stuck how i do this. Please help! Thanks, James
Hi, I am seeking a hopefully easy solution to spit back an error message when a user receives no results from a SQL server db with no results. My code looks like this What is in bold is the relevant subroutine for this problem I'm having. Partial Class collegedb_Default Inherits System.Web.UI.Page Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] WHERE [name] like '%" & textbox1.Text & "%'" SqlDataSource1.DataBind() If (SqlDataSource1 = System.DBNull) Then no_match.Text = "Your search returned no results, try looking manually." End If End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End Sub Protected Sub reset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles reset.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End SubEnd Class I'm probably doing this completely wrong, I'm a .net newb. Any help would be appreciated. Basically I have GridView spitting out info from a db upon Page Load, but i also have a search bar above that. The search function works, but when it returns nothing, I want an error message to be displayed. I have a label setup called "no_match" but I'm getting compiler errors. Also, next to the submit button, I also have another button (Protected sub reset) that I was hoping to be able to return all results back on the page, similar to if a user is just loading the page fresh. I'd think that my logic would be OK, by just repeating the source code from page_load, but that doens't work.The button just does nothing. One final question, unrelated. After I set this default.aspx page up, sorting by number on the bottom of gridview, ie. 1,2,3,4, etc, worked fine. But now that paging feature, as long with the sorting headers, don't work! I do notice on the status bar in the browser, that I receive an error that says, "error on page...and it referers to javascript:_doPostBack('GridView1, etc etc)...I have no clue why this happened. Any help would be appreciated, thanks!
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
I've been trying to build a data table that allows a client to add, edit and delete his itinerary. I've been using the products table in Northwind to get up to speed on this functions and everything works fine - (ie-connects to the database, shows the data I need, allows me to edit the data) but when I try to use the delete function, I keep getting the error below. DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Order_Details_Products'. The conflict occurred in database 'Northwind', table 'Order Details', column 'ProductID'.The statement has been terminated.
Can anybody help me with this problem? thanx Code used is below:
aspx.cs code used below: using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class Gigs : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } }
We found the problem that when the SP call function,there must have 'dbo.' before the function.Does it necessarily?Can delete 'dbo.' schema when call function in SP?
i have the odbc connection connected to our SQL databasse via sql user account through MS access Frontend and having problem controling the delete function of the account.
In SQL i am only allow the account to read, insert,update and denied delete on the database and tables. However, the account still be able delete the records.
Can someone let me know what am i missing in the permission granting.
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
Background: Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...
Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.
The Problem: There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.
This appears possible in SqlServer too --...as long as there are no CASCADE operations. Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.
So -- please correct me if I am wrong here -- it appears that the operations would be along the lines of: a) Remove the Foreign Key references b) Copy the table structure, and make a new temp table, adding the column c) Copy the data over d) Add the FK relations, that used to be in the first table, to the new table e) Delete the original f) Done?
The questions are: a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'. b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant? c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.
I am having great difficulty with cascading deletes, delete triggers and referential integrity.
The database is in First Normal Form.
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:
Table A / Table B Table C / Table D
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
This is an example of my delete trigger:
CREATE TRIGGER [DeleteA] ON A FOR DELETE AS Delete from B where MeetingID = ID; Delete from C where MeetingID = ID;
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
Hi, I use WVD and SQL Express 2005. I have a table “SignIn� that one of fields inserted automatically by getdate() And I have GridView that I use to display this table because I would like take advantage of GridView sorting and paging methods that are embedded in. Currently I display all records at once.
My problem is how to make the GridView show today’s records only. I tried this code below, but I get only this message “There are no data records to display.�
Hi I have a business search box and gridview pair. When the user enters a business name, the search results are displayed. I also generate a "more information" link which takes the user to a new page, passing the business name ("memberId")to this page (see the template field below). The problem I have is if the name contains a QUOTE (') or other special characters. The "memberId" is chopped off at the quote (e.g. "Harry's Store" is passed as "Harry"). Can anyone tell me a way around this please? Is there anything I can do with the Eval method? Kind regards, Garrett
HiI need to add in gridview control asp code "delete from t1 where id=@id1"how to declare @id1 because the server give me mistake down is the code of asp i use GridView how i can link @id with field there id???Thank u and have a nice daybest regardsthe code if u need it i use c# <ASP:SQLDATASOURCE id=SqlDataSource1 <br ConnectionString="<%$ ConnectionStrings:libraryConnectionString %>" runat="server"><BR></ASP:SQLDATASOURCE></ASP:BOUNDFIELD>
this is my Delete Query NO 1 alter table ZT_Master disable trigger All Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0) alter table ZT_Master enable trigger All
I have troble in Delete Query No 2 here is a select statemnt , I need to delete them select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0) I tried modified it as below delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0) but this doesn't works..
can you please help? and can I combine these 2 SQL Query into one Sql Query? thank you
I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question)
The requirement is: I should allow single row delete from a table but not bulk delete. An audit table should get updated if there is any single delete or single update. So I wrote the triggers as follows: for single and bulk delete
ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance] ON [dbo].[tbl_attendance] AFTER DELETE AS
[code]...
When I try to run the website, the database error I am getting is:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:
DELETE FROM sql2005.production.dbo.products WHERE vendor='Foo' AND productId NOT IN ( SELECT productId FROM sql2000.staging.dbo.fooProductList )
The status message (and @@ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.
I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.
Does anyone have suggestions on what might cause this sort of behavior?
