Current KeyID From Sql Update
Mar 6, 2005When I return from executing a sql storedprocedure where I insert a record, where can I find the keyID?
View 2 Replies
ADVERTISEMENT
SQL: UPDATE, DELETE Current Record Only
May 6, 2006Well, 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?
How To Update Current Date And Time Into DB
Mar 16, 2015I am new to SQL and want to understand how to update current date and time into DB2 in SQL.
View 1 Replies View RelatedHow To Retrieve Current Userid To Update Database
Feb 12, 2008I 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 RelatedUpdate SQL Column With Comparision Between Last And Current Records
Jun 13, 2006My 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
Update Current Column Gnumpics By A Numeric Value
Oct 9, 2013I 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.
Auto Update A Field With The Current Date/time
Nov 2, 2006How can I set a column in a table to auto update the date and time everytime something in that row is updated or when the row is first added? Thanks ahead for the help,Jason
View 4 Replies View RelatedEdit / Update Query - Change Current Sales Information For A Specific Product
Apr 11, 2012I am currently working on a website that deals with sales of products. For one of my pages for the website I need it to be able to change the current sales information for a specific product.
The top part of the following code selects the specific product however I cannot get the update query to work.
Code:
$describeQuery = "SELECT p.ID, p.NAME, dt.[Year], dt.[Month], dt.SalesVolume FROM Products p
join
(select ProductCode, sum(SalesVolume) as SalesVolume, [Year], [Month] from MonthlySales
group by ProductCode, [Year], [Month])dt
on dt.ProductCode = p.ID WHERE [NAME] = '$desiredProduct' AND [Year] = '$desiredYear' AND [Month] = '$desiredMonth'";
$editQuery = "UPDATE MonthlySales SET SalesVolume = '$NewSales' WHERE ID = '$desiredProduct' AND Year = '$desiredYear' AND Month = '$desiredMonth'";
$query = sqlsrv_query($link, $describeQuery);
Default Date(current Date) Function W/ Update?
Mar 10, 2008I have a reference table that currently has no web front-end. It's a small table(<10 rows) that's not going to change very often (maybe once every few months).
We manually update rows on the table via the GUI table interface in Enterprise Mgr., not in T-SQL.
What I'd like to do is have SQL Server automatically update the "Last_Modified" column with the current timestamp. I can do it on an Insert using the GetDate() function, but if I update a row, this doesn't work.
Is there a function I can use that can auto-populate for both insert and updates?
Get Rows Between Current Date And Current Time
Jun 8, 2008I've this query
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
Let's say, current date is 8 AUG 2005 and current time is 2045
So, i will get
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
206 | 8/1/2005| 1950 | 88
...
...
207 | 8/7/2005| 1845 | 77
208 | 8/7/2005| 2255 | 77
209 | 8/7/2005| 2140 | 77
Can someone can show me to filter data between
t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime
If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
...
...
207 | 8/7/2005| 1845 | 77
I only have this query,
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
lack of idea to put the TIME condition.
Plz help me..
Check If Date Is Current Date And Stay Current
Apr 25, 2008Hi,
I'm making some sort of application where people can add their resume.
They also need something to add places where they have worked or currently are working.
I have a form where they can add this and i add this experience using the following stored procedure:
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_Insert_Experience]
@ExperienceId uniqueidentifier,
@ExperienceEmployee nvarchar(100),
@ExperienceFrom datetime,
@ExperienceUntil datetime,
@ExperienceQualifications nvarchar(250),
@ExperienceTechnologies nvarchar(250),
@ExperienceTasks nvarchar(250)
as
insert into Experiences
values(@ExperienceId,@ExperienceEmployee,@ExperienceFrom,@ExperienceUntil,@ExperienceQualifications,
@ExperienceTechnologies,@ExperienceTasks);
It must be possible to add the place where they currently are working. Then the ExperienceUntil has to be something like still going on. Then I decided that the user then had to set the current date.
But what I want is the following, I want that the ExperienceUntil keeps updating automatically, like everytime i get that record, it has to have current date.
Is this possible ?
But if the ExperienceUntil isn't the current date , it just had to take the supplied parameter date.
Current Row No?
Mar 14, 2007i know there is a function to count number of rows in a group, but is there anyway of numbering each row?
So that I can use =Last(ReportIems("norow") to find the last row on the page????
Thanks
While And Current Value
Apr 18, 2008
I have following query
....
while ( select fieldID from table where age <> 2)
begin
how I can get current value of "fieldID" for future calculations or operations?
like
insert into othertable values (fieldID, .....)
end
Get The Current Year Using SQL
Aug 13, 2004Hi everyone,
Currenly, I'm getting the current year in my code, and passing this to my stored procedure.
I'm sure there's a way just to get the current year (ie '2004') just by using SQL.
I was just about to post this question here when I did a bit more digging around and found my solution:
DATEPART(yyyy, GETDATE())
This will get the year.
Hope this helps someone searching for the answer!
Appending Current Row ID
Dec 19, 2005I am trying to append the current row ID to a string I am trying to
insert via a sproc. I have retrieved the @@Identity and I am passing it
into a class with a parameter and calling it using:
Listings.UpdateDB AddNewListing = new Listings.UpdateDB();
AddNewListing.InsertListing (Bathrooms.Text, Bedrooms.Text,
Description.Text, Features.ToString(), Address.Text, Price.Text, FN);
I would like to add the current row ID to "FN" like:
Listings.UpdateDB AddNewListing = new Listings.UpdateDB();
AddNewListing.InsertListing(Bathrooms.Text, Bedrooms.Text,
Description.Text, Features.ToString(), Address.Text, Price.Text, FN +
ID);
Thanks in advance,
Justin.
Current SQL Statement
Sep 25, 2001My backup fails in the night, because of a non logged operation being executed at the same instant. I am interested in finding out the statement that is causing the problem. What would be the best way to do that?
View 2 Replies View RelatedCurrent Activity
Apr 17, 2000I have registered a server on to my client machine successfully,but I am not finding the current activity item in the management folder on EM.How do I see the current activity on the server.Do I need to be SA for that server?My machine has NT workstation with SQL server 7.0 on it.Any one to help?
View 1 Replies View RelatedCurrent Activity Not Available? 6.5
Aug 12, 1999When viewing Current Activity, nothing shows up.
If i run sp_who, the return is normal.
If I run sp_who2, the following message is returned. (it also is returned randomly while using function in EM such as Backup/Restore.)
Msg 268, Level 16, State 1
You cannot run SELECT INTO in this database. The DBO would have to run sp_dboption to enable this option.
What is it??? It's buggin me......
Dano
p.s. I remember a similar problem in the past, it had to do with turning truncate on checkpoint in one of the system databases msdb/master/tempdb or something like that. I turned off all Trunc. and Select/Bulk options on these...
Current Database
Oct 19, 1999Hi,
Is there any command that gives the current database name.
Regards
Kishore
Can Not See Current Activities
Mar 31, 1999Today when I opened current activity window on my SQL6.5 server, I could not able to see any activities listed. What will be the problem?
But sp_who works fine.
Srini
1st Day And Last Day Of The Current Month
May 15, 2002l want to run my procedure every month,but it must look at the 1st and the last day of the month.So that when l use my where clause l'll
say where 1st and last day between @startdate and @enddate. How do l do that?
collections_hexagon '2002-05-12','2002-05-12'
Alter Procedure Collections_Hexagon
(
@Startdate datetime,
@Enddate datetime
set @Startdate = 1stdayof currentMonth
set @Startdate = lastdayof currentMonth
)
How To Get Current Database Name?
Aug 4, 2006How to get current database name using stored procedure?
View 2 Replies View RelatedCurrent Database Name...
Mar 26, 2004Hi,
I want to know a sql query that would show the current database name.
I have tried looking into the online books but couldn't find the right query.I hope u can do better.This is urgent.Thanxs. -- Vijay
Problem Using CURRENT
Apr 11, 2008Hello,
I have a column in one of my tables called CURRENT. However, when I try to do a select on this column I get an error, I assume because CURRENT is also a SQL term.
Is there any way I can rename my CURRENT column (maybe using an alias?) within my SQL script? I am unable to change the column name within the database.
Thanks,
Jon
Current Week
Apr 30, 2008How can I filter Current Weeek Date? For example today week dates starts on April 27 and ends on May 3. Thanks
View 20 Replies View RelatedGet The Current Status
Jun 5, 2008Hello All,
Can someone help me with this query?
I have the following table and data structure
Table1: Orders
Field1: OrderID
Table2: OrderStatus
Field1: OrderStatusID
FIeld2: OrderID
Field3: StatusID
Field4: InsertDate
Sample Data:
Table1:
11
12
Table2:
1,11,1,6/1/2008
2,12,1,6/1/2008
3,11,2,6/2/2008
4,12,2,6/2/2008
5,11,3,6/3/2008
6,11,4,6/4/2008
Wanted Results:
11,4,6/4/2008
12,2,6/2/2008
How To Get Number For Current Row
Nov 25, 2013I am trying to get the current row number using this:
SELECT ROW_NUMBER() OVER (ORDER BY QUANTITY) FROM RDR1 WHERE Row = CurrentRow
However i got error message says row is an invalid column name.
How To Query For Current Day
Sep 21, 2014SELECT c.cust_fullname AS Customer, c.cust_membership_id AS Account#, SUM(t.c_amount) AS ChargeTotal
FROM Transactions AS t
INNER JOIN Customers AS c ON t.s_ref_num = c.cust_id
where s_credit_tran_type = 'House Account' and b_cancel = 0
GROUP BY c.cust_fullname, c.cust_membership_id
I need this simple query to run automatically every night at 11:30pm, everyday, but only for the Current Day's business.My file will output to .CVS format for import into Quickbooks. I'd love to have it import directly INTO Quickbook's, but I am not at that level yet..
Current Date
Dec 6, 2005Is there a system word for the current date in a stored procedure or do I have to pass it in as a parameter ?
ie
BK_DateRequired <= Today
SQL - Current Year
May 30, 2006Hello,
I'm trying to write a function which will give me, among other things, the current year.
I'm brand new to SQL so don't really know how to use functions properly yet, although from my VB knowledge, the structure looks very similar.
I've tried SELECT GETDATE() but it gives me an error about getdate not being valid inside a function.
How can I get this to work please ?
Cheers,
J.
Current Date
Jun 26, 2007Hello,
I'm new to SQL server and the SQL language itself and I'm having a bit of problem.
Is it possible to obtain the current date from the system?
Something like 'sysdate' or anything like that.
I want to compare the current date with a date stored in the database but I can't seem to find the wright SQL server keyword, or is there none?
Many thanks
Current Date
Dec 20, 2007Does anyone know how I can get the current date inserted to a SQL query to only get results of today?
My SQL is:
SELECT Business.BusinessCODE, Business.Name, Business.BusinessNumber, Business.BusinessStatusCODE, Business.Done, Business.StartDATE, Business.EndDATE, Business.TotalPrice, Business.Contribution, Business.BusinessProcessCODE, Customer.Name1, CaesarUser.FirstName, CaesarUser.Surname, BusinessHistory.UpdateDATE
FROM Movex_SMS_50_Server.dbo.Business Business, Movex_SMS_50_Server.dbo.BusinessHistory BusinessHistory, Movex_SMS_50_Server.dbo.CaesarUser CaesarUser, Movex_SMS_50_Server.dbo.Customer Customer, Movex_SMS_50_Server.dbo.relCaesarUserBusiness relCaesarUserBusiness
WHERE Business.CustomerID = Customer.CustomerID AND relCaesarUserBusiness.BusinessID = Business.BusinessID AND BusinessHistory.BusinessID = Business.BusinessID AND relCaesarUserBusiness.CaesarUserID = CaesarUser.CaesarUserID AND
((BusinessHistory.Description='Créé' OR
BusinessHistory.Description='Created' OR
BusinessHistory.Description='Creado' OR
BusinessHistory.Description='Erstellt' OR
BusinessHistory.Description='Creato')AND
((BusinessHistory.updatedate = TODAYS DATE)) AND
(Customer.EmployeeCountCODE=0xEC6D3BDB8B6FD511AC240006294308D9 OR
Customer.EmployeeCountCODE=0xFC673BDB8B6FD511AC240006294308D9))
ORDER BY CaesarUser.Surname, Customer.Name1
Add To Current Data
Jan 14, 2008I have a table and that I need to update one column by add to the front of each row.As an example I currently has "Mydata" I want to add 2008 to all rows to show "2008Mydata". How do I add these 4 characters to the front of each row.
Thank you!