Results 1 to 19 of 19

Thread: VS 2022 can't put database into datasource

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Dec 2023
    Posts
    22

    VS 2022 can't put database into datasource

    Hi everyone! So I...

    - Start up VS 2022
    - Go to SQL Server Object Explorer
    - Go to (localdb)\ProjectModels and add a new database
    - Add a table to the database and input some info
    - Go to Data Sources
    - Try to add the database I just created
    - Get the error message "This file is in use. Enter a new name or close the file that's open in another program".

    I have no other programs open.

    Try different locations and different options. Nothing works.

    Visual Studio can't connect to a database it just created?

    Do I have to log in as administrator and change file permissions or something?

    Good thing I went bald or I'd be pulling my hair out.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,801

    Re: VS 2022 can't put database into datasource

    My first thought is what if the connection that was opened via the SQL Server Object Explorer is somehow keeping the connection open which prevents it from being used. If that's the case, restarting Visual Studio and not opening the object explorer might resolve your issue.

    Total shot in the dark though.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,562

    Re: VS 2022 can't put database into datasource

    Yeah, it's been a hot year or two since I've used it, but yeah, since you have it as a local db, it locks the db file so it can do what it needs to do. You can either quit the Object Explorer, and/or restart VS, but that may be extreme. You should be able to disconnect or close the connection from the Object Explorer, which would then free up the database file so you can connect to it from the DataSources interface in Visual Studio.

    -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??? *

  4. #4
    Addicted Member
    Join Date
    Jul 2022
    Posts
    243

    Re: VS 2022 can't put database into datasource

    If you have Windows Power Toys installed, you can right click on a file and select "What's using this file?" of course you can check this in the Resource Monitor as well via the performance tab in Task Manager, click the three dots, then Resource Monitor and view the Disk Activity to see which files are open (it doesn't identify which application though)
    Attached Images Attached Images   

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Dec 2023
    Posts
    22

    Re: VS 2022 can't put database into datasource

    That was it, I had to detach them from the Server Object Explorer and then close it. I figured the Server Object Explorer was a part of Visual Studio which is why I couldn't figure out why there would be a lock on the file. Maybe it's a third party add-on or something. Anyway, it works, so it's a Super Happy Friday! Thanks everyone and have a great weekend!

    Also - the database must be empty when you add it to the DataSource otherwise you get the 'cannot find type' error. So it works if I go to the Server Object Explorer, create the databases I need but don't add any tables or anything to them, then detach the databases from the Server Object Explorer and they can be added into the DataSource.

    You can add tables to the database in the Designer... now I just have to figure out how to put data into the tables.

    I'm doing all this in the designer because I don't know SQL... maybe it's time to learn it?

    Can't figure out how to add data to the tables so I go back into Server Object Explorer, hit refresh to make the empty databases show back up, add tables and data while in Server Object Explorer, hit build...

    Get error message BC30545 Property access must assign to the property or use it's value.

    Get warning message BC40004 property 'Clear' conflicts with sub 'Clear' in the base class 'DataSet' and should be declared 'Shadows'.

    Well I'm getting different error messages so I must be making progress!

    I delete the dataset from the datasource and the errors go away.

    Hmmm, maybe I have to detach the database from the Server Object Explorer again.. I go back into the Server Object Explorer, right click on the database and click Detach...

    'Cannot detach the database because it is currently in use' - yes, by everyone except me LOL.

    So now I can't detach the database from the Server Object Explorer nor can I add it to the DataSet. Oh well, delete everything and start over I guess.

    I knew I should have been a plumber.

    Close Visual Studio, go to My Documents and physically delete the database.

    Restart Visual Studio, go to Server Explorer and it's still there. Try to delete it and -

    ' Cannot delete database as physical file does not exist' - um, if the physical file does not exist why the hell did you list it?

    Shut everything down and reboot the computer.

    Restart Visual Studio, go to Server Explorer and it's finally gone.

    Sitting here grateful that I didn't have to reformat the hard drive.

    Guess I'll have to go to YouTube and watch a couple of hour long videos (and about two dozen ads).
    Last edited by arizona_mike87; Mar 22nd, 2024 at 12:13 PM.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Dec 2023
    Posts
    22

    Re: VS 2022 can't put database into datasource

    Out of curiosity I got the NorthWind sample database out of VB6 and tried to connect to it -

    "Cannot open a database created with a previous version of your application"

    What's the definition of job security?

    Writing error messages for Microsoft products.

  7. #7
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,801

    Re: VS 2022 can't put database into datasource

    To be honest, it's probably the driver. I.e. it was created using JET but Visual Studio uses ACE.

    Again, just a total shot in the dark.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Dec 2023
    Posts
    22

    Re: VS 2022 can't put database into datasource

    I found the solution!

    Uninstall Visual Studio and go back to Visual Basic 6.

    This program is an absolute buggy mess. Maybe I'll try again in a couple of years when enough patches have come out to make it usable.

    Wait, am I talking about a Microsoft product or a Bethesda game here?

    Now I know why Microsoft bought Bethesda. They were made for each other.

    But after going through all this I'm starting to think Bethesda bought Microsoft.

    Thanks for letting me ramble on.

    Oh and dday9, that bug in your sig made me smack my monitor LOL

  9. #9
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,801

    Re: VS 2022 can't put database into datasource

    Quote Originally Posted by arizona_mike87 View Post
    I found the solution!

    Uninstall Visual Studio and go back to Visual Basic 6.
    That is probably the absolute worst decision you could have made based on what you provided us with, but to each their own.

    Quote Originally Posted by arizona_mike87 View Post
    Oh and dday9, that bug in your sig made me smack my monitor LOL
    What bug?
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Dec 2023
    Posts
    22

    Re: VS 2022 can't put database into datasource

    You know thinking about it I bet it's a Windows 11 issue. I was chatting with a bot on the Visual Studio developers forum and it told me what to do. I followed it's instructions and this is what happened -

    < start of conversation >

    - Connect the sql server in SQL Server Object Explorer.

    I go to SQL Server Object Explorer. I expand SQL Server and there are two entries -
    (localdb)\MSSQLLocalDB(SQL Server 15.0.4153 with my user name after it)
    (localdb)\ProjectModels(same as above)


    - Right click ‘Databases’ then click ‘Add New Database ’ to create a new database, such as Test1.

    Not knowing which one to use I expand MSSQL… and create a new database ‘Test1’. I put it in My Documents so I know where it is.


    - Expand ‘Test1’ database, right click ‘Tables’ then click ‘Add New Table’.
    - Add some columns for the table, then click ‘Update’ to update the database to server.
    - Add some row for the table, then Open ‘Server Explorer’.

    That all works fine.


    - Connect ‘Test1’ database in ‘Server Explorer’.

    I open Server Explorer and click the ‘Connect to Database’ icon.

    I hit the browse button and go to My Documents and click on Test1.mdf

    I get an error message saying ‘This file is in use. Enter a new name or close the file that’s open in another program.’

    I then found if I create the databases in SQL Server Object Explorer and then restart my computer I’m able to connect to them in Server Explorer. Just closing and re-opening VS2022 doesn’t do it.


    - Open ‘DataSources’ wizard then adds a new Dataset with the new table of ‘Test1’ database.

    I click on ‘Add New Data Source’, select Database then Dataset and then click on ‘New Connection’.

    I browse to My Documents and select the Test1.mdf database.

    Once again I get the error message ‘This file is in use. Enter a new name or close the file that’s open in another program’.

    I closed the Server Explorer window, saved the project and rebooted.

    I re-opened the project and saw that neither the Server Object Explorer or Server Explorer windows were open and kept it that way.

    Trying again I was able to get all the way through and the Test1DataSet shows up in the Data Sources window. Hooray!!


    - Follow above 8 steps to generate second database and create second dataset

    I go to add a second datasource by following the same procedure. I get to Add Connection, browse to My Documents and select Test2.mdf - and once again I get the ‘file is in use’ error.

    I save everything, close all programs and reboot yet again.

    Try again, everything works and now I have both DataSets in my Data Source window.

    < end of conversation >


    So it seems I'm constantly having to reboot to clear file locks which would probably be more of an issue with Windows 11 than with VS2022. Come Monday I'm going to install VS2022 on a Windows 10 machine and see what happens. I bet it clears up.

    Sorry if I got a little iffy there... it can get frustrating. I'll let you know what happens.

    Thanks again.
    Last edited by arizona_mike87; Mar 22nd, 2024 at 09:23 PM.

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Dec 2023
    Posts
    22

    Re: VS 2022 can't put database into datasource

    It appears not to be a Windows issue.

    I installed VB2022 on a Windows 10 machine and the same thing happened, I had to reboot before I could add a dataset and reboot again before I could add another.

    I was doing all this by adding databases under 'MSSQLLocalDB' so out of curiosity I tried it with adding databases under 'Project Models'.

    Once again I had to reboot to add the first dataset but not for the second dataset. As I had quite a few test databases by this time I tried adding others, some gave me the 'file in use' error and some didn't.

    I checked the permissions on the databases and it said I have full control.

    So, far as I can tell, it's a file lock issue with VB2022.

    Short of rebooting is there any way to clear the lock on a file?


    Edit - I got this back from the bot on the developers forum -

    We have reproduced the issue following your steps. Upon investigation, we found that when you create a database using this procedure, a sql server instance is automatically created locally, so the generated .mdf is used by this instance. You can stop this instance by executing the following command from a command prompt with administrator privileges and then you will be able to connect to the .mdf in the server explorer.

    Command: SqlLocalDB.exe stop MSSQLLocalDB

    Not really sure what all that means but I'll try it.
    Last edited by arizona_mike87; Mar 25th, 2024 at 10:37 AM.

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Dec 2023
    Posts
    22

    Re: VS 2022 can't put database into datasource

    No luck -

    PS C:\Users\administrator> SqlLocalDB.exe stop MSSQLLocalDB
    Stop of LocalDB instance "MSSQLLocalDB" failed because of the following error:
    The specified LocalDB instance does not exist.
    PS C:\Users\administrator>

  13. #13
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,545

    Re: VS 2022 can't put database into datasource

    Did you try the suggestion from post 4, https://www.vbforums.com/showthread....=1#post5636984 to see if you can identify what is locking the file?

    Do you have any backup software, onedrive, etc. configured?

  14. #14
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,562

    Re: VS 2022 can't put database into datasource

    Local DBs are more file based than server based - if you were to install SQL Express and create a database in there, you'd have a different experience. But because what you're doing is essentially writing to the mdf file itself, it's going to get locked whenever there's a connection to it. That's not a problem, it's just something that needs to be understood. So... how does it get unlocked? Step one is to close all connections to it... this includes anything with the designer, or running code... if you .open a connection make sure you .close it. If you've got it open in the data designer, or in the object viewer... you'll want to close those down as well.

    The command "SqlLocalDB.exe stop MSSQLLocalDB" is supposed to tell MSSQLLocalDB process to "stop" running the "MSSQLLocalDB" database and release it. You'll want to change the "MSSQLLocalDB" (the final parameter) to what ever db you want to shut down.


    -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??? *

  15. #15

    Thread Starter
    Junior Member
    Join Date
    Dec 2023
    Posts
    22

    Re: VS 2022 can't put database into datasource

    Quote Originally Posted by PlausiblyDamp View Post
    Did you try the suggestion from post 4, https://www.vbforums.com/showthread....=1#post5636984 to see if you can identify what is locking the file?

    Do you have any backup software, onedrive, etc. configured?
    I don't have Windows Power Toys installed but I may try it later, thanks. No backup, onedrive, cloud stuff or anything like that.


    Quote Originally Posted by techgnome View Post
    Local DBs are more file based than server based - if you were to install SQL Express and create a database in there, you'd have a different experience.-tg
    I thought if maybe I created a database with something other than VS2022 it may go better... so I installed SQL Express and then installed SSMS (which needed a registry hack to make it work what's up with that), created a database and added a table with some info. Worked fine except VS2022 is denied permission so that's a different Tangent altogether...

    I was able to load SQL Express into the Server Object Explorer but trying to connect to the database gives me 'error 40 Could not open a connection' so I guess that's a Cosine...



    Quote Originally Posted by techgnome View Post
    The command "SqlLocalDB.exe stop MSSQLLocalDB" is supposed to tell MSSQLLocalDB process to "stop" running the "MSSQLLocalDB" database and release it. You'll want to change the "MSSQLLocalDB" (the final parameter) to what ever db you want to shut down.
    -tg
    Tried changing the name, no luck. I actually did get MSS... to work one time but it hasn't worked since.


    I told the company that I'm a CNC programmer (the job I was hired to do) but they said "A programmer is a programmer" so now I'm fumbling in the dark trying anything and everything hoping to find a way to make it work... the strange thing is that I'm sort of enjoying it in a weird Dark Souls kind of way, you have to fight the boss 50 times but it feels so good when you finally defeat it

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Dec 2023
    Posts
    22

    Re: VS 2022 can't put database into datasource

    Well I've tried everything I can think of and every suggestion that's been thrown my way, it just doesn't work and that's that.

    So much like a boss in Dark Souls that I can't get past I'll go do other things and come back later when I'm more experienced... and when they come out with a patch.

    Is anyone even using this program? I can't believe nobody has found a bug this large yet.

  17. #17
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,801

    Re: VS 2022 can't put database into datasource

    I personally do not use file based databases. I typically either use a server based database or occasionally I will use the Isolated storage (documentation).

    But what you're describing with a file being locked is not something that is uncommon. The SQL Server Object Explorer is obviously locking the file and you should be disconnecting from it.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  18. #18
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,688

    Re: VS 2022 can't put database into datasource

    Instead of using SQL Server Object Explorer, install SSMS

    Using SSMS to create NorthWind database

    1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
    2. Right-click Databases, and then select New Database.
    3. In New Database, enter a database name, in this case NorthWind2024
    4. Select OK
    5. Right click on NorthWind2024, select New query.
    6. Copy the contents from here into the new query
    7. Press F5 to create the database.


    Notes

    If using .NET Core there is no way to use the data source window functionality, must be for instance .NET Framework 4.8 or lower.

    Stick to using SQLEXPRESS for working with database rather than LocalDb for applications. I've never encountered locking issues going with this path.

    Stay away from creating databases that reside in the project structure until you have a firm grasp on the ins and outs for working with these databases.

    The NorthWind2024 version of Microsoft NorthWind is much better database which I modified from the original.

  19. #19

    Thread Starter
    Junior Member
    Join Date
    Dec 2023
    Posts
    22

    Re: VS 2022 can't put database into datasource

    You know what, I think I finally found it.

    I was making a database to store glass information and made tables named clear, frosted, beveled etc...

    Well it turns out you can't use 'clear' for a table name because it causes errors - I found this line in the glass dataset designer with the highlighted word marked as an error. What threw me off is that 'clear' is not one of the reserved keywords. I renamed the table 'clr' and it works. Live and learn!

    Public ReadOnly Property clear() As clearDataTable
    Get
    Return Me.tableclear
    End Get
    End Property

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