Second time database read

[hc98br]

I very new to this, so excuse any ignorance, but I have the following problem!

I have the following code:

OleDbConnection.Open()
selectResults.Parameters(0).Value = startDate
selectResults.Parameters(1).Value = endDate
Reader = selectResults.ExecuteReader()

While Reader.Read()
    ...
End While

OleDbConnection.Close()
Reader = Nothing

OleDbConnection is a data connection dragged onto the form
selectResults is the SelectCommand of the data adapter connected to OleDbConnection.


This code work fine, and does everything its supposed to do, but, if I run it a second time (click the button again), I get the follow error:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

on the line Reader = selectResults.ExecuteReader().

Interestingly I have another section of code doing a similar thing, without the parameters, and that works fine, time and time again.



Any ideas most welcome

Thanks

Ben.

 

[SqueakinSweep]

Ben
As you probably know datareaders are forward only read, and can be read only once.

I think your problem will be solved if you create a new instance of the datareader before doing the SQLExecute.

Dim reader As new SqlClient.SqlDataReader
Reader = selectResults.ExecuteReader()

or declare a variable which is scoped to the form
Dim reader As SqlClient.SqlDataReader
and then for each time it is required
Reader = new SqlClient.SqlDataReader



Sweep
...if it works dont mess with it

 

[hc98br]

Thanks for your reply, just got round to trying it.

I forgot to mention that I already have the line

Dim Reader As System.Data.OleDb.OleDbDataReader

I moved this to the header of the form, incase that was the problem, but nothing changed, so I left it there.

I tried using your SqlClient line instead, but I got an error on syntax error on ExecuteReader(), on the basis that selectResults is a OleDbDataReader.

I also tried creating a new object, as you suggested, this also gave a syntax error, telling me that it is Private!


Am I missing something?


Ben.

http://www.ben-robinson.com

 

[SqueakinSweep]

Apologies Ben

Reader = new System.Data.OleDb.OleDbDataReader
Reader = selectResults.ExecuteReader()

Sweep
...if it works dont mess with it

 

[hc98br]

I've put that extra line in, but get a error on the new line (with the squiggly line), the tool-tip tells me that it is "not accessible in this context because it's private"

any ideas?


I still don't understand that the identical code, but without parameters works fine, time after time - without error.

http://www.ben-robinson.com