Urgent: Need some advice on Connection

[Mike L.]

Hi,

Pls, beware that I'm new to this

I've developed several web appl, either with ASP or ASP.NET. All using SQL
server as the back end.
In my development environment, I have a single server running Win2K, which
serves as Web Server and SQL server as well.
And, since my hosting (production server) using the same config; ie: using a
single server to serve both web server and SQL server; it's always been easy
for me to set my connection string in my applications. I just need to point
to "localhost" as my data source in my conn string.
It works just fine either in my developmetn server and my production server.
I just need to upload all the project files, and everything will run just
fine, just like it is in my developmetn env.
Then, my hosting decided to separated SQL server into a dedicated machine
(with, of course, diff IP).
That's when the problem started. Because, all my appl which point to
'localhost' couldn't recognize it anymore.
As with my ASP appl, I used to create a single .asp page with my connection
string in it. And just 'include' it on top of every .asp page that needs to
access data.
So, i just need to change the 'data source=' to point to the SQL server for
the production server, and use the ussual 'localhost' when use it in my
development env.
Even though it's a little bit tedious, but it can do the trick.

Now, for ASP.NET. I used to create my SQL server connection from "Server
Explorer" window, which VS automatically created a proper
conn.connectionString in my web.config
But, in every .aspx page that accesses data, (I know all of you know this) I
used to drag & drop SqlDataAdapter or SqlCommand directly onto the page,
which VS automatically created a corresponding SqlConnection based on the
connection I created in "Server Explorer". I changed the conn string in my
web.config but still can't recognize the sql server in production env. It's
because in all pages that I have SqlConn, it's still refer to my
'localhost'.

Is there any quick, efficient and effective way, that I can still work on my
development server (using localhost); and when I uploaded the projects to my
production server I can change the Sql server reference to my hosting's Sql
server IP?

Many thanks in advance,
Mike L.

 

[Ollie Riches]

just change the web.config value for the connection string

HTH

Ollie Riches

 

[Mike L.]

Ollie,

I did so. It still didn't work. Like I said, In every .aspx page that
accesses data, I drag & drop a SqlConnection from toolbox, which got
populated with my current connection settings (which is points to
localhost).
Any other idea?

thks,
andy

 

[Ken Cox [Microsoft MVP]]

Hi Mike,

Just change the SQL connection string in your page load event. You can
detect the server it is running on and then substitute the production
server's SQL string. Here's the code:

Private Sub Page_Load _
(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
If Request.ServerVariables.Item("SERVER_NAME") <> "localhost" Then
SqlConnection1.ConnectionString = System.Configuration. _
ConfigurationSettings.AppSettings("strSQLConn")
End If

and this goes in the Web.config:

<configuration>
<appSettings>
<add key="strSQLConn" value="workstation id=P4320;user id=userIDHere;data
source=P4320;initial catalog=cathere;password=pwdhere"/>
</appSettings>

<system.web>

 

[IPGrunt]

Ollie,

I did so. It still didn't work. Like I said, In every .aspx page that
accesses data, I drag & drop a SqlConnection from toolbox, which got
populated with my current connection settings (which is points to
localhost).
Any other idea?

thks,
andy


in

Andy or Mike

Don't do that.

Dragging from Server is simply a RAD device that is cool at first, but not
always useful for enterprise development.

Setup a connection string value in Web.config, then use that as your
connection string when needed in your classes.

This way, you change it once (in Web.config) when you change servers. Good
practice, time saver.

-- ipgrunt

 

[Mike L.]

thanks guys,

i'll give it a try.

regards,
mike

 

[Alan Silver]

So, i just need to change the 'data source=' to point to the SQL server

for the production server, and use the ussual 'localhost' when use it
in my development env. Even though it's a little bit tedious, but it
can do the trick.

Quite aside form the issue you are trying to solve, you could save
yourself a bit of hassle if you add an entry to your HOSTS file.

As an example, suppose your local machine is called HOME, and the two
production machines are called WEB and DATA (dumb names I know, I'm just
making them up for clarity). You are currently using localhost in your
connection string when the site is on your own machine, and having to
change it to DATA before you upload.

Add a line to C:\WINDOWS\System32\Drivers\Etc\HOSTS (no extension) to
fool your machine into thinking that your local machine is called DATA.
That way you can keep your connection string using DATA, and it will
work on both your local machine and the production one.

The downside to this is that you won't be able to access the remote data
machine by name, only by IP address as your HOSTS file will direct any
requests for DATA to localhost, but this is 9in my experience) a very
small price to pay for the convenience.

Hope this helps