Problem while connecting Oracle and VB6!!!

Hello friends,
I am working on my project which makes use of VB as the front end and Oracle as its back-end. My oracle database consists of 2 coloumns named topic and des. This topic must appear in a listbox and when we click a topic the description should appear in a textbox in the form named txtcontent.
(The connection and recordset have been declared in a module)
The codings are as given below:

Dim item As String
Private Sub Command1_Click()
Unload Me
End Sub

Private Sub Form_Load()
con.Open "Provider=MSDAORA;user id=scott;password=tiger;"
rs2.Open "elearn", con, adOpenDynamic, adLockOptimistic
Set lstcom.DataSource = rs2
lstcom.DataField = "topic"
Dim n As Integer
n = rs2.RecordCount
Do While Not (rs2.EOF)
lstcom.AddItem rs2.Fields("topic").Value
rs2.MoveNext
Loop
lstcom.ToolTipText = "Click the topic to view the related information"

End Sub

Private Sub imgexit_Click()
frmintro.Show
Unload Me
End Sub

Private Sub imggal_Click()
frmgal.Show
Unload Me
End Sub

Private Sub imgnb_Click()
frmnb.Show
End Sub

Private Sub imgnet_Click()
frminet.Show
Unload Me
End Sub

Private Sub lstcom_GotFocus()
item = lstcom.Text
Dim rs3 As New ADODB.Recordset
rs3.CursorType = adOpenDynamic
rs3.LockType = adLockOptimistic
rs3.Open "Select des from elearn where topic=item", con, adOpenDynamic, adLockOptimistic
Set txtcontent.DataSource = rs3
txtcontent.DataField = des
End Sub

But when I run this app., I get a error saying that coloumn does not exist and highlights this part of the code:

rs3.Open "Select des from elearn where topic=item", con, adOpenDynamic, adLockOptimistic

So Please help me as soon as possible. It is very urgent!!!

is list box populated with the data Present in the datebase after running the Application ??????

tell me this first

yea the listbox lists all the items in the database.

Try this (just copy paste this line in place of existing one)
rs3.Open "Select des from elearn where topic='" & item & "'", con, adOpenDynamic, adLockOptimistic

The query in your code is actually looking for a field named item.

mod-the-pc said:
Try this (just copy paste this line in place of existing one)
rs3.Open "Select des from elearn where topic='" & item & "'", con, adOpenDynamic, adLockOptimistic

The query in your code is actually looking for a field named item.

Now the error is gone but when I click a topic the description is not being displayed in the textbox. Is there anything wrong with this part of the code:

Code:

Private Sub lstcom_GotFocus()
Item = lstcom.Text
Dim rs3 As New ADODB.Recordset
rs3.CursorType = adOpenDynamic
rs3.LockType = adLockOptimistic
rs3.Open "Select des from elearn where topic='" & Item & "'", con, adOpenDynamic, adLockOptimistic
Set txtcontent.DataSource = rs3
txtcontent.DataField = des
End Sub

(I tried giving it in the click event too but did not work.)

Write this
rs3.Open "Select des from elearn where topic='" & Item & "'", con, adOpenDynamic, adLockOptimistic

as

rs3.Open "Select des from elearn where UPPER(topic)=UPPER(TRIM('" & Item & "'")), con, adOpenDynamic, adLockOptimistic
textcontent.text = rs3!des

DukeNukem said:
Write this
rs3.Open "Select des from elearn where topic='" & Item & "'", con, adOpenDynamic, adLockOptimistic

as

rs3.Open "Select des from elearn where UPPER(topic)=UPPER(TRIM('" & Item & "'")), con, adOpenDynamic, adLockOptimistic
textcontent.text = rs3!des

I tried doing so
But then an error appears saying that either EOF or BOF is true or current record has been deleted.
It highlights this part of the code:

Code:

textcontent.text = rs3!des

It is error no.3021.
What is the problem now???

remove "textcontent.text = rs3!des" from code to see its effect
what it do is insert the record in the textbox "Simple"
or make the cursor dynamic

