Solved

query a view using vb

Posted on 2008-10-20
11
528 Views
Last Modified: 2013-12-18
llo

i have an  oracle  database that has a view that i need to query in my vb application can anyone help
0
Comment
Question by:Chris Jones
  • 7
  • 4
11 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22757850
querying a view is the same as querying a table.
assuming that you did already query some tables with vb, there should be no problem?
otherwise, what is the problem, exactly?
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 22757984
i get this error in my vb code

 character string buffer too small
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22758231
what is the view/vb code, what are the data types, what is the connection string ...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:Chris Jones
ID: 22758294

there are a lot of types in my view but i am trying to query a field called PIDM_KEY


here si my vb code
Partial Class _Default
    Inherits System.Web.UI.Page
    Dim rs As ADODB.Recordset ' recorde set for the connection 
    Dim sqlstmt As String
    Dim ConnPortal As ADODB.Connection
    Private Sub localDBConnectionOpen()
        ConnPortal = DBConnectionOpenOracle(ConnPortal, "PROD.TAMU-COMMERCE.EDU", "joneschris", "cjones_1")
    End Sub
 
    Private Sub localDBConnectionClose()
        ConnPortal = DBConnectionClose(ConnPortal)
    End Sub
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 
    End Sub
 
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim PIDM As String
        Call localDBConnectionOpen()
 
        sqlstmt = "SELECT * FROM BANINST1.AS_STUDENT_ENROLLMENT_SUMMARY WHERE PIDM_KEY = '" & txtCWID.Text & "'"
        rs = DBRecordSetNewFast(ConnPortal, sqlstmt)
 
        While Not rs.EOF
 
            PIDM = DBGetField(rs, "CNAME")
            '   PNAME = DBGetField(rs, "PNAME")
 
            rs.MoveNext()
        End While
        '   PageBody.Controls.Add(t)
        rs = DBRecordSetDestroy(rs)
        Call localDBConnectionClose()
    End Sub
End Class

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22758376
please try to limit the problem, by putting a list of columns instead of SELECT *.

now, anything "large" or "special" in those columns returned?
timestamp, blob, clob, ... ?
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 22758443
pidm_key,
 term_code_key,
 enrolled_ind,
 registered_ind,
 id,
 ssn,
 last_name,
 first_name,
 middle_initial,
 name_prefix,
 name_suffix,
 birth_date,
 age,
 gender,


is that what you mean ?
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 22758455
buta all i am trying to get is the first field the PIDM_KEY
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 22758502
i am not sure if this will help here is my full error

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "BANINST1.G$_DATE_PKG", line 628
ORA-06512: at "BANINST1.GOKSELS", line 660
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22758532
>but all i am trying to get is the first field the PIDM_KEY
you do a WHERE against that field, but you return all the fields, at least right now..

>ORA-06502: PL/SQL: numeric or value error: character string buffer too small
what is the data type of PIDM_KEY?
is it numerical?
        sqlstmt = "SELECT * FROM BANINST1.AS_STUDENT_ENROLLMENT_SUMMARY WHERE PIDM_KEY = " & txtCWID.Text & " "

Open in new window

0
 
LVL 1

Author Comment

by:Chris Jones
ID: 22759887
ok i fixed the problem i just did not select all of the fields i dont need all the fields and it worked

thanks
0
 
LVL 1

Author Closing Comment

by:Chris Jones
ID: 31618367
great expert
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question