SQL: UPDATE, DELETE Current Record Only
Well, I really messed up. Instead of changing the name of a current company record in a table I changed ALL the company names in the table. Me.CustomerDataSource.SelectCommand = "UPDATE tbl_customers SET company = '" & companyTextBox.Text & "'"
So, I need to insert a WHERE clause to fix this. My problem is that I've been searching everywhere for this simple command structure and cannot find anything that specifically addresses a simple way to reference the current record.
I tried...Me.CustomerDataSource.SelectCommand = "UPDATE tbl_customers SET company = '" & companyTextBox.Text & "' WHERE recno = @recno"
But I get the error:
Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@recno".
Can anyone provide this simple query clause?
View Complete Forum Thread with Replies
Related Forum Messages:
VS2005 - Using Detailsview To Update, Insert And Delete Rows From SQL 2005 Database. Delete And Insert Work But Update Does Not - No Errors Returned
Using VS 2005 DetailsView to insert, delete, and update rows in SQL 2005 database. insert and delete work but update does not. I recieve no errors and the detailsView comes back unchanged (as well as table row is unchanged). I am trying to use as little code behind as possible. However I do have ItemUpdating routines that seem to work (i.e Checking table for new login duplicates and encrypting passwords). The following is the source code generated by VS2005:<%@ Page Language="VB" AutoEventWireup="false" CodeFile="frmDbRegionMgrNew.aspx.vb" Inherits="frmDbRegionMgrNew" Title="Region Manager DB Update" Theme="detailsVeiwTheme" %><%@ Import Namespace="System.Data" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"><title>Untitled Page</title> </head><body><form id="form1" runat="server"><div><asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Style="z-index: 101; left: 14px; position: absolute; top: 10px" Width="199px">Data Base Maintenance </asp:Label><asp:HyperLink ID="HyperLink1" runat="server" BackColor="ActiveBorder" BorderColor="ActiveBorder"BorderStyle="Outset" Font-Bold="True" Font-Size="X-Small" ForeColor="#004000"Height="31px" NavigateUrl="DataBaseMaint.aspx" Style="z-index: 133; left: 524px;position: absolute; top: 7px" Width="96px">DB Main Menu</asp:HyperLink><br /><br /><br /><table style="width: 654px"><tr><td style="width: 120px"><asp:Label ID="Label2" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Text="Regional Manager" Width="189px"></asp:Label></td><td style="width: 100px"></td><td style="width: 203px"></td></tr><tr><td style="width: 120px" valign="top"><asp:Label ID="Label3" runat="server" Font-Bold="True" Font-Size="Small" ForeColor="#004000"Style="z-index: 128; left: 2px; position: absolute; top: 115px" Width="128px">Select Greenhouse -></asp:Label></td><td style="width: 100px" valign="top"><asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"DataTextField="Name" DataValueField="GrnHseID"></asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>"SelectCommand="SELECT [GrnHseID], [Name] FROM [Greenhouse]"></asp:SqlDataSource></td><td style="width: 203px"> <asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"DataKeyNames="RegionMgrID" DataSourceID="SqlDataSource2" Height="50px" Width="125px"><Fields><asp:TemplateField HeaderText="RegionMgrID" InsertVisible="False" SortExpression="RegionMgrID"><EditItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Eval("RegionMgrID") %>'></asp:Label></EditItemTemplate><ItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Bind("RegionMgrID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:TemplateField HeaderText="GrnHseID" SortExpression="GrnHseID"><EditItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Eval("GrnHseID") %>'></asp:Label></EditItemTemplate><InsertItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:TextBox></InsertItemTemplate><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:BoundField DataField="DispLvl" HeaderText="DispLvl" SortExpression="DispLvl" /><asp:BoundField DataField="FName" HeaderText="FName" SortExpression="FName" /><asp:BoundField DataField="Minit" HeaderText="Minit" SortExpression="Minit" /><asp:BoundField DataField="LName" HeaderText="LName" SortExpression="LName" /><asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /><asp:BoundField DataField="Ext" HeaderText="Ext" SortExpression="Ext" /><asp:BoundField DataField="Cell" HeaderText="Cell" SortExpression="Cell" /><asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" /><asp:BoundField DataField="Login" HeaderText="Login" SortExpression="Login" /><asp:BoundField DataField="Password" HeaderText="Password" SortExpression="Password" /><asp:BoundField DataField="AccessLvl" HeaderText="AccessLvl" SortExpression="AccessLvl" /><asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" /></Fields></asp:DetailsView><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>" DeleteCommand="DELETE FROM [RegionMgr] WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"InsertCommand="INSERT INTO [RegionMgr] ([GrnHseID], [DispLvl], [FName], [Minit], [LName], [Phone], [Ext], [Cell], , [Login], [Password], [AccessLvl]) VALUES (@GrnHseID, @DispLvl, @FName, @Minit, @LName, @Phone, @Ext, @Cell, @Email, @Login, @Password, @AccessLvl)"OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [RegionMgr] WHERE ([GrnHseID] = @GrnHseID)"UpdateCommand="UPDATE [RegionMgr] SET [GrnHseID] = @GrnHseID, [DispLvl] = @DispLvl, [FName] = @FName, [Minit] = @Minit, [LName] = @LName, [Phone] = @Phone, [Ext] = @Ext, [Cell] = @Cell, = @Email, [Login] = @Login, [Password] = @Password, [AccessLvl] = @AccessLvl WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"><DeleteParameters><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></DeleteParameters><UpdateParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></UpdateParameters><SelectParameters><asp:ControlParameter ControlID="DropDownList1" Name="GrnHseID" PropertyName="SelectedValue"Type="Int32" /></SelectParameters><InsertParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /></InsertParameters></asp:SqlDataSource> </td></tr><tr><td style="width: 120px"></td><td style="width: 100px"></td><td style="width: 203px"></td></tr></table></div></form></body></html>Appreciate any and all help!Dave
View Replies !
Delete Syntax To Delete A Record From One Table If A Matching Value Isn't Found In Another
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 DELETE FROM Broker_Rates WHERE (Broker_Rates.BID <> Broker.BID) Thanks
View Replies !
Delete Record Based On Existence Of Another Record In Same Table?
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
View Replies !
Trigger - Current Record ?
CREATE TRIGGER test ON [Table_1] FOR UPDATE AS UPDATE [Table_1] set [Field_1] =SUSER_SNAME() This trigger update all record, I want to update only the current record which is currenty update. How I cant to this ? Sorry for my english
View Replies !
How Do I Make Sure Only One Record Is The Current Issue?
I have a table of magazine issues. The table are defined as below: issueID int Uncheckedname varchar(50) Uncheckedtitle varchar(100) Checkeddescription varchar(500) CheckedcrntIssue bit Checkedarchived bit CheckednavOrder int CheckeddateCreate datetime Checked And here is what I want. Is there a way when inserting/updating or on the table itself to make sure that there is only one record that is marked as the current issue? The way I have it here in my table, any records can have the current issue (crntIssue) field checked. I only want one crntIssue field checked regardless of how many records or issues are in the table. If there is no way to automatically have SQL Server to manage that then that means I must check all the records before hand before the update/insert query, correct?
View Replies !
How To Make A Trigger Refer To The Current Record
I have a table full of items that have a "date_updated" field. I'd like this field to be set to GETDATE() whenever a record is updated. I've got this trigger: CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as update cp_shiptos set date_updated = GETDATE() Problem is, of course, there's no WHERE clause..yet. I don't know how to refer to the record that was updated.... for example: CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as update cp_shiptos set date_updated = GETDATE() where shipto_id = @THIS_ID I imagine there's some kind of builtin variable or something like that. How is this done? Thanks in advance.
View Replies !
Lookup && Update Record && Insert Record
Hi All, I am trying to create package something like that.. 1- New Customer table as OleDB source component 2- Lookup component - checks customer id with Dimension_Customer table 3- And if same customer exist : I have to update couple fields on Dimension_Customer table 4- if it does not exist then I have insert those records to Dimension_Customer table I am able to move error output from lookup to Dimension_Customer table using oledb destination but How can I update the existing ones? I have tried to use oledb command but somehow it didnt work my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup) I will be appreciated if you can help me...
View Replies !
Update A Record Based Of A Record In The Same Table
I am trying to update a record in a table based off of criteria of another record in the table. So suppose I have 2 records ID owner type 1 5678 past due 2 5678 late So, I want to update the type field to "collections" only if the previous record for the same record is "past due". Any ideas?
View Replies !
UPDATE Trigger On Current Row???
Anyone familiar w/ the syntax for creating a SQL 2k UPDATE trigger on tableABC which will set the row's LAST_UPDTD_TIMESTAMP to GETDATE() whenever this row is updated? FYI, I have a UNIQUE CLUSTERED KEY on tableABC comprised of: open_date timestamp not null seq_no int identity (1,1) not null Our MS-Access appl. displays a row to a user (via a form) and allows changes to occur. I'd like to create a TRIGGER on the table which will dynamically update the row's LAST_UPDTD_TIMESTAMP w/ the value sourced in GETDATE() thx... BT
View Replies !
Trigger To Update One Record On Update Of All The Tables Of Database
hi! I have a big problem. If anyone can help. I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time. I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database. But i don't know exactly how to do the coding for this? Is there any other way to do this? can DBCC help to retrieve this info? Please advise me how to do this. Thanks in advance. Vaibhav
View Replies !
Delete Record
please helpi was trying to delete a specific record on a table but it still showsthe same record that should be deleted. i dont know how.
View Replies !
Delete Record From SS7
I accidently made a duplicate record and I am unable to delete it. Everytime I try to delete, it gives me an error message saying: Insufficient oe incorrect key coloumn information; too many rows affected by the update. I am unable to delete either records. Is there another way of accomplishing this task. Thanks for your help in advance. Bud
View Replies !
Delete A Record
Hi, How can i delete a record from a table . table contains approx 3 million data and when i execute a query for delete it give error message from transaction log space problem. Is there any way to stop the log in transaction log and delete the record. thanks
View Replies !
Cant Delete A Record
Hello i am new to MS SQL. Just moved in from MS Access. I used to program in VB6. mY Question is when i attemp to delete a record the followin error message displays "Key column information is insufficiant or incorrect.Too many rows were affected by the update." I have put the as code for the 'DELETE Record' command button in VB6 as follows. adomain.Recordset.Delete adAffectCurrent Please help me to safley delete a record from the SQL database using VB6.
View Replies !
Best Way To Delete A Parent Record
whats the best way to delete a parent record? for now what i am doing is to have a special delete page (means not DetailsView/GridView) just to delete parent records so user can see what child records will be deleted too. is the a better way?my code below: The T-SQL Stored ProcedureALTER PROCEDURE dbo.DeleteInventory ( @InventoryID int ) AS DELETE FROM Booking WHERE InventoryID = @InventoryID DELETE FROM Inventory WHERE InventoryID = @InventoryID The code in the Code Behind to execute when a button(delete) click Try Dim temp As Integer = SqlDataSource4.Delete If temp > 0 Then lblDeleteDtatus.ForeColor = Drawing.Color.Blue lblDeleteDtatus.Text = temp & " Records/Rows Deleted." Else lblDeleteDtatus.ForeColor = Drawing.Color.Orange lblDeleteDtatus.Text = "No records Deleted!" End If Catch ex As Exception lblDeleteDtatus.ForeColor = Drawing.Color.Red lblDeleteDtatus.Text = "An Error Occured.<br />" & ex.Message.ToString End Try any better methods anyone wants to share?
View Replies !
Can't Delete Single Record? HELP!
OK, This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? :confused: :confused: Here's the command I'm executing: DELETE FROM Invoices WHERE InvoiceID = 153345 Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot. TIA
View Replies !
Left Only 1 Record... Delete The Others
Hi everyone... I have a problem on deleting a record. I accidentally duplicate the record. I need to delete other records and left only 1 record based on each date and employeenumber. supposing i have a table hrempshiftschedule i have 3 fields... empno, shiftdate, and shifttype sample data... empno shiftdate shifttype 1 1/1/2008 S1 1 1/1/2008 S1 2 1/1/2008 S2 2 1/1/2008 S2 2 1/1/2008 S2 2 1/2/2008 S2 2 1/2/2008 S2 2 1/3/2008 S3 3 1/1/2008 S3 4 1/4/2008 S4 4 1/5/2008 S5 expected output.... empno shiftdate shifttype 1 1/1/2008 S1 2 1/1/2008 S2 2 1/2/2008 S2 2 1/3/2008 S3 3 1/1/2008 S3 4 1/4/2008 S4 4 1/5/2008 S5 pls help and guide me.... thanks RON ________________________________________________________________________________________________ "I won't last a day without SQL"
View Replies !
Delete Duplicate Record
Hi , How can i delete the duplicate record from a table use Northwind create table Emp (Ecode char(2), Ename char(10)) Insert into Emp(Ecode, Ename) values('A1','A') Insert into Emp(Ecode, Ename) values('A1','A') Insert into Emp(Ecode, Ename) values('A2','B') Insert into Emp(Ecode, Ename) values('A2','B') Insert into Emp(Ecode, Ename) values('A3','C') Insert into Emp(Ecode, Ename) values('A3','C') Insert into Emp(Ecode, Ename) values('A4','D') Insert into Emp(Ecode, Ename) values('A4','D') select * from emp order by Ecode Thanks ASM
View Replies !
Update SQL Column With Comparision Between Last And Current Records
My question is concerned with the three columns below (customerID, RepairDate, CompletedRepair (Yes or No). The column name "CompletedRepair " is blank initially. I need to update the CompletedRepair column with this logic below: - A customer comes to our store to fix their car, if we fix their problem on the first time and they don’t return later for this same issue, then the •CompletedRepair column = Y - If a customer needs to come back to our store to re-fix the same issue within 7 days windows based on the RepairDate on the previous transaction then •On the last return transaction: CompletedRepair = Y (example: RepairDate =6/12/2006) •On all previous transactions: CompletedRepair = N (example: RepairDate =6/8/2006, 6/9/2006, 6/10/2006) - If a customer needs to come back to our store to re-fix the same issue but out of the 7 days windows based on the RepairDate then •On the last return transaction: CompletedRepair = Y (RepairDate =6/12/2006) •On the previous transaction: CompletedRepair = Y (RepairDate =6/1/2006) Every time customer comes to for car repair shop for a new issue or an old issue, we create a new repair transaction in our SQL db. The update on the "CompletedRepair " column will be run every day. Today's records will be run against with last 7 days records (based on Repair Date) to check when customer has been really fixed: the last fix counted Y, the previous fix counted as N but comparison in only 7 days. In other words, a repair today is considered as a completed repair when comparing with last 7 day repairs but it might become not a completed repair if this same customer would come back within next 7 days for the same issue. The CompletedRepair column is dynamic column and is updated daily by using the logic above. Below is the expected outcome after we update the Completed Repair column: CustomerID Repair DateCompleted Repair ab1 06/12/06 Y ab1 05/28/06 Y ab1 05/18/06 Y ab105/15/06 N ab1 05/12/06 N Initially 5/12/06 had Y, when 5/15/06 transaction came, it took the Y and made the 5/12/06 become N. The 5/18/06 transaction did the same to 5/15/06 transaction, made itself Y and converted 5/15/06 into N. The 5/28/06 is Y because comparing with 5/18/06, it is out of 7 days window. The 6/12/2006 is Y because comparing with 5/28/06, it is out of 7 days window. ab2 06/02/06 Y ab2 05/28/06 N ab2 04/19/06 Y ab2 04/14/06 N The 4/14/06 transaction initially was Y, it became N when new transaction on 4/19/06 came. Same thing with transactions on 5/28/06 and 6/2/06 ab3 05/11/06 Y ab3 03/29/06 Y ab3 03/23/06 N ab3 03/12/06 Y The 3/23/06 was Y, when new transaction on 3/29/06 came, it became N and the new transaction is Y. The 5/11/06 is Y because comparing back to 3/29/06, they are out of 7 days window. ab4 05/11/06 Y This ab4 customer came to fix her car only one time and don't come back. We supposed the fix was sucessfully and so we mark the CompletedRepair as Y. I think that I would need to use SQL cursor or case statement for this but I really don't know how to start. Please advice and help me out. Any ideas and suggestion are really appreciated! If you need more information, please let me know! Thank you! Tracy
View Replies !
4011 When Trying To Delete Record From Sql Database
Hi i have a very annying problem that i cant seem to solve by myself. I have developed a content managment system for a webpage where people can manage the page. It's almost done except for the fact that i cant seem to delete records from my sql express database. To access the database i use an sql login in my code to delete witchever record is retrived from a querystring in the URL field. Below is a sample of my delete code when a button is pressed. protected void ButtonDelete_Click(object sender, EventArgs e) { string dID = Request.QueryString["dID"]; string myConnectionString = @"Data Source=SRVWEBSQLEXPRESS;Initial Catalog=se;User ID=xx;Password=xx"; SqlConnection myConnection = new SqlConnection(myConnectionString); string myDeleteQuery = "DELETE FROM drift WHERE dID = @dID"; SqlCommand myCommand = new SqlCommand(myDeleteQuery); myCommand.Parameters.AddWithValue("dID", dID); myCommand.Connection = myConnection; myConnection.Open(); myCommand.ExecuteNonQuery(); myCommand.Connection.Close(); Response.Redirect("list.aspx"); } When the i run the code on my development machine located at 10.12.0.80 and the server is located at 10.12.1.65 and this is where the databse is located. The strangest thing is that when i press my deletebutton while debugging in VS2008 on my devmachine the record is deleted! BUT when i run the code live on the server i get an error. See the below log file taken from the windows 2003 server application log. Event Type: InformationEvent Source: ASP.NET 2.0.50727.0Event Category: Web Event Event ID: 1314Date: 2008-04-07Time: 10:26:45User: N/AComputer: SRVWEBDescription:Event code: 4011 Event message: An unhandled access exception has occurred. Event time: 2008-04-07 10:26:45 Event time (UTC): 2008-04-07 08:26:45 Event ID: 8bdda96aeee44448b570891c593bdb3e Event sequence: 242 Event occurrence: 1 Event detail code: 0 Application information: Application domain: /LM/W3SVC/1015505475/Root-1-128520196339603398 Trust level: Full Application Virtual Path: / Application Path: C:wwwwebsite Machine name: SRVWEB Process information: Process ID: 5156 Process name: w3wp.exe Account name: NT AUTHORITYNETWORK SERVICE Request information: Request URL: http://website/cms/drift/editdrift.aspx?dID=19 Request path: /cms/drift/editdrift.aspx User host address: 10.12.1.1 User: webmaster Is authenticated: True Authentication Type: Forms Thread account name: NT AUTHORITYNETWORK SERVICE Could anyone help me solve this problem. Thanks.
View Replies !
Delete Record Selection Question
I'm having trouble with the below sql command. What I'm trying to do is to delete records from tblPhotoHeader table where there are no corresponding child records in tblPhoto. The select statement works correctly, I'm just not sure about how to apply the syntax to correctly select the records in the delete statement. Any help from the experts here would be helpful. Thanks Tom --------------------------------------------------------------------- DELETE FROM tblPhotoHeader WHERE Exists (SELECT tblPhotoHeader.photoid, photoOrderID, tblPhoto.photoType FROM tblPhotoHeader LEFT OUTER JOIN tblPhoto ON tblPhotoHeader.photoID = tblPhoto.photoID WHERE (tblPhotoHeader.photoOrderID = 143) AND (tblPhoto.photoType IS NULL))
View Replies !
Why Can't I Delete A Record With A Variable In The Querystring?
If I script the SQL statement with a constant, deleting the record from the database works. If I script the SQL statement to delete based on the WHERE clause being a variable name, it will not delete the record. The value being compared in the WHERE clause comes directly from the Sequel database. I have a dropdown box that is filled from the database. The dropdown1.selecteditem.text is placed in a variable. The script is to delete a record from the database where the table.name in the database equals the item name selected from the dropdown box. This querystring does not delete the record from the database: dim queryString As String = "DELETE FROM [Table1] WHERE ([Table1].[name] = 'variableCompareText')" This querystring does delete the recrod from the database: dim queryString As String = "DELETE FROM [Table1] WHERE ([Table1].[name] = 'John')" Why can't I delete a record with a variable in the querystring? Otherwise, you would have to always know in advance which record to specify rather than being deleted dynamically.
View Replies !
Howto: Delete Every Second Record If Duplicates
Hi.I have a "union" table which results of a union of two tables.Occasionally I could have duplicates, when the same PIN has been addedto both tables, albeit at different Datees/Times, such as:PINNameAdded Date100411A7/11/2007 10:12:58 AM100411A7/17/2007 10:54:23 AM100413B7/11/2007 10:13:28 AM100413B7/17/2007 10:54:39 AM104229C7/6/2007 2:34:13 PM104231D7/6/2007 2:34:25 PM104869E6/10/2007 11:59:12 AM104869E6/22/2007 2:40:18 PMThe question is - how can I delete by queries the first occurence(time-wise) of these duplicates - i.e. I would want to delete thefirst occurence of 100411 (A), the first occurence of 100413 (B), andthe first occurence of 104869 (E) in the example above - records C andD show only once, so they are fine.Is there a MsAccess solution ? Is there a SQL-server solution ?Thank you very much !Alex
View Replies !
Multiple Record Delete Via Join
Hi, I have a table with a large number of records that I need to delete, before attempt to perform the delete I also archived the records to another table. So I need to delete all of these selected records stored in the archive table from the main table. I can now reference all the records that qualify for the delete in the main table by performing a join on the archive table like so: select * from my_main_table a join my_archive_table b on a.distinct_id=b.distinct_id and a.surrogate_key=b.surrogate_key and a.identifier=b.indentifier So all the records check out to be the ones I'd like to perform a delete on but I just can't figure out how to perform a delete of the records with little or no change to the existing query. Obviously something like this won't work: delete from my_main_table a join my_archive_table b on a.distinct_id=b.distinct_id and a.surrogate_key=b.surrogate_key and a.identifier=b.indentifier Though it would be nice if it did.:D So my question is how would I use the existing query with some modification to delete only the records that this query returns. I've tried selection of records in the main table based on the existing records in the archive table but it can return a higher number of records than what I know is expected. I actually need the join specified to be in place to do it. Can anyone render any assistance on this one??? I would certainly appreciate it. Thanks.
View Replies !
Why I Can Not Delete Record Rows With OLE DB Interface?
In a C++ project, I implement a component of accessing database with programming in ole db com interface. I delete many rows in following style: IRowsets::RestartPosition( NULL ); IRowsets::MoveNext( ) do { IRowsetsChange :: DeleteRows( DB_NULL_HCHAPTER, 1, &(m_hRow), &nStatus ); }while( IRowSet::MoveNext( 0 ) ); The Problem: In this style, I only delete two records. while use ' IRowSet::MoveNext( 0 ) ' to get the third records, the result is END_OF_RECORDSET. Why I only delete two rows? Why I do not get the third row? Thank you!
View Replies !
Ms Sql 2005 Delete Record Problem
I'm facing problem when delete a record in the ms sql 2005 . The error message i got is stated below: No rows were deleted A problem occured attempting to delete row 1304. Error: .Net Sql Clint Data Provider Error Message: Possible index corruption detected. Run DBCC CHECKDB. Correct the errors and atempt to delete the row again or press ESC to cancel the changes. just certain record i can not delete and face this problem. i detach the database and attached in the ms sql 2000, and select the same record to delete. there is no problem at all. i wander is it ms sql 2005 bugs? can anyone tell me how to fix it .. how to to use the DBCC CHECKDB ?? thanks alot.... P/s : if i posted at the wrong section just let me know, I'm beginner in this forum here ... thanks...
View Replies !
Single Statement To Delete Record Into More Tables
Hi , I little question for you ... is it possibile to write a SQL statement to delete records in several tables at the same time? For example if I've two tables involved by join DELETE <...> from Customers A INNER JOIN CustomerProperties B ON A.CustomerID=B.CustomerID I Must use two statement to remove records from both the tables? Thx
View Replies !
Create Trigger Which Wil Do Event Before Delete A Record..
Hi There, I have created a trigger which supposingly will do event before delete the record from its own table.unfortunately when i try delete the record to test it whether it will do the event (inserting some records to another table), i found that it was not doing the event like i wanted to be. :(the trigger is as below :=======================CREATE TRIGGER TG_D_AGENT ON dbo.AgentFOR DELETEASbegindeclare @vAgentID as numeric,@vAgency as varchar(50),@vUnit as varchar(50),@vAgentCode as varchar(50),@vName as varchar(50),@vIC as varchar(14),@vAddress as varchar(100),@vContactNumber as varchar(50),@vDownlink as varchar(50),@vGSM as varchar(10),@vAM as varchar(10),@vDeleted_date as datetime set @vDeleted_date = convert(datetime, convert(varchar(10) , getdate(),103),103)declare cur_policy_rec CURSOR forselect AgentID,Agency,Unit,AgentCode,[Name],IC,Address,ContactNumber,Downlink,GSM,AM from insertedopen cur_policy_recfetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM WHILE @@FETCH_STATUS=0BEGIN INSERT INTO [Agent_history] (AgentID,Agency,Unit,AgentCode,Name,IC,Address,Con tactNumber,Downlink,GSM,AM,Deleted_date) VALUES(@vAgentID,@vAgency,@vUnit,@vAgentCode,@vNam e,@vIC,@vAddress,@vContactNumber,@vDownlink,@vGSM, @vAM,@vDeleted_date)fetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM enddeallocate cur_policy_recend===============================in oracle , i normallly can do something like this...====================================CREATE TRIGGER TG_D_AGENT ON dbo.AgentBEFORE DELETE ON dbo.Agent FOR EACH ROWbeginIs that such thing function like 'BEFORE' in MS SQL SERVER 2000, coz in sql server im not sure they do have or not. Plz someone help me on this...realy appreciated if can!
View Replies !
Delete Record Where Values Exist, Else Create Them..
Dear friends, I am thinkking there has to be away to do this in a SQL statement. can't you do some kind of... Create Procedure sp_update_users @User_Id Int, @Title_Id Int AS UPDATE USERS DELETE WHERE @User_ID = User_Id and @Title_Id = @Title_Id ELSE INSERT INTO USERS VALUES (@User_ID,@Title_Id;)
View Replies !
Edit/Delete Record In Reporting Services
Is there any way I can achieve Edit and Delete functionality at the record level in Reporting Services? Something similar to Datagrid Edit, Delete functionality per record. Any help would be highly appreciated. Regards
View Replies !
-1.#IND Value In A Foat. Unable To Alter Or Delete Record
Somehow I got the value -1.#IND into several records in a table in SQL 2000 DB. I assume that it is a kind of €œNot A Number€?? The problem I am facing now, is that I can not modify the value to a legal value. Nor can I delete the record that holds the number. I get the error message: €œAnother user has modified the contents of this table or view, the database row you are modifying no longer exists in the database. Database error: €˜[Microsoft][ODBS SQL Server Driver][SQL Server] A floating point exception occurred in the user process. Current transaction is cancelled.€? Any suggestions or comments?
View Replies !
SQL 2000 Corrupted Record! Can't Delete Table
Hi All, I have just one corrupted record on a table: I copied everything else to another table but I can't delete or rename the old one! Is restoring the whole DB my only way out there? Any help or suggestion would be much appreciated! Cheers
View Replies !
Slective Duplicate Record Delete/Identify Question
HI, I have a table similiar to this: Division Type Section Location ModificationDate ------- ----- ------- -------- --------------- 3 4 2 Los Angeles 2/1/05 3 4 2 New York 2/4/05 4 5 1 Los Angeles 2/4/05 3 4 2 Seattle 2/7/05 4 5 1 Dallas 2/6/05 3 4 4 London 2/3/05 I need to remove duplicate records that have the same division,type,section pair by slected the most recent modification date and keeping the data in the rest of the columns. The results of what I want to do would look like: Division Type Section Location ModificationDate ------- ----- ------- -------- --------------- 3 4 2 Seattle 2/7/05 3 4 4 London 2/3/05 4 5 1 Dallas 2/6/05 Does anyone have idea how I would do something like this? Thanks.
View Replies !
How To Delete A Record In The Data Base Using Linq(sql Server 2005 Express Edition Database)
following is the code which i am trying to use it throws an error and dosent work. error details: Unable to cast object of type 'System.Data.Linq.DataQuery`1[tbl_temp_bank]' to type 'tbl_temp_bank'. source code(aspx.vb file) Dim c As New temp_business_bankDataContextDim tag = From t In c.tbl_temp_banks Where t.TIN = Convert.ToInt32(tin.Text) Select t c.tbl_temp_banks.DeleteOnSubmit(tag) c.SubmitChanges() please can some one help me with this.thanks
View Replies !
|
TRACKER
