Connectionstring woes

[Ian]

Hi all

I'm coming to ASP/SQL Server after 15 years in Delphiland. Like all good
newbies I'm trying to work my way through samples and RTFMing like mad. But
I'm having a major problem getting even the most basic thing off the ground.

I'm trying to run a sample from the M$ library that connects to the
Northwind DB. It's about the most basic thing there is, and I've been
struggling with it for over a day. Time for me to find a kind guru.

Function RunQuery(ByVal QueryString As String) As DataSet

' Declare the connection string. This example uses Microsoft SQL Server
' and connects to the Northwind sample database.
Dim ConnectionString As String = "blah, blah"

Dim DBConnection As SqlConnection = New SqlConnection(ConnectionString)

For the ConnectionString I have tried every possible combination of
'server', 'data source', 'database', 'security', etc, etc that I can think
of or have found in examples of on the web. Even the ones at
connectionstring don't get me to connect. All I get when I go through the
connect stage is 'Invalid operation. The connection is closed' in the
DBConnection properties. I am using SQL Server Express and I've included the
\sqlexpress after server name, long names for databases and servers, and I
am now getting a tad frustrated. Heck, this is for newbies like me and I
can't even get it to work out of the box! Google has retired with a
headache I've spent that long poking around trying to find an answer.

Anyway, any free clues will be very much appreciated. I'm *sure* I can get
to enjoy this thing, once I get in the front door.

TIA

Ian

 

[Norman Yuan]

Your frustration is obvious, but if you do not show the ConnectionString you
used, who can help?

Also, when one start to use SQL Server/Express, he is expected to have some
basic SQL Server knowledge, after all, it is a very powerful/complicated
server software, while most available ASP.NET samples you can find assume
the user knows the basics of the SQL Server.

I am trying to say is the ConnectionString you use is dependent to the SQL
Server setup. So, when showing your ConnectionString here, you may also want
to tell how you set up the SQL Server: security mode (Windows or SQL
Server), SQL user login, database user/user role...

 

[Ian]

Your frustration is obvious, but if you do not show the ConnectionString
you used, who can help?

Hi

As I put in my other post, I've tried just about everything I can think of.
The only thing I haven't done is feed it all into my anagram generator.

Also, when one start to use SQL Server/Express, he is expected to have
some basic SQL Server knowledge, after all, it is a very
powerful/complicated server software, while most available ASP.NET samples
you can find assume the user knows the basics of the SQL Server.

I must admit that I don't know too much about SQL Server. But I do have a
fair amount of experience on Oracle, Ingres, Interbase, MySql and a few
others going back to dBase. I've never come across anything like this
before.

I am trying to say is the ConnectionString you use is dependent to the SQL
Server setup. So, when showing your ConnectionString here, you may also
want to tell how you set up the SQL Server: security mode (Windows or SQL
Server), SQL user login, database user/user role...

It's set up to Windows Security. The reason I've been messing about is that
I copied in the example from M$ where the connection string is:

Dim ConnectionString As String =
"server=localhost;database=NorthWind;Integrated Security=SSPI"


When it all barfed I found that I had the 'Invalid operation. The connection
is closed' error listed in the connection properties. I went a-Googling and
found all sorts of things on it.

But now I have an even bigger problem, which is nothing to do with SQL
Server. I can't even get a 'hello world' to run in VS after making the
mistake of doing a disk cleanup. It doesn't like breakpoints and symbols and
all sorts of other things. A repair hasn't done it for me, and from a
search, there are as many solutions as there are problems and everyone and
his mate seems to have hit this at some time. So it's time for it to go to
the big bit-bucket in the sky and I'll reinstall and start from scratch. If
it doesn't work this time then it's back to Delphi and MySQL. I only wanted
to use some prettier gizmos, not have to go on a full course of Valium.

Let's see what joys the next couple of hours brings.

Cheers

Ian

 

[Dave]

Ian,
I am sorry you are having so much trouble with your first program
in .net. I find many times when these kinds of issues occur with a
product it is a result of negatively charged vibes towards the
product. The darn things seem to be sensitive to them.

