Return PkID Of Last Saved Record - ASPUpload && @@Identity

Nov 26, 2005

We're using ASPUpload as a tool to upload files to our server and save
the details to SQLServer. However, I have an application where I need
to return the pkID of the just saved file. I'm assuming that I could
use the @@Identity command but cannot get this to function.

Has anyone used this command with ASPUpload with an success, or any
other methods that could be used?

Thanks.

View 8 Replies


ADVERTISEMENT

How To Retrieve The Value Of Last Saved Identity Column ?

Oct 13, 2007

Hello Everyone,

I'm using Nhibernate and SQL Server 2005.

I have two tables something like this :
Table : Account
AccountID
UserName
Password

Table : Address
AddressID
AccountID
Line1
Line2
State

My NHibernate mapping file looks like this :
For Table Account
<id name="ID" type="long" column="AccountID" access="property">
<generator class="identity"/>
</id>

<property name="Username" column="Username" type="String" />
<property name="Password" column="Password" type="String" />

For Table : Address
<id name="ID" type="long" column="AddressID" access="property">
<generator class="identity"/>
</id>

<many-to-one name="Account" column="AccountID" class="..., ...." fetch="select"/>
<property name="Line1" column="Line1" type="String" />
<property name="Line2" column="Line2" type="String" />

My Problem is it does entry in the Account table perfectly, but when it comes to do entry in Address Table it says AccountID can't be null, I'm wondering shouldn't it pick it up from the Account table as I'm passing in that object.

For the Workaround I was thinking maybe I can get the identity of the last entry saved and somehow save it to the other table myself.

I was hoping if someone can please help me.

View 1 Replies View Related

ASP 3.0 Record Is Getting Saved Twice.

Mar 1, 2007

Hello All,

I am facing this unusual error. In My ASP Page I am submitting the Page and after submitting the form I am calling another ASP page, which contains sql command to insert the record.

My Problem is when I am submitting the Form. Record is getting inserted twice with same values.

What is the cause of this problem as I am facing this problem in every form.

With Regards

Sunil Dutt

View 8 Replies View Related

How To Return First Record Child Record And Count

Jan 31, 2006

I've been looking for examples online to write a SPROC to get some data. Here are the tables.

Album_Category
AlbumCategoryID (PK, int, not null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)

Albums
AlbumID (PK, int, not null)
AlbumCategoryID (int, null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)

I need to return:
-[Album_Category].[AlbumCategoryID]
-[Album_Category].[Caption]
-[Albums].[Single AlubmID for each AlbumCategoryID]
-[Count of Albums in each AlbumCategory]

I hope I was fairly clear in what I'm trying to do. Any tips or help would be appreciated. Thanks.

View 3 Replies View Related

SQL Challenge - How To Return A Record Set Starting At A Particular Record?

Feb 25, 2007

I have a directory of user information. What I would like to do isallow someone to search for person X and then return not only theinformation for person X, but also the information for the next 15people following person X sorted alphabetically by lastname.So if someone searched for the lastname = "Samson", it would return:Samson, JohnSaxton, GregScott, HeatherSears, Rebecca.... (15 names following "Samson) ...How do you in SQL return a record set of X records starting atparticular record (e.g. lastname = "Smith)?Thanks in advance.

View 4 Replies View Related

Where Are The SSI Files Saved When Saved To The Server?

Apr 20, 2006

This is a pretty simple question, but I'm going nuts trying to find the answer. After creating an SSI package, I told it to save to the SQL server... Now where do I go to pull that package up again and make changes and/or execute the package?

View 4 Replies View Related

Return A Identity

Mar 22, 2008

 How do i return a Identity through a stored procedure?

View 18 Replies View Related

Return Identity

Jul 20, 2005

Sorry I'm new in this,Add a record using T-SQL and Connection.ExecuteHow can i insert the identity in a VB-variableDim objConn As ADODB.ConnectionSet objConn = New ADODB.ConnectionobjConn = CurrentProject.ConnectionobjConn.OpenobjConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES ('012345678')SELECT @@IDENTITY as 'NewID'"Msgbox NewID triggers an error !Filip

