List Box And Adodb Combination

[darrel]

Hey guys i have a question am having trouble in displaying record in a list box, what i want to achieve is this... i have 2 check boxes, checkbox1 is name MEASLES and checkbox is name "CHICKEN POX". i have a database name MEDICALDB and has 4 tables named STUDENT NUMBER, NAME, CHIKEN POX AND MEASLE..

i have this example database record:

Student Number Name Chiken Pox Measles
200013253 Darrel NO NO
200013254 Gary YES NO
200013255 Norman NO YES
200013256 Larry YES YES

The scenarion i wanted to search for the names that have value of YES in the Chicken Pox Field and display the name in a listbox..

I'am having trouble on how to implement it in my program so far i have this codes:

Dim rsShowrec1 As New ADODB.Recordset
Dim cnn As New ADODB.Connection
 
Set cnn = New ADODB.Connection
cnn.ConnectionString = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & App.Path & "\MedicalDB.mdb;"
cnn.Open
 
If check1.value = 1 Then
Label1.caption = "YES"
 
On Error Resume Next
rsShowrec1.Source = "SELECT * FROM MecidalData where [CHICKEN POX] = ' " & Label1.caption & " '
rsShowrec1.Open , cnn, adOpenStatic, adLockOptimistic
 
If rsShowrec1.EOF <> True Then
List1.AddItem rsShowrec1.Fields("Name").Value
rsShowrec1.MoveNext
end if
End Sub
 

So far its not working... Any help will do... Thank you... i hope your getting wat i want to achieve... By d way am using ADODB for my database connection.

[debasisdas]

try this

sqlstr= "SELECT * FROM MecidalData where [CHICKEN POX] = ' " & Label1.caption & " '
rsShowrec1.Open sqlstr, cnn, adOpenStatic, adLockOptimistic
 
Do While rsShowrec1.EOF = False
List1.AddItem rsShowrec1!name
rsShowrec1.MoveNext
Loop
 

[Killer42]

My guess is, this is VB6 - right?

So what's wrong with your current code? "Not working" is pretty vague. Is it producing an error? If so, what error and at what line of code? If not, in what way does it not match what you expect?

[darrel]

Theres no error on runtime my problem is the name are not populated in the list box. i will try another sequence. ill keep you posted . Many thanx.

[darrel]

Hello, ive tried what debasisdas suggestion, no sad to say no luck, there no error on runtime, but it doesnt populate my database record in the listbox and it has infinite loop.

Any more suggestion am badly need to finish this task so that my system will be implemented... Thank you..

[9815402440]

hi

the problam is in your query. if datatype of the [CHICKEN POX] is YesNo then
then your query must like this

SELECT * FROM MecidalData where [CHICKEN POX] = " & iif(Label1.caption = 'Yes',True,False)


regards
manpreet singh dhillon hoshiarpur

[darrel]

Thank you for all the advice and tips. I figured out how I can populate database records in my list box, I just used the long method procedure.

Now, I have a new question for you guys. Now that I'm able to populate database records in my lists, what I wanted to do now is to populate all the items in my lists to a datareport to produce a hard copy.

Can you give me sometimes, I'm nearly close in finishing my Medical and Dental Record System.

Thank you, it's good to belong here... hooray!

[QVeen72]

Hi,

In DataEnvironment, add a Connection object and set the parameters to the database and test the connection. Right click Conn and add a Command object. Change the SQL query as "Select * From MyTable".

Add a new DataReport to the project. Open the Command Object node. You will see all the fields. Drag them and drop them on the DataReport. Adjust the lines. And in VB6 to show the report:

DataReport1.Show

Regards
Veena