Solved

query a view using vb

Posted on 2008-10-20
11
536 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 143

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 143

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 143

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 143

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

734 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