-
Jun 14th, 2018, 05:08 PM
#1
Thread Starter
Hyperactive Member
[RESOLVED] msSQL 2017 Express
It appears that you can not set a Primary key field to auto fill with an integer. It does not allow the feature to be turned on in the properties Identity Specification can not be turned,
Any thought around this other than counting the number of rows prior to adding a new record?
Thanks
-
Jun 14th, 2018, 05:22 PM
#2
-
Jun 14th, 2018, 07:52 PM
#3
Re: msSQL 2017 Express
You certainly can do that. How EXACTLY are you trying to do it? Is this in SSMS Express?
-
Jun 14th, 2018, 08:00 PM
#4
Re: msSQL 2017 Express
You certanly can... the only reason you wouldn't be able to is if there's existing data that conflicted with it some how. Or there is some other issue.
-tg
-
Jun 15th, 2018, 09:35 AM
#5
Thread Starter
Hyperactive Member
Re: msSQL 2017 Express
I am just starting a project in VB 2017 Community. The Database is 2017 MSSQL Express the free downloadable from Microsoft. If I am going to write records I need to have some of the Primary Key fields auto increment. But if I can not turn this on How else do I get the next number other than look at recordcount and add 1 to it for the ID Field number. I have always used and ID type field for my Primary Key in my database work. This my first go with MSSQL as much as I hate to admit it I have always worked wit MSACCESS before But since I retired and took this job on I thought why not step up to real SQL.
-
Jun 15th, 2018, 09:38 AM
#6
Re: msSQL 2017 Express
Originally Posted by jmcilhinney
How EXACTLY are you trying to do it? Is this in SSMS Express?
Have you considered answering those questions? Are you saying, without actually saying, that you added an MDF data file to your project and you're building the schema in VS?
-
Jun 15th, 2018, 09:48 AM
#7
Re: msSQL 2017 Express
You need to answer jmc's questions... just HOW are you trying to set it up? Are you using the table designer or are you using SQL scripts? How are you determining that you can't use identity column? Are you getting an error message? If so, what is the error message?
Using record count and adding 1 isn't going to work... let's say you have 5 records, 1-5... and you delete record #4... you now have 4 records, 1-3 & 5... but your recordcount is 4... so you add 1, get 5... but that already exists... so, yes, you're on the right track, you want an Identity field for the table to auto increment when you add a record. But there's also some rules you need to abide by. First is in making sure that you set it up right. I only ever create tables through sql scripts, so I can't tell you how to do it from the designer. but in a script it looks like this: ID int not null identity(1,1) ... The other rule is when you insert... you can't insert into that field when creating the record... so you need to leave it out of the field listing.
Don't:
insert into tbl (ID, Fld1, Fld2, Fld3) values( 1, "F1", "F2", "F3")
Do:
insert into tbl (Fld1, Fld2, Fld3) values("F1", "F2", "F3")
-tg
-
Jun 15th, 2018, 10:32 AM
#8
Re: msSQL 2017 Express
I have my suspicions that the issue here is that you've done it before in SSMS and you can't do it the same way in VS. In VS, creating a table in an MDF data file generates a script in the lower window. As far as I can tell, the only way to make the PK an identity is to edit that script and you do it exactly as tg has shown, i.e. add "identity (1,1)" to the specification for that column.
Of course, if my suspicions are wrong then I've just wasted my time and yours providing irrelevant information. We should have to guess because you should give us the relevant information, especially when we explicitly ask for it.
-
Jun 15th, 2018, 12:09 PM
#9
Thread Starter
Hyperactive Member
Re: msSQL 2017 Express
i'll try to answer this the best I can. I made the database and the tables in MS SQL server Management Studio V17.7. I set the properties in my Primary Key field to Data type (INT) . Allow Nulls(NO). But the Identity Specification is to (NO) and I can change it.
Code:
ALTER TABLE [yourTable] DROP COLUMN ID
ALTER TABLE [yourTable] ADD ID INT IDENTITY(1,1)
I tried this from the MS SQL server Management Studio changing the table and column names
but it not work Error for table Employees
[COLOR="#FF0000"]
Msg 5074, Level 16, State 1, Line 1
The object 'PK_Employees' is dependent on column 'EmployeeID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN EmployeeID failed because one or more objects access this column.
This table has data in it. So I created a new Table "Table_1 with only one column EmpID and no Data received the same error.
So in answer to your questions that is what I have tried to do I have very little if any SQL background But I am trying.
I am trying to learn this by watching and coding from YouTube VB.NET Tutorial - INSERT records into a SQL Server Database 1 and 2 so far
Thanks for all your help
-
Jun 15th, 2018, 01:42 PM
#10
Re: msSQL 2017 Express
The identity field isn't your problem... it tells you what the problem is... you cannot drop a field that is your primary key. You need to first drop the PKey off of the field, THEN drop the field.
1) Alter the table and add the new column as an identity field
2) drop any FKey constrains on OTHER TABLES that reference the current PKey field
3) drop the current PKey
4) drop the old field
5) add a new pkey index on the new field
6) restore your foreign key references with the new field
it wasn't clear that you were dealing with an existing table with data already in it.
-tg
-
Jun 15th, 2018, 01:45 PM
#11
Re: msSQL 2017 Express
You should create your table with the FIRST COLUMN being the ID field - make it INT and NOT NULL and IDENTITY
If you have data in the table already you certainly cannot go dropping and re-adding that ID column - right?
-
Jun 15th, 2018, 09:20 PM
#12
Thread Starter
Hyperactive Member
Re: msSQL 2017 Express
Well I have tried and tried to create a table from your last instructions to no avail. I do not understand theand IDENTITY" " That makes no sense to me. Sorry my fault. But I can not get this ID column to auto increment when adding data through SQL Server Manager
I have created new tables with just two columns no avail. I deleted all of my other tables they are not any good until I fix this one table. Here is a screen shot
-
Jun 15th, 2018, 09:24 PM
#13
Re: msSQL 2017 Express
See how there's a little arrow next to Identity Specification? That means that you can expand that node. Expand it now by clicking that arrow and then blush with embarrassment.
-
Jun 15th, 2018, 09:53 PM
#14
Thread Starter
Hyperactive Member
Re: msSQL 2017 Express
Damn
I tried to change Identity Specification and could not I NEVER tried to change anything after expanding it.
What a dummy!!!
I sure wasted a lot of you folks time.
Thanks for the showing an old dog a new trick
Alfarata
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|