Querying a DataSet

  • Thread starter Thread starter Rob Venable
  • Start date Start date
R

Rob Venable

Hi all,

Am I on the right track here or am I going about this the wrong way. I'm
new at VB.Net and I've written a little App that displays all contacts
in a Listbox.That part I've got but what I want to do is display all
contact information in the textboxes when I click on a Selected Item in
the Listbox.

I've been reading up and decided the best way to populate the listbox
would be to populate a dataset and then query the dataset to fill in my
textboxes raher than going back to the database.
Here's the code to populate the listbox:

Private Sub ShowContacts()
Dim cn As SqlConnection = New SqlConnection()
cn.ConnectionString =
Configuration.ConfigurationSettings.AppSettings("CONN_STRING")

Dim strSQL As String = "SELECT * FROM contacts"
Try
Dim CM As SqlCommand = New SqlCommand(strSQL, cn)
Dim DA As SqlDataAdapter = New SqlDataAdapter()
DA.SelectCommand = CM

cn.Open()

DA.Fill(DS, "Contacts")
lstContacts.DataSource = DS.Tables("Contacts")
lstContacts.ValueMember = "contact_id"
lstContacts.DisplayMember = "name"

cn.Close()

Catch ex As SqlException
MessageBox.Show(ex.Message)
Finally
cn.Close()
End Try
End Sub

I've declared my dataset at the form level so I could use it in my
lstContacts_SelectedIndexChanged Subroutine.
I know I can get the id from the Selected Item by calling the
lstContacts.SelectedValue property but I'm not sure how to query the
dataset and have the contact information return from the selected
item(contact_id) to populate these textboxes e.g txtFirstname,
txtLastname, txtAddress etc...

Am I going about this the right way or is there a better way of doing
this.

Any help would be greatly appreciated.

Thanks
Rob
 
Hi Rob,

If I did read it well than is this all you need.

textbox1.DataBindings.Add(New Binding("Text", ds.Tables("Contacts"),
"LastName"))
textbox2.DataBindings.Add(New Binding("Text", ds.Tables("Contacts"),
"Firstname"))
etc

Did you know you have a connection.close to much in your code. But for that
code you do not
need no open or close from a connection. The dataadapter does it for you.

If you do it like you do, you have to clear your databindings everyway, try
to bind and set the dataset only the first time you have the schema and
constrains from the dataset.
(Or just with a switch the first time ).

But this becomes to much so try this advices first

I hope this helps?

Cor
 
Thanks Cor,
I wasn't aware that you didn't have to open or close a connection when
using a DataAdapter.

I've added your code to my SelectIndexChanged sub...

Private Sub lstContacts_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
lstContacts.SelectedIndexChanged
Try
txtFirstname.DataBindings.Add(New Binding("Text",
DS.Tables("Contacts"), "firstname"))
Catch ex As System.ArgumentException
MessageBox.Show(ex.Message)

End Try
End Sub

...but I get an error "This would cause two bindings in the collection
to bind to the same property. Parameter name: Binding"
After I click on the error, the textboxes get populated. And everytime I
click on another contact...the same thind happens.

Any Ideas?

Thanks again for your help.

Rob
 
Hi Rob,

This was also in my message
If you do it like you do, you have to clear your databindings everyway, try
to bind and set the dataset only the first time you have the schema and
constrains from the dataset.
(Or just with a switch the first time ).
Click to expand...

(I wrote everyway has to be every time, I think I first wrote anyway and
corrected it.)

If you put in the routine where you do the binding a static bool and you
test that the first time you bind on not true and set it on true when you
did the databinding, than you have in my opinion the easiest way to do that.

Cor
 
Thanks again Cor,
I didn't know what you meant on the first email but I do now. Works like
a charm.

Rob
 
Back
Top