connection string issue. not writing to database on remote server

[jimjim]

HI,

We have shifted our database from sqlserver2003 to sqlserver2005.
1. connection strings stored in web.config

 <!-- Machine name to connection string keys --> 
<add key="*777*" value="**ConnString_DEV" />
<add key="server2" value="**ConnString_TST" />
<add key="server1" value="**ConnString_PRD" />
 
<!-- Literal Connection String Keys -->
<add key="**ConnString_DEV" 
value="Server=localhost; data source=*777*;
database=dbname;
User ID=xxx;
password=xxx;
Trusted_Connection=no;
Initial Catalog=dbname;
Persist Security Info=False" />
 
<add key="**ConnString_TST" 
value="Server=sqlserver2005.ad; data source=sqlserver2005;
database=dbname;
User ID=xxx;
password=xxx;
Trusted_Connection=no;
Initial Catalog=dbname;
Persist Security Info=False" />
 
<add key="**ConnString_PRD" 
value="Server=sqlserver2005.ad; data source=sqlserver2005;
database=dbname;
User ID=xxx;
password=xxx;
Trusted_Connection=no;
Initial Catalog=dbname;
Persist Security Info=False" />
 
<add key="IsTest" value="true" />
<add key="TestEmail" value="hhhhhhh"/> 
 

2. fetch connectionn string function

 
Public Shared Function FetchConnString() As String
 
'Fetch machine name and retrieve the right connection string from web.config
 
Dim strMachineName As String
Dim strConnectionStringKey As String
Dim strConnectionString As String
 
strMachineName = System.Environment.MachineName
If strConnectionStringKey <> Nothing Then
strConnectionString = ConfigurationSettings.AppSettings(strConnectionStringKey)
Else
'An unmatched machine name will default to _TST
strConnectionString = ConfigurationSettings.AppSettings("**ConnString_TST")
End If
 
If strConnectionString <> Nothing Then
FetchConnString = strConnectionString
Else
FetchConnString = "<WEB.CONFIG CONNECTION STRING ERROR>"
End If
 
End Function
 

3. call fetchConnectionstring function to connect to database

 
Public Sub imgBtnProceedApplNY_Command(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs)
 
Dim CommandName As String, connectionString As String
Dim ID As Guid
 
Dim conn As System.Data.SqlClient.SqlConnection
Dim cmd As System.Data.SqlClient.SqlCommand
Dim da As System.Data.SqlClient.SqlDataAdapter
 
conn = New System.Data.SqlClient.SqlConnection
conn.ConnectionString = clsDatabase.FetchConnString
conn.Open()
 
cmd = New System.Data.SqlClient.SqlCommand
cmd.Connection = conn
 
'Dim ds As DataSet
Dim DocName As String, FormName As String
 
CommandName = e.CommandArgument
 
Select Case CommandName
Case "Filled"
 
Case "Blank"
'bind(Nothing)
' This section used to run 'bind' and download a blank application form
 
FormName = "ApplyNYBlank"
'DocName = DisplayProcessing.DocumentLookupFileName(FormName, "NY")
DocName = "../Requests/DownloadDocument.aspx?DocName=" + FormName + "&state=" + "NY"
Me.Response.Redirect(DocName, True)
'Case Else
End Select
 
conn.Close()
 
End Sub
 

ISSUE: when button on apply page is clicked it does not go to next page and errors "page cannot be displayed" "cannot find server or dns error". When details are entered directly from applydetails page it does write to local instance of dtatabase but not to remote database instance.

i don't know whats is happening here. Can anybody suggest teh right solution for this.

[Frinavale]

Make sure that your 2005 SQL Server has been configured to allow for remote connections.

I'm not sure why you'd be getting "page cannot be displayed" or "cannot find server or dns error" errors...Check your Web Server's event logs to see if any more details have been recorded there on why the page could not be displayed.

-Frinny

[jimjim]

Now the database is on the new server and from VS2005 its connected. but when I fill in details in a form and submit it does not write to the database on that server. currently I am not bothered about my local instance of the database. I wonder if the connection strings in Web.config is right or fetching the string in aspx page is ok

[jimjim]

After juggling with SQL Server and going thru the application form i see that the data entered was written to local instance of the database but not to the remote database. WHY

[Frinavale]

After juggling with SQL Server and going thru the application form i see that the data entered was written to local instance of the database but not to the remote database. WHY

There has to be something preventing you from connecting to your remote SQL Server.

Check that your connection string is correct.
Check that your SQL Server accepts remote connections.
Check also that your SQL user connecting to the database has the appropriate rites to connect to the database.

Also check that there are no firewalls etc. blocking the connection.


-Frinny

[jimjim]

There has to be something preventing you from connecting to your remote SQL Server.

Check that your connection string is correct.
Check that your SQL Server accepts remote connections.
Check also that your SQL user connecting to the database has the appropriate rites to connect to the database.

Also check that there are no firewalls etc. blocking the connection.


-Frinny

I checked all of this. Remote connections are fine. Could you figure out if connection string has something to do with this. ALso the function FetchConnString() is not being called.

[Frinavale]

I Also the function FetchConnString() is not being called.

The FetchConnString is your own function....I'm assuming it fetches the connection string.

I would suggest using the debugger to step through your application and determine why this function isn't being called.

-Frinny

[meghanav]

The FetchConnString is your own function....I'm assuming it fetches the connection string.

I would suggest using the debugger to step through your application and determine why this function isn't being called.

-Frinny

I am unable to use the debugger in VS 2003. Could it be because I have VS2005 installed too. Also please tell me on dev environment I have datasource=machinename(say89899) and server=localhost - is this right. Also userid and password for the database on remote server. is it required in dev config.

[Frinavale]

I am unable to use the debugger in VS 2003. Could it be because I have VS2005 installed too. Also please tell me on dev environment I have datasource=machinename(say89899) and server=localhost - is this right. Also userid and password for the database on remote server. is it required in dev config.

Please take a look at what the MSDN library has on Connection String Syntax to make sure that your connection string is correct.

Your "sever=localhost" will only work if the server is located on the same computer as the web server. I would recommend changing this to the name or IP of the computer instead of using localhost to see if it makes a difference.

-Frinny

[meghanav]

Please take a look at what the MSDN library has on Connection String Syntax to make sure that your connection string is correct.

Your "sever=localhost" will only work if the server is located on the same computer as the web server. I would recommend changing this to the name or IP of the computer instead of using localhost to see if it makes a difference.

-Frinny

Can you be more specific with above statement. Also for datasource i am using my compauter name.

[meghanav]

I set a breakpoint at page_load() and did stepinto process and whole events were going thru but doesn't step into imgbtnProceed() which calls the fetchstring() function and it doesn't get called.