Recordset closes for no reason

[Adam Maltby]

Hi,

I am using vb.net and sql2000.
I am running a sql statement using a udl to connect to the database.
Throuhg one sub, several sql statements are executed against the udl's cn and I just reuse the rs to hold the next result set.

My problem is that through a few statements this works fine - I can use "With rs" and set up a cycle to loop through the rs to confirm data returned. But when I reach one of them (they are the same except for the sql statement passed) the rs closes unexpectedly and I cannot see the data within it hence it then returns an error stating the rs is closed.

Any ideas why it might be doing this? I am not telling the rs too clsoe anywhere in my code (yet).

Cheers
Adam

 

[Gerry O'Brien [MVP]]

You should be using a dataset not a recordset in VB.NET.

If you are using a recordset though using ADO and not ADO.NET, then verify what type of cursor you are using.

--
Gerry O'Brien [MVP]
Visual Basic .NET(VB.NET)




Hi,

I am using vb.net and sql2000.
I am running a sql statement using a udl to connect to the database.
Throuhg one sub, several sql statements are executed against the udl's cn and I just reuse the rs to hold the next result set.

My problem is that through a few statements this works fine - I can use "With rs" and set up a cycle to loop through the rs to confirm data returned. But when I reach one of them (they are the same except for the sql statement passed) the rs closes unexpectedly and I cannot see the data within it hence it then returns an error stating the rs is closed.

Any ideas why it might be doing this? I am not telling the rs too clsoe anywhere in my code (yet).

Cheers
Adam

 

[Cor Ligthert]

Gerry,

You should be using a dataset not a recordset in VB.NET.

The upgrade uses that, so this sounds for me a little bit that you say that
the Microsoft product is not OK, do you want to say that?

Cor

 

[Cor Ligthert]

Adam,

This is in my eyes impossible to answer without the part of code you are
talking about.

And forgive me tha I do not know it, however what is an UDL .

Cor

 

[Adam Maltby]

Cor,

A UDL is a Universal DataLink file used to connect to database.

To make one ....
Right click on your desktop and use the context menu shortcut to create a new text file.
rename it as "somefilename.udl".
It will change the file type and the icon (when you change the file extension and save it windows converts the file automatically).
If you then double click to open the new created file it should be self explanatory for filling in sql server etc.

I then use it my code like this example:
#######
Dim udl As String = "FileName=Someath\to\the\udl\file.udl"
Dm cn As New ADODB.Connection
Dim SQLStatement As String = "Select ColumnA From ThisTable Where ColumnA = 123"

cn.ConnectionString = udl
cn.Open
rs = cn.Execute(SQLStatement)
With rs
If Not (.BOF And .EOF) Then
.MoveFirst()
Do While Not .EOF
MsgBox(.Fields(0).Value)
Loop
End If
End With
cn.Close
##############

I'll try using this with a dataset to see if I get a better result than using recordsets.

Cheers
Adam

 

[Adam Maltby]

Hi Gerry,

Any reason why that is?
The first few queries execute fine using rs to hold the results.

Cheers
Adam

 

[Gerry O'Brien [MVP]]

No, not saying that at all Cor.

Saying that the Dataset is so much easier to use and in my opinion, better.

 

[Gerry O'Brien [MVP]]

ADO.NET is designed to work in a disconnected environment which moves the responsibility of managing the records over to the client. This works well in an evironment where the data source may be disconnected due to network issues or latency such as across the Internet or a network connection.

Not only that, but simple updates take place in an in-memory representation of the data which eliminates a round trip to the server. You merely do what amounts to a batch update when you are ready to write the changes back to the data source.

As to why your connection is being closed, can you provide more information?

1) Does it occur at random? On the same record? After a certain number of reads?

2) Is the data source local or across the network?

3) Are there any consistencies that you can see that constitute a pattern of some sort?


--
Gerry O'Brien [MVP]
Visual Basic .NET(VB.NET)




Hi Gerry,

Any reason why that is?
The first few queries execute fine using rs to hold the results.

Cheers
Adam

 

[Cor Ligthert]

Gerry,

No, not saying that at all Cor.

Saying that the Dataset is so much easier to use and in my opinion, better.

I agree that of course, however that "You should" sounded so cruel and so
definitive when I readed it, ADODB can be used in VB.Net, however I will as
well never recommend it..



Cor

 

[Adam Maltby]

It always occurs after a certain sql statement has been run.
But I don't how this would affect it.
The datasource is a local install of sql.
The only pattern is it always stops after one particular sql statement - but how would a statement force a record closed......... ?

 

[OpticTygre]

Possibly if the statement is returning 0 records, in which case an rs.EOF or rs.BOF would be true, and cause errors trying to access anything else having to do with the result set.
It always occurs after a certain sql statement has been run.
But I don't how this would affect it.
The datasource is a local install of sql.
The only pattern is it always stops after one particular sql statement - but how would a statement force a record closed......... ?

 

[Cor Ligthert]

Adam,

I tried your code (not using the UDL however making from that a connection
string, that goes very nice when changing it back to txt) however when it
was running your code comes never at to an end. It did when I made this
little change, (It was a while ago I did something with ADO and my advice is
as well when it is posible to go direct to ADONET).

With rs
If Not (.BOF And .EOF) Then
.MoveFirst()
Do While Not .EOF
.MoveNext
Loop
End If
End With
cn.Close



And thanks for learning me that method I did not know.

Cor

 

[Adam Maltby]

DOH!!!!!!!!!!!!!!!!!!!!!!!!!!!!
So simple and right under my nose!!!!!
Sorry guys for wasting your time - I should have seen that one coming!

Thanks
Adam

 

[Gerry O'Brien [MVP]]

Hmmm, you're right. My choosing of words sometimes is not best based on the
context. Especially in written communciation where intent is hard to
discern without the facial or vocal expressions.

You certainly can use ADODB and it is my preference to NOT use it, however,
others are still comfortable with it.

 

[Adam Maltby]

Apologies for forgetting the ".movenext" - Was a bit silly of me!

Out of prefernce our company prefers apps to be written using UDL's.....
Do you know if UDL's can populate a .net dataset?

Cheers
Adam

 

[Cor Ligthert]

Adam,

The link bellow is OleDB and you should use SQLClient.SQLConnection I never
saw a difference exept with the parameters.

http://msdn.microsoft.com/library/d...OleDbConnectionClassConnectionStringTopic.asp
However this text is in it.

To reference a UDL, add a reference to the UDL to the ConnectionString in
the form "File Name = myfile.udl". If more than one UDL is specified in the
connection string, only the last occurrence is loaded. If you specify a UDL,
and subsequently call the OleDbConnection object to retrieve the
ConnectionString, the ConnectionString contains the reference to the UDL
file, not the contents of the file. Connection strings that contain UDLs are
parsed each time the OleDbConnection is opened. Because this can affect
performance, a ConnectionString that does not reference a UDL is
recommended.

Maybe you can try it,

I saw it when I was busy with your sample.

Cor