INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

InValid Column Name

InValid Column Name

(OP)
Hi
I Have a table named schedule that holds all the orders shipped and not shipped, Also I have a view Named Schedule_view Puling all the orders from Schedule that have not been Shipped and both the table and the View have a Column Named RevisionNo this in CPP 12.
In My Application When I Pull all the orders from the View I Get this error
'ERROR [HY000] [PSQL][ODBC Client Interface][LNA][PSQL][SQL Engine]Error in expression: View1Tab1 . RevisionNO "
"ERROR [42S22] [PSQL][ODBC Client Interface][LNA][PSQL][SQL Engine]Invalid column name: 'RevisionNO'.
But when pulling from the Table I get No Error.
Why is this So.
Thanks


RE: InValid Column Name

(OP)
Thanks Mirtheil for your reply
See the atachment for the fields that Consistes the View, i didn't post it here because it is Huge.
This is the query I Used to create the view.
CREATE VIEW "SCHEDULE_View" AS
Select * From SCHEDULE
WHERE REQUIRED<>'00000000'
AND BVRES_00 = ''
AND WHSE=01
AND (SHIPPED = 0 OR SHIPPED IS NULL)
AND (shpreqComp=0 OR shpreqComp IS NULL)

RE: InValid Column Name

What happens if you run the Select part of the view by itself. Does that work?
If you create the following view, can you access the RevisionNo field?

CODE --> SQL

Create View ViewTest as
Select RevisionNo From SCHEDULE
WHERE REQUIRED<>'00000000'
AND BVRES_00 = ''
AND WHSE='01'
AND (SHIPPED = 0 OR SHIPPED IS NULL) 
AND (shpreqComp=0 OR shpreqComp IS NULL) 

What tool are you using to execute the queries? If you're not using the Pervasive COntrol Center (PCC), please try that tool.

Mirtheil
http://www.mirtheil.com

RE: InValid Column Name

(OP)
When I run The Query on Both PCC And also tried on Flyspeed SQL and the column Shows.
If I run the query from the Appliction the column does not show. I also tried renaming the column on the table , the same thing happens.

RE: InValid Column Name

So, the view works correctly in the PCC and Flyspeed SQL?
If the view is working outside of the application (in PCC and Flyspeed SQL), it seems that the application might be the issue.
What's the application where it fails? If it's something you're writing, please post the code that sets up the query and runs it.

Mirtheil
http://www.mirtheil.com

RE: InValid Column Name

(OP)
If I Use SelectString the same query used to create the view the column does not show.
If Selectstrin2 is used, I get the error that was originaly posted and it Fails at dr=.ExecuteReader

CODE -->

Private Sub Read_Data()
        Dim wbcon As New OdbcConnection
        Dim cmd As New OdbcCommand
        Dim dr As OdbcDataReader
        Dim dt As New DataTable
        Dim SelectString As String = "Select * From SCHEDULE WHERE REQUIRED<>'00000000' AND BVRES_00 = '' AND WHSE=01 AND (SHIPPED = 0 OR SHIPPED IS NULL) AND (shpreqComp=0 OR shpreqComp IS NULL)"
        Dim SelectString2 As String = "Select SCHEDULE_View.* From  SCHEDULELOG.SCHEDULE_View"
        Try
            wbcon = Main.CW_Live_WB
            wbcon.Open()
            cmd = New OdbcCommand
            With cmd
                .CommandType = CommandType.Text
                .CommandText = SelectString2
                .Connection = wbcon
                dr = .ExecuteReader
                dt.Load(dr)
            End With
            dv_BVProgram = New DataView(dt)
        Catch ex As Exception
            MsgBox("Programming.Read_Data " & ex.Message)
        End Try
        wbcon.Close()
    End Sub 

RE: InValid Column Name

Does it still work if you use:

CODE --> vb.net

Dim SelectString2 As String = "Select * From  SCHEDULE_View" 
By specifying "SCHEDULELOG" on the query, you are changing the database being used. Is it possible that you have two databases and one doesn't have the field you are looking for?

On another note, if your view is a "select *", you won't gain any performance using the view. If the standard SELECT works, that might be a better option.

Mirtheil
http://www.mirtheil.com

RE: InValid Column Name

(OP)
I specified "SCHEDULELOG" on the query because I have two databases in the same Engine.
Instead of creating two Connection strings I was refering the Database by Spesifying it on the query.
There is only one SCHEDULELOG Database.
But by reading your last comment I created a new DSN for each DataBase and created a Connection String for each,and removed the database name from the Query and that fixed the Problem.
Just Curious now I dont understand what you meant by not gaining any performance using the view, can you elaberate.

RE: InValid Column Name

Glad it's working.
A "View" is just a stored select statement. When running in the engine, whether you call a View or a Select, the engine runs the same code. The view isn't pre-compiled or cached. Also, a view usually limits the columns to "hide" non-necessary columns.
For example, if you run the following:

CODE --> sql

create table t1 (f1 int, f2 char(10), f3 char(10), f4 int);
insert into t1 values (1,'test value', '1',1);
insert into t1 values (2,'2', '2',2);
create view v1 as 
select f1, f4 from t1 where f2 = 'test value';
select * from t1;
select * from v1; 

You'll get this:

CODE

select * from t1
         f1   f2           f3                    f4
===========   ==========   ==========   ===========
          1   test value   1                      1
          2   2            2                      2

select * from v1
         f1            f4
===========   ===========
          1             1 

Mirtheil
http://www.mirtheil.com

RE: InValid Column Name

(OP)
Thanks For Demostration.
The Schedule Table holds all the Orders.
While the view only holds the Orders ready for production but not shipped.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close