View 5 Replies View Related

Return @@identity For Another Function

May 14, 2004

What I'm trying to do is provide a solution where users can upload an image and a description, to a database, so I'm trying to insert the title and description then return the @@identity for the image upload function which will name the image like this
image_23.jpg (23 being the @@identity) resize it and save it to specified directory

I cant seem to get the identity to return to my script.
This is my SP

CREATE PROCEDURE SP_Insertad
(
@catid int,
@subcatid int,
@areaid int,
@uid int,
@adtitle varchar(255),
@addescription varchar(1000)
)

AS
Insert Into Tbl_ad
(ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location)
VALUES
(@adtitle,@addescription,@areaid, @uid, 0,convert(varchar, GETUTCDATE(), 101), @catid, @subcatid, 1)

select @@identity
return
GO


I tested in query analyser, and it works fine, so It must be my code. this is my function

Sub Insert_pic(sender as object, e as eventargs)

Dim catid = Request.form("ddcats")
Dim subcatid = Request.form("subcatrad")
Dim adtitle = Request.Form("txttitle")
Dim AdDescription = Request.form("txtdescription")
Dim uid = getUID(Context.User.Identity.Name)
Dim areaid = Request.form("ddarea")
SQLConnect = new SqlConnection(ConfigurationSettings.Appsettings("mydB"))

SQLCommander = New SQLCommand("SP_INSERTad", SQLConnect)

SQLCommander.Commandtype = Commandtype.StoredProcedure

SQLCommander.Parameters.add("@adtitle", adtitle)
SQLCommander.Parameters.add("@addescription", addescription)
SQLCommander.Parameters.add("@catid", catid)
SQLCommander.Parameters.add("@subcatid", subcatid)
SQLCommander.Parameters.add("@uid", uid)
SQLCommander.Parameters.add("@areaid", areaid)

'// this section not working right, it wont write return id

Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue

response.write(SQLCommander.Parameters("ReturnValue").Value)

SQLConnect.open()
SQLCommander.ExecuteNonQuery()
SQLConnect.close()

End sub

Can anybody see anything I missing? I appreciate any imput

View 4 Replies View Related

How Do You Use A SP To Do An Insert And Return The Identity To ADO

Apr 12, 1999

I would like to use ADO in a new project but I need to find out how to return the identity field value to ADO out of a stored procedure. I have not done alot with ADO or with SQL 7.0 so if anyone has an example of the SQL and the ADO code that I would need I would greatly appriate it.

Thanks

View 1 Replies View Related

I Want To Return The Value Of An Identity Column

Mar 3, 2008

Hello,
I'm using C# to access sql server.
When I execute an insert command, I want to get the value of the column ID(ID is an identity column in my table defination) . Is there any method I can use?
Thanks.

View 9 Replies View Related

Return Identity In Stored Procedure

Mar 23, 1999

I have a stored procedure where I am passing in several strings which are concatenated to form an Insert SQL statement. I then return the identity value from the insert - problem is, the identity value is coming back NULL.

Here is the sp. Any ideas?

CREATE PROCEDURE sp_ExecuteInsert
@pIdentity int OUTPUT,
@pSQL1 varchar(255),
@pSQL2 varchar(255) = NULL,
@pSQL3 varchar(255) = NULL,
@pSQL4 varchar(255) = NULL,
@pSQL5 varchar(255) = NULL,
@pSQL6 varchar(255) = NULL,
@pSQL7 varchar(255) = NULL,
@pSQL8 varchar(255) = NULL
AS

EXECUTE (@pSQL1 + @pSQL2 + @pSQL3 + @pSQL4 + @pSQL5 + @pSQL6 + @pSQL7 + @pSQL8)

SELECT @pIdentity = @@IDENTITY
GO

