[RESOLVED] Give myself permission to access a sql database I created through a vb.net program?

[Darby]

I am trying to create a sql database, and table, programmatically with vb.net. The database gets created OK but when it tries to create the table, I get the following exception error:

System.Data.SqlClient.SqlException
HResult=0x80131904
Message=The specified schema name "MoviePlot" either does not exist or you do not have permission to use it.
Source=.Net SqlClient Data Provider

MSSMS shows the database exists, but there is no table.

I plagiarized from one of the forums and used the following code changing only the filenames and table criteria. The program creates the database on my computer of which I am the administrator. How do I change the security criteria when I create the database so it will create the table? Better question, why do I have to?

My version:

Imports System.Data.SqlClient
Public Class Form1
    Private Sub CreateDatabase_Load(sender As Object, e As EventArgs) Handles 
         MyBase.Load
    End Sub

    Private Sub BtnCreateDatabase_Click(sender As Object, e As EventArgs) 
        Handles btnCreateDatabase.Click
        Dim Conn As New SqlConnection("Data Source=DESKTOP- 
            H7RL6S9\SQLEXPRESS;Initial Catalog=master;Integrated 
              Security=True")
        Dim Text1 As String = "I:\Developement\CreateDatabase"
        Dim Text2 As String = "MoviePlot"
        Using Conn
            Conn.Open()
            Dim cmd As SqlCommand = Conn.CreateCommand
            Dim str As String = "CREATE Database {0} ON (Name= N'{0}', 
                FileName='{1}\{0}.mdf')"
            Dim Tbl As String = "CREATE TABLE MoviePlot.MyPlots (Title 
               NVARCHAR(150) NOT NULL Primary Key, Plot NVARCHAR(750)  NULL)"

            cmd.CommandText = String.Format(str, Text2, Text1)
            cmd.ExecuteNonQuery()
            MessageBox.Show("Database is created successfully",
        "MyProgram", MessageBoxButtons.OK,
        MessageBoxIcon.Information)

            cmd.CommandText = String.Format(Tbl)
            cmd.ExecuteNonQuery()
            MessageBox.Show("Table is created successfully",
             "MyProgram", MessageBoxButtons.OK,
             MessageBoxIcon.Information)
        End Using

    End Sub

End Class

[Shaggy Hiker]

When you write 'MoviePlot.MyPlots' you are saying, "create table MyPlots in Schema MoviePlot". You almost certainly don't want to do that. You didn't mention anything about creating a schema, and schema are not all that common, overall, so just leave off the MoviePlot part, or use dbo. So, either:

CREATE TABLE MyPlots...etc.

or

CREATE TABLE dbo.MyPlots...etc.

[kareninstructor]

Your code is difficult read not being in a code block so my question - is the connection for creating the table for the database? It appears the connection is pointing to master, not the newly created database.

[Darby]

Shaggy Hiker: I tried both options and received the same exception for both:

System.Data.SqlClient.SqlException: 'There is already an object named 'MyPlots' in the database.'

Then I went to SQL and created a Query in T-SQLand received a similar exception:

SQL Query:

Create Database MoviePlots
Create Table MyPlots (
Title Nvarchar(100) Not Null,
Plot nVarChar(750) Not Null,
Rated nVarChar(5) Not Null,
Constraint Title_pk Primary Key (Title)
)
Go

Exception:

Msg 2714, Level 16, State 6, Line 2
There is already an object named 'MyPlots' in the database.

The problem has to be me, but I have no idea what I'm doing wrong.

[PlausiblyDamp]

Shaggy Hiker: I tried both options and received the same exception for both:

System.Data.SqlClient.SqlException: 'There is already an object named 'MyPlots' in the database.'

Then I went to SQL and created a Query in T-SQLand received a similar exception:

SQL Query:

Create Database MoviePlots
Create Table MyPlots (
Title Nvarchar(100) Not Null,
Plot nVarChar(750) Not Null,
Rated nVarChar(5) Not Null,
Constraint Title_pk Primary Key (Title)
)
Go

Exception:

Msg 2714, Level 16, State 6, Line 2
There is already an object named 'MyPlots' in the database.

The problem has to be me, but I have no idea what I'm doing wrong.

I would look at Karen's suggestion - it almost certain you are still in the master database and not the MoviePlots database. You would either need to switch to the newly created database or use a three part prefix for your object names e.g. MoviePlots.dbo.MyPlots

[Darby]

PlausiblyDamp, Thank You, your help was very helpful. See reply to Karen above.

[Darby]

Karen, I apologize for not responding earlier. Between your and PlausiblyDamp's replies I have solved the problem. I wasn't totally sure of what you were trying to tell me but when I read Plausibly's reply and changed my code using the 3-part prefix everything worked out. I apologize also for my poor outlining of my code, I am a 78-year-old self-taught VB.Net programmer and have forgot more than I like. This code is going to be part of a larger app that will list all the movies I have (994) with a description of the plot and rating. Again, Thank You for the assistance.

[kareninstructor]

One when is self-taught many times the person does not perform deep dives into what they are attempting to achieve thus can cause undue wait time for others to reply and then take time to understand said reply. Where I'm going with this is, rather than attempt to fix code, instead take ample time to understand the code you wrote and in the case of databases (in this case SQL-Server) use available tools like SSMS to validate the data aspect of a task before writing any VB code.

Anyways good you are back on track now.