Problem while connecting Oracle and VB6!!!

[deepak.krishnan]

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!!!

 

[DukeNukem]

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

tell me this first

 

[deepak.krishnan]

yea the listbox lists all the items in the database.

 

[mod-the-pc]

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.

 

[deepak.krishnan]

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:

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.)

 

[DukeNukem]

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

 

[deepak.krishnan]

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:

textcontent.text = rs3!des

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

 

[DukeNukem]

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

 

[deepak.krishnan]

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:

textcontent.text = rs3!des

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

 

[DukeNukem]

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

 

[deepak.krishnan]

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!!!

 

[DukeNukem]

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

 

[deepak.krishnan]

tried with the necessary changes but still the error is occuring

 

[deepak.krishnan]

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.