?
Solved

ORA-00904: "SURNAME": invalid identifier

Posted on 2010-01-11
27
Medium Priority
?
1,536 Views
Last Modified: 2013-12-19
Hi Experts,

Why am i geting this error the above error?

Surname is part of the data fied in the database

 if (txtSurname.Text == "")
            dsStudSurname.SelectCommand = "SELECT 'Course Reference' AS Course_Reference, 'Course Name' AS Course_Name, Surname" +
                                       "Forenames,  Subject_A, Percentage, Attempt, PassFail  FROM ASSESSMENTBOARDFINAL" +
                                       " WHERE ('Course Reference' = :ReportFilter) ";

        else

         dsStudSurname.SelectCommand = "SELECT 'Course Reference' AS Course_Reference, 'Course Name' AS Course_Name, Surname" +
                                       "Forenames,  Subject_A, Percentage, Attempt, PassFail  FROM ASSESSMENTBOARDFINAL"+
                                        " WHERE ('Course Reference' = :ReportFilter) AND Surname LIKE '" + txtSurname.Text + "%'  ";
0
Comment
Question by:SirReadAlot
[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
  • 11
  • 5
  • 4
  • +2
27 Comments
 
LVL 40

Expert Comment

by:Gurvinder Pal Singh
ID: 26283607
there is no space between the 'surname' and 'fornames' in the select clause
0
 
LVL 15

Accepted Solution

by:
Colosseo earned 500 total points
ID: 26283611
you need a comma after the surname..

you have

dsStudSurname.SelectCommand = "SELECT 'Course Reference' AS Course_Reference, 'Course Name' AS Course_Name, Surname" +

but you need

dsStudSurname.SelectCommand = "SELECT 'Course Reference' AS Course_Reference, 'Course Name' AS Course_Name, Surname," +

HTH

Scott

0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26283615
could you paste structure of table here?
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26283630
I don't think this is the reason for the error but I think you're missing a comma after surname.

Double check the database table and make sure surname is a column and is spelled correctly.

If you still have problem please post the table definition for: ASSESSMENTBOARDFINAL
0
 

Author Comment

by:SirReadAlot
ID: 26283676
I have made the changes as suggested
if (txtSurname.Text == "")
        dsStudSurname.SelectCommand = "SELECT 'Course Reference' AS Course_Reference, 'Course Name' AS Course_Name, Surname," +
                                       "Forenames,  Subject_A, Percentage, Attempt, PassFail  FROM ASSESSMENTBOARDFINAL" +
                                       " WHERE ('Course Reference' = :ReportFilter) ";

        else

            dsStudSurname.SelectCommand = "SELECT 'Course Reference' AS Course_Reference, 'Course Name' AS Course_Name, Surname," +
                                       "Forenames,  Subject_A, Percentage, Attempt, PassFail  FROM ASSESSMENTBOARDFINAL" +
                                        " WHERE ('Course Reference' = :ReportFilter) AND Surname LIKE '" + txtSurname.Text + "%'  ";

the error is still there................
I will chk hte table struc
0
 

Author Comment

by:SirReadAlot
ID: 26283685
"Course Reference"   VARCHAR2(20 BYTE),
  "Course Name"        VARCHAR2(255 BYTE),
  "Surname"            VARCHAR2(255 BYTE),
  "Forenames"          VARCHAR2(255 BYTE),
  "Student reference"  VARCHAR2(20 BYTE),
  "Student Id"         NUMBER(16),
  "Subject_M"          VARCHAR2(255 BYTE),
  "Subject_Code"       NUMBER(16)               NOT NULL,
  "Subject Reference"  VARCHAR2(20 BYTE),
  "Subject_A"          VARCHAR2(255 BYTE),
  "Percentage"         NUMBER,
  "Attempt"            NUMBER,
  "PassFail"           VARCHAR2(10 BYTE),
  "Assessment ID"      NUMBER(16)               NOT NULL,
  "EnrolmentId"        NUMBER(16)               NOT NULL,
  "LevelCode"          VARCHAR2(4 BYTE),
  "ID Number"          VARCHAR2(20 BYTE),
  "Enrol Start"        DATE,
  "Enrol End"          DATE
0
 
LVL 40

Expert Comment

by:Gurvinder Pal Singh
ID: 26283708
Is it still giving the same error?
Please post the complete stack trace
0
 

Author Comment

by:SirReadAlot
ID: 26283722
Stack Trace:

[OracleException (0x80131938): ORA-00904: "SURNAME": invalid identifier
]
   System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) +181
   System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) +2138
   System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals) +53
   System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior) +204
   System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior) +36
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +39
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +207
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +342
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +181
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +3326
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +95
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +217
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +125
   System.Web.UI.WebControls.GridView.DataBind() +28
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +104
   System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e) +43
   System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +79
   System.Web.UI.Control.PreRenderRecursiveInternal() +200
   System.Web.UI.Control.PreRenderRecursiveInternal() +322
   System.Web.UI.Control.PreRenderRecursiveInternal() +322
   System.Web.UI.Control.PreRenderRecursiveInternal() +322
   System.Web.UI.Control.PreRenderRecursiveInternal() +322
   System.Web.UI.Control.PreRenderRecursiveInternal() +322
   System.Web.UI.Control.PreRenderRecursiveInternal() +322
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4838

