Query has some issues

Question

User-1506965535 posted

When I m writing this query in the code on button click to insert the mkey in the xxacl_pn_new_cha_part_h table
it gives me error as "ORA-00904: "A": invalid identifier"

Here is my code;-

try
        {
            conn.Open();
            OracleDataReader sdr = cmd.ExecuteReader();
            while (sdr.Read())
            {
                cmd.CommandText = "insert into xxacl_pn_new_cha_part_h select sysdate, a.* from xxacl_pn_new_cha_part where mkey= " + sdr[0].ToString();  // this query gives error
                cmd.ExecuteNonQuery();
                strQuery = "UPDATE xxacl_pn_new_cha_part set Rating='" + sdr[2].ToString() + "', RATING_UPDATE_DATE=convert(datetime,'" + System.DateTime.Now.ToString() + "',103) WHERE mkey = " + sdr[0].ToString();
                cmd.CommandText = strQuery;
                cmd.ExecuteNonQuery();
            }

            ScriptManager.RegisterStartupScript(this, GetType(), "alertMessage", "alert('Broker rating updated succesfully');", true);

        }

I am using Oracle. Please suggest what is wrong

Answer 1

User753101303 posted

Hi,

You are using a.* but you have no table or alias named "a" declared in your statement. What if using * rather than a.*

Not directly related but a good practice is to provide explicitely a field list

Answer 2

User603616845 posted

Hi,

This error comes when You tried to execute a SQL statement that included an invalid column name or the column name is missing. This commonly occurs when you reference an invalid alias in a SELECT statement.

ORA-00904 string: invalid identifier

Cause: The column name entered is either missing or invalid.

Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

http://www.techonthenet.com/oracle/errors/ora00904.php

https://community.oracle.com/thread/2206152

Hope this will help you.

thanks