Making a simple connection with SQL 2005 wont work!! | Tech Off

Winston Pang

Hey guys i've got this block of code i'm trying to just connect to the DB, pull out a table and traverse the columns.

String dbpath = Path.Combine(Application.StartupPath, "Database1.mdf");

String conn = @"Server=.\SQLExpress;AttachDbFilename=" + dbpath + ";Database=database1;Trusted_Connection=Yes;";

SqlConnection con = new SqlConnection(conn);

con.Open();

SqlCommand comm = new SqlCommand("SELECT * FROM Student", con);

SqlDataReader reader = comm.ExecuteReader();

while (reader.Read())

{

MessageBox.Show("Retrieved ID:" + reader["ID"] + ", Name: " + reader["Name"]);

}

I have no idea what's wrong.

I keep getting thrown exceptiosn about cannot attach a DB.

What i'm trying to do is connect to a SQL 2005 .mdb file locally.

I've setup my SQL configuration to allow remote connections, it's all set.

I have created the DB already called Database1.mdf, with one Student table with ID, Name, LastName.

Also one more question, how do i find out about the Database name that i specify int he connection string normally, or is that an arbitrary name i make up that's unique?

And, how does one set the User and Password of a .mdf file?

Thanks for your help guys.

geekling
I am an artist

Full exception text?

Off the top of my head, I think you're not pointing it at the sql server instance correctly. Maybe .\MACHINE_NAME\SQLExpress instead of just .\SQLExpress.

That's how mine was configured by default, anyway.

Ang3lFir3
Codito Ergo Sum

if there is a .ldf file with the same name in the directory and "database" is set you won't be able to attach the db and will recieve an exception..... make sure there is no ldf and drop the "database" and replace it with

Initial Catalog=Database1

see if that helps any...... otherwise pasting the actuall exception message would be helpful to figure out whats wrong.......

you can use a SQLConnectionStringBuilder object if you want to make the connection string more dynamic...and eliminate a bunch of nasty concats

Winston Pang

Thanks for the replies guys.

I've tried qualifying the Server with the whole instance path, i.e.

WINSTONSPC\SQLEXPRESS.

I've tried using Initial Catalog instead of Database (btw what's the diff between the two, and how do you obtain it normally? or is it an arbitrary name)

Here is the full exception:

Directory lookup for the file "C:\Documents and Settings\Winston Pang\Desktop\WindowsAppplication2\WindowsApplication2\bin\Debug\Database1.mdf" failed with the operationg system error 5(Access is denied.).

Could not attach file 'C:\Documents and Settings\Winston Pang\Desktop\WindowsAppplication2\WindowsApplication2\bin\Debug\Database1.mdf' as database 'database1'.

littleguru
<3 Seattle

The SQL Server user does not have the rights to access the directory with the database file. The exception message seems to try to tell you that:

(Access denied).

borosen

Have you attached the db via the SQL Management studio?

In that case, detach it and make sure you mark 'Drop connections';
now the db is available for you to use again.
(You might have to restart your development environment).

I don't know what is going on, but this is my experience.

Winston Pang

Well, basically this is all i wanted to do:

I installed SQL Express, didn't do any configurations to it apart from allowing Remote connections in the surface configuration.

Then i created a brand new project in VS.

Created a .mdf file, added one Student table and 3 fields.

Then i whipped that code i posted above, and that's it.

I haven't found any articles online that actually tell me what i need to do to configure it to get it working, it seems to cumbersome just to connect to SQL db and doing something simple.

Do i need to set the user id, password? if so how do i do that, is it specific to the .mdf file itself?

Also, little guru, you stated the SQL user doesn't have the rights, how do i set that?

I admit, i am absolutely a new comer to using SQL as a backend, so this i'm just trying to learn.

Thanks for everyones help.

borosen

