Question About Some Code (1 Viewer)

jcruzAME

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2011
Messages
135
Below is a piece of code I'm using right now to grab a value from a table on the SQL Server. The site is on the Web Server.

Dim dbCrew As String
Dim currentCrew As String
Using connObj As New SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString)
Using cmdObj As New SqlClient.SqlCommand("SELECT CrewID FROM dbo.CrewAssociation WHERE UserID = '" & Context.User.Identity.Name & "'", connObj)
connObj.Open()
Using readerObj As SqlClient.SqlDataReader = cmdObj.ExecuteReader
'This will loop through all returned records
While readerObj.Read
dbCrew = readerObj("CrewID").ToString
currentCrew = dbCrew
End While
End Using
connObj.Close()
End Using
End Using

EDIT: Apparently it put a space between connectionstr and gs, it is correct in the actual code.


I can ping the SQL Server. I can log in to the site with one of the users I've created (indicating the site and SQL Server are communicating). What I can't do is run this code when I open a second page. It errors out on the line connObj.open() and hits me with this error:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Now, both myself and a co-worker have got this site to run correctly on our own laptops (with the SQL Server being on our laptops as well).

Is there something wrong with my code that would cause it to not connect when on a different server than the SQL Server? I know they've opened the ports that need to be opened in order for the Web Server to communicate with the SQL Server.

Any ideas what could be causing the error? All the threads I've found talk about checking that the name is correct or that the SQL Server allows remote connections and what not, and I've already verified all those things. Any help would be greatly appreciated, I'm at the end of my rope here with this error.
 
Last edited:

AccessMSSQL

Seasoned Programmer-ette
Local time
Today, 13:57
Joined
May 3, 2012
Messages
636
First thing I see is you have a space where it shouldn't be. the "gs" you see is supposed to be part of the ConnectionStrings attribue.
you have this...
Code:
ConfigurationManager.ConnectionStrin gs("
it's supposed to be this:
Code:
ConfigurationManager.ConnectionStrings("
 

jcruzAME

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2011
Messages
135
That is how it is in the code. For some reason it put a space there when I created this thread.
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Today, 13:57
Joined
May 3, 2012
Messages
636
What is the connection string you actually have set up in the web.config file?

Also, you might have this posted in wrong forum. This is Access Database forum. Looks like you are asking for .NET help.
 

jcruzAME

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2011
Messages
135
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="server=(ip address here);database=Webfair;Persist Security Info=True;User ID=ASPPortalLogin;Password=(password here)"/>
</connectionStrings>

The above is what is on the web server in the web.config file. It is also the only connection string we have, as it's a simple site.
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Today, 13:57
Joined
May 3, 2012
Messages
636
Where are you trying to run the page from? Are you running it from the webserver or another machine??
 

jcruzAME

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2011
Messages
135
There is a web server that the web portal will be on. The structure of the site is as follows:

Portal
*Default.aspx
*web.config (where the connection string is set up
*Crew Folder
*(In the Crew Folder)Cleans.aspx/Cleans.vb.aspx (where the code above is found)

Those pages are run on the web server, which connects to an SQL Server 2012 on another server (the IP address in the connection string).
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Today, 13:57
Joined
May 3, 2012
Messages
636
One more thing to try: From the machine you are working on I would do a test. Create an ODBC connection to the SQL Server manually with the same credentials you are using and see if you can connect. This might give you more information on the error. Also, how is your network set up? Is the machine you are running from connected to the domain properly?
 

jcruzAME

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2011
Messages
135
How do I do that? I assumed that since I can log in it is connecting to the SQL Server (otherwise the credentials wouldn't work or I figured I'd get some other error about not connecting). Also, I'm not sure about that, I just developed the website, the network guys handled setting it up so that the servers could communicate properly.
 

jcruzAME

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2011
Messages
135

jcruzAME

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2011
Messages
135
I just checked on the Server Browser, on my laptop (where the site works) it is not on either. I will turn it on and check.
 

jcruzAME

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2011
Messages
135
After checking the last two checks from that article I still get the same error.
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Today, 13:57
Joined
May 3, 2012
Messages
636
So what machine are you running the website from where it is producing an error? Does the machine you are having the problems on have a windows login account where you login to a domain? Also, is the install of SQL Server that is on your network set up for Mixed Mode authentication or is it only SQL Server authentication?
Did you try the method I suggested earlier to set up a manual ODBC connection from the machine you are having trouble with? I would try that now to eliminate possibilities.
 

jcruzAME

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2011
Messages
135
How do I go about setting up a manual ODBC connection?

Also, I remote into the SQL and Web servers from my laptop. I will check the authentication. I'm not sure on that. I know when I open the SQL Server Management Studio it defaults to Windows Authentication.

EDIT: It is set up for mixed authentication.

Also, on the servers when I remote in, the user names are WFINC\(username here). Like on my laptop, the SQL Server name is something like RH9XWMW\DEV2012(SQL Server 11.0.2100 - RH9XWMW\jcruz) and on the SQL server it's WFSQL01(SQL Server 11.0.2100 - WFINC\(username here)).
 

jcruzAME

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2011
Messages
135
Just created a manual ODBC connection and connected to the server no problem. Tested Data Source and it took like a split second to confirm.
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Today, 13:57
Joined
May 3, 2012
Messages
636
Not sure what the issue is then. You created the ODBC from the exact same machine you are trying to run the code on right? What credentials did you put in? Test ODBC with the same credentials that are in your web.config. Another question for you? Are you running the code locally in debug mode to test it? Or, is it already published to a sever. You should also check to make sure the internet guest account has rights to the SQL Server database. You need to actually add the internet guest account to SQL Server as a login and then give that login rights to your database.
 

jcruzAME

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2011
Messages
135
Not sure what the issue is then. You created the ODBC from the exact same machine you are trying to run the code on right? What credentials did you put in? Test ODBC with the same credentials that are in your web.config. Another question for you? Are you running the code locally in debug mode to test it? Or, is it already published to a sever. You should also check to make sure the internet guest account has rights to the SQL Server database. You need to actually add the internet guest account to SQL Server as a login and then give that login rights to your database.

When testing the ODBC, I did use the credentials I'm using for the site.

We have IIS installed on the server and it's being run from that. I click browse to test it.

That last part about the internet guest, can you expand on that? I've never heard that before. What would that entail for checking that?

Thanks for all your help so far.
 

Users who are viewing this thread

Top Bottom