Solved

query a view using vb

Posted on 2008-10-20
11
534 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

740 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