DukeNukem said:
remove "textcontent.text = rs3!des" from code to see its effect
what it do is insert the record in the textbox "Simple"
or make the cursor dynamic

But still I am unable to get the description of the topics in the textbox.
For that have I to add something to the code???
If I remove the code:

Code:

textcontent.text = rs3!des

then how will I be able to view the content in the textbox???

Check this code for errors (iam not having VB at home or at Office)
First Make a DSN for ORACLE

Then try This Code (Make Necessary Changes). This will Work For Sure
------------------------------------------------------------------------
Public item as String

Private Sub Form_Load()
set con = new connection
con.provider = "MSDAORA"
con.connectionSrting = "DSN=<DSN_NAME>;USER ID = <UID>;PASSWORD = <PWD>"
con.open
FillList
End Sub

Private Sub lstcom_GotFocus()
item = lstcom.Text
FillText
End Sub

Private Sub FillList()
set ress = new recordset
ress.activeconnection = con
ress.cursortype =adopendynamic
ress.cursorlocation = adopenserver
ress.open "Select topic from <tableName>"

Do While Not (ress.EOF)
lstcom.AddItem ress!Topic
rs2.MoveNext
loop
End Sub

Private Sub FillText()
set ress1 = new recordset
ress1.activeconnection = con
ress1.cursortype =adopendynamic
ress1.cursorlocation = adopenserver 'i think its wrong check for proper option
ress1.open "Select des from elearn where UPPER(topic)=UPPER(TRIM('" & Item & "'"))"
txtcontent.text = ress1!des

End Sub

DukeNukem said:
Check this code for errors (iam not having VB at home or at Office)
First Make a DSN for ORACLE

Then try This Code (Make Necessary Changes). This will Work For Sure
------------------------------------------------------------------------
Public item as String

Private Sub Form_Load()
set con = new connection
con.provider = "MSDAORA"
con.connectionSrting = "DSN=<DSN_NAME>;USER ID = <UID>;PASSWORD = <PWD>"
con.open
FillList
End Sub

Private Sub lstcom_GotFocus()
item = lstcom.Text
FillText
End Sub

Private Sub FillList()
set ress = new recordset
ress.activeconnection = con
ress.cursortype =adopendynamic
ress.cursorlocation = adopenserver
ress.open "Select topic from <tableName>"

Do While Not (ress.EOF)
lstcom.AddItem ress!Topic
rs2.MoveNext
loop
End Sub

Private Sub FillText()
set ress1 = new recordset
ress1.activeconnection = con
ress1.cursortype =adopendynamic
ress1.cursorlocation = adopenserver 'i think its wrong check for proper option
ress1.open "Select des from elearn where UPPER(topic)=UPPER(TRIM('" & Item & "'"))"
txtcontent.text = ress1!des

End Sub

But DNS is not in our syllabus and we are not allowed to make use of that!!!

try This Code (Make Necessary Changes). This will Work For Sure
------------------------------------------------------------------------
Public item as String
dim con as connection
dim ress as recordset
dim ress1 as recordset

Private Sub Form_Load()
set con = new connection
con.open "Provider=MSDAORA;user id=scott;password=tiger;"
FillList
End Sub

Private Sub lstcom_GotFocus()
item = lstcom.Text
FillText
End Sub

Private Sub FillList()
set ress = new recordset
ress.activeconnection = con
ress.cursortype =adopendynamic
ress.cursorlocation = adopenserver
ress.open "Select topic from <tableName>"

Do While Not (ress.EOF)
lstcom.AddItem ress!Topic
rs2.MoveNext
loop
End Sub

Private Sub FillText()
set ress1 = new recordset
ress1.activeconnection = con
ress1.cursortype =adopendynamic
ress1.cursorlocation = adopenserver 'i think its wrong check for proper option
ress1.open "Select des from elearn where UPPER(topic)=UPPER(TRIM('" & Item & "'"))"
txtcontent.text = ress1!des

End Sub

tried with the necessary changes but still the error is occuring

hey found out something
when i tried the same code with emp table there is no error. Is the error related with database structure???
My oracle D/B consists of only 2 fields:
topic
des

topic consists of the headings and des contains the decription of the topic.