FYI, the real problem I am trying to solve is simply to return the identity after an insert. I could write a simple insert sp that returns the identity, but my code is written to handle generic situations and build an INSERT statement as needed. So if there are any other ideas of executing an insert statement and getting the identity, please let me know.

Thanks...

View 4 Replies View Related

Return The Lastest [Identity] Field Value

Feb 17, 2004

Hi!

I have a question that might sound obvious to some of you but (obviously) not to me.

I have a stored procedure that adds a new record to a table that has an [Identity] field (TableID).

I want to return the value of the TableID of the newly created record. Sound simple ?

Thanks.

View 4 Replies View Related

Get @@IDENTITY Of New Inserted Record,

Jan 18, 2005

I am using VS.net (2003) SQLcommand TEXT. with input params.
SQL server 2000


Can anyone tell me how to get the Identity value (field value, Idenity col) when you do an insert? I want to get the Identity value, then redirect the user to another page and use this identity value so they can update more (other) fields that are on that page.

My code so far that works... but Where do I put @@IDENTITY ?
How do I call or assign the @@IDENTITY value to a value in my aspx.vb code page?

Question: how do I get the Identity value from the ID column.
Question: How do I assign that value to some variable in code, say, assign it to (Session("App_ID")) = IdentityValueOrSomething?Help...
---------------------
INSERT INTO App
(AppName, Acronym, Description,bla bla bla bla........)
VALUES (@AppName, @Acronym, @Description, bla bla bla bla........)

-----------------------------

