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 ?
Hi ,,
How to write the Sql Query to return the next generated Identity from the Sql server database.
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
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!
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
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
)
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 RelatedHi 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?
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
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);
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
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....
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
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 RelatedI 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 RelatedI 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.
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!
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
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?
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#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):
View 6 Replies View RelatedI'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
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 ?
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;
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
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.
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)
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
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
say i have some columns and a person can vote either once, twice or 3 times ( checkbox style control ) and in the database it looks something like this: eg: ID.... vote1..... vote2.... vote3 1 0 0 1 2 1 0 1for ID = 2, what type of query would i have to write to return the column names vote1 & vote3 ( vote2 is not null ) Cheers!!!
View 1 Replies View RelatedIn this query
SELECT E1.id, E2.id
FROM productHeadings AS B1, productHeadings AS E1,
productHeadings AS E2
WHERE B1.id = E1.parent_id
AND E1.id = E2.parent_id
and B1.id = 1
the result is 2 id columns... is there a way to return all id in 1 column?!
Hello, { Merry xmas to all few hours early but hey !! }
I'm trynig to create an application that will connect to any database that i specify and return the colums names within any table that i specify.
I have done the code that establishes the connection but im a bit stumped on how to get the colum names from a tabel and was wondering if anybody had any sourse code for this in VB.
Thanks
Rob
ps: have a good new year !!
My table has a column called Period i want to get a list of different periods example:
Period
1
2
31
1
2
4
12
31
2
then run an sql statement and should return you the following
Period
1
2
4
12
31