VS 2022 can't put database into datasource

[arizona_mike87]

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.

[dday9]

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.

[techgnome]

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

[jdelano]

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)

[arizona_mike87]

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).

[arizona_mike87]

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.

[dday9]

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.

[arizona_mike87]

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

[dday9]

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.

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

What bug?

[arizona_mike87]

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.

[arizona_mike87]

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.

[arizona_mike87]

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>

[PlausiblyDamp]

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?

[techgnome]

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

[arizona_mike87]

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.

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...

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

[arizona_mike87]

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.

[dday9]

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.

[kareninstructor]

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.

[arizona_mike87]

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