How do I create a new Database and new tables in SQL MSDE 2000 programmatically with C#.net?

Question

I am writing a program that will create a new datatbase, then add tables to that datatbase. Here is my code....

InsertTable("Create Database iBlast", "Null");
InsertTable("Create Table iBlast.tblBoreHoles (HoleID uniqueIdentifier, HoleName nvarchar(40), JobID uniqueidentifier, CreateDate datetime, Longitude float, Latitude float, Altitude float, HoleDia real, ExpectedDepth float)", "iBlast");


static void InsertTable(String sqlQuery, string InitialCatalog)
{
    SqlConnection sqlConn = new SqlConnection();
    //sqlConn.ConnectionString = "Data Source=VIRTUAL2KB;Initial Catalog=PCS6000SQL;User ID=sa;Password=password;Integrated Security=False";
    if (InitialCatalog == "Null")
    {
        sqlConn.ConnectionString = "Data Source=VEEMER11;Integrated Security=True";
    }
    else
    {
        sqlConn.ConnectionString = "Data Source=VEEMER11;Initial Catalog=" + InitialCatalog + ";Integrated Security=True";
    }   
    sqlConn.Open();
    SqlCommand sqlCommand = new SqlCommand(sqlQuery, sqlConn);
    sqlCommand.ExecuteNonQuery();
}

The database creation works fine but I get an error when the code trys to create the table.

Error = "The specified schema name "iBlast" either does not exist or you do not have permission to use it."

Any help would be appreciated.

The program is running on a PLC that only runs Windows 2000. — MagnumPI
– MagnumPI, Commented Jan 18, 2012 at 15:38

Blorgbeard · Accepted Answer · 2012-01-18 15:35:38Z

3

You are specifying "iBlast" as the initial catalog, so you don't need to specify it in the query:

 InsertTable("Create Table tblBoreHoles (...)", "iBlast");

If you did need to specify it, the syntax would be iBlast..tblBoreHoles or iBlast.dbo.tblBoreHoles.

answered Jan 18, 2012 at 15:35

Blorgbeard

104k50 gold badges237 silver badges276 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MagnumPI Over a year ago

I was leaving it out originally but was getting the same error. That is why I added it. That and because the function will add databases as well as tables. However, the iBlast.dbo.tblBoreHoles... worked. Thanks, I feel stupid that I didn't realize this myself!

linkerro · Accepted Answer · 2012-01-18 15:35:58Z

2

In your case you're not specifying the database with iBlast on the second line, you're specifying the schema of the current database. Should be something like [database].[schema].[table]

answered Jan 18, 2012 at 15:35

linkerro

5,4783 gold badges28 silver badges31 bronze badges

1 Comment

MagnumPI Over a year ago

I cannot mark two answers as the correct one, but this was also the correct answer...thank you

CD Jorgensen · Accepted Answer · 2012-01-18 15:34:53Z

1

you need to do iblast.dbo.tblBoreHoles or iBlast..tblBoreHoles. In SQL's naming convention, the part right before the table name is always the schema, which it doesn't appear you're trying to reference with "iBlast"

answered Jan 18, 2012 at 15:34

CD Jorgensen

1,40111 silver badges8 bronze badges

1 Comment

MagnumPI Over a year ago

I cannot mark two answers as the correct one, but this was also the correct answer...thank you.

Kyle Savage · Accepted Answer · 2012-01-18 15:37:05Z

0

Try this:

    else
    {
        sqlConn.ConnectionString = "Data Source=VEEMER11;Initial Catalog=" + InitialCatalog + ";Integrated Security=SSPI";
    }

Changing to SSPI may give you the permission you need. Check in your server that your windows user has full DBO privileges.

also, change your insert to this:

InsertTable("Create Table tblBoreHoles (HoleID uniqueIdentifier, HoleName nvarchar(40), JobID uniqueidentifier, CreateDate datetime, Longitude float, Latitude float, Altitude float, HoleDia real, ExpectedDepth float)", "iBlast");

answered Jan 18, 2012 at 15:37

Kyle Savage

7921 gold badge5 silver badges19 bronze badges

Collectives™ on Stack Overflow

How do I create a new Database and new tables in SQL MSDE 2000 programmatically with C#.net?

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related