|
d00_ape wrote: Retrieving the next value that an identity column would get if a new row is inserted.
IDENT_CURRENT + 1[^] gives the next identity value for specified table.
d00_ape wrote: Change the value of the next value that will be created when the next row is inserted.
Set IDENTITY_INSERT[^] to "on" for inserting into an identity column.
|
|
|
|
|
1. OK - IDNTITY_CURRENT + 1 works perfect for me!
2. I don't know if I missunderstand but can really helpe me to modify what the next identity number will be? I dont want to manually do INSERTs with the identity column value, only step the counter forward to a specific value.
----------------------------------------------------------------
My goal is to make users of a program (that uses the db and the table) to set a new start value each year.
Year 2008: next new row id = 200800001
Year 2008: next new row id = 200900001
I use the id to represent an Invoice-number and my clients like to see on the number from which year it is created.
_____________________________
...and justice for all
APe
|
|
|
|
|
d00_ape wrote: modify what the next identity number will be
Ok, I misunderstood you. If you need to change the next identity value, reseed it. Try DBCC CHECKIDENT (table, reseed, value)
|
|
|
|
|
Perfect!! That's what I needed!
_____________________________
...and justice for all
APe
|
|
|
|
|
you can do this using two methods.
1) using sql server column identity property:
in this method, which column you want to give identity, open the table definition and right click on the field (which you want to be indent), set the identity specification - IsIdentity to 'yes' & Identity Increment and Identity seed to 1.
2) using c# in your asp.net page:
using asp.net page, first get the maximum record of the table using max(column name) using data reader & increment that value to 1 and assign that value to new inserted row.
for ex:
sqlconnection cn = new sqlconnection();
cn.open();
string str = "select max(userid) from user";
sqlcommand cmd = new sqlcommmand(str,cn);
idatareader rdr = cmd.executereader();
int id = 0;
if(rdr.read()==true)
{
id = rdr[0].tostring();
}
id = id+1;
now the id you will get is incremented id for inserting new query or row to table.
use this id and insert value of this id as the field of userid to insert new row.
|
|
|
|
|
Hi,
I need help with an sql query please.
I wish to insert a new record into a table, and instantly retreive the new records id. Is it possible to do this in one sql statement rather than an INSERT followed by the SELECT statement?
My INSERT looks like this:
<code>string conn = System.Configuration.ConfigurationManager.ConnectionStrings["artisanConnectionString"].ConnectionString;
SqlConnection uniqueCon = new SqlConnection(conn);
//set SQL statement
SqlCommand cmd = new SqlCommand("INSERT INTO [tbl_Additional] (add_Item, add_Price) VALUES ('" + TextBox10.Text + "', '" + TextBox11.Text + "') ", uniqueCon);
cmd.Connection.Open();
try
{//insert/ add new record
//commit to database
cmd.ExecuteNonQuery();
//clear form add/edit
TextBox10.Text = null;
TextBox11.Text = null;
//confirm to user
ShowMessageBox("New unique Item Added!");
//update dataset
GridView2.DataBind();
}
catch
{
ShowMessageBox("Unable to add record!!! Please try again later.");
}
cmd.Connection.Close();</code>
Any help would be greatly appreciated.
Thanks & Happy Easter!
|
|
|
|
|
You should use a stored procedure, then you can populate an OUTPUT parameter with SCOPE_IDENTITY() value, example: here[^].
|
|
|
|
|
Hi,
Any one suggest me how to write a sql query to execute the following.
I am having three tables as show below:
Table 1:
EmpID Name
1 aaa
2 bbb
3 ccc
Table 2:
SalID EmpID DesigID Salary
1 1 000000 000000
2 3 000000 000000
Table 3:
DesigID SalID DesigID Left
1 1 000000 false
2 2 000000 True
In the above i have to get the SalID,Salary details, based on the below condition
1. Based on the EmpId
2. The DesigID from table 3 should be retireved, but that DesigID should not be presented in Table 2(only those rows not presented in table 2 should be retrieved from table 3) based on the DesigID in both tables, also with one more condition.
3.Also i have to retrieve the rows from table 3 if the boolean value is true based on the SalID.
Am i clear with the above process? If no please kindly excuse me.
Any one please suggest me
Thanks in advance
Know is Drop, Unknown is Ocean
|
|
|
|
|
how to get columns of table in order of their ordinal position instead of
alphabetical order of column name?
cn.GetSchema("Columns", restrictions)
output: column_name ordinal_position
...........ambit...............3
...........noise...............1
...........z...................2
required output: column_name ordinal_position
....................noise...............1
....................z...................2
....................ambit...............3
|
|
|
|
|
I want the columns of table in order of their ordinal position.
is it possible?
|
|
|
|
|
exec sp_columns '[table name]'
If that's too much info then you could try:
SQL 2000
SELECT o.name, c.name as columnname FROM syscolumns c
INNER JOIN sysobjects o ON o.id = c.id
WHERE o.[name] = 'reports'
ORDER BY c.colorder
SQL 2005
SELECT O.name, C.name as columnname FROM sys.columns C
INNER JOIN sys.objects O ON O.object_id = C.object_id
WHERE O.[name] = 'reports'
ORDER BY c.column_id
|
|
|
|
|
Thank You Mark. It Works. Well Done.
|
|
|
|
|
i m planning to create a job site,so plz guide me for design the database,tables in sqlserver 2005
|
|
|
|
|
If you can't design the tables are you sure you are competent enough to create a complete web site?
Try reading up on the subject rather than asking someone to do your work for you.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
AbhishekAgarwal15 wrote: so plz guide me for design the database,tables in sqlserver 2005
Well - I'd start by gathering requirements, working out what fields need to be in the database and then normalize it to at least third normal form. Here's a technical hint for you - consider investing in some paper and pencils for your design.
|
|
|
|
|
You need to do alot of planning, investigation and searching (on the web). If you don't know how to design the database and tables I agree with the other person find out for yourself. You'll get alot more satisfaction if you've done the work yourself. Trust me I know! 
Kris MCP
|
|
|
|
|
Hi All,
I wanted to go for the OCP. Can any one please let me know How many exams I have to give? Which are the exams and the fees structure? And from where I can give this exam and how to pay the fees of the exams. I wanted to do OCP in 9i or 11i.
Thanks,
RK
|
|
|
|
|
Have you tried looking on the Oracle web site? I bet they have loads of information about OCP 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi, I have the following problem,
I have 2 different databases for 2 seperate systems I some of the data in db 2 is needed in db 1, I want to create an sql trigger for ms sql 2000 that will run when ever an update happens in db 2 the fields that I want in db 1 from db 2 must update with the changes made in db 2.
So my problem is in the trigger how to I get the values that where updated into the update stament I want to send to the other database
CREATE TRIGGER tr_table_UPDATE
ON dbo.vehicle_db1.Vechicle_list
FOR UPDATE
AS
Update dbo.vehicle_db2.Vechicles set Registration_No = 'PYS 789 GP', Model = 'Data from the original update' where ID = 'id from original update'
I hope this makes sense, in a way its kinda replication.
Thank in adavance.
|
|
|
|
|
If the databases are on the same server then there is no need to replicate the tables because you can reference tables using:
select * from <database>.<owner>.<table> If the databases are on separate servers, but have reliable connectivity then consider using "linked servers" (look them up in BOL).
Otherwise consider using "replication" (look it up in BOL).
Hope that helps.
Andy
|
|
|
|
|
thanks man, but thats not what I need, i will have redesign the entire package if I am to go that route this is supposed to be a quick fix, I know its not ideal, but I figured it out about an hour ago thanks.
|
|
|
|
|
I have a table having columns like values, like this
col1 col2 col3
1000 10 2
1000 20 3
1000 30 4
2000 10 5
2000 20 6
2000 30 7
3000 10 8
3000 20 9
3000 30 10
I want to fetch records like if i enter col2 as 20 and col1 as 1500 then
with
"SELECT col3 FROM table1 WHERE col2 = @col2"
I will get 3 rows having respective records of value 20 for col2(col2 values are fixed)
but col1 values are variable i.e. can fall into different ranges like 1400,1500,2300 etc.
but col2 values are fixed
I want to return only one row for eg. col2 as 20 and col1 as 1500
then
1000 20 3
this row should get returned
How can i do this?
I don't know how to use cursors...
|
|
|
|
|
salon wrote: I don't know how to use cursors...
Any problem for searching and learning how it works ?
|
|
|
|
|
No issues to learn...
I am just asking is there anyone have idea to do it in optimized way?
as I am trying
col1 have variable inputs like 1200,1300,2100 etc...
and col2 have fixed inputs
I am doing like this calculating min and max values for col1
and trying to put col2 in that range
but till I m not able to achieve...
|
|
|
|
|
There's no problem in using a cursor within a while loop -> have a look at the MSDN, to review the example and you'll have your're problem solved.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