Try adding 'user instance=true;' to your connection string. (Without the ')

If it still does not work, create a new db with another name and try again.

Winston Pang

Hmmm wow, user instance worked, how strange, so is this how i should always access it if it was local? what does the user instance value indicate to the connection?

I also want to know like i've asked before, what the Database and Initial catalog values come from, are they arbitrary values?

Also, finally, if i wanted to set a user id and password on the .mdf file, how does that work?

borosen

The 'user instance' causes a new instance of the sql express beeing started, running with the callers credentials.

Database:
A sql instance can have more than one database or catalog attached, to distinguish these databases, they are given names.

Initial catalog:
When you connect to the sql instance, this property specifies which of the named databases the caller should initially be connected to. (See above)

If you want to specify userid and password, then you should not use the 'user instance' property.
You should also assign permissions, preferably? via the Sql management studio.
Make sure the primary instance of sql express has read/write access to your mdf file. (The primary instance normaly runs with another sets of credentials than the users account.)

blowdart
Peek-a-boo

borosen wrote:

Make sure the primary instance of sql express has read/write access to your mdf file. (The primary instance normaly runs with another sets of credentials than the users account.)

And by default it runs as "Local Service" whch is very limited to what it can do and where it can access (which is why it can't get to "Documents and Settings"). It's not a good idea to change this unless you really have to, SQL should be run with as narrow a set of privileges as possible.

Winston Pang

Wow, very informative, thanks!!

Well besides using SQL server studio management how else can u set a user id and password?

So the initial catalog is basically saying if "database1" is attached to the sql instance already, then just read from that? What happens if the database name doesn't exist for the initial catalog value u set, does it then attach itself to the instance?

So how do i adjust privlidges on the mdf file or the SQL server itself so that you can locally connect to a DB without using the user instance property?

Also one last question, typcially how are things setup when people use SQL server as their backend for their applications?

Do they just leave the .mdf file on the users machine, and specify the Server path as some other remote path?

Also, how when you specify the SQL Server instance name, how is that obtained normally? I mean yeah it's common that an SQL Express install is named as SQLExpress, but what about other versions, and what if someone renamed it, what happens in terms of deploying an application, can you dynamically determine if an SQL Server install is present and detect the named instance?

Sorry for all these questions, but i can't find a good guide on a thorough introduction to it.

Thanks again for everyone's help.

borosen

Winston Pang wrote:

Well besides using SQL server studio management how else can u set a user id and password?

Sql server studio manager is freely downloadable. I would propose using it.
Otherwise, you could use DDL and system stored procedures, but now we have left a simple connection far behind.

Winston Pang wrote:

So the initial catalog is basically saying if "database1" is attached to the sql instance already, then just read from that? What happens if the database name doesn't exist for the initial catalog value u set, does it then attach itself to the instance?

Try it! Smiley

Winston Pang wrote:

So how do i adjust privlidges on the mdf file or the SQL server itself so that you can locally connect to a DB without using the user instance property?

Just use the ordinary security settings for files.

As Blowdart mentioned above, the permissions on files in the user directory are usually quite restrictive, take care of where you put the file and how you assign permissions.

Winston Pang wrote:

Also one last question, typcially how are things setup when people use SQL server as their backend for their applications?
Do they just leave the .mdf file on the users machine, and specify the Server path as some other remote path?

AFAIK sql express does not support having data files on a share.

If this is a database deployed together with your application, I bet the normal way of connecting to the database is through user instance.

Winston Pang wrote:

Also, how when you specify the SQL Server instance name, how is that obtained normally? I mean yeah it's common that an SQL Express install is named as SQLExpress, but what about other versions, and what if someone renamed it, what happens in terms of deploying an application, can you dynamically determine if an SQL Server install is present and detect the named instance?

If that could be an issue for you, you could ask the user when the application is installed.
In a hosted environment, i.e. web-server, the sys-admin of that server should know.

Edit.

My suggestions above is targeted SQL Server express.

If you develop for something that should be deployed on SQL server, I think you should go for creating a database from within sql management studio on the default instance and connect to that instead of via an attached database file.

I can recommend this site for short info on connection strings.