Problem while connecting Oracle and VB6!!!

Discussion in 'Software Q&A' started by deepak.krishnan, Oct 25, 2006.

Thread Status:
Not open for further replies.
  1. deepak.krishnan

    deepak.krishnan New Member

    Joined:
    Jan 11, 2005
    Messages:
    423
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    Palakkad/Bangalore
    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!!!
     
  2. DukeNukem

    DukeNukem Come get Some

    Joined:
    Aug 28, 2005
    Messages:
    417
    Likes Received:
    2
    Trophy Points:
    0
    Location:
    Home Sweet Home
    is list box populated with the data Present in the datebase after running the Application ??????

    tell me this first
     
  3. OP
    OP
    deepak.krishnan

    deepak.krishnan New Member

    Joined:
    Jan 11, 2005
    Messages:
    423
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    Palakkad/Bangalore
    yea the listbox lists all the items in the database.
     
  4. mod-the-pc

    mod-the-pc Back to School Mr. Bean !

    Joined:
    Apr 1, 2004
    Messages:
    344
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Chennai
    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.
     
  5. OP
    OP
    deepak.krishnan

    deepak.krishnan New Member

    Joined:
    Jan 11, 2005
    Messages:
    423
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    Palakkad/Bangalore
    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.)
     
  6. DukeNukem

    DukeNukem Come get Some

    Joined:
    Aug 28, 2005
    Messages:
    417
    Likes Received:
    2
    Trophy Points:
    0
    Location:
    Home Sweet Home
    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
     
  7. OP
    OP
    deepak.krishnan

    deepak.krishnan New Member

    Joined:
    Jan 11, 2005
    Messages:
    423
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    Palakkad/Bangalore
    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???
     
  8. DukeNukem

    DukeNukem Come get Some

    Joined:
    Aug 28, 2005
    Messages:
    417
    Likes Received:
    2
    Trophy Points:
    0
    Location:
    Home Sweet Home
    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
     
  9. OP
    OP
    deepak.krishnan

    deepak.krishnan New Member

    Joined:
    Jan 11, 2005
    Messages:
    423
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    Palakkad/Bangalore
    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???
     
  10. DukeNukem

    DukeNukem Come get Some

    Joined:
    Aug 28, 2005
    Messages:
    417
    Likes Received:
    2
    Trophy Points:
    0
    Location:
    Home Sweet Home
    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
     
  11. OP
    OP
    deepak.krishnan

    deepak.krishnan New Member

    Joined:
    Jan 11, 2005
    Messages:
    423
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    Palakkad/Bangalore
    But DNS is not in our syllabus and we are not allowed to make use of that!!!
     
  12. DukeNukem

    DukeNukem Come get Some

    Joined:
    Aug 28, 2005
    Messages:
    417
    Likes Received:
    2
    Trophy Points:
    0
    Location:
    Home Sweet Home
    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
     
  13. OP
    OP
    deepak.krishnan

    deepak.krishnan New Member

    Joined:
    Jan 11, 2005
    Messages:
    423
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    Palakkad/Bangalore
    tried with the necessary changes but still the error is occuring
     
  14. OP
    OP
    deepak.krishnan

    deepak.krishnan New Member

    Joined:
    Jan 11, 2005
    Messages:
    423
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    Palakkad/Bangalore
    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.
     
Thread Status:
Not open for further replies.

Share This Page