Data Reader Issue 2

[dvannoy]

I've been using data readers for some time now and have never had a problem until now. I can't figure this out.

I'm using a datareader to populate some controls. Textboxes and comboboxes. I'm typing a record number into a textbox to execute the reader below. everything gets populated correctly except when I type in another record number to call another set of data. what is happening is not all the controls get refreshed with the correct data. meaning some data is left behind from the previous record.

e.g. I type in 41 into the search textbox and press the search button. the SP fires off and the reader populates the controls below. everything is fine. But if I type in say, 42 into the textbox and press the search button, the SP gets fired off and so does the reader. The issue is that some of the controls still have the data from record 41 and not record 42.

it seems like the reader is stopping during the SP execution ???

any help would be appreciated.



Dim cmd As New SqlCommand
Dim reader As SqlDataReader
Dim param As SqlParameter
Dim con As New SqlConnection(MyConnection)

Try

con.Open()

cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "MYSP"

param = New SqlParameter("@MY_FIELD", l8.Text)
param.Direction = ParameterDirection.Input
param.DbType = DbType.Int32
cmd.Parameters.Add(param)

reader = cmd.ExecuteReader()

If reader.HasRows Then

While reader.Read


t16.Text = reader("field1") & ""

ddl17.Text = reader("field2") & ""

t17.Text = reader("field3") & ""

ddl18.Text = reader("field4") & ""

End While

End If

reader.Close()

 

[JRB-Bldr]

what is happening is not all the controls get refreshed with the correct data. meaning some data is left behind from the previous record

Are you sure that you have the same number of records/rows returned in the subsequent query results as you did in the first run?

If not, then you populated all of your fields on the first pass.
Then when a new query was run, the While reader.Read/End While ended the field populate loop sooner than expected and therefore did not populate the other fields with new data.

Just a guess.
I'd put a breakpoint into the While reader.Read/End While loop and single step through - paying special attention to watching the code execute on the subsequent queries.

Good Luck,
JRB-Bldr

 

[dvannoy]

Connection Implicit function evaluation is turned off by the user System.Data.SqlClient.SqlConnection
Depth Implicit function evaluation is turned off by the user Integer
FieldCount Implicit function evaluation is turned off by the user Integer
HasRows Implicit function evaluation is turned off by the user Boolean
IsClosed Implicit function evaluation is turned off by the user Boolean
RecordsAffected Implicit function evaluation is turned off by the user Integer
VisibleFieldCount Implicit function evaluation is turned off by the user Integer


This is all I'm seeing unless I'm not doing this correctly

 

[jbenson001]

Most likely, when you get back the second set of data, not all the fields you expect to have values, do. So the old values stay. It is hard to say what is gong on since you have not posted any code. You should post the code you have to access the DB and then populate your controls.
I suspect that each time you go to the DB, before populating the controls, you will have to clear any previous values.