Irritating problem c# Ado.net

[akshaycjoshi]

I am developing one web app in which i need to connect to 6 access databases one at a time.
The problem is that the code does not work well properly, sometimes it gets stuck over
Executereader() function and when i use the debugger to know the value of Connection , Command
it 's shown as
Error: can not obtain value.(there is one sign of refresh button)
The same sign is for all the members of the connection object and all the onjects invloved like command and reader !
The problem is that the code jams sometims in the 1st database sometimes in the second etc etc.

How to get around this problem ?

Please help it's very urgent !

[nukefusion]

First thing I would probably do is review the code to make absolutely sure that you're closing all connections when finished with them.
I've seen similar things happen myself when connections aren't closed properly, so I'd eliminate that first and take it from there.

[akshaycjoshi]

Thanks bro.
Here is the code

        While (isfound = False)
            Try
                con = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;Persist Security Info=False; data source=" & Server.MapPath("App_data/PAT_ATT" & dbiterator.ToString() & ".mdb"))
                con.Open()
                If mode = "appno" Then
                    cmd = New OleDbCommand("Select * from temp where appno = '" & appnumber & "'", con)
                Else
                    cmd = New OleDbCommand("Select * from temp where cnm = '" & appname & "'" & " and dob='" & appdob & "'", con)
                End If
                reader = cmd.ExecuteReader()
                If reader.HasRows Then
                    isfound = True
                    Session("patravi2008searchedappno") = reader("appno").ToString()
                    Session("patravi2008founddatabase") = dbiterator
                    LabelRoll.Text = reader("rol").ToString
                    LabelName.Text = reader("cnm").ToString
                    LabelFName.Text = reader("fnm").ToString
                    LabelappNo.Text = reader("AppNo").ToString
                    LabelCat.Text = reader("cat").ToString
                    LabelDOB.Text = reader("dob").ToString
                    Labelsex.Text = reader("sex").ToString
                    LabelClass.Text = reader("class").ToString
                    Labelcentre.Text = reader("cnt_cd").ToString & " --" & reader("cnt_nm").ToString
                    LabelCN.Text = reader("vypno").ToString
                    studentimage.ImageUrl = "patravi2008showpic.aspx?"
                    sign.ImageUrl = "patravi2008showsign.aspx?"
                End If
 
            Catch ex As Exception
 
            Finally
                If con.State = ConnectionState.Open Then
                    con.Close()
                    con = Nothing
                End If
 
                If reader.IsClosed = False Then
                    reader.Close()
                End If
                dbiterator = dbiterator + 1
            End Try
 

I dont think there is some problem in the code.
One imp thing is that i can see some thread.ThreadabortException in mscorlib.dll in the output window.
Is there any relation with that ?

PS:It's very urgent.

[akshaycjoshi]

I can also see some "code has exited with code 0" three times in the output window.

[Ramk]

Try changing the order of releasing in the finally block as shown below, also, include the null check before you access the object...

Finally
If reader <> Nothing and reader.IsClosed = False Then
reader.Close()
End If
 
If con <> Nothing con.State = ConnectionState.Open Then
con.Close()
con = Nothing
End If
 
dbiterator = dbiterator + 1
End Try
 

[Ramk]

@Ramk
hi akshay,
did your problem is solved?

[akshaycjoshi]

I came to know that that it was happening coz of the slow OleDbConnection and OleDbCommand.
As far as "Value not shown" error, it was coz of the fact that the fuction was getting executed somewhere else (System.Data.dll) so the debugger is unable to obtain the data for al the objects involved (Connection,Comand and reader)

[Frinavale]

After reviewing the code you've posted I don't see where you are connecting to 6 Access databases at the same time.

You will see a thread.ThreadabortException when you redirect to a new page...this aborts the current thread before it's finished executing. If you specify the URL and a Boolean as parameters for the Redirect() method you can avoid these errors.


Are you seeing the ADO errors in your Catch block?

-Frinny

[Plater]

Well I see that you are only trying to close the connection if the state is "open"
What if it is in one of the other states? Then it gets left open, and the jet DB driver will be angry

[akshaycjoshi]

@Frinavale

See the while loop.
Also i am not connecting to 6 access databases at the same time, it's one by one i am iterating over them.

[akshaycjoshi]

@Plater
yes a good point.Now i guess the code should be
if(con.State <> ConnectionState.Close)
Con.Close()

?

[Plater]

@akshaycjoshi
It probably wouldn't hurt. Can't be sure if it will solve problem or not

[akshaycjoshi]

@Plater
Can a broken connection be closed ?
I have hunch.

[Plater]

I guess if you really wanted to, you could switch on the connectionstate and look to see if it ever comes back in an odd state.
I have not run into a problem closing a broken connection before, that I am aware of.