Answered Create Database problem in SQL

[SmokyRick]

I am currently going through a tutorial book called Visual Basic and Databases by Philip Conrod and Lou Tylee. I am doing well with it and learning a lot from it. However, I have got to a point where I can not figure out what my computer is doing. I am trying to create a database using the "CREATE DATABASE database_name" command from SQL in Visual Basic. I am putting a picture here showing my command with the error it has.



I see that there is no backslash between the C:\Users\Administrator and the filename (which is RicksDB) Has anyone come across this problem before? Does anyone know what to do about it?

Any help would be appreciated. Thanks for your time.

 

[JuggaloBrotha]

I am currently going through a tutorial book called Visual Basic and Databases by Philip Conrod and Lou Tylee. I am doing well with it and learning a lot from it. However, I have got to a point where I can not figure out what my computer is doing. I am trying to create a database using the "CREATE DATABASE database_name" command from SQL in Visual Basic. I am putting a picture here showing my command with the error it has.

View attachment 4417

I see that there is no backslash between the C:\Users\Administrator and the filename (which is RicksDB) Has anyone come across this problem before? Does anyone know what to do about it?

Any help would be appreciated. Thanks for your time.

The user account you're connecting to doesn't have Create Database permission, which is pretty normal as usually the database isn't create via code the program that uses it. Are you able to connect using SSMS and create the database there?

 

[jmcilhinney]

Are you sure that the tutorial is supposed to be run against a LocalDB instance? LocalDB is limited in a number of ways and is usually used to attach existing data files that already contain a database.

 

[SmokyRick]

Thanks for your replies, @JuggaloBrotha and @JMcIlhinney. This chapter shows 4 different ways of creating a database (that will be populated later). I am not familiar enough with this stuff to know how to use SSMS or to connect with any other connection than the local one I used. I spent three days (all day for 3 days) figuring out how to get connected with that, and am afraid to try with any other. I do know another way to create the DB, however, so I guess I will just proceed with that. This chapter showed me 2 ways to create an Access db and 2 ways to create an SQL db. This was the last of the four, so I can continue without doing it this way.
Maybe after I finish this book, I should learn more about the SSMS. I think that sounds reasonable.

Thanks for your help folks.
SmokyRick

 

[jmcilhinney]

Here are the two ways I'd suggest that you create a SQL Server database:

1. If you have installed VS then you likely have installed LocalDB as well, which is a stripped down version of SQL Server Express. You can confirm that it is installed and install it if it isn't by running the VS Installer. To create a database, right-click your project in the Solution Explorer and select 'Add' -> 'New Item'. In the 'Add New Item' dialogue, select 'Common Items' -> 'Data' on the left and then 'Service-based Database' on the right. After naming you database and clicking the 'Add' button, and MDF data file will be created and added to your project. That's the database created. You can then double-click that MDF file in the Solution Explorer to open a connection to the database in the Server Explorer. You then have a similar (although not the same) interface as SSMS provides. You can right-click the 'Tables' node and select 'Add New Table' and go from there to create the schema.

2. If you install SQL Server Express separately to VS then you can install SSMS as well. You can then open SSMS and connect to your SQLEXPRESS instance, then right-click the 'Databases' node in the Object Explorer and select 'New Database'. Once the database is created, you can build the schema in the same way as before.

Note that, in the first case, the MDF data file is part of your project and gets attached to a SQL Server instance at run time. That means that the database is deployed with your application and attached to the user's local SQL Server Express instance when they run your app. In the second case, the database is permanently attached to a SQL Server instance and is not part of your project or application. That means that it needs to be created independently of your application when you deploy. There are a number of ways that that might be done, e.g. SQL script(s) or restoring a backup, and you can do it on a single server and have every user connect to that one database.

Your connection string will differ in each case as a result. In the first case, the 'Data Source' will be something like "(LocalDB)\MSSQLLocalDB" and the 'AttachDbFilename' will be the path of your MDF data file. In the second case, the 'Data Source' will be something like ".\SQLEXPRESS" and the 'Initial Catalog' will be the name of the database. In both cases, you may well have to change the connection string once the app is deployed.

 

[SmokyRick]

This is solved now. I took the time to learn enough about SSMS to be able to create a database in it. It really wasn't that much different from what I was doing in Visual Studio. However, when I create it here, I can look at the connection and see what connection to use in Visual Basic when I am writing the code. Thanks for your help, it is appreciated.