SQLCon.Open() Error

[jlcruzAME]

I have quick question about this code as I am trying to call a stored procedure from VB.NET. Previously in my code I had tried to set up something like this:

[COLOR=#003399][FONT=Verdana]Dim SQLCon As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString)
'SQL Select Statement Here
SQLCon.Open()[/FONT][/COLOR]

For the Connection String I was using the same one that is set up in my web.config. I have the SQL Server installed on my laptop from which I was testing the site, and this would work. HOWEVER, when we moved it to the live server, where we were connecting to the SQL Server that was on another server, it would always error out saying it couldn't open a connection. Is it because my web.config already has this connection open and therefore trying to open it again would cause an error? Is there a way to use the connection without trying to open it again?

Any help would be appreciated.

 

[IanRyder]

Hi,

You are making us guess a bit here since we have no idea how or when your website is accessing your database on the server. You are also using "past tense" in your post so I am also guessing that you have already got round this problem?

The simple thing to remember is that an error will occur if you call SQLCon.Open on a defined SQLConnection type that is already open. You can create and call additional SQLConnection types fine but if you are using a global connection type then you should check to see if it's open first before you call the open method. i.e-

If Not SQLCon.State = ConnectionState.Open Then
  SQLCon.Open()
End If

All that being said, it could easily be that your Online Connection string is incorrect, but again and I quote, you seem to be talking in "past tense"?

Hope that helps,

Cheers,

Ian

 

[jlcruzAME]

Thanks for the reply. In the previous problem I was having, I created a view in the SQL Server to get around it. Now I need to call a stored procedure when a certain button is clicked and the code I have found for doing so involves creating code similar to the above. I will implement your check and reply to this thread with my results.

 

[IanRyder]

Hi,

It seems strange that creating a view solved your problem? This implies that there is no issue with your connection question. In the SQLCommand type that, I assume you are using, have you remembered to change the default command type from Text to StoredProcedure? i.e:-

SQLcmnd.CommandType= CommandType.StoredProcedure

Cheers,

Ian

 

[jlcruzAME]

Ian, the initial problem I was having was that I had a page load even that would grab an ID from a table to load data on the page according to that ID. I had code similar to what I pasted in the initial post and it was erroring out on the object.open line and I didn't know to put that check on it, so I created a view to use as the Page source that would avoid that code. Also, this is my first project with ASP.NET and I'm new to it as well. I worked with VBA before this and was unfamiliar with VB.NET at the time.

 

[jlcruzAME]

Ian, thank you for your help. I just tested the code that was giving me issues with the check to see if the connection was open and it worked. I guess the connection on my laptop doesn't stay open but the one on the actual web server does? Not really sure what's going on there. Thanks again!