Linking a listbox to database

[verbalurbs]

Hi All,

I have a database (sql server) where i store activity information example (activityid,id,summary,signs etc.)
I can successfully save this information to the database. what I also want to achieve is to add this information to a listbox so that it can be displayed in the corresponding textboxes at runtime for an encounterid.
I got an idea but not 100% sure how to go about this; If possible can the steps be explained to me. Code would be appreciated but I'd like to understand it as well.

Thanks..:)

[RicardoE]

There are several ways to do this, I'll show you one:

Step 1:
you have to import Sql Client libraries, otherwise this wont work

Imports System.Data.SqlClient

Step 2:
Set a connection string to the DB, you can find several connection strings types at: http://www.connectionstrings.com/

to set this use:

Dim myConnection As SqlConnection
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")

you migth use your own connection string, this is just an example

Step 3:
Open your connection to the database

Try
   myConnection.Open()
   ....

know, I will take a database table with only one column for this example, lets say the table name is "PART" and contains part's Id's or something

Step 4:
Now comes the intresting part, which is to retrieve data from the db, for this you need the following:

Dim myCommand As SqlCommand
myCommand = New SqlCommand("Select * from part", myConnection)
Dim dr As New SqlDataReader()
dr = myCommand.ExecuteReader()

in this code we started a new database command (query), and a data reader instance to read query returning values.

Step 5:
Go through the data reader, and populate the listbox

Dim i As Integer
i=0
while dr.read() 
   listbox1.Items.Add(dr(i).ToString);
   i++
End While

Step 6:
Dont forget to close every connection!!

dr.Close()
myConnection.Close()
Catch e As Exception
End Try

And that's it... hopefully it will work :D

[verbalurbs]

Hello Ricardo thanks for the explanation and code. very useful and it works :)
Just a few more questions:
Is there a way to display the text in the list box as a string for example each row in the table will be a string in the list box so when its clicked on it can be displayed in the respective text boxes. Also after I have added quite a few activities I get an error message saying index out of bounds.
I put code under the listbox selected index changed but nothing was displayed. and also in the click event but again nothing.

Thanks..

[thanlin]

hi, do u know how to download link vb.net application?

[thanlin]

I lost my vb.net sofware cd. I can't install. I am just vb.net applcation developer.

[thanlin]

Hi All,

I use SQL server 2005, I can't attach database, if , plz let me know. i am finding a way around 3 weeks. thanks everybody.

[OldManStarting]

I know this is an older post, however I have just come across it and wanted to thank RicardoE as this helped me solve a problem i had been working on all day :)