4 Replies - 1198 Views - Last Post: 13 May 2010 - 07:41 AM Rate Topic: -----

#1 Choscura  Icon User is offline

  • D.I.C Lover


Reputation: 470
  • View blog
  • Posts: 2,235
  • Joined: 18-October 08

Some help understanding what's going on with SQL

Posted 11 May 2010 - 07:33 AM

Ok, I've been bashing my head into this and I give up, I need help.

What I'm trying to do is create a local sql database with an app that I'm making, and I don't understand what's going on. What I think I understand already is that sql sever has to be running on some host somewhere (my computer, a remote computer, a web server somewhere, whatever), I understand that you first have to connect to the server and *then* to the database.

what's really throwing me off is that whenever I create a database project (or a C# database project, because this is a C# project) I can't connect to it, the connection string never works. the simplest example I can give is probably something like this:
static void Main(string[] args)
{
	string connectionstring = @"server=C:\Users\Administrator\Documents\Visual Studio 2008\Projects\console DB\console DB\Database1.sdf;"
	+ "Trusted_Connection=yes;"+ "initial catalog= Table1;";
	SqlConnection connection = new SqlConnection(connectionstring);

	try
	{
		connection.Open();
		Console.WriteLine("connected!");
	}
	catch
	{
		Console.WriteLine("not connected!");
	}
}


I know there's a database file there, I think I know that my computer is the default server (no server specified- or am I mixed up in this? do I have to specify my own computer? how do I do this?). I've tried going through psychocoder's sql basics tutorial (here) and every other sql server tutorial I can find pretty much, most (ALL!) gloss over this detail. I know it's something stupid and I know I'm an idiot for not getting it, etc, but what is it?

If anybody can point me to a tutorial or something that will help with this I'd be very grateful, or if you read this and discover something so stupid it defies all logic, please tell me what it is so that I can figure out how to make this work!

Is This A Good Question/Topic? 0
  • +

Replies To: Some help understanding what's going on with SQL

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Some help understanding what's going on with SQL

Posted 11 May 2010 - 07:42 AM

For your connection string instead of using Server= try using Data Source= like this

string connectionstring = @"Data Source=C:\Users\Administrator\Documents\Visual Studio 2008\Projects\console DB\console DB\Database1.sdf;Persist Security Info=False"



A lot of times your SDF file isnt running in the current directory so you can use something like this

Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\Database1.sdf;Persist Security Info=False;



One more thing, since your file is SDF I know you're working with SQL Compact Edition so I would be using the SqlCeEngine Class and not the normal Sql stuff
Was This Post Helpful? 1
  • +
  • -

#3 MentalFloss  Icon User is offline

  • .
  • member icon

Reputation: 577
  • View blog
  • Posts: 1,500
  • Joined: 02-September 09

Re: Some help understanding what's going on with SQL

Posted 11 May 2010 - 11:00 AM

When in doubt, http://connectionstrings.com/
Was This Post Helpful? 1
  • +
  • -

#4 Choscura  Icon User is offline

  • D.I.C Lover


Reputation: 470
  • View blog
  • Posts: 2,235
  • Joined: 18-October 08

Re: Some help understanding what's going on with SQL

Posted 11 May 2010 - 05:21 PM

Ok, just to make sure this is idiot-proof because I seem to need it right now... Where do I tell the database what I want the username and password to be? Have I simply missed an option when i add a database to a project? is there an 'advanced' option somewhere that I should be clicking to look for this when I do this?

-I think I understand that the program just needs to know the location of the database file itself... is this correct? I really want to understand what's going on here, I keep seeing in the connection string syntax some mythical 'server' that has to be connected to before I can connect to the database (or is the 'server' the database and the 'database' the initial table?).

-How do I make a database (sql server) and set the username and password (not log in, not provide data to be logged in with, but actually set an admin username and an admin password)?

PsychoCoder, I've currently got about a dozen of each SQLCE and SQL server projects that I've started and tried to figure out what's what and what's going on. I want to do SQL server, SQL CE is just nice to have. There's not a lot of code and not a lot of variations (I've played with the code you wrote in your tutorial a bit, I want to figure out the absolute lowest level of this that I can before I try to build anything out of it however).

MentalFloss, the connectionstrings resource is a great help or it looks like it will be when I get this working. Thanks both of you for the help so far.
Was This Post Helpful? 0
  • +
  • -

#5 Choscura  Icon User is offline

  • D.I.C Lover


Reputation: 470
  • View blog
  • Posts: 2,235
  • Joined: 18-October 08

Re: Some help understanding what's going on with SQL

Posted 13 May 2010 - 07:41 AM

GAH I feel like an idiot. The solution, after so many tries, was that I can't connect to an empty database.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1