As for your question here is a SQL connection string to the northwinds
database on my sqlexpress instance

"Data Source=<machineName>\sqlexpress;Initial
Catalog=Northwind;Integrated Security=True"

The easiest way to get a connection string is to open the Server
Explorer window from the View menu. Then click on the yellow cylindar
with a plus on it at the top, this will start a new connection wizard.
Go through the wizard and make sure you click the test connection
button at the end. Once completed with the wizard you should see your
database listed in the Data Connections section. Righ click on in and
select properties, there you will see the connection string it is
using and you can copy and paste it into your code.

Best practice, by the way is to place you connection string in a
config file and use the configurationManager class to retrieve it. But
for hello world placing it the code will work.

Good luck

 

[Ian]

Hi Dave

Ian,
I am sorry you are having so much trouble with your first program
in .net. I find many times when these kinds of issues occur with a
product it is a result of negatively charged vibes towards the
product. The darn things seem to be sensitive to them.

I know what you mean. Even Hello World becomes a test of wills.

As for your question here is a SQL connection string to the northwinds
database on my sqlexpress instance

"Data Source=<machineName>\sqlexpress;Initial
Catalog=Northwind;Integrated Security=True"

Tried that one as well. Also didn't work. Just tried it again, and still no
go.

The easiest way to get a connection string is to open the Server
Explorer window from the View menu. Then click on the yellow cylindar
with a plus on it at the top, this will start a new connection wizard.
Go through the wizard and make sure you click the test connection
button at the end. Once completed with the wizard you should see your
database listed in the Data Connections section. Righ click on in and
select properties, there you will see the connection string it is
using and you can copy and paste it into your code.

Tried that, except I got it from webconfig. It's the long pathname to the
sdf file and I tried that yesterday with no luck. I might have a spend a
couple of hours fiddling around, but I *usually* get things going and
include it is a valuable part of the learning curve. This one has me
completely stumped. It's also very frustrating to be able to see the content
of the db in the window on the left but not be able to connect from the
window on the right!

Cheers

Ian

 

[Norman Yuan]

Since your ConnectionString is something like:

"Data Source=<machineName>\sqlexpress;Initial
Catalog=Northwind;Integrated Security=True"

Then, it mean the SQL Server uses "Windows integrated security" that means,
the application's runni8ng user account (a Windows user account) is used for
SQL Server to authenticate, then autorize the access to the Northwind DB.

Also, since your are working on a ASP.NET app, which runs as a server
process with configured user account (ASPNET or Network services, depending
on the OS, and the APS.NET app configuration, which you should know when
developing ASP.NET app), you must set up SQL Server to allow that user
account to have access to the Northwind DB.

Yes, when you use Server Epxlorer in the VS (left side as you said), you
would be likely no problem to access the database, because your user account
is very likely a local admin (or VS user account, which is set up by VS
installation and have a lot local access to the develoment box, close to
being a local admin) when running VS. When you actually runs the ASP.NET
app, the app could be running by any user account/machine account that you
configured (if you did not do/know ASP.NET configuration, it is likely be
ASPNET account, if the OS is XP, or Network Services9Win2003/VISTA/Win7). In
this case, you must have a SQL Server login created that maps to the user
account, and make this SQL Server a user/role with proper permission to the
NorthWind.

So, in order to use SQL Server in ASP.NET app (or any other server based DB
system, Oracle/MySql...), you need to know both security things on database
and ASP.NET. It sounds quite complicated, and it should be that complicated.

However, as starter, if your user account is a local admin (WinXP) and you
configure your ASP.NET to set "impersonate=true", you should have access to
any database with the local SQL Server (note, with Vista/WIn7, being local
admin is not enough to gain almighty access to database, you must explicitly
to set particular local windows user account as the SQL Server's sysadmin
role). In this case, you do not need to do any security configuration to
access database with this SQL server.

 

[William Vaughn \(MVP\)]

See
http://betav.com/blog/billva/2006/06/getting-and-staying-connected-1.html or
search on "Connecting" in my blog.
Again, my 7th Edition is designed specifically for you and developers like
you trying to get started with SQL Server.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________