0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26283736
try this:

if (txtSurname.Text == "")
            dsStudSurname.SelectCommand = "SELECT 'Course Reference' AS Course_Reference, 'Course Name' AS Course_Name, Surname," +
                                       "Forenames,  Subject_A, Percentage, Attempt, PassFail  FROM ASSESSMENTBOARDFINAL" +
                                       " WHERE ('Course Reference' = :ReportFilter) ";

        else

         dsStudSurname.SelectCommand = "SELECT 'Course Reference' AS Course_Reference, 'Course Name' AS Course_Name, Surname," +
                                       "Forenames,  Subject_A, Percentage, Attempt, PassFail  FROM ASSESSMENTBOARDFINAL"+
                                        " WHERE ('Course Reference' = :ReportFilter) AND Surname LIKE '" + txtSurname.Text + "%'  ";

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26283739
It's possible to create mixed case columns/tables and columns with spaces but it is not recommended.

If the table names are actually mixed case you need to put double quotes around the column names.

select ... "Surname" ...
0
 
LVL 40

Assisted Solution

by:Gurvinder Pal Singh
Gurvinder Pal Singh earned 500 total points
ID: 26283754
and use the double quotes in the where clause and order by clause also

see this link
https://cs.senecac.on.ca/~nconkic/Chap1.txt
0
 

Author Comment

by:SirReadAlot
ID: 26283766
REZA_RAD i have tried your last post but still giving the same error

slightwv  the cols are not mixed
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 26283775
>>the cols are not mixed

Where did you get the output for the table definition above?

"Course Reference"   VARCHAR2(20 BYTE),
...
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 26283781
does it work when the txtsurname field is blank?
0
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 500 total points
ID: 26283813
did you rebuild your project ?
this seems that this new code doesn't compiled
0
 

Author Comment

by:SirReadAlot
ID: 26283848
OK,  i SEE MORE PROBLEMS

i have inserted quotes around the col names this has resolved the error. But no data is being pulled across when i search.... I know that data exists

 if (txtSurname.Text == "")
            dsStudSurname.SelectCommand = "SELECT 'Course Reference' AS Course_Reference, 'Course Name' AS Course_Name, 'Surname'," +
                                       " 'Forenames',  'Subject_A', 'Percentage', 'Attempt', 'PassFail'  FROM ASSESSMENTBOARDFINAL" +
                                       " WHERE ('Course Reference' = :ReportFilter) ";

        else

            dsStudSurname.SelectCommand = "SELECT 'Course Reference' AS Course_Reference, 'Course Name' AS Course_Name, 'Surname'," +
                                          "'Forenames',  'Subject_A', 'Percentage', 'Attempt', 'PassFail'  FROM ASSESSMENTBOARDFINAL" +
                                           " WHERE ('Course Reference' = :ReportFilter) AND 'Surname' LIKE '" + txtSurname.Text + "%'  ";
0
 
LVL 40

Expert Comment

by:Gurvinder Pal Singh
ID: 26283861
sorry, please remove the quotes in the where clause for the Surname
0
 

Author Comment

by:SirReadAlot
ID: 26283883
SAME ERROR
0
 

Author Comment

by:SirReadAlot
ID: 26283927
>>does it work when the txtsurname field is blank?

THE ERROR DISAPPEARS BUT NOT DATA IS RETURNED
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 26283993
how about removing the where clause entirely, does that return any data?
0
 

Author Comment

by:SirReadAlot
ID: 26284037
yes it does
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26284098
single quotes in Oracle is a string literal.  Double quotes is an object name.

ie./
select 'hello' from dual;
--returns the string 'hello'

select "hello" from dual is looking for a column named hello in lower case.
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 26284103
at least you know now that the error is in the where clause section of the code so try the surname where clause on its own without the 'Course Reference' clause
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26284120
Please connect to the database using SQL*Plus and post the output of:  desc  assesmentboardfinal;
0
 

Author Comment

by:SirReadAlot
ID: 26284220
just re testing it
0
 

Author Comment

by:SirReadAlot
ID: 26291207
sorry guys... the database was update while we were trying to correct things. I have now refreshed oracle and the code seems to work. I will award grades asap.

thanks
0
 

Author Closing Comment

by:SirReadAlot
ID: 31675498
thanks
0

Featured Post

Independent Software Vendors: 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

When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses

801 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