Results 1 to 14 of 14

Thread: [RESOLVED] msSQL 2017 Express

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    Resolved [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

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: msSQL 2017 Express


  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: msSQL 2017 Express

    You certainly can do that. How EXACTLY are you trying to do it? Is this in SSMS Express?

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    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.

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: msSQL 2017 Express

    Quote Originally Posted by jmcilhinney View Post
    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?

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    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

  10. #10
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    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

    Name:  SQL server Manager.jpg
Views: 196
Size:  32.0 KB

  13. #13
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    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
  •  



Click Here to Expand Forum to Full Width