Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ORA-00904: "SURNAME": invalid identifier

Posted on 2010-01-11
27
Medium Priority
?
1,600 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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 78

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 78

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 78

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 78

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 78

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

581 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