about ASPNetDB.mdf

bela_sush · October 18th, 2010, 10:49 AM

Hi all,
Recently I made a website in asp.net , which worked perfectly alright on my local host...then I launch it on live server.

This site is about the hotel, which having its club and I made a form to register as a club member..

for this I made separate folder as ClubMember , In that I have CreateWizard.aspx and CreateWizard.aspx.cs in which I wrote a code for membership form...I have used createWizard control for this membership form...

code in CreateWizard.aspx.cs is

Code:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class ClubMember_CreateNewWizard : System.Web.UI.Page
{
    private void Page_Load(object sender, EventArgs e)
    {

    }
    // CreatedUser event is called when a new user is successfully created
    public void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
    {

        // Create an empty Profile for the newly created user
        ProfileCommon p = (ProfileCommon)ProfileCommon.Create(CreateUserWizard1.UserName, true);

        // Populate some Profile properties off of the create user wizard
        p.Country = ((DropDownList)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("DropDownListCountry")).SelectedValue;
        p.Gender = ((DropDownList)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("DropDownListGender")).SelectedValue;
        p.Age = Int32.Parse(((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtAge")).Text);
        p.Address =(((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtAddress")).Text);
        p.City = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtCity")).Text);
        p.Contact1 = Double.Parse(((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtContact1")).Text);
        p.Contact2 = Double.Parse(((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtContact2")).Text);
        p.AccountType = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtAccountType")).Text);
        p.FirstName = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtFirstName")).Text);
        p.LastName = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtLastName")).Text);
        p.PostalCode = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtPostalCode")).Text);
        p.MemberType = ((DropDownList)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("DropDownListMemberType")).SelectedValue;
        p.Email = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtEmail")).Text);
        p.CardType = ((DropDownList)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("DropDownListCardType")).SelectedValue;
        p.PhotoIDProof = ((DropDownList)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("DropDownListPhotoID")).SelectedValue;
        p.PhotoIDNumber = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtPhotoIDNumber")).Text);
        p.CardNumber = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtCardNumber")).Text);
        (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtJoin")).Text) = DateTime.Now.ToString();
        p.DateOfJoin = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtJoin")).Text);
        
        Roles.AddUserToRole(CreateUserWizard1.UserName, "ClubMembers");
        lblDateOfJoin.Text = DateTime.Now.ToString();
        
        // Save the profile - must be done since we explicitly created this profile instance
        p.Save();




         SqlConnection conn = null;
        SqlTransaction trans = null;
        SqlCommand cmd;

        try
        {
            conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Roy"].ConnectionString);
            conn.Open();

            trans = conn.BeginTransaction();

            cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.Transaction = trans;


            //set Reservation details

            cmd.CommandText = "INSERT INTO ClubMembers(FirstName,LastName,Age,MembershipType,DateOfJoin,PhotoIDNo,Contact1,CardNo,EmailID,PhotoIDProof,CardType,Address,City,PostalCode,Country,Contact2)"
                                + "VALUES(@FirstName,@LastName,@Age,@MembershipType,@DateOfJoin,@PhotoIDNo,@Contact1,@CardNo,@EmailID,@PhotoIDProof,@CardType,@Address,@City,@PostalCode,@Country,@Contact2)";

            cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50);

            cmd.Parameters.Add("@DateOfJoin",SqlDbType.DateTime);
            cmd.Parameters.Add("@MembershipType",SqlDbType.NVarChar,50); 
            cmd.Parameters.Add("@PhotoIDNo", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@Age", SqlDbType.Int);
            cmd.Parameters.Add("@Contact1", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@Contact2", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@CardNo", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@EmailID", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@PhotoIDProof", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@CardType", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 255);
            cmd.Parameters.Add("@City", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@PostalCode", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@Country", SqlDbType.VarChar, 50);


            cmd.Parameters["@FirstName"].Value = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtFirstName")).Text);
            cmd.Parameters["@LastName"].Value = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtLastName")).Text);
            cmd.Parameters["@DateOfJoin"].Value=(((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtJoin")).Text);
            cmd.Parameters["@PhotoIDNo"].Value = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtPhotoIDNumber")).Text);
            cmd.Parameters["@Contact1"].Value = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtContact1")).Text);
            cmd.Parameters["@Contact2"].Value = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtContact2")).Text);
            cmd.Parameters["@Age"].Value = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtAge")).Text);
            cmd.Parameters["@CardNo"].Value = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtCardNumber")).Text);
            cmd.Parameters["@EmailID"].Value = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtEmail")).Text);
            cmd.Parameters["@PhotoIDProof"].Value = (((DropDownList)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("DropDownListPhotoID")).Text);
            cmd.Parameters["@MembershipType"].Value=(((DropDownList)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("DropDownListMemberType")).Text);
            cmd.Parameters["@CardType"].Value = (((DropDownList)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("DropDownListCardType")).Text);
            cmd.Parameters["@Address"].Value = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtAddress")).Text);
            cmd.Parameters["@City"].Value = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtCity")).Text);
            cmd.Parameters["@PostalCode"].Value = (((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtPostalCode")).Text);
            cmd.Parameters["@Country"].Value = (((DropDownList)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("DropDownListCountry")).Text);

            int MemberID = Convert.ToInt32(cmd.ExecuteScalar());

            trans.Commit(); //commit the transaction
        }

        catch (SqlException SqlEx)
        {
            //some form of error- rollback the transaction
            //and rethrow the exeption
            if (trans != null)
                trans.Rollback();

            // Log the exception
            // Tools.log("An error occurred while executing the transaction", SqlEx)
            throw new Exception("An error Occured while Create Membership", SqlEx);

        }

        finally
        {
            if (conn != null)
                conn.Close();
        }

    }

    
       
       
}

I have a web.config file in ClubMember folder which I gave connection string as (here i m not giving username password for my online db)

Code:

<connectionStrings>
    <add name="Roy" connectionString="Data Source=xx.xxx.xxx.xx;UID=someuid;PWD=somepwd;DATABASE=RoyalH" providerName="System.Data.SqlClient"/>
  
  </connectionStrings>

my RoyalH database is online on SQL server...but my ASPNETDB.mdf database is not on online SQL server, but it is on my FTP...means I moved my App_Data folder on FTP which contains ASPNETDB.mdf.... the above code use both ASPNET.mdf and RoyalH database...

When I do entry on local server(on my PC)...code works finely and respective data goes to RoyalH and ASPNETDB.mdf database, but when I try to fill the membership form online after clicking button, I get following error

Code:

 A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846887
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +4860189
   System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +90
   System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +376
   System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +221
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +189
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +4861315
   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +31
   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +433
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
   System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +499
   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +65
   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +117
   System.Data.SqlClient.SqlConnection.Open() +122
   System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +87
   System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +221
   System.Web.Security.SqlMembershipProvider.CreateUser(String username, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, Object providerUserKey, MembershipCreateStatus& status) +2430
   System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +318
   System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +102
   System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +418
   System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +161
   System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +19
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +118
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +166
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

I am not understanding and not able to find solution , when I googled all answer I get is regarding Remote connection in SQL Server..which is correct in my SQL server.
Is there any need to do with my ASPNETDB.MDF database or is any other problem is there??

Please help,

Thanks in advance
Sushant

Imar · October 18th, 2010, 12:14 PM

If it works locally, but not remotely, my guess is that your hosted server doesn't allow outbound connections to your remote SQL Server. You to contact your host to find out if they support this.

Cheers,

Imar