How to refresh data

[Cmr]

know its a trivial thing but some how I cannot sort it out.

I use the following code for deleting a record in ADO.NET backend SQL SERVER
I populate the combo box first by the following

MyCon.Open()
MySelect = New SqlCommand("select pid from CustomerDetails", MyCon)
MyDr = MySelect.ExecuteReader
cboDSEARCH.Items.Clear()
While MyDr.Read
cboDSEARCH.Items.Add(MyDr("pid"))
End While
MyCon.Close()

Then I wrote the below code in cboDSEARCH Text_changed event

MyCon.Open()
MySelect = New SqlCommand("select name from CustomerDetails where pid=" & Val(cboDSEARCH.Text), MyCon)
MyDr = MySelect.ExecuteReader
MyDr.Read()
txtDNAME.Text = MyDr("name")
MyDr.Close()
Dim A As Integer
Dim Msg, Style, Title, Response, MyString
Msg = "Do you want to Delete ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton1 ' Define buttons.
Title = "Bank Details" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
MyClean = New SqlCommand("delete from CustomerDetails where pid=" & Val(cboDSEARCH.Text), MyCon)
MyDel = MyClean.ExecuteReader
MyDel.Read()
MsgBox("deleted")
 
Else 'Perform some action.
MsgBox("Details not deleted")
End If
 
MyCon.Close()

The data gets deleted. But eg. if i deleted record no 2 and I scroll the combobox upwards then i get the error
"invalid attempt to read when no data is present"
I want to know how to refresh the data reader.
In VB6.0 I used to write data1.refresh
what is its equivalent in ado.net

Thanks
cmr

 

[cjard]

I sort-of answered this in another thread to you regarding how we put database data into comboboxes etc

Rather than adding items to the combo, vb6 style, you read the data into a DataTable, and tell the combo to view that data. This is a notion called MVC, whereby the model (data) is separated from the view (the thing used to view the data) and the controller (the thing used to manipulate the data)

In MVC style, you put your data into the datatable M, the combo is the V, and the button that deletes item 2, is the controller. Because .NET is set up in this fashion, the combo would notice as soon as you deleted the data from the model, and adjust accordingly. This is a step apart from the old ways of shoving everything into the one control ala vb6