How to display text files from a database

[WebNewbie]

Hi, please help I've been wrestling with this for a very long time and its not working. I'm trying to display text files from a database when someone selects one or more list items of my listbox. The list box contains names of various professions. The database I'm accessing contains Resumes field, Professions field, and ResumesID field. When a user selects one or more list items, the profession in the Professions field that match the text of the selected listitem, will be selected. The resumes corresponding to the profession is also selected and retrieved for displaying. Please help me display the resumes in a label or text area. The following is my code, thank you in advance for your help.


Private Sub ListBox2_Select edIndexChanged( ByVal sender As System.Object, ByVal e As System.EventArg s) Handles ListBox2.Select edIndexChanged

Dim myCon As OdbcConnection
Dim i As Integer
Dim cmd As OdbcCommand
For i = 0 To ListBox2.Items. Count - 1
If ListBox2.Items( i).Selected Then

myCon = New OdbcConnection( "Driver={My SQL ODBC 3.51 Driver};Server= myServer;Databa se=myDatabase;U ser=myUserasswo rd=myPass;Optio n=3;")
cmd = New OdbcCommand("SE LECT * FROM myTable where Professions = '" & ListBox2.Items( i).Text & "'")

End If
Next
If Not IsNothing(myCon ) Then
myCon.Open()

cmd.Connection = myCon

Dim ds As New DataSet

Dim ad As New OdbcDataAdapter (cmd)

ad.Fill(ds)

Me.ListBox2.Dat aSource = ds

Me.ListBox2.Dat aTextField = "ResumesID"
Me.ListBox2.Dat aTextField = "Resumes"
Me.ListBox2.Dat aBind()

End If
End Sub

[RoninZA]

So where is the problem, how is your code not working?

I see you are setting the DataTextField property for ListBox2 twice, that not maybe the problem?

[WebNewbie]

Hi, thanks for responding, that was an error I have fixed already and that line is now
Me.ListBox2.Dat aValueField = "Resumes"

Also the select statement has been changed to

("SELECT ResumesID,Resum es FROM contenttbl where Professions = '" & ListBox2.Items( i).Text & "'")

Unfortunately the changes didn't help.Can you tell me how to edit my post,? I'm new here and I can't seem to find the Edit option.

[RoninZA]

OK, sorry, I must've still been asleep when I read your first post...

The problem is that you're filling your dataset outside the FOR loop...

What I would suggest you do is to change your code to create the connection outside the FOR loop, and use the FOR loop to generate a SQL statement with a 'IN' where clause, and then filling your dataset AFTER the loop, ie something like this:

Dim myCon As OdbcConnection
Dim i As Integer
Dim cmd As OdbcCommand
Dim sqlCommand As String
 
sqlCommand = "SELECT * FROM myTable where Professions IN ("
 
For i = 0 To ListBox2.Items.Count - 1
    If ListBox2.Items(i).Selected Then
         sqlCommand = sqlCommand + "'" & ListBox2.Items(i).Text & "',"
    End If
Next
 
'Trim out the last comma, replacing it with a ) to complete the SQL statement
sqlCommand = sqlCommand.SubString(1, sqlCommand.Length - 1) + ")"
 
myCon = New OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=myServer;Database=myDatabase;User=myUser;Password=myPass;Option=3;")
myCon.Open()
cmd = New OdbcCommand(sqlCommand, myCon)
 
Dim ds As New DataSet
Dim ad As New OdbcDataAdapter(cmd)
 
ad.Fill(ds)
 
Me.ListBox2.DataSource = ds
Me.ListBox2.ValueMember = "ResumesID"
Me.ListBox2.DisplayMember = "Resumes"
 

I haven't really tested this code, and my VB is a little rusty, but this should point you more or less in the right direction.

Good luck.