Click here to Skip to main content
15,886,067 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Table Structure in Oracle
SQL
CREATE TABLE ENDO_IMAGES
  (
    EM_IMAGEID NUMBER NOT NULL ENABLE,
    EM_ID      NUMBER NOT NULL ENABLE,
    EM_IMAGE BLOB NOT NULL ENABLE
  ) ;


XML
<asp:FileUpload ID="fu" runat="server" />
                <br />
        <asp:Button ID="btnsave" runat="server" Text="save" onclick="btnsave_Click" /><br />


C#
protected void btnsave_Click(object sender, EventArgs e)
{
   if (fu.PostedFile != null)
   {
      Save();
   }
}

private void Save() 
{
   FileStream fls;
   fls = new FileStream(Server.MapPath("~/Images/" + fu.FileName), FileMode.Open, FileAccess.Read);
   //a byte array to read the image 
   byte[] blob = new byte[fls.Length];
   fls.Read(blob, 0, System.Convert.ToInt32(fls.Length));
   fls.Close(); 

   string _connectionstring = @"data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=202.202.0.1)(PORT=1521)))(CONNECT_DATA=(SID=hims)(SERVER=DEDICATED)));user id=ehms;password=ehms";
   OracleConnection orcl = new OracleConnection(_connectionstring);
   orcl.Open();

   OracleCommand cmd = orcl.CreateCommand();

   OracleParameter ID = new OracleParameter("EM_IMAGEID", OracleType.Number);
   ID.Value = 1;
   ID.Direction = ParameterDirection.Input;
   cmd.Parameters.Add(ID);

   OracleParameter EMID = new OracleParameter("EM_ID", OracleType.Number);
   EMID.Value = 1;
   EMID.Direction = ParameterDirection.Input;
   cmd.Parameters.Add(EMID);

   OracleParameter paramImage = new OracleParameter("EM_IMAGE", OracleType.Blob);
   paramImage.Value = blob;
   paramImage.Direction = ParameterDirection.Input;
   cmd.Parameters.Add(paramImage);

   string sqlStmt = "insert into endo_images(em_imageid,em_id,em_image) values("+ID+","+EMID+","+paramImage+")";
   cmd.CommandText = sqlStmt;
   cmd.CommandType = CommandType.Text;
   cmd.ExecuteNonQuery();
   orcl.Close();
}

Error Occured
ORA-01036: illegal variable name/number
How can i solve this error?

[edit]SHOUTING removed - OriginalGriff[/edit]
Posted
Updated 9-Sep-14 22:29pm
v4
Comments
Herman<T>.Instance 4-Sep-14 6:00am    
Where does the error occur? Did you debug? Can you pinpoint more specific?
aftab5124 10-Sep-14 1:27am    
cmd.ExecuteNonQuery();
Error Occured here...
OriginalGriff 4-Sep-14 7:20am    
DON'T SHOUT. Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalization if you want to be taken seriously.
V. 5-Sep-14 4:41am    
I'm confused,you add the values as a paramtrized query, but the query itself does not contain the param names, rather the concatenated values. (oh and BTW SQL injection ?) :-)

http://www.aspsnippets.com/Articles/Exception---ORA-01036-illegal-variable-namenumber.aspx

1 solution

1. Page Design
XML
<asp:FileUpload ID="fu" runat="server" />
        <input type="file" id="fu1" runat="server" />
        <br />
        <asp:Button ID="btnsave" runat="server" Text="save" onclick="btnsave_Click" /><br />
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataKeyNames="em_imageid">
            <Columns>
                <asp:TemplateField HeaderText="Picture">
                    <ItemTemplate>
                        <asp:Image ID="imgPhoto" runat="server" ImageUrl = '<%# "ShowImage.ashx?em_imageid=" + Eval("em_imageid")%>' Height="100px" Width="100px"/>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>


