Adding Entry To DB And Getting Unique ID At The Same Time
Jul 20, 2005
Hi all,
I'm writing a website with Cold Fusion and when a user submits a
request and it's stored in the MS SQL database, I want the unique ID
(Identity field in table) to be given to the user on screen plus
emailed to user.
Now can I store data to the database (where the ID is created) and
return this as a variable in the same statement? I've seen this done
on many websites, but I have no idea how to do it in one step.
Thanks,
Alex.
View 1 Replies
ADVERTISEMENT
Nov 23, 2007
hey folks,
i am looking for code to show the last entry of a database that has a unique field. for eg.. i have columns AREA, NAME, NUMBER. i need to show the last entry for each unique area.
so if there was 5 seperate areas, the table would show the last five entries for each area. anyone help?
View 6 Replies
View Related
May 26, 2005
Hi,
I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.
Here's what I have so far:
Code:
select * from TICK_D
where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Does anyone know how i could do this? Thanks a lot for any help!
View 2 Replies
View Related
Jun 4, 2008
how to prevent the insertion of the same entry of an unique value into the database?
assume that I have a primary key username " abc" in the database
and then I insert the "abc" again ,the debuggging error msg pops out saying the primary key cannot be duplicated..
how can I do an if--else statement to check the database against this unintentional inputs of the same unique data " abc"???
View 7 Replies
View Related
Jan 2, 2007
I am trying to delet a row duing run-time but everytime i go to save the program I get an error message saying: "Update Requires a valid DeleteCommand when passed DataRow collection with deleted row.
Anyone any ideas?
View 3 Replies
View Related
Jul 5, 2001
A table gets data every 4 minutes, I only need spread of every 15 minutes. Can I select only records spread every 15 minutes apart from this table without having to run a scheduled job every 15 minutes and loading one record closest to getdate() at that point into another table(this is how I am doing it now) Is there a better way. Please help
Thanks
View 2 Replies
View Related
Aug 22, 2006
Would anyone please instruct how to prevent the duplicate record bysetting the unique keys on the ms sql server? i've been checking theduplicate record as front-end and i found out if there is an internetdelay or some other reasons, it has a chance to store the duplicateddata into the database. so i realized it has to be done on the back-endside.for example, if i have three columns (office code, office id, officesection) as a unique key, how can i setup this? thanks in advance.
View 1 Replies
View Related
Nov 25, 2013
create table #temp_Alpha_num (
[uniquenum] [int] Not NULL,
[Somenum] [int] Not NULL,
)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values
(1,121)
[Code] ....
For the somenum column I need to add alphabets to make them unique. for example
121a,121b,121c....121z,121aa,121ab,101a and so on...
View 4 Replies
View Related
Aug 29, 2006
Hi, I have a doubt about the behaviour of SQL Server 2005 in the situation I'm going to describe you.
Suppose that you have a SQL Server 2005 database on your PC, and suppose that this database has a table with a column classified as "unique" (so it's impossible for this table to contain 2 records having the same value in this column).
Suppose that you publish this database and you create 2 SQL Server Mobile 2005 subscriptions on 2 Pocket PCs.
Suppose now that the first PPC (using an embedded program) creates a record with a certain value for the column (and adds it to the table), and the second PPC makes the same thing (it inserts a record with the same column value of the first PPC).
At this point, you connect the 2 PPCs to your PC (one by one, of course), to synchronize (using merge replication) the databases...
WHAT HAPPENS??? Does an error raise?
Must you give a publication setting in which you say that, if this situation occurs, PC SQL Server holds the last (or the first, as you decide) record acquired? Is it possible?
Thank you very much
View 1 Replies
View Related
Dec 11, 2007
I've googled everything about GUID, seems there is no good thing to say about it. Here's my scenario, a purchase order (PO) application:
We want to have a centralized database with remote sites connected to it
Some of the sites are without connection, they will have their own servers with scheduled replication to the centralized database.
The design is something like this:
Each PO will have many revisions
Each revision will have many PO line items
Each PO line item will have many Delivery Schedules
In the past i used int IDENTITY as transaction ID in revision and line item tables.
transaction ID in revision table is FK to line item table, and transaction ID line item table is FK to Delivery Schedules table.
This work well in standalone database.
Now that we need to merge replicates, int IDENTITY produced in remote DB will conflict with IDENTITY produced in central DB.
I'm thinking of using GUID to replace int IDENTITY.
Question:
How bad is my decision?
Can't GUID size indexing problem be solved with partitioning?
Can you suggest other alternatives to GUID, based on the above scenario?
Thanks in advance
View 4 Replies
View Related
Nov 19, 2004
Hi,
i am trying to add milliseconds to a time. For example if i have a time of 01:01:05:000 and i want to add 0.297 milliseconds to it i use the following simplified query
SELECT CONVERT(nvarchar(20), DATEADD(ms, 0.297, '00:01:05:000'), 14) AS Expr1
However instead of getting 01:01:05:0.297 i get 01:01:05:000. Can somebody please tell me what i am doing wrong.
Thanks in advance.
View 4 Replies
View Related
Nov 19, 2004
Hi,
i am trying to add milliseconds to a time. For example if i have a time of 01:01:05:000 and i want to add 0.297 milliseconds to it i use the following simplified query
Code:
SELECT CONVERT(nvarchar(20), DATEADD(ms, 0.297, '00:01:05:000'), 14) AS Expr1
However instead of getting 01:01:05:0.297 i get 01:01:05:000. Can somebody please tell me what i am doing wrong.
Thanks in advance.
View 2 Replies
View Related
Feb 27, 2004
Using FoodMart, how can i add time to this:
WITH MEMBER [Measures].[Rank] AS 'Rank ( Product.CurrentMember,Order( {Product.CurrentMember.Parent.Children} ,[Profit], DESC) ) '
SELECT
{[Profit] , [Rank]} ON COLUMNS,
Drink.Children ON ROWS
FROM Sales
View 2 Replies
View Related
Dec 12, 2005
I am trying to get a total for times, but I am having trouble gettinganything to work. Basically I created a query to pull up severalpeople's name that performed certain details and how long each detailwas. I now want to add up all the hours to a total for each person. Cananyone point me in the right direction?*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Oct 29, 2014
I have three different columns, hour(s), min(s), sec(s)
I can add it up, but will like to convert it into. hrs, mins and sec.
this is how, i am adding it up into seconds.
SELECT ((TotalTimeSpentHrs*60*60)+(TotalTimeSpentMin*60)+(TotalTimeSpentSec))AS totaltime
FROM EST1
How can I convert the total seconds, so that i can input the result in a new column.
View 1 Replies
View Related
Jul 20, 2005
I have a shopping cart that will get full from time to time becausecustomers click out of the site before they confirm their purchase,therefore leaving a full cart behind. I'd like to have a timestamp onthis table, so that I can delete anything that I find is more than a dayold.How can I do this?Thanks,Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Aug 4, 2015
I am trying to add integer value to the datetime to get the configurable "date".But it is not working out. Here is the code for it.
declare @Id int;
exec @Id = up_GetConfigurableDayInterval
print getdate() + @Id
The sp "up_GetConfigurableDayInterval", will return number of days to add the the current date time. But print getdate() + @Id , is not producing the updated result.But when ever i have replaced the variable "@Id" with a value, Say "5", it is producing the expected result.I have also tested the above code by the following:-
create table Temp(value datetime);
insert into Temp values(getdate() + @Id);
select * from Temp
But it is not having the new added datetime.
View 12 Replies
View Related
Jan 11, 2007
I know that I can add a column to a mining structure and/or model more than once using XMLA. However, I am having a problem doing the same using DMX. I now that DMX does not support data binding and that it relies on the INSERT INTO... construct to essentially map the structure columns to the incoming comluns from the data source. I tried to use "AS" like I would in SQL but it doesn't seem to like it.
Can anyone confirm that this can only be done using XMLA and not DMX? If I am mistaken, it would be greatly appreciated if you could provide an example on how to do this using DMX.
For background on why I would want to add the same column to a model more than once please read this post.
Thanks.
View 3 Replies
View Related
Oct 6, 2014
I have data that looks like the following.
ID Date Time Length Interval_Num
1 10/11/2014 9:00 420 14
Basically, length represents the # of minutes a person is scheduled for. We have 30 minute intervals. Interval_Num = (Length/30). I need the data to show each interval. For the above example it should only be 14 intervals.For the above example the solution should look like....
ID Date Time Length
1 10/11/2014 9:00 30
1 10/11/2014 9:30 30
1 10/11/2014 10:00 30
1 10/11/2014 10:30 30
for 14 intervals up until 16:00
View 4 Replies
View Related
Oct 30, 2006
Hello all. I was just wondering how I could add the current date and time to the name of the .bak file. I perform a full backup daily and would like to keep a weeks worth of backups, but everytime I run the job, it just overites the previous one.
The command in the wizzard is:
BACKUP DATABASE [snl] TO DISK = N'C:Backups_for_FTPSNL_Full.bak' WITH NOINIT , NOUNLOAD , NAME = N'snl backup', SKIP , STATS = 10, DESCRIPTION = N'SNL Backup', NOFORMAT
Thanks,
Parallon
View 2 Replies
View Related
Feb 26, 2013
I am trying to add the hours between each time block stored in a database.
In this database a user enters the begin time and the end time. For example the course MATH0001 would start at 8am and end at 10am. Therefore the user would enter 0810 in the start field and 1000 in the end field. The course MATH0001 doesn't run the entire semester it may only run from 8th Jan - 15th March and the course is scheduled in a room called GR4. Now because a course can be scheduled modularly, one room could have several courses scheduled in this manner.
The problem: I need to find out how many hours GR4 is used but it contains the following courses
Code:
Schedule for room called GR4
Course StartDate EndDate BeginTime EndTime HoursPerClass
MATH0001 06-FEB-13 19-FEB-13 0810 1700 9
MATH0002 20-FEB-13 04-Mar-13 0810 1700 9
MATH0003 10-JAN-13 05-Feb-13 0810 1700 9
MATH0004 22-APR-13 17-May-13 0810 1700 9
MATH0005 08-MAR-13 21-APR-13 0810 1700 9
MATH0006 07-JAN-13 09-JAN-13 0910 1300 4
MATH0007 20-JAN-13 17-MAY-13 1710 2000 3
MATH0008 08-JAN-13 18-JAN-13 1710 2000 3
A day only has 13 hours. Therefore the total hours spent in GR4 should be 12 hours. This is calculated by adding the hours between 8am and 5pm = 9 hours and 5pm an 8pm = 3 hours. I would not include 9am to 1pm because it is a subset of the 8am to 5pm slot.
Now, how to accomplish this but below is the code that i have thus far:
I forgot to mention that this code was to just test my 'final code' results and it outputs the table shown above. Anyway for testing purposes I have limited the search to the room GR4 and the day Tuesdays.
Code:
select DISTINCT ssrmeet_room_code,
ssrmeet_start_date,
ssrmeet_end_date,
ssrmeet_crn,
ssrmeet_begin_time,
ssrmeet_end_time,
(((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 As HoursPerClass,
[code]....
View 2 Replies
View Related
Nov 2, 2015
I have a report which takes around 5 seconds when run in BIDS but takes around 20 seconds when deployed on report server.Execution log says TimeDataRetrieval is around 3-4 seconds and rendering time is around 15-17 secs.From this report I am passing 8 parameters to a drill through report and there are 36 text boxes where I have defined these parameters for drill through action.All these parameters are populated in main dataset.When I deployed the same report without any drill through action and parameter, it takes 5 seconds.So I am suspecting that because of drill through parameters report is taking more rendering time on server.I am using 2008R2 and IE11.
Is it expected behavior that due to so many parameters for drill through action, report will take more rendering time?If yes, then why is it not taking same time in BIDS?
View 5 Replies
View Related
Nov 19, 2015
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015'
DECLARE @enddate date = '10-31-2015'
DECLARE @afterTime time = '17:00'
SELECT
General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
[Code] ....
View 20 Replies
View Related
Jul 5, 2015
This index is not unique
ix_report_history_creative_id
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
The statement has been terminated.
View 6 Replies
View Related
Jul 20, 2005
I have two columns in a table:StartDate DateTime and StartTime DateTime.The StartDate column holds a value such as 07/16/2004The StartTime column holds a value such as 3:00:00 PMI want to be able to add them in a stored procedure.When I use StartDate + StartTime I get a date two days earlier than expected.For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns7/14/2004 3:00:00 PM.Can anyone point out wht I'm doing wrong with this one?Thanks,lq
View 2 Replies
View Related
Sep 22, 2004
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
View 8 Replies
View Related
Jul 20, 2005
HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil
View 5 Replies
View Related
Jun 24, 2006
What's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
View 1 Replies
View Related
Mar 7, 2001
Hi everyone,
I need urgent help to resolve this issue...
As far as the performance goes which one is better..
Unique Index(col1, col2) OR Unique constraint(col1, col2) ?
Unique constraint automatically adds a unique index
and unique index takes care of uniqueness then whats the use of unique constraint ?
Which one do one use ?
thanks
sonali
View 4 Replies
View Related
Jan 20, 2006
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
View 2 Replies
View Related
Mar 26, 2008
hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
View 12 Replies
View Related
Mar 12, 2008
I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today.
Here is the code
Code Snippet
create procedure SearchPartKeyword
(
@Keyword varchar(250) = null,
@Universal_Id varchar(10) = null
)
as
select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM,
p.Legacy, p.Folder, p.Printer
from Parts p inner join notes n on p.PartNumber = n.Identifier
where n.Universal_ID = @Universal_ID and p.Description like @Keyword
union
select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly,
p.DMM, p.Legacy, p.Folder, p.Printer
from Parts p
where p.Description like @Keyword
and the results:
PartNo Description SO Notes LA DMM Legacy Folder Printer
de90008 MAIN BOARD 1 DGF1 114688 0 0 0
de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0
This could return multiple part numbers and If they have entered notes I want the row with the notes
Thank You
Dominic Mancl
View 1 Replies
View Related
Nov 13, 2007
What 's difference between Unique key and unique index in SQL server 2005?
View 9 Replies
View Related
