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


ADVERTISEMENT

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

Returning @@identity As Int

May 29, 2008

 Hi All :-)
 
I'm trying to return the identity from a stored procedure but am getting the error - "specified cast is invalid" when calling my function.
Here is my stored procedure - ALTER Procedure TM_addTalentInvite @TalentID INT
AS
Insert INTO TM_TalentInvites
(
TalentID
)
Values
(
@TalentID
)
SELECT @@IDENTITY AS TalentInviteID
RETURN @@IDENTITY
 
And here is my function -  public static int addTalentInvite(int talentID)
{
// Initialize SPROCstring connectString = "Data Source=bla bla";
SqlConnection conn = new SqlConnection(connectString);SqlCommand cmd = new SqlCommand("TM_addTalentInvite", conn);
cmd.CommandType = CommandType.StoredProcedure;
// Update Parameterscmd.Parameters.AddWithValue("@TalentID", talentID);
conn.Open();int talentUnique = (int)cmd.ExecuteScalar();
conn.Close();return talentUnique;
}
Any help you can give me will be greatly appreciated thanks!
 
 

View 3 Replies View Related

@@Identity Not Returning

Jun 21, 2008

I have a page that inserts customers into the client database. After the data is inserted it redirects to the customer's policy page using the customer's ID set by @@Identity.
The SQL Command is:
 ALTER PROCEDURE [dbo].[AddBasic]
(
@ln NVarchar(50),
@fn NVarchar(50),
@mAdd NVarchar(50),
@mCity NVarchar(50),
@mState NVarchar(50),
@mZip NVarchar(50),
@pAdd NVarchar(50),
@pCity NVarchar(50),
@pState NVarchar(50),
@pZip NVarchar(50),
@sAdd NVarchar(50),
@sCity NVarchar(50),
@sState NVarchar(50),
@sZip NVarchar(50),
@hPhone NVarchar(50),
@cPhone NVarchar(50),
@wPhone NVarchar(50),
@oPhone NVarchar(50),
@eMail NVarchar(50),
@DOB NVarchar(50),
@SSN NVarchar(50),
@liState NVarchar(50),
@liNum NVarchar(50),
@acctSource NVarchar(50),
@active NVarchar(50),
@County NVarchar(50)
)
AS
DECLARE @custNum int
INSERT basicInfo
(lastName, firstName, mailingAddress, mailingCity, mailingState, mailingZip, physicalAddress, physicalCity, physicalState, physicalZip,
seasonalAddress, seasonalCity, seasonalState, seasonalZip, homePhone, cellPhone, workPhone, otherPhone, email, DOB, SSN, liscenceState,
driverLiscense, acctSource, [status], county)
VALUES (@ln,@fn,@mAdd,@mCity,@mState,@mZip,@pAdd,@pCity,@pState,@pZip,@sAdd,@sCity,@sState,@sZip,@hPhone,@cPhone,@wPhone,@oPhone,@eMail,@DOB,@SSN,@liState,@liNum,@acctSource,
@active, @County)
SET @custNum = @@Identity
SELECT @custNum
 The ASPX page has two parts, the SQL Data Source: <asp:SqlDataSource ID="sqlInsertCustomer" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
InsertCommand="AddBasic"
SelectCommand="SELECT acctNumber FROM basicInfo WHERE (acctNumber = 5)" InsertCommandType="StoredProcedure">
<InsertParameters>
<asp:FormParameter FormField="lName" Name="ln" />
<asp:FormParameter FormField="fName" Name="fn" />
<asp:FormParameter FormField="mAdd" Name="mAdd" />
<asp:FormParameter FormField="mCity" Name="mCity" />
<asp:FormParameter FormField="mState" Name="mState" />
<asp:FormParameter FormField="mZip" Name="mZip" />
<asp:FormParameter FormField="pAdd" Name="pAdd" />
<asp:FormParameter FormField="pCity" Name="pCity" />
<asp:FormParameter FormField="pState" Name="pState" />
<asp:FormParameter FormField="pZip" Name="pZip" />
<asp:FormParameter FormField="sAdd" Name="sAdd" />
<asp:FormParameter FormField="sCity" Name="sCity" />
<asp:FormParameter FormField="sState" Name="sState" />
<asp:FormParameter FormField="sZip" Name="sZip" />
<asp:FormParameter FormField="hPhone" Name="hPhone" />
<asp:FormParameter FormField="cPhone" Name="cPhone" />
<asp:FormParameter FormField="wPhone" Name="wPhone" />
<asp:FormParameter FormField="oPhone" Name="oPhone" />
<asp:FormParameter FormField="eMail" Name="eMail" />
<asp:FormParameter FormField="DOB" Name="DOB" />
<asp:FormParameter FormField="SSN" Name="SSN" />
<asp:FormParameter FormField="dlState" Name="liState" />
<asp:FormParameter FormField="dlNum" Name="liNum" />
<asp:FormParameter FormField="aSource" Name="acctSource" />
<asp:FormParameter FormField="txtCounty" Name="County" />
<asp:Parameter Name="active" DefaultValue="Active" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
 and the redirect:Public Sub Redirect()
Dim x As Integer
x = SqlDataSource1.Insert()
Response.Redirect("~/Main/Main.aspx?q=" & x)
End Sub 

View 2 Replies View Related

@@Identity Not Returning Value

Jun 23, 2004

I have a stored procedure that inserts a record. I call the @@Identity variable and assign that to a variable in my SQL statement in my asp.net page.

That all worked fine when i did it just like that. Now I'm using a new stored procedure that inserts records into 3 tables successively, and the value of the @@Identity field is no longer being returned.

As you can see below, since I don't want the identity field value of the 2 latter records, I call for that value immediately after the first insert. I then use the value to populate the other 2 tables. I just can't figure out why the value is not being returned to my asp.net application. Think there's something wrong with the SP or no?

When I pass the value of the TicketID variable to a text field after the insert, it gives me "@TicketID".

Anyone have any ideas?


CREATE PROCEDURE [iguser].[newticket]
(
@Category nvarchar(80),
@Description nvarchar(200),
@Detail nvarchar(3000),
@OS nvarchar(150),
@Browser nvarchar(250),
@Internet nvarchar(100),
@Method nvarchar(50),
@Contacttime nvarchar(50),
@Knowledge int,
@Importance int,
@Sendcopy bit,
@Updateme bit,
@ClientID int,
@ContactID int,
@TicketID integer OUTPUT
)
AS

INSERT INTO Tickets
(
Opendate,
Category,
Description,
Detail,
OS,
Browser,
Internet,
Method,
Contacttime,
Knowledge,
Importance,
Sendcopy,
Updateme
)
VALUES
(
Getdate(),
@Category,
@Description,
@Detail,
@OS,
@Browser,
@Internet,
@Method,
@Contacttime,
@Knowledge,
@Importance,
@Sendcopy,
@Updateme
)
SELECT
@TicketID = @@Identity


INSERT INTO Contacts_to_Tickets
(
U2tUserID,
U2tTicketID
)
VALUES
(
@ContactID,
@TicketID
)

INSERT INTO Clients_to_Tickets
(
C2tClientID,
C2tTicketID
)
VALUES
(
@ClientID,
@TicketID
)

View 2 Replies View Related

Returning Identity With SQLDataSource

Mar 25, 2008

Hello all,I have a sqldatasource that inserts data to the database but I want to get the ID from an Identity field after I am done inserting the data.  How can I best do that?  I'm not using a stored procedure to do this.  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:onepduConnectionString %>"          InsertCommand="INSERT INTO [tblaccounts] ([fname],[lname], [address], [city], [state], [zip], ,[phone],[credits]) VALUES (@fName,@lname, @address, @city, @state, @zip, @email, @phone, @credits)>                                    <InsertParameters>                <asp:FormParameter Name="fname" FormField="FirstNameTextBox" Type="String" />                <asp:FormParameter Name="lname" FormField="LastNameTextBox" Type="String" />                <asp:FormParameter Name="address" FormField="AddressTextBox" Type="String" />                <asp:FormParameter FormField="cityTextBox" Name="city" Type="String" />                <asp:FormParameter FormField="stateTextBox" Name="state" Type="String" />                <asp:FormParameter FormField="zipTextBox" Name="zip" Type="String" />                <asp:FormParameter FormField="emailTextBox" Name="email" Type="string" />                <asp:FormParameter FormField="phoneTextBox" Name="phone" Type="string" />                <asp:Parameter Name="credits" DefaultValue=0 />                                           </InsertParameters>        </asp:SqlDataSource> 

View 2 Replies View Related

Returning The Identity Value After Insert?

Apr 5, 2008

Hi All: I have what I'm sure is a common scenario...I have a table to track pageviews of a form, and which also tracks when a person viewing the form submits it.
The table has three fields: an INT identity/PK field, a DATETIME (default getdate()) field, and a BIT field with default "false".
When the page is viewed, I insert a record into the dB:
Protected Sub Page_load(ByVal src As Object, ByVal e As EventArgs)

conn = New SqlConnection("Server=myserver;Database=mydb;User ID=user;Password=password;Trusted_Connection=false;")
If Not IsPostBack Then
AddTrack()
End If
End Sub
 Sub AddTrack()

Dim myCommand As SqlCommand
Dim insertTrack As String
insertTrack = "Insert PageTracker (submitted) Values (0)"myCommand = New SqlCommand(insertTrack, conn)
myCommand.Connection.Open()
Try
myCommand.ExecuteNonQuery()tempTxt.Text = "<br>Ticked</b><br>" & insertTrack
 Catch ex As SqlException
tempTxt.Text = ex.Number.ToString()
 
End Try
myCommand.Connection.Close()
End Sub
And if I view the page, the record is inserted into the table. But now I need to know the value of the identity field, so when the form is submitted, I can update the field "submitted" from "0" to "1".
The way I would do it in ASP is to add a "SELECT @@identity" to the query, and get the value using RS.nextrecordset. How would I do this in .NET? or is there a better way for me to do this?

View 6 Replies View Related

Returning Identity Column In SQL 7

Jun 9, 2004

I am currently using IDENT_CURRENT to return the Id of a new row in SQL 2000, but I am looking for a similar way to do this in SQL 7. Ihave no experience with SQL 7

Anyone remember how they did this ?

View 1 Replies View Related

Returning Inserted Row Identity ID

Oct 17, 2004

I am inserting a record by calling a stored procedure in my asp.net code. I need to return the identity field from the insert to use elsewhere in my code. I have found many things regarding this but nothing has worked.

Here is my code that works for the insert....

conClasf.Open()
strSQLInsert = "Exec spInsNewCAR '" _
& calDateInitiatedPopup.SelectedDate _
& "', '" & calResponseDatePopup.SelectedDate _
& "', '" & elbPartName.Selectedtext _
& "', '" & elbPartName.Item(elbPartName.SelectedValue).Text2 _
& "', '" & calSupplyDatePopup.SelectedDate _
& "', '" & elbMRBApproval3.Selectedtext _
& "'"
cmdAd = New OleDbCommand(strSQLInsert, conClasf)
cmdAd.ExecuteNonQuery( )
conClasf.Close

Here is the last part of my stored procedure. BTW, I have added Scope_Identity to the end of my sp and when I open my sp up, it is not there...not sure what that is about. I guess my main problem is what command do I use in ASP.net to execute the sp AND hold my returned value...

Thanks,
JOE

View 7 Replies View Related

Returning Identity Field

Jun 12, 2006

Hi all i'm trying to get the identity field after inserting into db, what am i doing wrong? thanks a lot
my sproc:
CREATE PROCEDURE ng_AddCotacao(...@Codigo_cotacao   int OUTPUT)ASBEGINSET NOCOUNT ONINSERT INTONegocios_cotacoes(...)VALUES(...)SELECT @Codigo_cotacao=SCOPE_IDENTITY()SET NOCOUNT OFFENDGO
 
class file
public class Cotacoes
{
public int codigoCotacao;
}
public class CotacaoAtualiza {  public Cotacoes cotacoes = new Cotacoes();
  public CotacaoAtualiza()  {  }
  public void AdicionarCotacao(   ...   )  {   SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["stringConexao"]);   SqlCommand myCommand = new SqlCommand("ng_AddCotacao", myConnection);
   myCommand.CommandType = CommandType.StoredProcedure;
   ...
   SqlParameter paramCodigo_cotacao = new SqlParameter("@Codigo_cotacao", SqlDbType.Int, 4);   paramCodigo_cotacao.Direction = ParameterDirection.Output;   myCommand.Parameters.Add(paramCodigo_cotacao);
   ...
   myConnection.Open();   SqlDataReader result = myCommand.ExecuteReader();
   while(result.Read())    {    this.cotacoes.codigoCotacao = (int) result["@Codigo_cotacao"];   }
   myConnection.Close();  }
 
calling into code-behind file:
CotacaoAtualiza ca = new CotacaoAtualiza();
Cotacoes cotacoes = ca.cotacoes;
Response.Redirect("Cotacao_confirma.aspx?cotacao=" + cotacoes.codigoCotacao);

View 1 Replies View Related

@@Identity Is Returning NULL

Feb 22, 1999

Hi,

I have upgraded database from sql6.5 to sql 7.o

I have a table tblnetwork on which identity property
is defined on column networkid.

When I issue the following command new record is getting inserted into
tblnetwork and identity column is getting incremented properly.
But, I am not able to get the @@identity value
It is returned as NULL.

insert tblnetwork(formalname,networktype)
values ("name2",11897)
select @@identity

I need this value to insert into some other table.
Pls suggest me why @@identity is returning NULL
thanks,
MMS

View 5 Replies View Related

Returning Value Oof Identity Culomn

Aug 17, 2000

Hello...

This group has been really helpful on a couple of recent qwuestions I have posted - thanks to all!

Now, I have another question. I have a table set up to automatically generate a uniquie number when a record is added(INT NOT NULL IDENTITY).

Now, when this number is generated, I need it returned back to my perl script (thorough DBD::ODBC). Any ideas how to do that?

Thnaks....

View 2 Replies View Related

Returning The Identity Of The Last Value Added...

Nov 20, 2006

Hi All,

I have a createStudent SProc.  When this is called it calls a createContact SProc.  This in turn calls a Create Address Stored procedure.  Now when I create the address, an Identity column automatically creates a new Identity for me.  I need to return this value back to the calling stored procedure.  I know how to return a value and all, however, how to I get the identity just entered (the corresponding identity) reliably?  I know that select MAX <Identity> could suffice in this case, but is flawed as if someone was to add another object near the same time we could get the wrong identity returned...

Thanks heaps

Chris

View 6 Replies View Related

Returning @@IDENTITY FROM Stored Proc

Nov 15, 2005

I am trying to get the identity of an inserted record using this SP:

<code>
ALTER PROCEDURE acereal_Admin.AuctionInsertCommand
(
    @AuctionID Int OUTPUT,
    @StartDate char(255),
    @StartTime char(255),
    @Location char(255),
    @Title char(255),
    @Description char(255),
    @Images char(255)
)
AS
INSERT INTO Auctions
(
    StartDate,
    StartTime,
    Location,
    Title,
    Description,
    Images
)
VALUES
(
    @StartDate,
    @StartTime,
    @Location,
    @Title,
    @Description,
    @Images
)

SELECT     AuctionID AS ID
FROM         Auctions
WHERE     (AuctionID = @@IDENTITY)
</code>

Then, I am using this class for a file called dataaccess.cs to return the ID:

<code>
public string SaveImageName(string ImageName, string AuctionID)
        {
            SqlConnection
sqlConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

           
this.newSqlCommand = new
System.Data.SqlClient.SqlCommand("[AuctionImageSave]", sqlConnection);
           
this.newSqlCommand.CommandType =
System.Data.CommandType.StoredProcedure;

            SqlParameter
paramImagePath = new System.Data.SqlClient.SqlParameter("@ImagePath",
System.Data.SqlDbType.Char, 255);
            paramImagePath.Value = ImageName;
            this.newSqlCommand.Parameters.Add(paramImagePath);

            SqlParameter
paramAuctionID = new System.Data.SqlClient.SqlParameter("@AuctionID",
System.Data.SqlDbType.Int, 4);
            paramAuctionID.Value = AuctionID;
            this.newSqlCommand.Parameters.Add(paramAuctionID);

            SqlParameter
paramImageID = new System.Data.SqlClient.SqlParameter("@ImageID",
System.Data.SqlDbType.Int, 4);
            paramImageID.Direction = ParameterDirection.Output;
            this.newSqlCommand.Parameters.Add(paramImageID);

            //Return SqlDataReader Struct
            this.newSqlCommand.Connection.Open();
            this.newSqlCommand.ExecuteNonQuery();
            this.newSqlCommand.Connection.Close();

            int returnID = (int)paramImageID.Value;

            return returnID.ToString();
        }
</code>

The above code returns null.

Can anyone tell me what I am doing wrong?

THanks, Justin.

View 2 Replies View Related

SQL Query Not Returning Identity Field - Sometimes

Feb 2, 2006

Hello, I have a C# application that adds records to a SQL Server database using a query something like this one:

INSERT INTO table_name
(first_name, last_name, date_added) ('john', 'smith', '1/1/2005 12:00:00pm') ;
SELECT SCOPE_IDENTITY() AS [Scope_Identity]

This works fine unless there's already a John Smith in the database. When that happens, Scope_Identity is null even though the date_added is different. About half the time the record is added even though Scope_Identity is null. I've added code to notify me when this happens, but it's a pain in the neck to re-run my import utility for individual records.

(The table I'm adding to does have a autonumbered key field)

Thanks in advance!



View 1 Replies View Related

Returning More Than One Record From SP...

Feb 11, 2004

Hi ,

Is there a way in SQL Server stored procedure to return multiple records/ more than one records....?

Thanks in Advance...

-Mohit.

View 1 Replies View Related

Max Returning More Than One Record

Nov 8, 2007

when this query is run it returns the max value for each of the activity types eg. phone calls, emails etc.
what i want to achieve is for it to return only one record. whichever is more recent. but it only has to be either a phone call or an email.


SELECT regardingobjectidname, MAX(actualend) AS Last_Contacted_On, activitytypecodename, owneridname
FROM FilteredActivityPointer AS A
WHERE (statecodename = 'completed') AND (activitytypecodename IN (@activitytypes))
GROUP BY regardingobjectidname, activitytypecodename, owneridname

Melvin Felicien
IT Manager
DCG Properties Limited

View 8 Replies View Related

Returning The Identity Of The Auto Number Column

Jan 24, 2005

Hi ,,


How to write the Sql Query to return the next generated Identity from the Sql server database.

View 1 Replies View Related

Returning The Most Rated Record

Mar 7, 2008

ALTER PROCEDURE dbo.GetMostRatedRecipe

(

@CurrentDate datetime

)

AS

SET NOCOUNT ON

SELECT *

FROM Recipe

WHERE TotalRating =

(SELECT MAX(TotalRating)FROM Recipe) AND published = 1 AND ReleaseDate <= @CurrentDate AND ExpireDate > @CurrentDate

This doesn't work good when the recipe having max total rating is not published & expired. I guess I need to first filter the recipes which are published & unexpired and then select the recipe having max total rating. But I don't know how to do that. Could anyone of you please help me doing this ?

View 1 Replies View Related

Help With Sql Query Returning Last Max Record

Oct 19, 2006

I am having problems with a SQL query.

Table has 10 fields and I need to return them all. The three most importaint, at least for the filter I need are:

id, studentid, date, canceled.

I need to return the last max(date) grater than or equal to @dateparam which is not canceled for each studentid

I have worked out some solutions but am not happy with them. Specially woried about performance when the table grows. I am expecting in full production a table growth of about 3 million records per month.

what would be grate is if there where a way of returning a the coresponding id like in:

select studentid, max(date), related(id) as ids
from tablea
where canceled=0
group by studentid

then I could do:

Select *
from tablea inner join (select studentid, max(date), related(id) as ids
from tablea a
where canceled=0
group by studentid
) b on (a.id=b.ids)

View 5 Replies View Related

SCOPE_IDENTITY() Returning The ID Value Of An Inserted Record.

Jul 29, 2006

There are loads of postings on the net about this problem but none I have found explain the cause.
Whenever returning a value from a TableAdapter.Insert method followed by a SELECT SCOPE_IDENTITY() , the value returned is always 1. I have run the same select in SQL management studion and the correct value is returned but with a 1 showing in the column selector (just to the left of the first column. The column selector column is not data column. This must be the reason that issuing a SELECT after an INSERT does not work when using a TableAdapter isert method.
Has anyone come across the solution for this issue?
Thanks
 

View 6 Replies View Related

Returning Table Structure And First Record

Sep 29, 2006

I need to do something sort of like the DESCRIBE function in MySQL. I need to return the table structure, AND the first row from each column sort of as an example of the data in each column.

then i would just need to run this query on each of my tables...

any ideas?

View 1 Replies View Related

Returning One Single Record Rather Than Multiples

Feb 12, 2007

HiIs it possible to return the results of a query so that instead ofhaving say 10 rows its concatenated, egMy query returns 'M' 10 times, can this be returned as 'M M M M M M MM M M'?ThanksLee

View 3 Replies View Related

Fetch Returning Duplicate Last Record

Sep 13, 2006

Ok, this thing is returning the last record twice. If I have only one record it returns it twice, multiple records gives me the last one twice. I am sure some dumb pilot error is involved, HELP!

Thanks in advance, Larry

ALTER FUNCTION dbo.TestFoodDisLikes

(

@ResidentID int

)

RETURNS varchar(250)

AS

BEGIN

DECLARE @RDLike varchar(50)

DECLARE @RDLikeList varchar(250)

BEGIN

SELECT @RDLikeList = ''

DECLARE RDLike_cursor CURSOR

LOCAL SCROLL STATIC

FOR

SELECT FoodItem

FROM tblFoodDislikes

WHERE (ResidentID = @ResidentID) AND (Breakfast = 'True')

OPEN RDLike_cursor

FETCH NEXT FROM RDLike_cursor

INTO @RDLike

SELECT @RDLikeList = @RDLike

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM RDLike_cursor

INTO @RDLike

SELECT @RDLikeList = @RDLikeList + ', ' + @RDLike

END

CLOSE RDLike_cursor

DEALLOCATE RDLike_cursor

END

RETURN @RDLikeList

END

View 5 Replies View Related

SQL 2012 :: Select Top 100 - A Record Is Not Returning From Database

Aug 24, 2015

I have a strange situation with an select. I've noticed that when I select top 100, a record is not returning from the database, but when doing top 101 the record appears on position 41.

The query is like this:

select top 100 GroupId, count(HouseId)
from House h
group by h.GroupId
order by max([DateCreated]) desc

From all discussions about top 100 vs top 101 I've noticed that everybody is saying that top 101 is using another algorithm and we can have a speed problem, but my problem is not about this. With top 100 I'm missing a record that should appear at index 41.

View 9 Replies View Related

Correlated Query Returning Only 1 Record And Repeat

Aug 10, 2014

I have SQL query/dual sub-query in MS Access that is returning data from the left side of the query FROM correctly, but is only returning one record from the right side of the query FROM. Furthermore, it repeats the display of the one record and it repeats the entire results set with a different one record each time until all the records have been displayed. I expect that problems described as “Furthermore” will not exist by fixing the one record issue. I have tried using all the join types available in MS Access, but none change the result.

The desired output is:

Yellow Blue
11/23/201311/19/2013
11/19/210310/01/2012
10/01/210210/08/2010
10/08/201012/14/2007

The actual output is:

Yellow Blue
11/23/201311/19/2013
11/19/210311/19/2013
10/01/210211/19/2013
10/08/201011/19/2013
11/23/201310/01/2102
11/19/210310/01/2102
10/01/210210/01/2102
10/08/201010/01/2102

The same pattern is repeated 2 more times with Blue values of 10/08/2010 and then 12/14/2007.

Here is the SQL:

SELECT Long_List.Yellow,Short_List.Blue
FROM
(
SELECT DISTINCT BirthDate AS Blue
FROM (
SELECT DISTINCT BirthDate FROM citizens

[Code] .....

View 9 Replies View Related

Sql Question - Returning Multiple Rows As One Record

Jul 20, 2005

Hi,In the process of localizing the 'regions' table, we added three newtables. The localized data will be stored in the TokenKeys andTokenValues tables. It would be easier if we did away with theTokeyKeys/TokenValues tables and just added a localeid in the regionstable, but this is the desired schema by the client. Here's theschema:Table: regionsid nameabbreviation1 United StatesUSTable: localesid locale1 en_US2 fr_CATable: TokenKeysid key1 db.regions.name2 db.regions.abbreviationTable: TokenValuesid keyid valuelocaleid1 1 Etas Unis22 2 EU2The old sql was simply this:select name, abbreviation from regionswhich returns:United States, USBut the new sql needs to link in the localized data from the tokeykeysand tokenvalues tables using the localeid... Im trying to figure outwhat the sql statement would look like to return this:Etats Unis, EU (This is supposed to be the French version)My confusion is we are trying to return multiple column values fromthe same column (TokenValues.value) and make them act as separatecolumns in the same record, like it was with the original.Thanks

View 1 Replies View Related

Any Help With Returning The Last Instance Of A Multiple Version Record In Joined Tables?

Sep 28, 2007



We have an archive table which keeps each instance of a sales order that was archived under a "Verion No" field. Each time the sales order is archived it is entered into the archive tables (Sales Header Archive, Sales Line Archive). What I am trying to do is write a query to return all sales orders but only the most recent archived version.

For example this table layout is similar to what I am working with. Version No, Order No and Customer No. are the keys between the Header and Line tables, Customer Name column in the output is from only the Sales Header Archive table

SALES LINE ARCHIVE TABLE
Version No - Order No. - Customer No -----> (other columns)
1 s-5 1000

2 s-5 1000
1 s-6 2000

1 s-7 3000
2 s-7 3000
3 s-7 3000
1 s-8 4000
1 s-9 2000
2 s-9 2000


Here is what I need to output to show:

RESULTS OF JOINED TABLES
Version No - Order No - Customer No - Customer Name ---> (other columns)
2 s-5 1000 Something, Inc.
1 s-6 2000 Acme
3 s-7 3000 Company, LLC
1 s-8 4000 Blah & Associates
2 s-9 2000 Acme

It should return the last Version No of each Sales order.

Does that make sense? It is something probably easy... But, I've spent two days using multiples and multiples of different ways, that just aren't working: I'm about to dropkick my server cabinet...

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

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







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