I was thinking that this question probably comes up a lot...but my forum searches didn't turn anything up, so here goes:
I have an id field that auto increments (identity) for each record.
I insert a record.
How can I safely extract the id field of the record for use in another query, avoiding the possiblity that another record is inserted before I can get at it?
Thanks,
G
Hello,
For one of my clients I have built 2 applications. The 1st one is a windows based application that is used for production and employee scheduling. The data for this application is stored in a SQL Express database. The 2nd one is a ASP.NET 2.0 site that contains the clients website and a portal page. The portal page uses forms authentication and redirects a customer to a page that shows the production schedule for this customer. So I have 2 kind of users: local (LAN) and web. Both using the same database.
For test purposes the site is currently hosted on the clients SBS 2003 server (this is the only server). However I don't think it is wise to host the public website on this server. What is the best configuration to host the public website safely? I read about putting a webserver in a DMZ. But I need a connection with the SQL Express server to retrieve the data for the portal section. As well for authenticating the customer and for retrieving the schedule data.
I'm currently using Windows Authentication on the SQL server. Can this still be used when the webserver is in the DMZ?
If I understand it correctly the webserver in the DMZ is not part of the local domain? Also could remote hosting be an option at all?
Any help and advice would be highly appreciated.
Techs,
Every morning I come into find my LDF is about 70 gigs. So I shrink it based on the percentage it will allow because that seems safe, because this is a critical production server, which reduces the size down to 65 gigs. However, I return the next morning to find it has grown back to the original size plus some. How can I safely shrink this monster and keep it manageable?
Many thanks in advance.
I have one table with primary key index, when i want to select the result from this particular table, i wish the result arrange by record inserted squence and not by the primary key i had been set.
View 4 Replies View RelatedHi,
Check this SQL
SELECT DISTINCT
TblOrder.CustomerUID,
TblOrder.OrderHiddenID,
TblPayment.PaymentAmount,
TblPayment.Result,
TblOrder.OrderID
FROM TblOrder
CROSS JOIN TblPayment
WHERE (TblOrder.CustomerUID = @IsCustomerID)
AND (TblOrder.OrderHiddenID = @IsHiddenID)
AND (TblPayment.Result = 'Pending')
AND (TblOrder.OrderID IN (SELECT MAX(TblOrder2.OrderID) FROM TblOrder TblOrder2))
one customer can have more than one orders.
So i need to select customer last inserted order details from database.So when i use above sql i returns null.what might be the reason for this
<g class="gr_ gr_54 gr-alert gr_gramm Grammar multiReplace" data-gr-id="54" id="54">I want</g> to get row number of last inserted into employee table?
How can i get it @@identity function returns null.
is there any way to do it?
hi
I want to do a "bulk" insert and then get the id (primary key) of the inseret rows:
insert into table1 select * from table2
If I get the value of the @@identity, I always get the last inserted record.
Any idea how to get the ids of all inserted values?
Thx
Olivier
Hi all!In a insert-trigger I have two joins on the table named inserted.Obviously this construction gives a name collition beetween the twojoins (since both joins starts from the same table)Ofcourse I thougt the usingbla JOIN bla ON bla bla bla AS a_different_name would work, but itdoes not. Is there a nice solution to this problem?Any help appriciated
View 1 Replies View Relatedis there any way of getting the identity without using the "@@idemtity" in sql??? I'm trying to use the inserted event of ObjectDataSource, with Outputparameters, can anybody help me???
View 1 Replies View RelatedHi
We have a bunch of servers running server 2000 & 2003 along with many sql server (versions 2000 & 2005) databases in a production environment pulling transactions then doing warehousing & reporting.
An audit has shown up 1 production server using English_US 'mmddyyyy' system date formats, all others being English_Australian 'ddmmyyyy'.
Is it safe to simply change the regional settings on this English_US server to English_Australian or will it mess up the data in our SQL 2005 databases? I've not been able to get a definitive answer from anyone yet!
many thanks in advance; GaryP
Hello,
I have a table with 2 columns, let's say: "No. Piece" and "Price".
I need to write a select statment that allows me to retrieve the last added records, is it possible?
Example:
At time t1, we have:
Piece 1, 300 Euros
Piece 2, 400 Euros
Piece 3, 250 Euros
At time t2 = t1 + 5mns, we have:
Piece 1, 300 Euros
Piece 2, 400 Euros
Piece 3, 250 Euros
Piece 4, 120 Euros
Piece 5, 390 Euros
The select statement should be able to give the following result:
Piece 4, 120 Euros
Piece 5, 390 Euros
Thank you in advance for your help and your time.
Leïla
I'm doing a data migration job from our old to our new database. The idea is that we don't want to clutter our new tables with the id's of the old tables, so we are not going to add a column "old_system_id" in each and every table that needs to be migrated.
I have created a number of tables in a separate schema "dm" (for Data Migration) that will store the link between the old database table and the new database table. Suppose that the id of a migrated record in the old database is 'XRP002-89' and during the insert into the new table the IDENTITY column id gets the value 1, the link table will hold : old_d = 'XRP002-89', new_id = 1, and so on.
I knew I can get the value of IDENTITY columns with the OUTPUT INTO clause, although I have never actually used it. And now I can't get it to do what I need.
Below is some code to set up three tables: the old table, the new one, and the table that will hold the link between the id's of records in the old database table and the new database table.
Code:
CREATE TABLE DaOldTable(
pkCHAR(10)NOT NULL,
a_columnCHAR(10)
)
CREATE TABLE DaNewTable(
idINTNOT NULL IDENTITY,
[Code] ....
Below I tried to use the OUTPUT INSERTED INTO clause. Beside getting the generated IDENTITY value, I also need to capture the value of the old id that will not be migrated to the new table. When I use "OUTPUT DaOldTable.pk" the system gives me the error: "The multi-part identifier "DaOldTable.pk" could not be bound." Using INSERTED .id gives no problem.
Code:
INSERT INTO DaNewTable(a_column)
--OUTPUT DaOldTable.pk, INSERTED.id link_old2new_DaTable--(DaOld_id, DaNew_id)
SELECT a_column
FROM DaOldTable
[Code] ...
--but getting "The multi-part identifier "DaOldTable.pk" could not be bound."
DROP TABLE DaOldTable
DROP TABLE DaNewTable
DROP TABLE link_old2new_DaTable
How can I populate a table that must hold the link between the id's of records in the old database table and the new database table? The records are migrated with set-based inserts.
I will paste my code below. Â I inserted a breakpoint but nothing is being sent to the database and nothing came up when I ran it with the breakpoint. Â Can anyone tell me how to fix this?Protected Sub btn_addfriend_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_addfriend.Click Dim add_friend_source As New SqlDataSource add_friend_source.InsertCommand = "INSERT INTO [Friends] ([UserName], [UserID], [IP], [AddedOn], [FriendName]) VALUES (@UserName, @UserID, @IP, @AddedOn, @FriendName)" detailsview_addfriend.DataSource = add_friend_source add_friend_source.DataBind() add_friend_complete.Text = "Success!" End SubThe "Success!" text is the only thing that seems to work properly...
View 7 Replies View RelatedHello, I am stumped on how to get the last id that was inserted. I have searched all over the internet to find the answer but all of them turned up to be classic asp.
View 4 Replies View Relatedwhile executing this command locally, ita working fine
Insert into tblorderDetails
(OrderID,ProductID,SofaPackageID,Quantity,UnitPrice,TotalOrd,ItemStatus )
values(1, 3915, 0, 1, 2049.00, 2049.00, 'PO')
but when executing online, giving me following msg:
(1 row(s) affected)
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Location16FreeStock'.
and also data is not inserted in table.
I have a program to insert rows into a SQL Server table from an ASCII file. There appears to be some data mapping going on. For example:
"J & J Smith" is imported as "J _J Smith".
This doesn't happen on all servers. I can run it against my database and get the desired results.
I looked into the Server Configuation. Under the Security tab there are Mapping options. It appears that you can map _ to something else, but I don't see where you can map & to anything.
hi,
this is my spc:
select top 1 u.userid,
u.user_name,
u.password,
c.code_description as role_code,
u.expiry_date,
u.created_date,
u.active,
convert(varchar,v. user_date, 103) + ' ' + right('0' + stuff(right(convert(varchar,v.user_date, 109), 14), 8, 4, ''), 10) as user_date,
v.operation,h.user_name as updatedby
from [usermaster] u inner join [codeMaster] c
on 'sp'=c.code inner join [HRUser_developerlog] v on u.userid=v.inserted_id and v.operation='update' inner join [usermaster] h on v.userid=h.userid
where u.userid = '3' order by v. user_date
if i use it gives the v.user_date as fist modified date it is not giving last modified date.
select * from HRUser_developerlog
user_date operat userid
ion
2007-01-25 14:28:17.000insert1
2007-01-24 13:02:18.093insert4
2007-03-03 11:30:29.310update2
2007-03-03 11:30:55.373insert3
2007-03-03 11:31:31.717insert26
2007-01-25 14:28:17.000insert3
2007-03-03 11:43:39.733update26
2007-03-03 11:48:04.543delete3
2007-03-03 14:26:22.420update3
2007-03-03 14:27:00.280update3
2007-03-03 14:27:12.013update2
2007-03-03 14:27:35.763update1
2007-02-08 14:28:17.030update2
2007-03-03 14:27:55.967update3
2007-03-03 14:29:18.827update3
2007-03-03 14:30:52.983update3
so it has to show
2007-03-03 14:30:52.983
but it shows the first updated id only
2007-03-03 14:26:22.420
please help me to get my need
I have a dataset that uses generated stored procedures to do its select, insert, update, delete. I am inserting a row to that dataset, and after the update, using the ID of newly created row. This worked just fine until I added triggers to some of the tables on my DB, and now, when I insert a row, the row's ID is not available after the update (it's 0) Any idea what happened / what I have to do to fix this? Thnx!
View 1 Replies View Relatedhow do i insert a record, then return the inserted record back to VS (ASP + VB) to display? maybe just the ID will be enough. then i will do a select
View 6 Replies View RelatedHi,I use a Stored Procedure who works very well....INSERT INTO Computers (CategoryID, SubCategoryID, ......VALUES (@CategoryID, @SubCategoryID, .........But as soon as it creates the new row, i want to be able to get the Id (ComputerId) of this row. I use ComputerId as the primary key.How can i do that? I code in VB.Thanks
View 2 Replies View RelatedI'm creating a web application that has user input on 3 seperate pages. Each page prompts the user for specific information and at the bottom of the page the user will click on the "Submit" button to post the info from the form to the database table.
Once the user has submitted the first page of information how do I retrieve the ID from the CustID column of that row so I can use the Update function to add additional info to that row when the user clicks on the submit button on page 2 & 3. I don't want to hold all the information in variables until the end in case they bail out of the form.
TIA
Steve
I've seen a lot of info on how to do this with @@IDENTITY and SCOPE_IDENTITY(), but can't get this to work in my situation.
I am inserting a record into a table. The first field is a GUID (UNIQUEIDENTIFIER) that uses newid() to generate a unique GUID. Since I am not using an int, I can't set the IsIdentity property of the field. Without IsIdentity set, @@IDENTITY and SCOPE_IDENTITY() do not work.
How can I get the ID (or whole record) of the last record that I inserted into a SQL database? Note that I am doing this in C#.
As a last resort, I could generatate the GUID myself before the insert, but I can't find C# code on how to to this.
Hi!
I would like to get the last inserted ID in a sql 2005 table to a variable.My code is:
SqlDataSource ds = new SqlDataSource();ds.ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ToString();ds.InsertCommandType = SqlDataSourceCommandType.Text;ds.InsertCommand = "INSERT INTO Products(SellerID, Price) OUTPUT INSERTED.ProductID VALUES(@SellerID,@Price)"; ds.InsertParameters.Add("SellerID", "Sony"); ds.InsertParameters.Add("Price", "123");string output = ds.Insert().ToString(); // it's return rowsAffected but I need productID (SCOPE_IDENTITY or @@IDENTITY )Thank you for your time and help :)
How would I get the value of a ROWGUID column of the row I just inserted? (like using @@identity for an identity column.)
Thanks!
Hello there!
it was a while since i studied SQL and that brings us to my problem...
I'm creating a Stored Procedure wich first insert information in a table. That table has a uniqueidentifier fild that is default-set to newid().
later in the SP i need that uniqueidentifier value? how do I get it?
I tried this:
CREATE PROCEDURE spInsertNews
@uidArticleId uniqueidentifier = newid,
@strHeader nvarchar(300),
@strAbstract nvarchar(600),
@strText nvarchar(4000),
@dtDate datetime,
@dtDateStart datetime,
@dtDateStop datetime,
@strAuthor nvarchar(200),
@strAuthorEmail nvarchar(200),
@strKeywords nvarchar(400),
@strCategoryName nvarchar(200) = 'nyhet'
AS
INSERT INTO tblArticles
VALUES( @uidArticleId,@strHeader,@strAbstract,@strText,@dt
Date,@dtDateStart,@dtDateStop,@strAuthor,@strAutho
rEmail,@strKeywords)
declare @uidCategoryId uniqueidentifier
EXEC spGetCategoryId @strCategoryName, @uidCategoryId OUTPUT
INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId)
VALUES(@uidArticleId, @uidCategoryId)
But i get an error when I EXEC the SP like this:
EXEC spInsertNews
@strHeader = 'Detta är den andra nyheten',
@strAbstract = 'dn första insatt med sp:n',
@strText = 'här kommer hela nyhetstexten att stå. Här får det plats 2000 tecken, dvs fler än vad jag orkar skriva nu...',
@dtDate = '2003-01-01',
@dtDateStart = '2003-01-01',
@dtDateStop = '2004-01-01',
@strAuthor = 'David N',
@strAuthorEmail = 'david@davi.com',
@strKeywords = 'nyhet, blajblaj, blaj'
the errormessage is: Syntax error converting from a character string to uniqueidentifier.
does anyone have a sulution to this problem?
Can I use something similar to the @@IDENTITY?
I will be greatful for any ideas...
thanks
/David, Sweden
Hello,
I want to return the ID of the last inserted row. I am using an ObjectDataSource and a FormView to perform the insert operation. Here is my presentation code:
<asp:ObjectDataSource ID="odsOrders" runat="server" DataObjectTypeName="Auction.Info"InsertMethod="InsertOrder" SelectMethod="GetOrders" OnInserted="ReturnNewOrderID" OldValuesParameterFormatString="original_{0}" TypeName="Auction.Controller"> <SelectParameters> <asp:Parameter DefaultValue="00" Name="ModuleId" Type="Int32" /> </SelectParameters></asp:ObjectDataSource>
<asp:FormView ID="fvAddOrder" runat="server" DataSourceID="odsOrders" DefaultMode="Insert" HorizontalAlign="Center" Width="100%"> <InsertItemTemplate>....
and my VB code:
Protected Sub ReturnNewOrderId(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs) Response.Write(e.ReturnValue) Response.Write("Test") End Sub
and my Stored Procedure:
ALTER PROCEDURE [dbo].[Auction_InsertOrder] (@ModuleID int,@FirstName nvarchar(50),@LastName nvarchar(50),@Email nvarchar(250),@Phone nvarchar(20),@Fax nvarchar(20),@DelAddress1 nvarchar(250),@DelAddress2 nvarchar(250),@DelTown nvarchar(50),@DelPostCode nvarchar(20),@DelCountry nvarchar(50),@InvAddress1 nvarchar(250),@InvAddress2 nvarchar(250),@InvTown nvarchar(50),@InvPostCode nvarchar(20),@InvCountry nvarchar(50),@AuctionName nvarchar(50),@Quantity int,@Price decimal(18,2),@PostCosts decimal(18,2))ASINSERT INTO Auction_Orders(ModuleID, FirstName, LastName, Email, Phone, Fax, DelAddress1, DelAddress2, DelTown, DelPostCode, DelCountry, InvAddress1, InvAddress2, InvTown, InvPostCode, InvCountry, AuctionName, Quantity, Price, PostCosts, DateEntered)VALUES (@ModuleID, @FirstName, @LastName, @Email, @Phone, @Fax, @DelAddress1, @DelAddress2, @DelTown, @DelPostCode, @DelCountry, @InvAddress1, @InvAddress2, @InvTown, @InvPostCode, @InvCountry, @AuctionName, @Quantity, @Price, @PostCosts, getdate())RETURN SELECT @@IDENTITY As NewOrderID
I can insert the record into the database but the e.ReturnValue does not return anything.
Thank you for your help/
Many thanks,
Vincent
Hey!
I need to find the record_id of the record I just inserted so that I can display the newly created record id.
Right now it looks like this: (I'm using Cold Fusion to insert and retrieve records, but I think this is an SQL question)
INSERT INTO table (value1, value2, value3) values (value1, value, value3)
Select record_id
FROM table
WHERE value1 = value1 AND value2 = value2 AND value3 = value3
The problem is when someone puts in the exact same data for values 1 through 3 I get back only the first record_id, not the last one. Maybe that's the trick right there, add an ORDER BY field_id DESC??
I thought MySQL had a way to grab the last inserted record, or return the record_id while I'm still in the insert statement, that would be preferable.
-Matt
When I insert a row I need the PK assigned to that row. Is there a way to do this.
View 4 Replies View RelatedHello there!
it was a while since i studied SQL and that brings us to my problem...
I'm creating a Stored Procedure wich first insert information in a table. That table has a uniqueidentifier fild that is default-set to newid().
later in the SP i need that uniqueidentifier value? how do I get it?
I tried this:
CREATE PROCEDURE spInsertNews
@uidArticleId uniqueidentifier = newid,
@strHeader nvarchar(300),
@strAbstract nvarchar(600),
@strText nvarchar(4000),
@dtDate datetime,
@dtDateStart datetime,
@dtDateStop datetime,
@strAuthor nvarchar(200),
@strAuthorEmail nvarchar(200),
@strKeywords nvarchar(400),
@strCategoryName nvarchar(200) = 'nyhet'
AS
INSERT INTO tblArticles
VALUES( @uidArticleId,@strHeader,@strAbstract,@strText,@dt Date,@dtDateStart,@dtDateStop,@strAuthor,@strAutho rEmail,@strKeywords)
declare @uidCategoryId uniqueidentifier
EXEC spGetCategoryId @strCategoryName, @uidCategoryId OUTPUT
INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId)
VALUES(@uidArticleId, @uidCategoryId)
But i get an error when I EXEC the SP like this:
EXEC spInsertNews
@strHeader = 'Detta är den andra nyheten',
@strAbstract = 'dn första insatt med sp:n',
@strText = 'här kommer hela nyhetstexten att stå. Här får det plats 2000 tecken, dvs fler än vad jag orkar skriva nu...',
@dtDate = '2003-01-01',
@dtDateStart = '2003-01-01',
@dtDateStop = '2004-01-01',
@strAuthor = 'David N',
@strAuthorEmail = 'david@davi.com',
@strKeywords = 'nyhet, blajblaj, blaj'
the errormessage is: Syntax error converting from a character string to uniqueidentifier.
does anyone have a sulution to this problem?
Can I use something similar to the @@IDENTITY?
I will be greatful for any ideas...
thanks
/David, Sweden
Trying to get my head around this table:
I thought it contained all data used in an insert; meaning if i enter a row in a table the inserted table should then contain all data entered?
Ive created multiple tables linked via primary key Learner_ID this key is unique to any/all students and can never be duplicated hence i thought i would use it to link the tables.
I have tables Fab / Main / Elec / Learner_info / WS_WR.
For Fab / Main / Elec the only data i need form Learner_info is the Learner_ID field, i simply used:
INSERT INTO FAB(Leaner_ID)
SELECT Learner_ID
FROM Inserted
as a trigger to insert the ID into these tables. Everything works perfectly fine. So i assumed that i would be able to use something very similar to insert say:
Learner_ID, FNAME, LNAME, ONAME and C_NUM from the learner_info table into the WR_WS table. Apparently i was wrong I can insert the learner_ID perfectly but if i then try to add another insert trigger eg: insert fname i get an sql error:
Cannot insert the value NULL into the column 'LEARNER_ID', table 'swdt_im.WS_WR'; column does not allow nulls. INSERT fails.
Now from reading the error it suggests to me that because the second trigger is running my learner_id value has become a NULL. Obviously a primary key wont accept this value so the statement fails.
What im struggling to understand, im sure i will solve the problem eventually but i would apreciate help, and this is most important to me rather than a solution is this:
If i enter data into a row on a table does that data go into the inserted table in the same row? or does the data go into inserted as a single entry row, meaning when i tab along from learner_ID into FNAME does Learner_ID become row 1 (in inserted) and FNAME the current row? or do they go into the appropriate columns?
I was under the assumption that i could enter:
LEARNER_ID / FNAME / LNAME / ONAME / C_NUM
into one table and have a trigger insert those values into multiple other tables. Dont suppose someone can help me out im alright querying a SQL db but ive never had to develop anything other than a single table one myself before
Thanks in advance
I have created an updatabable view.
It uses the INSTEAD OF INSERT clause to fire because there are multiple base tables.
lets say the view has 6 fields
field1,field2, field3, field4, field5, field6
to get the values from the inserted record I have to declare the variables first
declare field1 varchar(50),field2 varchar(50), field3 varchar(50), field4 varchar(50), field5 varchar(50), field6 varchar(50)
I then have to assign the information to these variables
select @field1 = field1,@field2 = field2,@field3 = field3, @field4 = field4,@field5 = field5,@field6 = field6 from inserted
I can then use these values to execute store procedures
exec sp1 @field1, @field2, @field3
exec sp2 @field4, @field5, @field6
is there anyway to just do this
exec sp1 inserted.field1, inserted.field2, inserted.field3
exec sp2 inserted.field4, inserted.field5, inserted.field6
I tried
exec spec select field1, field2, field3 from inserted and it did not give me an error message BUT it did not work
I would like to use my stored procedures for data insertion
I appreciate your help
Hello,I would like do an insert into a table. The table has an autoincrimenting unique int id. After I do the insert how do i get theunique int id of the record that I just inserted? Is there a straightforward way of accomplishing this?Thanks,Billy
View 6 Replies View Relateddear all,
I have a WCF service which is host in a console application for the time beeing.
This service provide methods for retriving history data when request.
So far so good.
My WCF service need also to know when a particular table (ALARMS tabel ) gets updated with DELETE, INSERT, OR UPDATE. This because my client application (WinForm) need to refresh a datagrid binding to that ALARMS table.
In other words my WCF servcie would send a callback event to my client when it as been notice for a change..
But my problem is how my WCF service can be notify from an update in my SQL table ?
I have tried SQLDependency class, but I give up was not working properly...and hard to know the xact context you are runing on.
Was thinking also of having a timer whcih pool every 1s by calling my tabel store procedure and verifiy is somerow ha changed....
What to do, how to do, what is the best way and thred safe method
Thnaks for help and advise
regards
serge