Solved

query a view using vb

Posted on 2008-10-20
11
525 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access query that references subform 5 35
vb6 connector to mongodb 2 26
Clone Oracle 12c Database 5 26
SQL Retrieve Values 4 42
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now