2. Code Behind:
C#
protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable dt = new DataTable();
            string _connectionstring = @"data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=hims)(SERVER=DEDICATED)));user id=system;password=admin";
            OracleConnection orcl = new OracleConnection(_connectionstring);
            orcl.Open();
            OracleCommand cmd = orcl.CreateCommand();
            cmd.Parameters.Add(new OracleParameter("pDec", OracleType.Number)).Value = 0;
            cmd.Parameters.Add(new OracleParameter("pImageID", OracleType.Number)).Value = 0;
            cmd.Parameters.Add(new OracleParameter("pData", OracleType.Cursor)).Direction = ParameterDirection.Output;
            cmd.CommandText = "GET_ENDO_IMAGE";
            cmd.CommandType = CommandType.StoredProcedure;
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            da.Fill(dt);
            orcl.Close();
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
    }
    
    protected void btnsave_Click(object sender, EventArgs e)
    {
        if (fu.PostedFile != null) 
        {
            int imgLength = 0;
            string imgContentType = null;
            string imgFileName = null;
            Stream imgStream = fu.PostedFile.InputStream;
            imgLength = fu.PostedFile.ContentLength;
            imgContentType = fu.PostedFile.ContentType;
            imgFileName = fu.PostedFile.FileName;
            if (imgContentType == "image/jpeg" || imgContentType == "image/gif" || imgContentType == "image/pjpeg" || imgContentType == "image/bmp")
            {
                Byte[] ImageContent = new byte[imgLength]; 
                int intStatus; 
                intStatus = imgStream.Read(ImageContent, 0, imgLength);
                string _connectionstring = @"data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=hims)(SERVER=DEDICATED)));user id=system;password=admin";
                OracleConnection orcl = new OracleConnection(_connectionstring);
                orcl.Open();
                OracleCommand cmd = orcl.CreateCommand();
                cmd.Transaction = cmd.Connection.BeginTransaction(); 
                cmd.CommandText = "INSERT_ENDO_IMAGE";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new OracleParameter("pDec", OracleType.Number,9)).Value = 0;
                cmd.Parameters.Add(new OracleParameter("pEmID", OracleType.Number,9)).Value = 1;
                cmd.Parameters.Add(new OracleParameter("pEmImage", OracleType.Blob, ImageContent.Length)).Value = ImageContent;
                cmd.ExecuteNonQuery();
                cmd.Transaction.Commit(); 
                orcl.Close();
            }
        }
    }

3. Add Generic Handler:
C#
public void ProcessRequest (HttpContext context)
{
    string em_imageid;
    if (context.Request.QueryString["em_imageid"] != null)
    {
        em_imageid = context.Request.QueryString["em_imageid"].ToString();

        string _connectionstring = @"data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=hims)(SERVER=DEDICATED)));user id=system;password=admin";
        OracleConnection orcl = new OracleConnection(_connectionstring);
        orcl.Open();
        string sql = "select * from endo_images where em_imageid=" + Convert.ToInt32(em_imageid);
        OracleCommand com =orcl.CreateCommand();

        com.CommandText = sql;
        com.CommandType = CommandType.Text;
        OracleDataReader img = com.ExecuteReader();
        try
        {
            img.Read();
            context.Response.BinaryWrite((byte[])img[img.GetOrdinal("em_image")]);
            img.Close();
        }
        catch { }
        finally { orcl.Close(); }
    }
}

4. Oracle table Stucture & sp:
SQL
CREATE TABLE "BPKMCH"."ENDO_IMAGES"
  (
    "EM_IMAGEID" NUMBER NOT NULL ENABLE,
    "EM_ID"      NUMBER NOT NULL ENABLE,
    "EM_IMAGE" BLOB
  )

create or replace
SQL
procedure GET_ENDO_IMAGE
(
  pDec IN NUMBER:=0,
  pImageID IN endo_images.em_imageid%TYPE:=0,
  pEmID IN endo_images.em_id%TYPE:=0,
  pData OUT sys_refcursor
)
AS 
BEGIN
  if(pDec=0) then
    open pData for select  *from endo_images
      where (em_imageid=pImageID or pImageID=0)
        and (em_id=pEmID or pEmID=0);
  end if;
END;
create or replace
procedure INSERT_ENDO_IMAGE
(
  pDec IN NUMBER:=0,
  pImageID IN endo_images.em_imageid%TYPE:=0,
  pEmID IN endo_images.em_id%TYPE:=0,
  pEmImage IN endo_images.em_image%TYPE:=NULL
)
AS 
tmpID NUMBER;
tmpBLOB BLOB;
BEGIN
  if(pDec=0) then
    select nvl(max(em_imageid),0)+1 into tmpID from endo_images;
    insert into endo_images(em_imageid,em_id,em_image) values(tmpID,pEmID,pEmImage);
  end if;
  if(pDec=1) then
    update endo_images set em_image=pEmImage where em_imageid=pImageID;
  end if;
  commit;
END;

this way save & display image in Oracle
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900