ORA-00904: "SURNAME": invalid identifier

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 + "%'  ";
SirReadAlotAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gurvinder Pal SinghCommented:
there is no space between the 'surname' and 'fornames' in the select clause
0
ColosseoCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Reza RadConsultant, TrainerCommented:
could you paste structure of table here?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
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
SirReadAlotAuthor Commented:
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
SirReadAlotAuthor Commented:
"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
Gurvinder Pal SinghCommented:
Is it still giving the same error?
Please post the complete stack trace
0
SirReadAlotAuthor Commented:
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
Reza RadConsultant, TrainerCommented:
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
slightwv (䄆 Netminder) Commented:
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
Gurvinder Pal SinghCommented:
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
SirReadAlotAuthor Commented:
REZA_RAD i have tried your last post but still giving the same error

slightwv  the cols are not mixed
0
slightwv (䄆 Netminder) Commented:
>>the cols are not mixed

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

"Course Reference"   VARCHAR2(20 BYTE),
...
0
ColosseoCommented:
does it work when the txtsurname field is blank?
0
Reza RadConsultant, TrainerCommented:
did you rebuild your project ?
this seems that this new code doesn't compiled
0
SirReadAlotAuthor Commented:
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
Gurvinder Pal SinghCommented:
sorry, please remove the quotes in the where clause for the Surname
0
SirReadAlotAuthor Commented:
SAME ERROR
0
SirReadAlotAuthor Commented:
>>does it work when the txtsurname field is blank?

THE ERROR DISAPPEARS BUT NOT DATA IS RETURNED
0
ColosseoCommented:
how about removing the where clause entirely, does that return any data?
0
SirReadAlotAuthor Commented:
yes it does
0
slightwv (䄆 Netminder) Commented:
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
ColosseoCommented:
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
slightwv (䄆 Netminder) Commented:
Please connect to the database using SQL*Plus and post the output of:  desc  assesmentboardfinal;
0
SirReadAlotAuthor Commented:
just re testing it
0
SirReadAlotAuthor Commented:
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
SirReadAlotAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.