Private Sub btnAddApp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddApp.Click
With cmdAddApp
'.Parameters("@app_id").Value = Session("App_ID")
.Parameters("@AppName").Value = txtAppName.Text
.Parameters( bla bla bla.............
.Parameters( bla bla bla.............
.Parameters( bla bla bla.............

End With
Try
cnAppKBdata.Open()
cmdAddApp.ExecuteNonQuery()
cnAppKBdata.Close()
''''''''''''''Session("App_ID") = whatever the @@IDENTITY is....'''''''''''''''''''''''''''??
Response.Redirect("AppUpdate.asp")
Catch ex As Exception

End Try
End Sub


Anyone have the lines of code that does this?

Any advise or examples :) thanks you.

View 1 Replies View Related

Identity Of An Updated Record

Nov 15, 2001

Does anyone know how to get the indentity of the last updated record in a table. I need it for a cascading trigger.

Thank you,

Jody

View 3 Replies View Related

Identity Set , How To Reset Record No

Mar 28, 2006

i have a table with the following fiels ,
Column NameDataType
Sno intIdentity = Yes , Identity Speed =1 , Identity Increament =1
namechar

Now i entered data and coneected to database, worked.Now after cheking all the data entered with the form , now i have to send the table to client place.
The problem is , the sno column has the value which i entered last. now if i delete all records , then also the record no doesnot become 0.
what i have to do, to set the sno column to 1 again.

View 3 Replies View Related

How To Return Identity Value When Insert Into Slq Command Run Successful ?

Feb 10, 2006

hello,
        I'm new to store procedure in slq 2000.
       I want to create  a sproc in  my sql 2000 sevrer,I have a table named "Supplier",
now I want to return  my identiy field value when  I use insert into sql command (insert into "Supplier")  successful.
   So how to create this store procedure ?
much thanks

View 6 Replies View Related

SP Syntax Help - Return New Identity Value As Output Param

Feb 4, 1999

I have a stored proc that will insert a new row into a table
with the values of the parameters you pass in. I need it to
return the value of the ID that's generated by an Identity
column once the row has been written and that value has
been generated. If I just do a SELECT Max(), I could
accidentally grab a row written by someone else, right?

My current sp looks like this:

CREATE PROCEDURE sp_SaveNewLabel

-- @LabelID int output
@LabelType int
, @Logo int
, @Field01 char(30)

AS

INSERT INTO tbLabel
(LabelType
, Logo
, Field01)

VALUES
(@LabelType
, @Logo
, @PrintCC
, @Field01)


How do I grab the new LabelID (the column is int, Identity) and return
it from the stored proc. Any help would be greatly appreciated...

Zack

View 2 Replies View Related

Return The Last Identity Value Generated All Tables In A Database

Apr 14, 2008



I need a script that returns the last identity value generated for each table in a database that has an identity column.



I am using SQL Server 2005.

Thanks.

View 6 Replies View Related

Return Every Nth Record?

Apr 1, 2008

Is there a SQL command that returns every other result or every 3rd result. The reason being is i am using the data to plot a graph with many thousands of points, it would be useful if not every result was return. I.e. too much data to fit within the screens resolution.

Regards Dan Walmsley

View 4 Replies View Related

Dataset And Identity Of New Record Inserted

Oct 29, 2006

Hi, I have 2 tables in my database PrescriptionHeader and PrescriptionDetails.My PrescriptionHeader table has the following fields:PrescriptionID -identity fieldPatientID PatientfNamePatientlname PrescriptionDetails table has the following fields:PrescriptionDetailID -identityPrescriptionID -from PrescriptionHeader table MedicineDosage The function InsertPrescription inserts values into the table PrescriptionHeader. I want the same function to then insert the value of MedicineDosage  into PrescriptionDetails with the same PrescriptionID inserted into PrescriptionHeader. How do I tell the function to insert the PrescriptionID that was automatically inserted into PrescriptionHeader also into table PrescriptionDetails . How do I return the identity before proceeding to insert into PrescriptionDetails table?ThanksFunction InsertPrescription(ByVal PatientID As String, _ByVal PatientFname As String, _ByVal Patientlname As String, ByVal MedicineDosage as String)Dim DBAdapter As SqlDataAdapterDim DBDataSet As DataSetDim SQLString As StringDim DBCommandBuilder As SqlCommandBuilderSQLString = "SELECT * FROM PrescriptionHeader WHERE PrescriptionId = ''"DBAdapter = New SqlDataAdapter(SQLString, DBConnection)DBDataSet = New DataSetDBAdapter.Fill(DBDataSet)Dim AddedRow As DataRow = DBDataSet.Tables(0).NewRow()AddedRow("PatientID") = PatientIDAddedRow("PatientfName") = PatientFnameAddedRow("Patientlname") = PatientlnameDBDataSet.Tables(0).Rows.Add(AddedRow)DBCommandBuilder = New SqlCommandBuilder(DBAdapter)DBAdapter.Update(DBDataSet) End Function

View 1 Replies View Related

How To Retrieve Identity Of Just Inserted Record???

Jan 30, 2008

Ok I've been researching this for a day now and I'm not coming up with much. I want to store the auto-incrementing ID of the last inserted record in a session variable, so that I may put it in a foreign key column in another table, if the user wishes to fill out a form on another page. I think my stored procedure is correct. But don't know what code to add to my aspx page. Any help will be greatly appreciated.
 
Here is my VB ScriptProtected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
 
 
 
Dim personalContactDataSource As New SqlDataSource()personalContactDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DataConnectionString1").ToString()
 
 
personalContactDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
personalContactDataSource.InsertCommand = "PersonalContactInsert"
 
 personalContactDataSource.InsertParameters.Add("FirstName", FirstName.Text)
personalContactDataSource.InsertParameters.Add("LastName", LastName.Text)personalContactDataSource.InsertParameters.Add("KeyPerson", KeyPerson.Checked)
personalContactDataSource.InsertParameters.Add("DayPhone", DayPhone.Text)personalContactDataSource.InsertParameters.Add("EveningPhone", EveningPhone.Text)
personalContactDataSource.InsertParameters.Add("Fax", Fax.Text)personalContactDataSource.InsertParameters.Add("Email", Email.Text)
personalContactDataSource.InsertParameters.Add("HomeAddress", HomeAddress.Text)personalContactDataSource.InsertParameters.Add("City", City.Text)
personalContactDataSource.InsertParameters.Add("State", State.Text)personalContactDataSource.InsertParameters.Add("Zip", Zip.Text)
personalContactDataSource.InsertParameters.Add("ReqEffectDate", ReqEffectDate.Text)personalContactDataSource.InsertParameters.Add("MRID", MRID.Text)
personalContactDataSource.InsertParameters.Add("CurrentPremium", CurrentPremium.Text)personalContactDataSource.InsertParameters.Add("CurrentCarrier", CurrentCarrier.Text)
personalContactDataSource.InsertParameters.Add("CurrentDeductible", CurrentDeductible.Text)personalContactDataSource.InsertParameters.Add("CurrentCoins", CurrentCoins.Text)personalContactDataSource.InsertParameters.Add("ReasonForQuote", ReasonForQuote.Text)
 
 
End Sub
 
 
And here is my Stored ProcALTER PROCEDURE dbo.PersonalContactInsert

@FirstName varchar(30),@LastName varchar(30),
@DayPhone varchar(14),@EveningPhone varchar(14),
@Fax varchar(14),@Email varchar(60),
@HomeAddress varchar(80),@City varchar(30),
@State char(2),@Zip char(5),
@KeyPerson bit,@ReqEffectDate smalldatetime,
@CurrentCarrier varchar(30),@CurrentPremium smallmoney,
@CurrentDeductible smallmoney,@CurrentCoins smallmoney,
@ReasonForQuote varchar(150),@MRID int,
@ClientNumber int OUT
 
AS
 
INSERT INTO PersonalContact(FirstName, LastName, DayPhone, EveningPhone, Fax, Email, HomeAddress, City, State, Zip, KeyPerson, ReqEffectDate, CurrentCarrier, CurrentPremium, CurrentDeductible, CurrentCoins, ReasonForQuote, MRID, DateTimeStamp)
VALUES(@FirstName,@LastName,@DayPhone,@EveningPhone,@Fax,@Email,@HomeAddress,@City,@State,@Zip,@KeyPerson,@ReqEffectDate,@CurrentCarrier,@CurrentPremium,@CurrentDeductible,@CurrentCoins,@ReasonForQuote,@MRID, GetDate())
SET @ClientNumber = SCOPE_IDENTITY()
RETURN
 

View 8 Replies View Related

Returning The Next Identity Value Before Writing A Record.

Oct 28, 2005

I am storing product information in a SQL Server database table; the product information has no unique fields so I have created an Identity field called ‘uid’. Is there a way of querying the table to find out what value will be given to the next ‘uid’ field before the next record is written to the table? I need to use this as a FK in other tables.

View 3 Replies View Related

How To Start IDENTITY (1,1) From 1 For Each Time We Have New Record

Mar 7, 2001

hello everyone
I have table that look like

CREATE TABLE GuestAccount
(
Id_Guest smallint,
SeqNo int IDENTITY(1,1),
account money Null
PRIMARY KEY(Id_Guest,SeqNo)
)
Q?
I want my SeqNo start from 1 for new Id_Guest
is it possible ?

tnanks

View 3 Replies View Related

Retrieve Identity Key Of Record Just Inserted?

Aug 21, 2014

Best way to retrieve the identity key of the record just inserted?This question is for discussion purposes; the business process that spurred the question is currently working.Using SQL Server 2008 R2, a record is inserted from a stored procedure. Let's say the sp has something like this:

Code:
BEGIN
BEGIN TRANSACTION
INSERT INTO tblTools
([Desc],CreateDate,Model,CreatedBy,Notes)

[code]....

In Access, when you add a new record to the recordset, the identity field comes "pre-populated" making it east to get the actual, correct identity value assigned to the record you are inserting. In SQL Server, I know options include:

Code:
IDENT_CURRENT('tblX')
SCOPE_IDENTITY
@@IDENTITY
among other methods.

Each has pros and cons, such as user privileges (IDENT_CURRENT requires the user to have Select privileges on the table, and catches records created by other things, such as users and triggers), and the other two give you the last key inserted and don't allow specifying the object (which is a problem if the insert added records to multiple tables, or you have multiple inserts).

View 2 Replies View Related

Identity Not Displayed Until I Move Into Another Record

Oct 17, 2007

Hello,

I am using Access with tables linked to sql server 2005. I need the identity to be displayed directly when entering data so that i can enter enter data into other tables using this identity on the form without the need to go back and forward to other records to display the identity. Is there a feature in Sql server 2005 which permits this?

Thank you

View 2 Replies View Related

Problem With @@identity Return In Stored Procedure Insert.

Nov 24, 2003

I'm having a problem I do an insert into a table but I want to return the value of the identity field of that insert so I can email a confirmation. For some reason this code doesn't work.
Below is the stored procedure I'm calling and below that the code I'm using. What am I doing wrong. The value I have returned is null when it should be a number. Any suggestions. Why does finalMagicNum2 come back null when it should grab the identity field of the inserted record.




CREATE PROCEDURE addMagicRecTest
(
@theSequence int,
@theSubject int,
@theFirstName nvarchar(50)=null
@theLastName nvarchar(75)=null
)



AS

INSERT INTO employees([Sequence],subject,firstname,lastname)
VALUES(@theSequence,@theSubject,@theFirstName,@theLastName)
SELECT @@identity AS finalNum



magicDataConnect = ConfigurationSettings.AppSettings("myDataConnect")
Response.Write(magicDataConnect)
magicCommand = New SqlDataAdapter("addMagicRecTest", magicDataConnect)
magicCommand.ConnectionType = CommandType.StoredProcedure
magicCommand.SelectCommand.CommandType = CommandType.StoredProcedure

' Sequence ID for request
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSequence", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@theSequence").Value = "41833"

' Subject for new Wac Ticket
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSubject", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@theSubject").Value = "1064"


' First Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theFirstName", SqlDbType.NVarChar, 50))
magicCommand.SelectCommand.Parameters("@theFirstName").Value = orderFirstName

' Last Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theLastName", SqlDbType.NVarChar, 75))
magicCommand.SelectCommand.Parameters("@theLastName").Value = orderLastName



DSMagic = new DataSet()
magicCommand.Fill(DSMagic,"employees")

If DSMagic.Tables("_smdba_._telmaste_").Rows.Count > 0 Then
finalMagicNum2 = DSMagic.Tables("_smdba_._telmaste_").Rows(0)("finalMagic").toString
End If





I need finalMagicNum2

View 2 Replies View Related

How Do I Return The Identity From Stored Procedure To Asp.net Code Behind Page?

Jun 2, 2006

how do i return the identity from stored procedure to asp.net code behind page?
CREATE PROCEDURE [dbo].[myInsert] ( @Name varchar(35),   @LastIdentityNumber int output)AS insert into table1 (name) values (@name)
DECLARE @IdentityNumber intSET @IdentityNumber = SCOPE_IDENTITY()SELECT @IdentityNumber as LastIdentityNumber
code behind:
public void _Insert(
string _Name,
{
DbCommand dbCommand = db.GetStoredProcCommand("name_Insert");db.AddInParameter(dbCommand, "name", DbType.String, _userId);
db.AddParameter(dbCommand, "@IdentityNumber", DbType.Int32, ParameterDirection.Output, "", DataRowVersion.Current, null);
db.ExecuteNonQuery(dbCommand);
int a = (int)db.GetParameterValue(dbCommand,"@IdentityNumber");
whats wrong with to the above code?
 

View 2 Replies View Related

Return Full Max Record

Mar 24, 2008

Is there a t-sql shortcut/ quick way to return the full max record.

So basically if I had:
prog_area glh description
HS 200 health and social care
EN 300 engineering
HS 400 health care
EN 250 engineering and construction

so grouping by prog_area and taking max glh would return appropriate descriptions like:
HS 400 health care
EN 300 engineering

This is quite a simple example and I would be using it on much larger datasets. I know how to do it by doing a join to itself with max glh but it seems to me there should be an easier way to return the appropriate full record set. Hopefully somethin fast in t-sql

View 2 Replies View Related

Query Help: Need To Return 2nd From Top Record

Jul 20, 2005

i need to retrieve the most recent timestamped records with uniquenames (see working query below)what i'm having trouble with is returning the next-most-recent records(records w/ id 1 and 3 in this example)i also need to return the 3rd most recent, 4th, 5th and 6th most recent- i figure if i can get the 2nd working, 3rd, 4th, etc will be cakethanks,brett-- create and populate tabledrop table atestcreate table atest(id int not null, name char(10), value char(10),timestamp datetime)insert into atest values (1,'a','2','1/1/2003')insert into atest values (2,'a','1','1/1/2004')insert into atest values (3,'b','2','1/1/2003')insert into atest values (4,'b','3','1/1/2002')insert into atest values (5,'b','1','1/1/2004')-- select most recent records with distinct "name"sselect a.* from atest as awhere a.id = (select top 1 b.id from atest as bwhere b.name = a.nameorder by timestamp desc )/*query results for above query (works like a charm)2a 1 2004-01-01 00:00:00.0005b 1 2004-01-01 00:00:00.000*/

View 6 Replies View Related

Return Record With MAX Column Value

Feb 26, 2008

Hello,
The following query:

SELECT submitRep1 AS Rep, tc, COUNT(tc)AS TCCount
FROM tbl_CYProcessedSales
WHERE
tc NOT LIKE 'T%'
AND
tc NOT LIKE 'R%'
AND ISNUMERIC(TC) = 0
AND NOT submitrep1 = ''
AND Submitrep1 = 'along'
GROUP BY submitrep1, tc

Returns a result like this:

ALONG KL 65
ALONG KP 35

How can I return the one record that contains the MAX(TCCount)?

Thank you for your help!

CSDunn

View 10 Replies View Related

Returning Timestamp From Inserted Record Using @@Identity

Oct 5, 2006

i recently found a little error in a stored procedure that was included in a project handed over to me....

the sp was rather simple. it just inserted a record into a table and returned the identity and the timestamp as follows

IF @@ERROR>0
 BEGIN
 SELECT @int_InterventionID = 0
 RETURN @@ERROR
 END
ELSE
 BEGIN 
 SELECT @int_InterventionIDReturned = MAX(InterventionID) FROM tblIntervention
 SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned
 SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned
 RETURN 0
 END

i figured that it should be using @@Identity for the interventionIdentity rather than max(InterventionID)

so i changed to...

IF @@ERROR>0
 BEGIN
 SELECT @int_InterventionID = 0
 RETURN @@ERROR
 END
ELSE
 BEGIN 
 SELECT @int_InterventionIDReturned = @@IDENTITY
 SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned
 SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned
 RETURN 0
 END

it returns the @int_InterventionIDReturned but the timestamp now comes back as null??? why??

how can i ensure that i always get the timestamp of the record it has just inserted

any help greatly appreciated,
Cheers,
Craig

 

 

View 3 Replies View Related

Code Inside! --&> How To Return The @@identity Parameter Without Using Stored Procedures

Oct 30, 2005

Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row. 
Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer
'that is the variable for the new id.Dim intNewID As Integer
Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _                                    "VALUES (@aCompany, @aName); SELECT @NewID = @@identity"
Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL
'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-(
dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim)
dbCommand.Connection = dbConnection
TrydbConnection.Open()dbCommand.ExecuteNonQuery()
'here i want to return the new ID!Return intNewID
Catch ex As Exception
Throw New System.Exception("Error: " & ex.Message.ToString())
Finally
dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose()
End Try
End Function

View 7 Replies View Related







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