I'll prelude this with I am a novice coder at best and this is mostly a hobby that I would like to do professionally one day, but that day may be 10+ years from now. So for now I tinker and learn here and there. Part of the reason I am using this design is to gain experience and understanding. Id like to start it off as a local database, then across my network, then the internet, then different users with different permission levels and different database types..etc. As for now though, this proverbial rocket wont even hop, muchless take off and land on the moon.

As the title suggests, I am trying to create a database from within my C# code on a windows form. I will then naturally create and use tables..etc. However, I am stuck on the initial creation of a blank database. Based off of "SQL Server Management Studio 2010" its (Microsoft SQL Server 2008) SQL Server Compact Edition database. I am coding with Visual Studio Ultimate 2010.

So part of my problem could be I am trying to create the wrong type of database? Or pairing the wrong syntax with the wrong database? I know that access is a file based DB and SQL is server based and that I am basically trying to create a local server database in this scenario.

I have the following code set to run the below code.

using System.Data.SqlClient;



SqlConnection myConn = new SqlConnection("server=(local);database=Master;integrated security=SSPI;");

            string str;
            str = "CREATE DATABASE MyDatabase";

            SqlCommand myCommand = new SqlCommand(str, myConn);
            try
            {
                myConn.Open();
                MessageBox.Show("Connection open");
                myCommand.ExecuteNonQuery();
                MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }

The abridged version of the error is "System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while stablishing a connection to SQL Server. The server was not found or was not accessible." I am trying to create a local database, so it shouldn't be a firewall issue? I went ahead and opened port 1433 anyway. I havnt yet tried using my own IP though. And I never get my 'connection open' messagebox, so that definitely narrows it to the connection string.

I've tried countless variations of the connection string without success. Below are some of the words/spellings/syntax I have swapped out throughout my various attempts, obviously not written as a proper string. After 3 days on and off of this and countless googling attempts, I am out of ideas.

server="local" "(local)" "." <user> <pcname> ".\SQL2008" ".\SQLEXPRESS"
"integrated security"="true" "yes" or "trusted_connection"="true" "yes"
database="master" database=""

