SQL: UPDATE, DELETE Current Record Only

May 6, 2006

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 2 Replies


ADVERTISEMENT

How Do I Pass A Value In The Next Record To The Current Record?

Sep 20, 2007

Hey Forum,
Below is a solution for passing a previous value (Height) to the current record in a view using two related tables (Plant= ID PK and plantHeight = ID FK) However, I was wondering how I could also do the reverse, that is, pass a next value to the current record.

View 2 Replies View Related

Delete Syntax To Delete A Record From One Table If A Matching Value Isn't Found In Another

Nov 17, 2006

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 6 Replies View Related

Delete Record Based On Existence Of Another Record In Same Table?

Jul 20, 2005

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 3 Replies View Related

Get Current Entered Record

Nov 20, 2007

Hi every one
I want to get the currently entered or updated record in the database table by using SQL Query or stored procedure.
 Thanx in advance
Take care
Bye

View 3 Replies View Related

Trigger - Current Record ?

Feb 5, 2004

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 2 Replies View Related

How Do I Make Sure Only One Record Is The Current Issue?

Jun 22, 2007

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 9 Replies View Related

T-SQL (SS2K8) :: Identifying Current Record

Mar 6, 2014

I have a snapshot table of about 15 million records in the form of:

InvoiceIDLineItemIDSnapshotDateAmount
1 1 20140101 12
1 2 20140102 14
1 3 20140103 17
2 1 20140101 10
2 2 20140102 5
1 2 20140105 15
1 3 20140105 20

I want to create an additional column called Current as shown below:

InvoiceIDLineItemIDSnapshotDateAmount Current
1 1 20140101 12 1
1 2 20140102 14 0
1 3 20140103 17 0
2 1 20140101 10 1
2 2 20140102 5 1
1 2 20140105 15 1
1 3 20140105 20 1

How can we write a query to achieve this while keeping in mind:

- We do not want to do unnecessary record lookups and Updates
- We only update records that corresponds to new entries. For example, we should not touch the record for InvoiceID = 2 in the above example

View 6 Replies View Related

Compare Previous To Current Record Row

Mar 3, 2014

My current code returns account_number with multiple start_date regardless of the value is same or not. However, I would like to get only the account number when the value on start_date is different within same account_number.

select
acct_number
count(start_date) from table_A
group by acct_number, start_date
having(count(start_date) > 1)

View 5 Replies View Related

How To Make A Trigger Refer To The Current Record

Jul 8, 2004

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 2 Replies View Related

How Do I Display Current SQL Server Record Number

Mar 21, 2008

Do anybody know how can I find or display to the current SQL server 2005 record number (eg. 10 of 1600) on a VB 2005 form label. The BindingNavigator on the form has been deleted. Thanks.

View 6 Replies View Related

Update A Record Based Of A Record In The Same Table

Aug 16, 2006

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 5 Replies View Related

Lookup &&amp; Update Record &&amp; Insert Record

Mar 26, 2008

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 3 Replies View Related

SQL Server 2008 :: Count How Many Records Within 6 Months From Current Record Date

May 27, 2015

My data has 2 fields: Customer Telephone Number, Date of Visit.

Basically I want to add a field ([# of Visits]), which tells me what number of visit the current record is within 6 months.

Customer TN | Date of Visit | # of Visits (Within 6 month - 180 days)
1111 | 01-Jan-2015 | 1
1111 | 06-Jan-2015 | 2
1111 | 30-Jan-2015 | 3
1111 | 05-Apr-2015 | 4
1111 | 07-Jul-2015 | 3

As you can see, the last visit would counts as 3rd because 180 days from 07-Jul-2015 would be Jan-8-2015.

View 3 Replies View Related

Trigger To Update One Record On Update Of All The Tables Of Database

Jan 3, 2005

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 10 Replies View Related

Current KeyID From Sql Update

Mar 6, 2005

When I return from executing a sql storedprocedure where I insert a record, where can I find the keyID?

View 2 Replies View Related

Delete Record From SS7

Jul 31, 2000

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 1 Replies View Related

Cant Delete A Record

Jan 1, 2006

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 4 Replies View Related

Delete A Record

Dec 14, 2007

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 5 Replies View Related

Delete Record

Jul 20, 2005

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 3 Replies View Related

How To Update Current Date And Time Into DB

Mar 16, 2015

I am new to SQL and want to understand how to update current date and time into DB2 in SQL.

View 1 Replies View Related

Best Way To Delete A Parent Record

Jul 22, 2007

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 2 Replies View Related

Can't Delete Single Record? HELP!

May 28, 2004

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 12 Replies View Related

How To Delete A Record Which Is Referential To Oth

Jun 6, 2008

Hi, I tried to delete a record in tblA which has an ID in tblB, i got error msg saying referential problem, please tell me what should i do? thanks.

View 2 Replies View Related

Delete Duplicate Record

Oct 23, 2006

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 7 Replies View Related

Left Only 1 Record... Delete The Others

Jan 22, 2008

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 6 Replies View Related

Recover Delete Record

Feb 22, 2008

how can i recover delete record from a table. is possible to find who delete record from a table.

View 3 Replies View Related

How To Retrieve Current Userid To Update Database

Feb 12, 2008

 I am making a web site in which i need to update a database with certain values which include the current user who invoked the insert command so how to retrieve the current userID 

View 4 Replies View Related

Update SQL Column With Comparision Between Last And Current Records

Jun 13, 2006

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 4 Replies View Related

Update Current Column Gnumpics By A Numeric Value

Oct 9, 2013

I have a table named galleryindex which contains rows of photo gallery names along with an associated id (gsid).

example:
gsid (assigned ID), gnumpics (# of photos), gname (gallery name)
=================
43, 133, our summer visit
493, 53, camping photos

When someone uploads more photos I do the following to recalculate the NEW number of photos for the gallery.

SELECT gnumpics FROM galleryindex where gsid= 43
(select current # of photos from location 43 - our summer visit)
in ASP classic I then do this:
sb = objrs("gnumpics")
sb = sb + totupl
SQL = "UPDATE galleryindex SET gnumpics =" & sb & " where gsid= '" & fnum & "'"

to update the gallery with the new amount of photos which is obtained by pulling the old value into SB, and adding "totupl" (total pics uploaded) to it and then updating the values.In SQL speak it would be like this if 20 photos were just uploaded:

SELECT gnumpics FROM galleryindex where gsid= 43
(results in 133 photos)

UPDATE galleryindex SET gnumpics =153 where gsid= 43
(write back 133 + 20)

Basically I'm trying to update the current column gnumpics by a numeric value and I know there's an easier way then pulling the current, adding to it and then writing it back to SQL.

View 2 Replies View Related

4011 When Trying To Delete Record From Sql Database

Apr 7, 2008

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 3 Replies View Related

Why Can't I Delete A Record With A Variable In The Querystring?

Dec 17, 2004

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 2 Replies View Related

Delete Record Selection Question

Sep 28, 2005

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 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved