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
ADVERTISEMENT
May 14, 2008
I have a table with an Identity column. Is there a way to get the automatically generated primary key after I insert something to the table? If so, what is the syntax?
View 5 Replies
View Related
Jan 10, 2006
Hello,
In my table, i've a GUID column type. I insert a new record with NewID() function in Sql request.
Is it possible to retreive the GUID column of this new record (without requerying the table) ?
I'm using EVC, Sql Mobile 3.0 and OLE DB interface.
Thanks in advance.
View 1 Replies
View Related
Jun 23, 2006
Hello
Using Visual Studio 2005 Prof and SQL Server everywhere.
How do get the identity column value after insert record.
With SQL Server 2005, its quite easy to get by creating and insert statement on the tabledapter ( Insert statement followed by a select statement where identitycolumn = scope_identity())
How do this is sql everywhere??
regards
View 1 Replies
View Related
Nov 26, 2005
We're using ASPUpload as a tool to upload files to our server and savethe details to SQLServer. However, I have an application where I needto return the pkID of the just saved file. I'm assuming that I coulduse the @@Identity command but cannot get this to function.Has anyone used this command with ASPUpload with an success, or anyother methods that could be used?Thanks.
View 8 Replies
View Related
Jun 19, 2008
Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
View 6 Replies
View Related
Jun 1, 2006
I am a "newbie" and have been struggling with this for days! I have users enter their residence information and insert which generates houseid. I want to use/display that houseid on next page/step. I am VERY FRUSTRATED and would appreciate any assistance!
<script runat="server">
Protected Sub SqlDataSource1_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Inserting
e.Command.Parameters("@house").Size = 5
End Sub
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted
Dim house = e.Command.Parameters("@house").Value
Response.Write(house)
End Sub
Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs)
End Sub
Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
End Sub
</script>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:ic_registerConnectionString %>"
oninserted="SqlDataSource1_Inserted"
oninserting="SqlDataSource1_Inserting"
DeleteCommand="DELETE FROM [household] WHERE [householdid] = @original_householdid AND [housenum] = @original_housenum AND [streeraddr] = @original_streeraddr AND [aptnum] = @original_aptnum AND [city] = @original_city AND [state] = @original_state AND [zipcode] = @original_zipcode AND [HHPhone] = @original_HHPhone AND [timedate] = @original_timedate"
InsertCommand="INSERT INTO [household] ([housenum], [streeraddr], [aptnum], [city], [state], [zipcode], [HHPhone], [timedate]) VALUES (@housenum, @streeraddr, @aptnum, @city, @state, @zipcode, @HHPhone, { fn NOW() }); SELECT @house = SCOPE_IDENTITY()"
OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [household]"
UpdateCommand="UPDATE [household] SET [housenum] = @housenum, [streeraddr] = @streeraddr, [aptnum] = @aptnum, [city] = @city, [state] = @state, [zipcode] = @zipcode, [HHPhone] = @HHPhone, [timedate] = @timedate WHERE [householdid] = @original_householdid AND [housenum] = @original_housenum AND [streeraddr] = @original_streeraddr AND [aptnum] = @original_aptnum AND [city] = @original_city AND [state] = @original_state AND [zipcode] = @original_zipcode AND [HHPhone] = @original_HHPhone AND [timedate] = @original_timedate" OnSelecting="SqlDataSource1_Selecting">
<InsertParameters>
<asp:Parameter Name="housenum" Type="String" />
<asp:Parameter Name="streeraddr" Type="String" />
<asp:Parameter Name="aptnum" Type="String" />
<asp:Parameter Name="city" Type="String" />
<asp:Parameter Name="state" Type="String" />
<asp:Parameter Name="zipcode" Type="String" />
<asp:Parameter Name="HHPhone" Type="String" />
<asp:Parameter Type = "String" Name="house" Direction= "Output"/>
</InsertParameters>
Next question is this easier to do using a Wizard Control and DetailsView on a "step" or using seperate pages and FormView? Or does it matter?
View 2 Replies
View Related
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
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
Aug 12, 2009
when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
View 2 Replies
View Related
Aug 7, 2005
Is there a clever way of reteiving the identity of an INSERT query in SQLServer?
The example below is a the first insert (into the "parent" table with an identity column) of a heirarchical multi-table update.
sqlCommand.CommandText = "INSERT INTO Orders VALUES ( Date, Customer ) ; SELECT @@IDENTITY AS ID" ;
sqlCommand.ExecuteNonQuery() ;
The ExecuteNonQuery() method returns the number of rows inserted but I need the ID for the INSERT into the "child" table.
My options based on my current understanding appear to be:
a) use command parameters and set ID as an output param
b) use a stored procedure, use command parameters and set ID as an output param
c) issue the SELECT @@IDENTITY as a subsequent seperate command using ExecuteScalar()
Is there a more elegant way to get the ID using SQLServer's "batch" query mechanism and ADO.NET v1.1?
Thanks,
View 1 Replies
View Related
Oct 12, 2015
I am working in an environment where i inherited bad design. I have a column in a table. This col contains huge HTML files. We are mostly reading these files and very little updates.
I am changing this whole architecture and going to using Azure Blob storage. I am stuck right now. I need to extract these html strings and save them into separate files in year/month/day/filename.html format. I have another column in the table which has create date saved in it.
I am planning to import all these files into BLOB storage and save link in the table.
1) How can i extract these strings from the table and save them in the year/month/day/filename.html directory/sub-directory format
2) How to import these files into BLOB storage.
View 1 Replies
View Related
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
Jan 25, 2015
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
View 6 Replies
View Related
Sep 19, 2005
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
View 2 Replies
View Related
Aug 1, 2014
I'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
* I can't change anything on T
* The PK is NOT an Identity
View 4 Replies
View Related
Oct 16, 2006
Hi all,
The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..
i cannot implement this constraint, it throws the error when i execute the below Alter query,
ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE
the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.
any workarounds for this ?
View 3 Replies
View Related
Sep 7, 2007
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
View 18 Replies
View Related
Apr 19, 2007
i have a table that has 9 columns that belongs to other tables.
i need to retrieve one column from this table. In order to do this, do i have to join all the other tables that has similar columns?
View 3 Replies
View Related
Jan 9, 2007
Hello;
My Memebership table has Guid column as Primary key.
But I would like to add Auto numbering Identity column to this table.
Is this idea OK or it will bring some problems?
Thank you in advance for your help
View 3 Replies
View Related
Aug 3, 2006
i have a table
table1
column1 int not null
column2 char not nul
column3 char
i want to script a change for table1 to alter column1 to be the table identity column. not primary.
View 5 Replies
View Related
Mar 5, 2004
Hi,
I have a column that is unique that I would like to make into an IDENTITIY column after I insert some data into it.
I tried
alter table <table_name>
alter column <col_name> int Identity (1,1)
but it fails.
Ajay
WORD4LIFE
(http://www.word4life.com)
View 2 Replies
View Related
Jul 20, 2005
Hi(SQL Server 2000)I have an existing table (t) with a column that is NOT an identity column(t.ID), but it has manually inserted "row numbers". I want to make thiscolumn become an identity column. This column is a key field to othertables, so I want to keep the row numbers that are allready inserted.From the Query Analyzer, how do I do this?Thanks in advance!Regards,Gunnar VøyenliEDB-konsulent asNORWAY
View 3 Replies
View Related
Apr 2, 2007
Hi,
I have two tables table1 and new_table
Table1 has id_value column which is int and it is idenity specification is yes and identity increment is 1 .
And I have a NEW_TABLE with column name new_id which should store current id_value of Table1.
This type of functionality is requirement for my project.
I should get a current value of id_value from table1 . if I say SELECT * FROM NEW_TABLE ;
Please help me out to fix this issue
Thanks
Purnima
View 3 Replies
View Related
Jan 5, 2008
suppose a table has 4 column,
Name x y z z1
A 1 2 3 3
B 0 2 0 3
C 1 0 3 3
if i pass parameter as @param=1, only column of x should be displayed, if i send parameter as @param=2 only y column should be displayed, if @param=3 is parameter then Z column And Z1 column should be displayed. plz help to create sql query.
View 3 Replies
View Related
Apr 3, 2008
Hi Guys
Does anybody know of an SQL query that will bring back a list of column names from a particular table?
Ive done it in Oracle before, but i cant seem to find how to do it in SQL Server 2005?
Thanks in advance.
View 5 Replies
View Related
Jun 2, 2004
Hi, all..
I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).
Is there any sp for this or any query for this?
Thank you all...
View 14 Replies
View Related
Jul 6, 2006
Table: Names
Columns: Name_RID char(10)
Name_Type smallint
Name_Last char(50)
Name_First char(25)
Name_MI char
I have search for and see how to put the columns for the last, first mi together (Name_Last + ', ' + Name_First + ' ' + Name_MI) as Name
But how can I test the value of the Name_Type field to determine how the Name column looks
if Name_Type = 1 then
Name = (Name_Last + ', ' + Name_First + ' ' + Name_MI)
else
Name = Name_Last
The Name_Type represents Individual versus an Entity
0,ABC Pipeline,,
1,Williams, John,A
View 3 Replies
View Related
Jul 18, 2012
I am trying to retrieve all rows with the largest value in a particular column. The largest value could return many rows for a particular users. Here is what I have thus far.
SELECT DISTINCT
ID, NAME, FOP, ACCT, CTNUM, ENDDATE, DEBIT, CREDIT, TRANSACTION_DATE, EXPORTED, CALENDAR_YEAR, FISCAL_YEAR, PAYROLL_IDENTIFIER,
PAYROLL_NUMBER, [EARN-SEQNO], EVENT_SEQUENCE_NUMBER
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, ACCT, PAYROLL_NUMBER,EVENT_SEQUENCE_NUMBER
ORDER BY EVENT_SEQUENCE_NUMBER DESC) AS RN
FROM PAYROLLYEAREND ) s
WHERE RN = 1 AND ID = '16443' AND PAYROLL_NUMBER ='7'
In the above example, the EVENT_SEQUENCE_NUMBER is populated with values from 0 to 12. Could vary per user and PAYROLL_NUMBER. The query above returns 48 rows. However, all I want are the rows where EVENT_SEQUENCE_NUMBER is equal to the highest, which is in the above example is 12. The result would be 29 rows. The where clause is not part of overall query. Just isolating on one ID.
View 2 Replies
View Related
Feb 3, 2014
What would be the most efficient method of constructing a sql statement to retrieve certain record into (this gets tricky) rows of a table with 3 columns date.desc.
<table>
@foreach(var row in maytable){
<tr>
<td>@row.Most_Recent_Record1</td>
<td>@row.Most_Recent_Record2</</td>
<td>@row.Most_Recent_Record3</</td>
</tr>
}
</table>
View 1 Replies
View Related
Jan 10, 2008
Is there any way to retrieve partial contents of a database column? For example, say a column holds 5,000 characters, but I only wish to retrieve the first 50.
Thanks
View 2 Replies
View Related
Mar 25, 2015
I want to just retrieve the numbers left of the space in this address column what can I use? The Address column is Varchar.
Address:
6501 Red Hook Place #201
32 Orchard St
1 Hardy Rd ste 210
379 E Center St.
170 Bridge St
399 Interpace Pkwy
1800 Valley Rd
2210 Harding Hwy
41 Maple Ct
Expected Results:
Address
6501
32
1
379
170
399
1800
2210
41
I tried using this:
Left([ADDRESS], Charindex(' ',[ADDRESS] - 1)) as 'Address'
But i received an error:
Conversion failed when converting the nvarchar value '6501 Red Hook Place #201' to data type int.
View 2 Replies
View Related
Mar 23, 2007
Hi,
We have a SSIS project where we load lot of image files using an OLE DB Source component and put it across the database as varbinary into OLEDB Destination component.
Things were fine until now where am getting an error like this. alongside the log also reads like,
There is not enough space on the disk. Long data was retrieved and can't be added to the data flow task buffer.
SSIS Error Code: DTS_E_InducedTransformFailureOnError.
Is this method of loading the files using an OleDb Souce not the efficient one ? If not is there a way I can get this done. Comments are highly appreciated!!
Thanks in Advance
View 8 Replies
View Related
