|
Hi,
I have a access database and my table of access database have a Auto number field. and i insert 50 record in the access data and then i delete the last 25 records in the database. after this i insert new record but auto generated filed show the Id is 51. but i want to change it with 26. and next is autogenerated according to this. how i do this. please suggest.
Thanks
~Khatri Mitesh
khatrimitesh@hotmail.com
Bikaner (Rajasthan)
INDIA
|
|
|
|
|
As far as I remember you can't (not used Access for nearly 5 years  ).
And why would you want too? Why was it made an Auto number in first place? If there's a good reason then you should not change it. For example, is it a foreign key in some other table?
However here's two possible approaches.
Use a non Auto number(long?) then before inserting a record do a SELECT to get the max in the field, add 1 to result and insert new record with this as value. You have to do this for every insert.
Alternatively, after deleting records, create a another table with same structure, copy records from the table to the new one (but exclude the Auto number field), delete the original table, rename the new one to the proper table name. Now you can insert and Auto number will work. But you would be mad to do this.
Regards
David R
|
|
|
|
|
Forget it, this is some stupid pratt who has no idea what a primary key is used for and is distressed about the missing numbers
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I was half hoping they'd go with the second alternative since it's obviously not the thing to do. 
Regards
David R
|
|
|
|
|
Do NOT frack with the autonumber process, it is used as a primary key, a primary key should have ABSOLUTELY NO intelligent meaning, there should be no relevance whether the number is 26 or 51, it should ONLY be used for maintaining the relational structures.
Read some books on data structures and database design. This is Database 101 and should be known by EVERY developer who touches a relational database.
Leave the bloody thing alone !slap!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Rather than beat up the poor questioner for asking, I'll just trust that he has his reasons and offer the suggestion that he use tools/database/compact and repair after deleting the records. This will cause the next autonumber to proceed in sequence. In general you should not do this for all the reasons others have supplied. But I have been known to do it myself when cleaning out test data before deployment.
|
|
|
|
|
I just finished installing a SQL server 2005 Express Edition and i just cant find the management studio. I had used this same CD before on my laptop and it installed perfectly well. Please can someone tell me what might be the cause?.....
Thanks
|
|
|
|
|
You need to install it separately. The installer is on the CD, can't remember exact location off the top of my head. Browse the CD in Explorer, you'll find it.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hello,
If you have internet, you can download it at :
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796
that's for SSMSEE 2005 without no service pack
You must install the same Service Pack for SQL Server Management Studio Express Edition than for your SQL Server
or you may use SQL Server 2005 Express Edition with tools to add SSMSEE ( same service pack )
|
|
|
|
|
Why are you posting to me?
I was answering the original poster, and, by the way, giving the correct response.
FYI there have been several posts here about problems with the stand-alone Management Studio Download. I don't know if they are true, but it is worth bearing in mind.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
i want to make a new server in sql server 2005 express or how to re name the server???
|
|
|
|
|
Servers aren't made, but installed. AFAIK, you can't rename a named instance.
You can install a second server-instance without touching the original, if needed 
I are troll
|
|
|
|
|
oky,,,
thanks
but how to install a second server-instance???
|
|
|
|
|
You have to run through the installer a second time. During the install the installer will ask you what the name of the instance should be
I are troll
|
|
|
|
|
|
You're welcome
I are troll
|
|
|
|
|
Good Morning All
i have the Following sql statement
DECLARE @idoc int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @TagXml
SELECT *
into [#Tag]
FROM OPENXML (@idoc, '/Tag',2) -- be very careful this is case sensitive
WITH ( ID int, Descr varchar(128), TypeID int, Note varchar(256), Static varchar(7), objID int )
-- get the list of ids from the edge table
SELECT convert(int, convert(varchar(10), [Text]) ) AS [ID]
into [#ObjList]
FROM OPENXML (@idoc, '/Tag/objID',2) -- be very careful this is case sensitive
WHERE [Text] is not null
Now from the above statements i understand this
DECLARE @idoc int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @TagXml
and the Problem comes here
-- get the list of ids from the edge table
SELECT convert(int, convert(varchar(10), [Text]) ) AS [ID]
into [#ObjList]
FROM OPENXML (@idoc, '/Tag/objID',2) -- be very careful this is case sensitive
WHERE [Text] is not null</pre>
I see that they convert varchar to int and call that ID , its cool i understand that and save it in a temp table. Now the unknown part is the part where
FROM OPENXML (@idoc, '/Tag/objID',2) -- be very careful this is case sensitive
WHERE [Text] is not null</pre></pre>
I understand what their doing, my Problem is that "2" why are they doing there. here is the Input example of the xml
'<Tag> <ID>0</ID> <Descr>new_taggy</Descr> <TypeID>1</TypeID> <Note /> <Static>true</Static></Tag>'
And MSDN Says
2 Use the element-centric mapping.
Can be combined with XML_ATTRIBUTES; in which case, attribute-centric mapping is applied first, and then element-centric mapping is applied for all columns not yet dealt with.
Can you please explain for me.
Thank you
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za
modified on Thursday, March 26, 2009 5:32 AM
|
|
|
|
|
Hi,
I want to load xml file i to the sql server 2005 table.
Foe e.x i have a xml file containing details
'<'employee'>'
'<'firstname'>'abc'<'/firstname'>'
'<'lastname'>'xyz'<'/lastname'>'
'<'/employee'>'
now i want to load this data into the table in sql server 2005 database.
The table contains 2 columns: firstname and lastname
i want to load this xml data to this table in to the corresponding column.
so is it possible through T-sql query.ID yes,please let me know and if not please give me some alternative to load this xml data in to the table.
Regards,
pranav.
Pranav Dave
|
|
|
|
|
|
hi
see this
http://www.codeproject.com/script/Forums/View.aspx?fid=1725&msg=2932578[^]
Hope it Helps
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za
|
|
|
|
|
hello
QUESTION 1: I scouted around a little bit, seems like in MySQL there's no such thing as NTEXT, only TEXT? Am I right?
http://dev.mysql.com/tech-resources/articles/visual-basic-datatypes.html
QUETSION 2: In MySQL you have DATETIME and TIMESTAMP, with TIMESTAMP you can have a table column to default to "current_timestamp" (but for DATETIME, in MySQL, it can only DEFAULT to NULL)
I hesistated to declare column as TIMESTAMP because my nhibernate file uses NHibernate type DateTime:
<component name="BasicAudit" class="Util.Security.BasicAudit">
<property name="CreateDate" column="CreateDate" type="DateTime" />
...
</component>
QUESTION 3: I am using VARBINARY in MySQL
create table xxx
{
...
blob varbinary(MAX), -- you don't have MAX as in M$SQL, what should I use in MySQL?
...
);
QUESTION 4. Reading blob - haven't got around to test this feature yet, big util lib ... is this right?
...
if (!Convert.IsDBNull(oRdr["blobValue"]))
{
byte[] blobValue = new Byte[(oRdr.GetBytes(10, 0, null, 0, int.MaxValue))];
oRdr.GetBytes(10, 0, blobValue, 0, blobValue.Length);
oValue.BlobValue = blobValue;
}
...
QUESTION 5. Does this corresponds to System.Data.DbType.Binary? I need to make this work for M$SQL/MySQL/Oracle. I understand there's "SqlDbType.VarBinary" but that's M$SQL specific and this is not available for IDbParameter (Db vendor neutral version of db param class in .net)
QUESTION 6: Is it true that you can only declare variable within stored proc or UDF in MySQL?
After scouting around for a long time it seems to be you can only declare variables from within storedproc or function!!
http://dev.mysql.com/doc/refman/5.0/en/if-statement.html
QUESTION 7. Is there an equivalent of TSQL PRINT statement (or dbms_output.put_line in case of oracle) in MySQL?
Thanks
dev
<div class="ForumMod">modified on Wednesday, March 25, 2009 10:43 PM</div>
|
|
|
|
|
ANS 1: Thats correct.
ANS 2: You are right. One can not use CURRENT_TIMESTAMP() function as a defult value for DATE and DATETIME datatypes in MySQL. However, this function can be used in TIMESTAMP datatype.
Additionally, one can also use NOW() function as a defult value for TIMESTAMP datatype.
ANS 3: MAX can not be used for VARBINARY.
The allowable maximum length is the same for VARBINARY as it is VARCHAR, except that the length for VARBINARY is a length in bytes rather than in characters.
ANS 4,5: Thats the way!
ANS 6: Variables can be declared and accessed outside Procedure and Functions as follows:
---------------------------------------
mysql> SET @a = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> select @a;
+------+
| @a |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
---------------------------------------
ANS 7: SELECT statements can used directly in Procedures/Functions in MySQL for viewing desired output as follows:
---------------------------------------
mysql> DELIMITER //
mysql> CREATE PROCEDURE print_test()
-> BEGIN
-> SELECT 'This is test printing' AS "Output";
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> call print_test();
+-----------------------+
| Output |
+-----------------------+
| This is test printing |
+-----------------------+
1 row in set (0.00 sec)
---------------------------------------
|
|
|
|
|
|
I have to syncronize two dbs one db is running SQL 2000 SP4 Ent
edition
The other SQL 2005 SP2 Ent edition
Goal is to have data be availabe as read\write on both systems and get
as close to real time replcaition from the SQL 2000 box to the SQL
2005 box as possible. The 2000 box will be sunsetted soon
I know I can take a full back up restore and do log shipping with
recover every 5 minutes or so but that seems to not be a good option.
Anyone have any ideas?
Thanks,
Regards,
Hulicat
|
|
|
|
|
Hi
I need any comparison between SQL server and Oracle (...types...function...)
thank's in advance
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
