Why does this happen 1

[compu66]

Hello
Public Function GetFTPInfoSQL() As SqlDataReader
Dim cmd As New SqlCommand
Dim da As New SqlDataAdapter
Dim ftpInfo As SqlDataReader

Try
cmd.Connection = New SqlConnection(ConnString)
cmd.Connection.Open()

cmd.CommandType = CommandType.Text
cmd.CommandText = _
"SELECT SystemValue, SystemSetting FROM SystemSettings2 " + _
"WHERE SystemSetting = 'FTPUserName' OR SystemSetting = 'FTPAddress' " + _
"OR SystemSetting = 'FTPPassword' OR SystemSetting = 'FTPRemoteDirectory'"

da.SelectCommand = cmd

ftpInfo = da.SelectCommand.ExecuteReader(CommandBehavior.CloseConnection)------------1

GetFTPInfoSQL = ftpInfo
Return GetFTPInfoSQL

Catch ex As Exception
Dim errorMsg As String
errorMsg = "Error getting FTP Info"
'--- Throw error to DALException class for handling
Throw New DALException(errorMsg, ex)
Finally

'cmd.Connection.Close()---------------------2
da.Dispose()
End Try
End Function



From the above code if i write the cmd.connection.close() i am getting an error as"u cant access when the reader is closed."So i commented it.

Usually we try to dispose or close, connection and objects in Finally block.
So inpite of 1 I wanted 2.If 1 dont gets executed the connection might be open so i attempted to write 2.But its giving an error.

Thanks for any help in advance.

 

[compu66]

hey If i send the item short other pals ask for the code if I send the code I get no replies.Do i have to improve in any way to get more replies.

I am posting as i need someones help not to waste others time.

 

[jmeckley]

wrap a using statement around the connection object. it will automatically close/dispose objects.
c# looks like this

using(IDbConnection cnn = new SqlConnection())
{
   IDbCommand cmd = new SqlCommand(cnn);
   IDbReader reader = cmd.ExecuteReader();
}

whatever is delcared within the using block is disposed upon exiting the using statement.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[mbiro]

Maybe I'm missing something, but I would do it like this:

Public Function GetFTPInfoSQL() As SqlDataReader
Dim cmd As New SqlCommand()
Dim cn As New SqlConnection(ConnString)
GetFTPInfoSQL = new SqlDataReader
cmd.CommandType = CommandType.Text
cmd.CommandText = _
"SELECT SystemValue, SystemSetting FROM SystemSettings2 " + _
"WHERE SystemSetting = 'FTPUserName' OR SystemSetting = 'FTPAddress' " + _
"OR SystemSetting = 'FTPPassword' OR SystemSetting = 'FTPRemoteDirectory'"
cmd.Connection = cn
cn.Open()
Try
GetFTPInfoSQL = cmd.ExecuteReader
Catch ex As Exception
Finally
cn.Close()
End Try

Return GetFTPInfoSQL
End Function

 

[jmeckley]

depends if you want to swallow the exception or not. my prefernce is to allow exceptions to bubble up the callstack and handle it at the domain level. (AppDomain.Current.UnHandledExcpection)

it's not considered good practice to try/catch every possible exception. Theres alot of discussion on the subject. "experts" recommend only catching errors you expect to occur. Or catching errors to wrap them with your own custom exception and rethrow. The also recommend never catching [tt]Exception[/tt] rather handle a specific exception like SqlClientException.

at a minimum place the cn.Open call within the try block. this is the first logical point your code would fail. also execute reader will automatically open the connection, so cn.Open is really needed at all.

if this is your production code [tt]try ... Catch ex As Exception Finally cn.Close() End Try[/tt] is the worst thing you can do, because you loose all detail about the exception.

At a minimum you should log the error

try
{
   using(IDbConnection cnn = SqlConnection())
   {
      ...
   }
}
catch(SqlException e)
{
   MyLogger.Error(e);
}

where MyLogger is some form of logging code. there are plenty of loggin libraries out there log4net; MS EL 1, 2, 3; nLog; etc...


Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[mbiro]

Perhaps a discussion about proper exception handling is outside the scope of the question? I certainly wasn't trying to write the complete solution; only a nudge in a direction. But, you are correct. You should avoid swallowing an exception and log it.

 

[ca8msm]

hey If i send the item short other pals ask for the code if I send the code I get no replies.Do i have to improve in any way to get more replies.

There are some general rules in this forum that will help you get better help. From reading some of your questions, I'd say the most important are:

1) Use an appropriate title for your thread. "Why does this happen" doesn't mean anything and doesn't relate to the problem. At a guess, I'd say 90% of threads with titles like this I wouldn't even bother to read.

2) Don't be impatient. You bumped this thread after only 27 minutes asking why you haven't had any replies. People are not here to serve you - they will answer you when and if they want to. If I see this trend with a poster, their future posts generally don't get my attention.

3) Mark posts as "helpful". There is a link under each post that allows you to do this. This shows that the help someone gave was useful, and shows other posters which answers may be helpful if they get the same problem.

4) Give something back. If you only ask questions, and don't try to help other people on the site by starting to help answer their problems, then you aren't contributing to the community. This will usually result in restricted access to the site.

For a full list of what you can do to improve the questions you write, and answers you get, read faq222-2244


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]