• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1673
  • Last Modified:

ORA-01747: invalid user.table.column, table.column, or column specification

Hi Experts,

I understand in .Net you need to add a colon infront of the attributes when writing a query string, i.e

string sql = "UPDATE HE_STUDENT_GRADES_II_TEMP SET :A_PERCENTAGE='" + percentage + "' Where :A_ID=" + aid;

if so how do I fix the above error? I am trying to update data from a gridview into oracle

thanks
0
Sky4
Asked:
Sky4
  • 8
  • 5
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot specify a column name as variable, only the values...

string sql = "UPDATE HE_STUDENT_GRADES_II_TEMP SET A_PERCENTAGE = :percentage Where A_ID = :aid "

but after that, you need to bind the variables to your grid columns...
0
 
Sky4Author Commented:
okay, seems to have progressed a bit.

now I get
ORA-01008: not all variables bound.

the datagrid has about 7 cells but I am only interested in updating one cell. should I include the remaining 6?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
we would need to see more of the relevant .net code ...
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Sky4Author Commented:
hi,

this are the codes
===========update method================ 
public void Update(string aid, string percentage)
    {
        try
        {
            string sql = "UPDATE HE_STUDENT_GRADES_II_TEMP SET A_PERCENTAGE = :percentage Where A_ID = :aid ";
            OracleConnection conn = new OracleConnection(cnstr);
            conn.Open();
            OracleCommand cmd = new OracleCommand(sql, conn);
            cmd.Parameters.Add(new OracleParameter(":percentage ", OracleDbType.NVarchar2));
            cmd.Parameters[":percentage "].Value = percentage;
            cmd.Parameters.Add(new OracleParameter(":aid", OracleDbType.NVarchar2));
            cmd.Parameters[":aid"].Value = aid;
            cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

asp.net page
==================
 <asp:GridView ID="gvSubDetails" AutoGenerateColumns="False" runat="server" Width="735px"
            OnRowCancelingEdit="gvSubDetails_RowCancelingEdit" OnRowEditing="gvSubDetails_RowEditing" DataKeyNames="A_ID"
            OnRowUpdating="gvSubDetails_RowUpdating">
            <AlternatingRowStyle CssClass="altrowstyle" />
            <HeaderStyle CssClass="headerstyle" />
            <RowStyle CssClass="rowstyle" />
            <Columns>                
                   <asp:TemplateField HeaderText="%" SortExpression="A_PERCENTAGE">
                    <EditItemTemplate>
                            <asp:TextBox ID="txtA_PERCENTAGE" runat="server" Text='<%# Bind("A_PERCENTAGE") %>'></asp:TextBox>
                    </EditItemTemplate>             
                    <ItemTemplate>
                        <asp:Label ID="LblA_PERCEnt" runat="server" Text='<%# Bind("A_PERCENTAGE") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField> 
                <asp:TemplateField HeaderText="Edit" ShowHeader="False">
                    <EditItemTemplate>
                        <asp:LinkButton ID="lbnUpdate" runat="server" CausesValidation="True" CommandName="Update"
                            Text="Update"></asp:LinkButton>
                        <asp:LinkButton ID="lbnCancel" runat="server" CausesValidation="False" CommandName="Cancel"
                            Text="Cancel"></asp:LinkButton>
                    </EditItemTemplate>
                      <ItemTemplate>
                        <asp:LinkButton ID="lbnEdit" runat="server" CausesValidation="False" CommandName="Edit"
                            Text="Edit"></asp:LinkButton>
                    </ItemTemplate>
                </asp:TemplateField>           
            </Columns>
            <EmptyDataTemplate>
                No Data is available
            </EmptyDataTemplate>
        </asp:GridView>    




code behind
============================
 customer.Update(gvSubDetails.DataKeys[e.RowIndex].Values[0].ToString(), txtA_PERCENTAGE.Text);

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this, according to:
http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleparameter.aspx
public void Update(string aid, string percentage)
    {
        try
        {
            string sql = "UPDATE HE_STUDENT_GRADES_II_TEMP SET A_PERCENTAGE = ? Where A_ID = ? ";
            OracleConnection conn = new OracleConnection(cnstr);
            conn.Open();
            OracleCommand cmd = new OracleCommand(sql, conn);
            cmd.Parameters.Add(new OracleParameter("@percentage ", OracleDbType.NVarchar2));
            cmd.Parameters[":percentage "].Value = percentage;
            cmd.Parameters.Add(new OracleParameter("@aid", OracleDbType.NVarchar2));
            cmd.Parameters[":aid"].Value = aid;
            cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Open in new window

0
 
Sky4Author Commented:
Now recieving Object reference not set to an instance of an object
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
indeed...
public void Update(string aid, string percentage)
    {
        try
        {
            string sql = "UPDATE HE_STUDENT_GRADES_II_TEMP SET A_PERCENTAGE = ? Where A_ID = ? ";
            OracleConnection conn = new OracleConnection(cnstr);
            conn.Open();
            OracleCommand cmd = new OracleCommand(sql, conn);
            cmd.Parameters.Add(new OracleParameter("@percentage ", OracleDbType.NVarchar2));
            cmd.Parameters["@percentage "].Value = percentage;
            cmd.Parameters.Add(new OracleParameter("@aid", OracleDbType.NVarchar2));
            cmd.Parameters["@aid"].Value = aid;
            cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Open in new window

0
 
Sky4Author Commented:
a bit confused

A_PERCENTAGE & A_ID are the column names in the table.

so is this block right

  cmd.Parameters.Add(new OracleParameter(":percentage ", OracleDbType.NVarchar2));
            cmd.Parameters[":percentage "].Value = percentage;
            cmd.Parameters.Add(new OracleParameter(":aid", OracleDbType.NVarchar2));
            cmd.Parameters[":aid"].Value = aid;
0
 
Sky4Author Commented:
am sorry , am I missing some thing with your last post

now I get
ORA-00911:
      invalid character
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hmm, please remove the space after :percentage in the 2 lines ...
0
 
Sky4Author Commented:
Done that, still getting

ORA-00911: invalid character
0
 
slightwv (䄆 Netminder) Commented:
Colons should work.  What data provider are you using?
0
 
Sky4Author Commented:
using
// ODP.NET Import(s)
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
0
 
Sky4Author Commented:
although I am no longer receiving the errors, everytime I try to update a record it reverts back to the original
0
 
slightwv (䄆 Netminder) Commented:
Below is a quick console app I created and successfully ran against the following table:

drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));

insert into tab1 values('a','a');
insert into tab1 values('b','b');
commit;

using System;
using System.Data;

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

public class Bob
{

	public static void Main(string[] args)
	{


			OracleConnection con = new OracleConnection();

			con = new OracleConnection("User Id=bud;Password=bud;Data Source=bud");

			OracleCommand cmd = new OracleCommand();
			cmd.Connection = con;
			cmd.CommandText = " update tab1 set col2=:myval where col1=:whichrow ";

			cmd.Parameters.Add("myval", OracleDbType.Char);
			cmd.Parameters.Add("whichrow", OracleDbType.Char);

            cmd.Parameters["whichrow"].Value = "a";
            cmd.Parameters["myval"].Value = "z";

			con.Open();

			cmd.ExecuteNonQuery();

			con.Close();
			con.Dispose();

	}

}

Open in new window

0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 8
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now