By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,939 Members | 1,602 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,939 IT Pros & Developers. It's quick & easy.

How to display text files from a database

P: 15
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_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox2.SelectedIndexChanged

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={MySQL ODBC 3.51 Driver};Server=myServer;Database=myDatabase;User=m yUserassword=myPass;Option=3;")
cmd = New OdbcCommand("SELECT * 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.DataSource = ds

Me.ListBox2.DataTextField = "ResumesID"
Me.ListBox2.DataTextField = "Resumes"
Me.ListBox2.DataBind()

End If
End Sub
Jul 24 '07 #1
Share this Question
Share on Google+
3 Replies


P: 78
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?
Jul 24 '07 #2

P: 15
Hi, thanks for responding, that was an error I have fixed already and that line is now
Me.ListBox2.DataValueField = "Resumes"

Also the select statement has been changed to

("SELECT ResumesID,Resumes 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.
Jul 24 '07 #3

P: 78
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:
Expand|Select|Wrap|Line Numbers
  1. Dim myCon As OdbcConnection
  2. Dim i As Integer
  3. Dim cmd As OdbcCommand
  4. Dim sqlCommand As String
  5.  
  6. sqlCommand = "SELECT * FROM myTable where Professions IN ("
  7.  
  8. For i = 0 To ListBox2.Items.Count - 1
  9.     If ListBox2.Items(i).Selected Then
  10.          sqlCommand = sqlCommand + "'" & ListBox2.Items(i).Text & "',"
  11.     End If
  12. Next
  13.  
  14. 'Trim out the last comma, replacing it with a ) to complete the SQL statement
  15. sqlCommand = sqlCommand.SubString(1, sqlCommand.Length - 1) + ")"
  16.  
  17. myCon = New OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=myServer;Database=myDatabase;User=myUser;Password=myPass;Option=3;")
  18. myCon.Open()
  19. cmd = New OdbcCommand(sqlCommand, myCon)
  20.  
  21. Dim ds As New DataSet
  22. Dim ad As New OdbcDataAdapter(cmd)
  23.  
  24. ad.Fill(ds)
  25.  
  26. Me.ListBox2.DataSource = ds
  27. Me.ListBox2.ValueMember = "ResumesID"
  28. Me.ListBox2.DisplayMember = "Resumes"
  29.  
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.
Jul 24 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.