Solved

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

Posted on 2011-02-16
15
1,149 Views
Last Modified: 2012-05-11
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
Comment
Question by:Sky4
  • 8
  • 5
  • 2
15 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
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
 

Author Comment

by:Sky4
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
we would need to see more of the relevant .net code ...
0
 

Author Comment

by:Sky4
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 

Author Comment

by:Sky4
Comment Utility
Now recieving Object reference not set to an instance of an object
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Sky4
Comment Utility
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
 

Author Comment

by:Sky4
Comment Utility
am sorry , am I missing some thing with your last post

now I get
ORA-00911:
      invalid character
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
hmm, please remove the space after :percentage in the 2 lines ...
0
 

Author Comment

by:Sky4
Comment Utility
Done that, still getting

ORA-00911: invalid character
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Colons should work.  What data provider are you using?
0
 

Author Comment

by:Sky4
Comment Utility
using
// ODP.NET Import(s)
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
0
 

Author Comment

by:Sky4
Comment Utility
although I am no longer receiving the errors, everytime I try to update a record it reverts back to the original
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Default parameter problem in C# 3 25
Migrate database to ASM disks. 1 24
sql query 5 51
Problem to open Excel file 15 37
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now