Is the sql server configured for remote access?
If it is a named instance you need to have sql server browser running to be able to locate the name instance (not an issue if you're using default naming).
Have you got a user with appropriate rights that you can include in the connection string?

You can try the following connection string:

public static string connectStr = @"Data Source='.\SQLExpress';Initial Catalog='DB1'; Integrated Security = True";

//public static string connectStr = string.Format("Data Source='.\\SQLExpress'; Initial Catalog=\'myDB\'; User Id=\'user1\'; Password=\'password\'");

//where .\SQLExpress2008 (serverName\DBInstanceName) 

//'Initial Catalog='database name'

//'Integrated Security=True' means that it uses Windows authentication 

SqlConnection connection = new SqlConnection(connectStr);

Wow, thank you both for the excellent replies. After glancing through the above, I think I may have ended up with just the client side end of the program or something and mistakenly thought I was installing a combined set?? It would be quite something, if in all this, I forgot to somehow download/install the server itself. I am thinking possibly I did and its just not running or configured.

@Hericles, I don't see where I would configure the SQL server at unless that is a non-GUI modifaction, which I wouldn't think so. I see options for the Windows Auth or SQL Serv Auth, but not where to create users.

Unless you were referring to the windows auth side. In which case the answer is yes, of course I have a windows user with admin rights. As for the server browser, I have tried it not running, running and running/logged into other tests databases that I created from within the program.

@ Cgeier
SQL Server Express 2008 R2 SP2
-According to my control panel I have "Microsoft SQL Server 2008 (64-bit)" and "Microsoft SQL Server Compact 3.5 SP1" (and compact 4.0 SP1 64 bit)
Do you think its worth taking the time to uninstall the above and install express R2 in its place?

.NET Framework Data Provider for SQL Server connection strings
I've been using that site quite a bit before I came to the forum. Its mostly what got me as far as I did. I also tried some connection strings with localdb, attachdbfilename and a name instance after referencing
[Click Here](http://msdn.microsoft.com/en-gb/library/hh309441.aspx)

Introducing LocalDB, an improved SQL Express
-I will definitely be taking a closer look at this in the next couple of days, it definitely looks like something I will be needing

SQL Server Compact...
-This looks exactly like what I will be needing in the coming weeks

@"Data Source='.\SQLExpress';Initial Catalog='DB1'; Integrated Security = True"

The above didn't work unfortunately. Though as I suspect I don't have the server properly setup to receive the communication, this doesn't surprise me.

Open Sql Server Configuration Manager (for SQL Server 2008) and check that TCPIP is enabled.

Win 7:

  • Click Start
  • In "Search programs and files" textbox, type: SQLServerManager10.msc
  • Press "Enter"

Win 8:

  • In the search charm, under Apps type: SQLServerManager10.msc
  • Press "Enter"

Then,

  • Double-click "SQL Server Network Configuration"
  • Click "Protocols for SQLExpress" (where "SQLExpress" is the name of the instance that you are using)
  • Look at "TCP/IP". If disabled, right-click, and select "Enable". Then restart the MSSQL service (or reboot your computer).

Yes TCP/IP is enabled.

SQL Native Client 10.0 Configuration (32 bit)

Shared Memory (1)
TCP/IP (2)
Named Pipes (3)
VIA [Disabled]

.. same for, what I am assuming is, 64

Local windows firewall TCP and UDP in and out 1433 opened as well. I am going to download the most updated copies of the programs now, particularly SQL Express Advanced since it includes the localdb.

WOOHOO, success. Well in a limited fashion anyway, I achieved an actual connection. I realized the problem when I flipped over to one of my other programs that needed a micro sized database. (Ok I could have hard coded it, but for long term use I went the DB route). While opening a form to throw in a datagridview I realized I would still need a connection string to read/modify the data even if I wasn't creating the database. It was then that I noticed I wasn't trying to connect to say SQL Server or SQL Express, but rather Compact Edition (CE). While roaming the web I found an article referencing a SqlCeConnection.

My main problem I think is I lack a full understanding of the core design of libraries vs providers vs wrappers and exactly what 'type' of database I am connecting to. Whether its express, CE, a database engine. I don't yet know if SQL Server Express 2005 and SQL Server Express 2008 are just the interfaces for the same 'Express database' or if they are their own seperate types of databases.

Technical side of it.
I discovered and replaced SqlConnection with SqlCeConnection.
The SqlCeConnection couldn't be found so Reference > Add > Assemblies > Extensions > SqlCeConnection v4.0
I made several test databases, some with passwords, some password blank, some window auth..etc.
During test I got an error again. 'The database file has been created by an earlier version of SQL Server Compact. Please upgrade using SqlCeEngine.Upgrade() method'. A new error, victory! (Never been so happy to see an error before). Research suggested I replace the reference with v3.5 and that would fix it. However I found an alternative method.

String temp;
            temp = "Data Source = C:\\Test3.sdf;Persist Security Info=False; Password=wordpass1!";

            SqlCeEngine engine = new SqlCeEngine(temp);

            engine.Upgrade(temp);

            SqlCeConnection myConn = new SqlCeConnection(temp);

            string str;

            //Ignore
            str = "CREATE DATABASE MyDatabase";


            SqlCeCommand myCommand = new SqlCeCommand(str, myConn);
            try
            {
                myConn.Open();
                MessageBox.Show("Connection open");
                myCommand.ExecuteNonQuery();
                MessageBox.Show("VICTORY", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }

Well. Now my management studio, Server 2008 is telling me
"Incompatible database version. If this was a compatible file run repair. [Db version 4.000000, Requested version = 3505053, File Name=Test3.sdf" SQL Server Compact ADO.Net Data Provider

Click Here
Confirms that 2008 cant handle CE 4.0. So I tried going to Program Files(x86)\Microsoft SQL Server Compact and adding the .dlls.. but none of them worked. I manged to add a couple references to my project, but none of them were v3.5 that I was looking for and x64 only has 4.0. I'm sure I will find some solution eventually.

@Sonny Looks like a great link, but I am already mentally exhausted. I'll add it to my near future read throughs.

OK. Finally solved that issue. I just needed to go one folder deeper into the \Desktop folder to find the proper dll, to use the.. well you know. It should be all downhill from here.

Much thanks to all. This definitely set me on the right track and is going to let me get on with all my miscellanous side projects now.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.