I would like to return the the Primary Key of the row altered from this query - what changes do I need to make?
UPDATE Members SET LastLog = @time WHERE UserName=@user AND Password=@pass;
Thanks in advance,
Hi All,
I am using a trigger. I want to get the data of a row before updating inside this trigger and insert it into a backup table. Please anybody help me. Example with code is highly appreciated.
Thanks in advance.
I'm using a Merge statement to update/insert values into a table. The Source is not a table, but the parameters from a Powershell script. I am not using the Primary Key to match on, but rather the Computer Name (FullComputerName).
I am looking on how-to return the Primary Key (ComputerPKID) of an updated record as "chained" scripts will require a Primary Key, new or used.As an aside: the code below does return the newly generated Primary Key of an Inserted record.
CREATE PROCEDURE [dbo].[usp_ComputerInformation_UPSERT](
@FullComputerName varChar(50) = NULL
,@ComputerDescription varChar(255) = NULL
,@ComputerSystemType varChar(128) = NULL
,@ComputerManufacturer varChar(128) = NULL
[code]....
Is there a way to get asp.net to return the primary key in SQLServer after INSERT as I need to re-name an uploaded file with the primary key. Thanks.
View 10 Replies View Related Stepping thru the code with the debugger shows the dataset rows being deleted.
After executing the code, and getting to the page presentation. Then I stop the debug and start the
page creation process again ( Page_Load ). The database still has the original deleted dataset rows.
Adding rows works, then updating works fine, but deleting rows, does not seem to work.
The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.
cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId); rowCountDb = cDependChildDs._ClientDependentChild.Count; for (row = 0; row < rowCountDb; row++) { dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0]; dr_dependentChild.Delete(); //cDependChildDs._ClientDependentChild.Rows.RemoveAt(0); //cDependChildDs._ClientDependentChild.Rows.Remove(0); /* update the Client Process Table Adapter*/ // cDependChildTA.Update(cDependChildDs._ClientDependentChild); // cDependChildTA.Update(cDependChildDs._ClientDependentChild); }
/* zero rows in the DataSet at this point */ /* update the Child Table Adapter */ cDependChildTA.Update(cDependChildDs._ClientDependentChild);
Hi,I've got a stored procedure that's inserting data into a sql database fine. The only problem is that I'm not sure how to read back the value of the auto increment field that was just generated by the insert (e.g the id field). Any help appreciated.
View 1 Replies View RelatedIs there a command that will tell me the number of rows that are updated in a statement. I would like to put this in an Stored Procedure and pass the #rows updated back out.
View 1 Replies View Relatedso i have an update query that is updating 50k rows per batch. my question is how do i campture how long it is taking to update 50k rows per batch.
View 3 Replies View RelatedCan this be done easilly through a stored proc?
View 1 Replies View Relatedis there a Trigger that monitors updated rows? I would like to run a
insert into sometable (pkcolumnname)
select pkcolumnname from monitoredtable where ????
"i dont know what goes here to only get value of rows that have been updated"
HI, I am wondering if it is possible to retreive this information without using row count transform. Can I get the # of rows inserted/updated or deleted by destination from the log?
Thank you,
Ccote
This was a usual day today in office and i was working on a requirement in which i was needed to fetch the total number of rows effected by an update query, so I asked my best code mate "Google" and to my surprised there was not enough correct answers at least the one i was looking for.There were suggestions that you can use a select statement for the updated rows and make it like a select (count) which works fine, but just looking into the SQL server books online, it shows that there is even a better way to do it.After the update statement in my stored procedure i used "@@ROWCOUNT" with a select statement and it works like a charm.so the little find for my first ever post on asp.net is that there is a better way to find the total updated rows by a query Example: DB: Northwind , Table Employeesupdate employees set extension='1234'select @@ROWCOUNT This will return 9 (default rows in this table) as the rows effectedHope this helps
View 2 Replies View RelatedHi,
I am trying to create a SSIS package, which will extract data from a SQL server view and populate the data in our local SQL server database tables. My objective is to get the data from the view such that only inserted and updated rows are fetched from the view.
Note: the view does not expose any updated date type of column thru which I can check. So I guess I have to compare each and every field with my destination table row's fields.
I would appreciate any suggestions on how to approach the problem.
Thanks in advance.
currently i m developing a web application with using sql server 2005
and i was testing yesterday a sql update query with sql server management studio.
in my update query i forgot to put where condition and now all the rows of table are updated. is there any solution to undo this and retrieve all rows back?
Regards Selena
how to track how many rows updated or deleted per day in a single table and load the information in another table .
View 7 Replies View RelatedI know 2008 MS SQL Server has a timestamp data type that adds date and time when the rows are inserted. Is there a way to automatically update the date and time when the rows are updated?
View 5 Replies View Relatedi am getting the above error on my database i have 2 rows with the same info on and another 2 with the same info on. example:
ID username password
1 bob bob
1 bob bob
1 john john
1 john john
I know this is a fault with ms sql 2005 however how do i fix it?
Ive found this link which explains everything but how do i start a query. I tried clicking on new query and copying the code. What is table1 meant to be? the database is dbl.tbl_admin. It wont find my database.
Im not sure how to do it anyway.
I need to change it though as its my admin password and Ive given it out to web design companys
http://geekswithblogs.net/allensb/archive/2006/07/27/86484.aspx
Can some 1 read the above page and give me full instructions, I dont know what im doing thanks
info@uktattoostudios.co.uk
Greetings!
I need to run a select statement that only returns 50 rows. How do I limit the amount of rows returned? Normally the query will return hundreds of rows but all I need is the first 50 it retrieves. I have looked in the BOL and can only find help with a block cursor not just a query.
Thanks a million
zachary
Using MS SQLServer 2000
Is there a way to create a query that will return only 1 row within a join for example:
select a,b,c,d
from tbl1
inner join tbl2 on top 1 tbl2.a = tbl1.a <-- return only the top record here...
I dont have too much of a problem with joins... however I was wondering if there was a mechanism to just specify what would be the top most record from a join clause.
thanks
Kevin
I have a query set up that returns the data that I would like, but Iwould only like the latest data for each vehicle number. The query Ihave set up isSELECT TOP 100 PERCENT dbo.vwEvents.EventName,dbo.luSessionAll.SessionName, dbo.luOuting.OutingNumber,dbo.luVehicle.VehicleName, dbo.luOuting.OutingID,dbo.tblOutings.OutingStartTime,dbo.tblSessions.Ses sionDate,dbo.tblSessions.SessionStartTimeFROM dbo.vwSessions INNER JOIN dbo.vwEvents ONdbo.vwSessions.Event = dbo.vwEvents.EventIDINNER JOINdbo.luSessionAll ON dbo.vwEvents.EventID =dbo.luSessionAll.Event INNER JOINdbo.luOuting ON dbo.luSessionAll.SessionID =dbo.luOuting.SessionID INNER JOINdbo.luVehicle ON dbo.luSessionAll.Vehicle =dbo.luVehicle.VehicleID INNER JOINdbo.tblOutings ON dbo.luOuting.OutingID =dbo.tblOutings.OutingID INNER JOINdbo.tblSessions ON dbo.tblOutings.[Session] =dbo.tblSessions.SessionIDGROUP BY dbo.vwEvents.EventName, dbo.luSessionAll.SessionName,dbo.luOuting.OutingNumber, dbo.luVehicle.VehicleName,dbo.luOuting.OutingID, dbo.tblOutings.OutingStartTime,dbo.tblSessions.SessionStartTime, dbo.tblSessions.SessionDateORDER BY dbo.luVehicle.VehicleName, dbo.tblSessions.SessionDate,dbo.tblSessions.SessionStartTime, dbo.tblOutings.OutingStartTimethis returns all the outings. I would like the outing that has, inorder of importance, the latest session date, latest session time andlatest outing start time. Outing start time can sometimes be <<Null>>but the other two always have values. How would I go about doing this?thanks in advance for any help
View 2 Replies View RelatedI have a stored procedure below that returns a table of coaches. It worked before now it does not. It returns nothing, in vistual studio 2005 and on my asp.net webpage. But when I execute it in query analyzer with just SELECT * FROM Coaches it returns the rows. There is no error just will not return what I need. I recreated the database and stored procedure still doing the same thing. Any ideas? Would this be on my server hosting side? ALTER PROCEDURE [dbo].[GetCo]ASSELECT * FROM Coaches
View 2 Replies View RelatedHi All:
My below sub in application is returning only the Header Row instead of the relevant rows I guess therez problem in my "str" Syntax. However I fail to understand where exactly is it faultering. Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Try
con.Open()Dim empID As String = txtEmpID.Text
Dim FName As String = txtFName.Text
Dim LName As String = txtLName.TextDim str As String = "Select Emp_ID, Emp_FName,Emp_LName,Emp_Address1,Emp_HNo,Emp_MNo from Emp_Details where Emp_ID='" & empID & " ' Or Emp_LName='" & LName & " ' Or Emp_FName=' " & FName & "'"""Dim da1 As New SqlDataAdapter(str, con)
da1.Fill(ds, "Emp_Details")
dgEmp.DataSource = ds
dgEmp.DataMember = "Emp_Details"
dgEmp.DataBind()
Finally
con.Close()
End Try
End Sub
Thanks in Advance for your quick help.
Regards,
Brandy
TotalSelected.Value = SqlDataSource1.SelectCommand = "SELECT COUNT(*) FROM tblNews";
the reason i am tring to do this is so if i can find out the amount of rows before sqldatasource selects for details view then i can make the sqldataesource select depends on total minus 5 so e.g. if total 200 then - 5 so i can select bottom 195 so it misses top 5 for details view any1 any ideas?
Thanks Andy,
Hi,
I created a temporary table inside a stored procedure called TmpCursor and the last time I include this..
Select * from #TmpSummary
GO
Inside my web page, I have the following code...
QrySummary = "Exec TmpCursor"
Set rsSummary = Server.CreateObject("ADODB.RecordSet")
rsSummary.Open QrySummary, cnCentral
cnCentral is my sqlconnection string..
This is the error I got when viewing the page
Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
BTW, the stored procedure works fine in Query Analyzer.
TIA
I was racking my brains trying to figure out why SomeCommand.ExecuteNonQuery() was not returning any rows...
SQL Server 2005 likes to put the SET NOCOUNT ON statement in every stored procedure you write. By hiding the count of records touched by your query, you also disable the results to be consumed by your application.
So I don't recommend using this statement for your stored procedures and ASP.NET applications, as this functionality is fairly critical for error trapping.
This question has been posted on the site before but I could not find any resolution....I want to return rows 11 - 20 from a query that returns 100 records without using a cursor or temp table.
The closest query I have found is a query that numbers the rows, but I can't seem to use rownumber in a between clause...
Use Pubs
SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY emp_id
Thanks,
Jim
I have two tables and I want to return data from both. Currently my select statement is returning just 1 child record for each parent record and I want to return all child records that match the parent record.
Here's a sample of my tables/data/etc.
t1
------------
speciesid | species
1 | Mammals
2 | Rodents
3 | Reptiles
t2
---------
animalid | animal
3 | Skink
3 | Iguana
3 | Rattlesnake
2 | Meerkat
1 | Hippo
1 | Elk
What I want to do is pull up a list of all the species and under each list all the animals currently listed under that species.
So the result I want should look like:
Mammals (Hippo, Elk)
Reptiles (Skink, Iguana, Rattlesnake)
Rodents (Meerkat)
so currently I have:
SELECT A.animalid, S.speciesid, A.animal, S.species from t2 as A, t1 as S where S.speciesid=A.animalid order by species
this works great, it's just that it only returns one animal instead of all of the animals. Any help would be appreciated.
Say you have a table that contains 1000 rows, you create a simple select statement with a where clause that returns 100 or so of those rows. Easy enough.
Now, let's say that you wish to modify that select so that out of the 100 rows that match the where clause criteria, you only wish to return 10 rows randomly (i.e., you could run this query multiple times and get different results). How exactly would you go about doing this, efficiently?
I've thought about creating a stored procedure that will query the initial 100 rows into a temp table with an additional column (to number the rows from 1 to 100). Then setting up a loop (10 iterations) that will then generate a random number from 1 to 100 and select that row number into another temp table. At the end of the loop I'll have my table of randomly selected records. I am sure there is probably a better way to do this...
~Mike
I'm trying to write a query that will return rows within a specified range and print to a Crystal Report. When I run the query, it produces 2 row of everything. I would use the SELECT DISTINCT clause, but Crystal Reports will not let me edit the Select statement. But I can edit the FROM, WHERE and ORDER BY clauses. I think the problem is in my INNER JOINS but I'm having a problem figuring it out. Can someone please guide me in the right direction.
SELECT
DrawingVouchers."PlayerID",
DrawingVoucherNumbers."PromoID", DrawingVoucherNumbers."VoucherNumber", DrawingVoucherNumbers."IssueDate", DrawingVoucherNumbers."UserID",
CDS_PLAYER."LastName", CDS_PLAYER."FirstName",
CDS_ACCOUNT."Address1A", CDS_ACCOUNT."City1", CDS_ACCOUNT."State1", CDS_ACCOUNT."Zip1"
FROM
{ oj (("WinOasis"."dbo"."DrawingVouchers" DrawingVouchers INNER JOIN "WinOasis"."dbo"."DrawingVoucherNumbers" DrawingVoucherNumbers ON
DrawingVouchers."PlayerID" = DrawingVoucherNumbers."PlayerID")
INNER JOIN "WinOasis"."dbo"."CDS_PLAYER" CDS_PLAYER ON
DrawingVoucherNumbers."PlayerID" = CDS_PLAYER."Player_ID")
INNER JOIN "WinOasis"."dbo"."CDS_ACCOUNT" CDS_ACCOUNT ON
CDS_PLAYER."Player_ID" = CDS_ACCOUNT."Primary_ID"}
WHERE
DrawingVoucherNumbers."VoucherNumber" >= 37806 AND
DrawingVoucherNumbers."VoucherNumber" <= 37813
SELECT
[tblSections].[pageTitle],
[tblSections].[sectionURL],
[tblSectionContents].[articleID],
[tblSectionContents].[fileID],
[tblSectionContents].[linkID],
[tblCopy].[copyText],
[tblFiles].[fileName],
[tblFiles].[fileCaption],
[tblGroupings].[grouping],
[tblLinks].[linkURL]
FROM [tblSections]
LEFT JOIN [tblSectionContents] ON [tblSectionContents].[sectionID] = [tblSections].[id]
LEFT JOIN [tblCopy] ON [tblSectionContents].[articleID] = [tblCopy].[id]
LEFT JOIN [tblFiles] ON [tblSectionContents].[fileID] = [tblFiles].[id]
LEFT JOIN [tblGroupings] ON [tblFiles].[groupingID] = [tblGroupings].[id]
LEFT JOIN [tblLinks] ON [tblSectionContents].[linkID] = [tblLinks].[id]
WHERE [tblSections].[id]=2
ORDER BY
[tblSectionContents].[articleID],
[tblSectionContents].[fileID],
[tblSectionContents].[linkID]
If I pass it the ID of a section that has files or copy or [stuff in other tables] attached, then I get a result set that makes sense.
But if I pass it a section ID that doesn't reference any other content tables (ie: the section just has a title and a link URL), I don't get anything back.
Shouldn't it should still get me the fields from the row in tblSections that matches the ID I'm passing it?
How can I make it so that it does?
Thanks :)
I can't even think how I would go about beginning to solve this problem, so any tips/pointers would help immensely.
I'm running a query that builds a time line of a process using messages in a table called Event. The Event table is joined with a table called Charge_Info based on N_Charge, the charge number. It builds the time line by looking in the Message field for various text strings that signify a specific point has been reached in the process and builds a record for each charge number with data from N_Charge and timestamps from the events table.
The problem I have is that there aren't records for every event in the Event table, ie, for every charge that doesn't have a "Heating Complete" event, the returned recordset has no mention of that charge. The system recording these events is pretty much crap, but there's nothing to be done about that right now. What I want to happen is for the query to return every record in N_Charge and a null string or NULL in place of the non-existing events.
The stored procedure is pasted below. I pass the dates I want and select from the Event table into a temporary table and then do the join/text search on that table. It helped performance tremendously compared to the text search searching all 400,000+ records in the Event table and then doing the date select.
Thanks for any help in advance,
TimC
SELECT EVENT.Time_Stamp, EVENT.N_CHARGE, EVENT.Message
INTO #EVENT_FILTERED
FROM EVENT
WHERE (EVENT.TIME_STAMP >= @StartDate) AND (EVENT.TIME_STAMP < @EndDate)
SELECT CHARGE_INFO.TIME_STAMP, CHARGE_INFO.N_CHARGE, CHARGE_INFO.BASE,
CHARGE_INFO.N_RECIPE, CHARGE_INFO.N_FCE, CHARGE_INFO.N_CH,
CHARGE_INFO.HEIGHT, CHARGE_INFO.CREW_EOC, CHARGE_INFO.CREW_SOC,
CHARGE_INFO.TIME_START, CHARGE_INFO.TIME_FCE_SET,
CHARGE_INFO.TIME_FCE_IGNITED, CHARGE_INFO.TIME_FCE_REMOVED,
CHARGE_INFO.TIME_CH_SET, CHARGE_INFO.TIME_CH_REMOVED,
CHARGE_INFO.WEIGHT, CHARGE_INFO.TIME_POST_PRG_COMPLETE,
EVENT.TIME_STAMP AS IC_Set,
EVENT_1.TIME_STAMP AS Cycle_Started,
EVENT_2.TIME_STAMP AS Leak_Test_Done,
EVENT_3.TIME_STAMP AS End_N2_PrePurge,
EVENT_4.TIME_STAMP AS Heating_Complete,
EVENT_5.TIME_STAMP AS Split_Temp_Met,
EVENT_6.TIME_STAMP AS End_N2_Final_Purge,
EVENT_7.TIME_STAMP AS Inner_Cover_Removed,
EVENT_8.TIME_STAMP AS Cycle_Complete,
EVENT_9.TIME_STAMP AS Post_Purge_Time_Met
FROM dbo.CHARGE_INFO CHARGE_INFO LEFT OUTER JOIN
#EVENT_FILTERED EVENT_9 ON CHARGE_INFO.N_CHARGE = EVENT_9.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_7 ON CHARGE_INFO.N_CHARGE = EVENT_7.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_6 ON CHARGE_INFO.N_CHARGE = EVENT_6.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_8 ON CHARGE_INFO.N_CHARGE = EVENT_8.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_5 ON CHARGE_INFO.N_CHARGE = EVENT_5.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_3 ON CHARGE_INFO.N_CHARGE = EVENT_3.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_1 ON CHARGE_INFO.N_CHARGE = EVENT_1.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_4 ON CHARGE_INFO.N_CHARGE = EVENT_4.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_2 ON CHARGE_INFO.N_CHARGE = EVENT_2.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT ON CHARGE_INFO.N_CHARGE = EVENT.N_CHARGE
WHERE (EVENT.MESSAGE LIKE '%Inner Cover Set%') AND
(EVENT_1.MESSAGE LIKE '%Cycle Started%') AND
(EVENT_2.MESSAGE LIKE '%Leak Test Done%') AND
(EVENT_3.MESSAGE LIKE '%End of N2 PrePurge%') AND
(EVENT_4.MESSAGE LIKE '%Heating Complete%') AND
(EVENT_5.MESSAGE LIKE '%Split Temp Met%') AND
(EVENT_6.MESSAGE LIKE '%End N2 Final%') AND
(EVENT_7.MESSAGE LIKE '%Inner Cover Removed%') AND
(EVENT_8.MESSAGE LIKE '%Cycle Complete%') AND
(EVENT_9.MESSAGE LIKE '%Post Purge Time Met%') AND
(CHARGE_INFO.TIME_STAMP >= @StartDate) AND
(CHARGE_INFO.TIME_STAMP < @EndDate)
ORDER BY CHARGE_INFO.BASE, CHARGE_INFO.TIME_STAMP DESC
Is there a way to allow the count () to return a 0 for the rows with a NULL value instead of not returning that row?
View 15 Replies View RelatedHeres my problem, the first part selects a row from the database, ifthere is no row with the criteria it inserts a row and then returns it,the problem is the IF statement that inserts the row, never returns theselect after it. if there is a row initially in the database, itreturns the right information, I just can't get it to return the rowafter inserting it. Anyone know what the problem could be?Stored procedure:ALTER PROCEDURE dbo.CheckCurrentPayPeriod(@UserID varchar(50))ASBEGIN-- This SP checks to see if the current PayPeriod exists,-- if not it will create the payperiod for them and returnDECLARE @appStartDate DATETIMEDECLARE @dt DATETIMEDECLARE @rows intSET @appStartDate = (SELECT PayPeriodStart FROM PayPeriodStart)SET @dt = GETDATE()SELECTUserIDFROMPayPeriodWHERE(PeriodStart <= CONVERT(varchar(10), @dt, 101)) AND (PeriodEnd >=CONVERT(varchar(10), @dt, 101)) AND (UserID = @UserID)-- Inserts their new PayPeriodDECLARE @PayPeriodID intif (@@ROWCOUNT = 0)BEGINDECLARE @sDate datetimeDECLARE @eDate datetimeSET @sDate = @appStartDateSET @eDate = DATEADD(day, 13, @sDate)INSERT INTOPayPeriod(UserID, PeriodStart, PeriodEnd)VALUES(@UserID, @sDate, @eDate)/*EXEC @PayPeriodID = InsertPayPeriod @UserID, @sDate, @eDate*/SET @PayPeriodID = @@IDENTITYSELECT * FROM PayPeriod WHERE PayPeriodID = @PayPeriodIDRETURNENDelseRETURNEND
View 1 Replies View Related