Uploading an SQL 2005 Database to discountasp.net

Hello,

I'm building an ecommerce website using Visual Studio and have got to the stage when I want to start testing it. I've purchased a hosting packaged with discountasp.net and added MS SQL Server 2005 as an add on. So far I've entered data directly into Visual Studio by going to server explorer, right clicking on the table I want to enter data into, selecting "Show Table Data" and entering what I want. The database I'm using is currently stored on my hard drive at C:\DOCUMENTS AND SETTINGS\LUKE JACKSON\MY DOCUMENTS\VISUAL STUDIO 2005\WEBSITES\JACKSONSNURSERIES\APP_DATA\DATABASE.MDF.

When I publish the website using VS "Publish" tool, the database is uploaded into an app_Data folder with file extgension ".MDF". However, when I go to Database Management on the asp.net control panel it tells me that just 1% of database space is being used up (the size of the database is much larger than this) which makes me think that I'm doing something wrong. When I go to the URL which I'm testing the website at (www.thegardeningdirectory.co.uk) an error occurs whenever I visit a page which need to access data stored in the database. The error is as follows:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I've enabled all protocols for SQLEXPRESS but the same error is still occuring. I'm beginning to think that I've uploaded the whole thing incorrectly. I'd be really grateful if you could answer some questions for me because I'm a complete beginner!...

1. In order to host a database driven (SQL Server 2005) website on discountasp.net do I need to have SQL Server 2005 installed on my computer or is it okay to use SQL Express?
2. Is the database uploaded automatically when uploading my website using VS "Publish" tool or is there something else I need to do?
3. Should my database be stored in the App_Data folder or somewhere else?
4. Is .MDF the right file extension for SQL Server 2005?


5. What do I need to change the connection string in my Web.Config file to so that it points to the database hosted on discountasp.net and not on my hard drive? What should I replace the following with?


connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\products.mdf;Integrated


6. When I publish my website using VS "Publish" tool the webpages aren't actually uploaded - I had to copy and paste them into the ftp folder manually myself. Is there any reason for this? Also, could someone tell me what the3 options provided when you publish a website using VS actually mean?


I realise I'm asking a lot in one post but I'd be really grateful for any help and advice you could offer me.


Many thanks,


Luke

 

If you go into the database manager of your control panel,click on one of the code examples (at the bottom).


In there, you will find an example connection string specific to your database.







Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

Hi Bruce,

Thanks very much for your help - all sorted now. Just as a suggestion, I think there should be a bit more information provided in knowledge base about the SQL attach tool. I'd image a lot of beginners (like me!) would assume that once they've published a database it will be operational. Maybe just a message on the database management page of the control panel to tell beginners that once they've uploaded their database they have to attach it using the SQL attach tool.

Anyway, everything's working fine now so thanks again for your help.

Regards,
Luke

 

I don't have all the answers but this may help?

Problem:
(provider: SQL Network Interfaces, Error: 26 - Error locating server/instance specified)
There are several reasons for this "generic error" for example, if you are using profile variables to manage application state, using ASP built in security, or allow users to change the look and feel of the page. Anyway...
Solution:
open Visual Studio Command Prompt (Start => All Programs => Microsoft Visual Studio 2005 => Visual Studio Tools => Visual Studio Comman Prompt)
run aspnet_regsql.exe
click next
select Configure SQL Server for applications
click next
Add the server name and LEAVE the database as <default>
click next
click next
the above will create a new database called ASPNETDB
NOW DO THE FOLLOWING

On your web server, search for the file machine.config (generally found in Windows\Microsoft.net\Framwork\vx.0xxxx\config) and find/change the following line of code
<!--
comment out the following...
<add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />
-->
<add name="LocalSqlServer" connectionString="Data Source=YOURSQLSERVER;Initial Catalog=ASPNETDB;User ID=bhammond;password=bhammond" />
By default, ASP uses SQLExpress. Comment out that line of code and add the one above (Setting appropriate properties)