sqldatasource insert into oracle

  1. #1

    sqldatasource insert into oracle

    Hi,

    I have a problem which has puzzled me for a few days. I have a billinadd.ascx to insert, delete or update data in a table. However when i tried to insert a new record it popped up the error message of "System.Exception: ORA-01036, illegal variable/number". My database is oracle 11g and I have installed Oracle 11g ODAC and Oracle Developer Tools for Visual Studio,which includes ODP.

    Here are the codes:

    BillInAdd.ascx

    <%@ Control Language="C#" AutoEventWireup="true" CodeFile="BillInAdd.ascx.cs" Inherits="BillInAdd" %>
    <%@ Register Assembly="Coolite.Ext.Web" Namespace="Coolite.Ext.Web" TagPrefix="ext" %>
    
        <%--datasource--%>
        <asp:SqlDataSource ID ="SqlDataSource2" runat ="server"
         ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" 
         ProviderName="<%$ ConnectionStrings:ConnectionString2.ProviderName %>"
         
         SelectCommand="SELECT * FROM BILL_IN_DTT WHERE  (BILL_IN_NO = :BILL_IN_NO3)"
         
         DeleteCommand="DELETE FROM BILL_IN_DTT WHERE WHERE (BILL_IN_DT_ID=:BILL_IN_DT_ID)"
         
         InsertCommand="INSERT INTO BILL_IN_DTT(BILL_IN_DT_ID, BILL_IN_NO,MATE_ID,PLAN_IN_NUM) 
     VALUES (
     :BILL_IN_DT_ID
     :BILL_IN_NO,
     :MATE_ID,
     :PLAN_IN_NUM
     )"
                            
         UpdateCommand="UPDATE BILL_IN_DTT SET
                        BILL_IN_NO=:BILL_IN_NO,
                        MATE_ID=:MATE_ID,
                        PLAN_IN_NUM=:PLAN_IN_NUM,
                        LOT_NO=:LOT_NO,
                        MAKE_DATE=:MAKE_DATE,
                        REG_NUM=:REG_NUM,
                        MEMO=:MEMO,
                        BILL_IN_DT_STATUS=:BILL_IN_DT_STATUS,
                        UP_NUM=:UP_NUM,
                        CREA_PER_NO=:CREA_PER_NO,
                        CREA_PER_NAME=:CREA_PER_NAME,
                        CREA_TIME=:CREA_TIME,
                        JINDU=:JINDU
          WHERE (BILL_IN_DT_ID=:BILL_IN_DT_ID) "            
          
         >
    
                            
             <DeleteParameters>
                <asp:Parameter Name="BILL_IN_DT_ID" Type="Int32" />
             </DeleteParameters>
            
             <SelectParameters>
                <asp:Parameter Name="BILL_IN_NO3" Type="String"  />              
             </SelectParameters>
                            
             <InsertParameters> 
                        <asp:Parameter Name="BILL_IN_DT_ID"   Type="String"  />
                        <asp:Parameter Name="BILL_IN_NO"  Type="String" />
                        <asp:Parameter Name="MATE_ID" Type="Int32" />
                        <asp:Parameter Name="PLAN_IN_NUM" Type="Int32" />
                        <asp:Parameter Name="LOT_NO" Type="String" />
                        <asp:Parameter Name="MAKE_DATE"  Type ="DateTime" />
                        <asp:Parameter Name="REG_NUM" Type="String" />
                        <asp:Parameter Name="MEMO" Type="String" />
                        <asp:Parameter Name="BILL_IN_DT_STATUS" Type="String"  />
                        <asp:Parameter Name="UP_NUM"  Type="String" />
                        <asp:Parameter Name="CREA_PER_NO"  Type="String" />
                        <asp:Parameter Name="CREA_PER_NAME"  Type="String" />
                        <asp:Parameter Name="CREA_TIME" Type ="DateTime" />
                        <asp:Parameter Name="JINDU"  Type="String" />
                        
            </InsertParameters>
            
            <UpdateParameters>
                        <asp:Parameter Name="BILL_IN_DT_ID"   Type="String"  />
                        <asp:Parameter Name="BILL_IN_NO"  Type="String" />
                        <asp:Parameter Name="MATE_ID" Type="String" />
                        <asp:Parameter Name="PLAN_IN_NUM" Type="String" />
                        <asp:Parameter Name="LOT_NO" Type="String" />
                        <asp:Parameter Name="MAKE_DATE"  Type ="DateTime" />
                        <asp:Parameter Name="REG_NUM" Type="String" />
                        <asp:Parameter Name="MEMO" Type="String" />
                        <asp:Parameter Name="BILL_IN_DT_STATUS" Type="String"  />
                        <asp:Parameter Name="UP_NUM"  Type="String" />
                        <asp:Parameter Name="CREA_PER_NO"  Type="String" />
                        <asp:Parameter Name="CREA_PER_NAME"  Type="String" />
                        <asp:Parameter Name="CREA_TIME" Type ="DateTime" />
                        <asp:Parameter Name="JINDU"  Type="String" />
            </UpdateParameters>
            
            
                                
        </asp:SqlDataSource>
             <asp:SqlDataSource ID="SqlDataSource1" runat="server" >
        </asp:SqlDataSource>
        
        <%--store--%>
        <ext:Store ID="DStore1" runat="server" 
            DataSourceID="SqlDataSource2" 
            OnAfterAjaxEvent="Store1_AfterAjaxEvent"
            OnBeforeAjaxEvent="Store1_BeforeAjaxEvent"
        >
            <Reader>
                <ext:JsonReader ReaderID="BILL_IN_DT_ID"  >
                    <Fields>
                        <ext:RecordField Name="BILL_IN_DT_ID"   />
                        <ext:RecordField Name="BILL_IN_NO"  />
                        <ext:RecordField Name="MATE_ID" />
                        <ext:RecordField Name="PLAN_IN_NUM" />
                        <ext:RecordField Name="LOT_NO" />
                        <ext:RecordField Name="MAKE_DATE" Type="Date" DateFormat="Y-m-dTH:i:s" />
                        <ext:RecordField Name="REG_NUM" />
                        <ext:RecordField Name="MEMO" />
                        <ext:RecordField Name="BILL_IN_DT_STATUS" />
                        <ext:RecordField Name="UP_NUM" />
                        <ext:RecordField Name="CREA_PER_NO" />
                        <ext:RecordField Name="CREA_PER_NAME" />
                        <ext:RecordField Name="CREA_TIME" Type="Date" DateFormat="Y-m-dTH:i:s" />
                        <ext:RecordField Name="JINDU" />
                    </Fields>
                </ext:JsonReader>
            </Reader>
            <BaseParams>
                <ext:Parameter Name="BILL_IN_NO3" Value="#{TextField1}.getValue()" Mode="Raw" />          
            </BaseParams>
            <Listeners>
                <LoadException Handler="Ext.Msg.alert('Products - Load failed', e.message || response.statusText);" />
                <CommitFailed Handler="Ext.Msg.alert('Suppliers - Commit failed', 'Reason: ' + msg)" />
                <SaveException Handler="Ext.Msg.alert('Suppliers - Save failed', e.message || e)" />
                <CommitDone Handler="Ext.Msg.alert('Suppliers - Commit', 'The data successfully saved');" />
               
    
            </Listeners>
        </ext:Store>
    
    <%--diaplay--%>
    
    <ext:Window ID="MtnWindow" runat="server" Icon="Group" Title="?????"
        Width="700px" Height="500px" AutoShow="false" Show&#111;nload="false" Plain="true">
        <Body>
            <ext:BorderLayout ID="BorderLayout1" runat="server">
                <North>
                     <ext:Panel ID="Panel1" runat="server" Title="??" Frame="true" BodyStyle="padding:5px 5px 0;" Height=120>
                        <Body>
                            <ext:Panel ID="Panel3" runat="server">
                                <Body>
                                    <ext:ColumnLayout ID="ColumnLayout1" runat="server">
                                        <ext:LayoutColumn ColumnWidth=".5">
                                            <ext:Panel ID="Panel4" runat="server" Border="false" Header="false">
                                                <Body>
                                                    <ext:FormLayout ID="FormLayout1" runat="server" LabelAlign="Left">
                                                        <ext:Anchor Horizontal="95%">
                                                            <ext:TextField ID="TextField1" runat="server" FieldLabel="????"/>
                                                        </ext:Anchor>
                                                        <ext:Anchor Horizontal="95%">
                                                            <ext:TextField ID="TextField2" runat="server" FieldLabel="??????" />
                                                        </ext:Anchor>
                                                    </ext:FormLayout>
                                                </Body>
                                            </ext:Panel>
                                        </ext:LayoutColumn>
                                        <ext:LayoutColumn ColumnWidth=".5">
                                            <ext:Panel ID="Panel5" runat="server">
                                                <Body>
                                                    <ext:FormLayout ID="FormLayout2" runat="server" LabelAlign="Left">
                                                        <ext:Anchor Horizontal="95%">
                                                            <ext:TextField ID="TextField3" runat="server" FieldLabel="?????" />
                                                        </ext:Anchor>
                                                        <ext:Anchor Horizontal="95%">
                                                            <ext:TextField ID="TextField4" runat="server" FieldLabel="????" />
                                                        </ext:Anchor>
                                                    </ext:FormLayout>
                                                </Body>
                                            </ext:Panel>
                                        </ext:LayoutColumn>
                                    </ext:ColumnLayout>
                                </Body>
                            </ext:Panel>
                            <ext:Panel ID="Panel6" runat="server">
                                <Body>
                                    <ext:FormLayout ID="FormLayout3" runat="server" LabelAlign="Left">
                                        <ext:Anchor Horizontal="98%">
                                            <ext:TextField ID="TextField5" runat="server" FieldLabel="??" />
                                        </ext:Anchor>
                                    </ext:FormLayout>
                                </Body>
                            </ext:Panel>
                        </Body>
                    </ext:Panel>
                </North>
                <Center Collapsible="true" Split="true">
                    <ext:Panel ID="Panel2" runat="server" Title="???">
                        <TopBar>
                            <ext:Toolbar ID="Toolbar1" runat="server">
                                <Items>
                                    <ext:ToolbarButton ID="ToolbarButton1" runat="server" Icon="Add" StandOut="true"
                                        Text="????">
                                        <Listeners>
                                            
                                            <Click Handler="#{dGridPanel}.insertRecord(1, {UniqeIdentity: 'myUniqueValue'});#{dGridPanel}.getView().focusRow(0);#{dGridPanel}.startEditing(0, 0);" />
                                        </Listeners>
    
                                        <ToolTips>
                                            <ext:ToolTip ID="ToolTip1" runat="server" Html="????" />
                                        </ToolTips>
                                    </ext:ToolbarButton>
                                    
                                    <ext:ToolbarButton ID="ToolbarButton2" runat="server" Icon="Delete" StandOut="true"
                                        Text="????">
                                        
                                        <Listeners>
                                                <Click Handler="#{dGridPanel}.deleteSelected();" />
                                        </Listeners>
    
                                        <ToolTips>
                                            <ext:ToolTip ID="ToolTip2" runat="server" Html="????" />
                                        </ToolTips>
                                    </ext:ToolbarButton>
                                    
                                </Items>
                            </ext:Toolbar>
                        </TopBar>
                        <Body>
                             <ext:FitLayout ID="FitLayout1" runat="server" >
                             <ext:GridPanel ID="dGridPanel" runat="server" StoreID="DStore1">
                             <ColumnModel ID="ColumnModel2" runat="server">
                                <Columns>
                                    <ext:Column DataIndex="BILL_IN_DT_ID" Header="???????" Sortable="True">
                                         <Editor>
                                            <ext:TextField ID="TextField6" runat="server" />
                                        </Editor>                            
                                    </ext:Column>
                                    <ext:Column DataIndex="BILL_IN_NO" Header="????" Sortable="True">
                                         <Editor>
                                            <ext:TextField ID="TextField7" runat="server" />
                                         </Editor>
                                    </ext:Column>
                                    <ext:Column DataIndex="MATE_ID" Header="????" Sortable="True">
                                         <Editor>
                                            <ext:TextField ID="TextField8" runat="server" />
                                        </Editor>
                                    </ext:Column>
                                    <ext:Column DataIndex="PLAN_IN_NUM" Header="??????" Sortable="True">
                                        <Editor>
                                            <ext:TextField ID="TextField18" runat="server" />
                                        </Editor>
                                    </ext:Column>
                                    <ext:Column DataIndex="LOT_NO" Header="???" Sortable="True">
                                        <Editor>
                                            <ext:TextField ID="TextField19" runat="server" />
                                        </Editor>
                                    </ext:Column>
                                    <ext:Column DataIndex="MAKE_DATE" Header="????" Sortable="True">
                                        <Renderer Fn="Ext.util.Format.dateRenderer('Y-m-d H:i:s')" />
                                        <Editor>
                                            <ext:TextField ID="TextField9" runat="server" />
                                        </Editor>
                                    </ext:Column>
                                    <ext:Column DataIndex="REG_NUM" Header="????" Sortable="True">
                                        <Editor>
                                            <ext:TextField ID="TextField17" runat="server" />
                                        </Editor>
                                    </ext:Column>
                                    <ext:Column DataIndex="MEMO" Header="??" Sortable="True">
                                        <Editor>
                                            <ext:TextField ID="TextField16" runat="server" />
                                        </Editor>
                                    </ext:Column>
                                    <ext:Column DataIndex="BILL_IN_DT_STATUS" Header="??????(" Sortable="True">
                                        <Editor>
                                            <ext:TextField ID="TextField15" runat="server" />
                                        </Editor>
                                    </ext:Column>
                                    <ext:Column DataIndex="UP_NUM" Header="????" Sortable="True">
                                        <Editor>
                                            <ext:TextField ID="TextField14" runat="server" />
                                        </Editor>
                                    </ext:Column>
                                    <ext:Column DataIndex="CREA_PER_NO" Header="?????" Sortable="True">
                                        <Editor>
                                            <ext:TextField ID="TextField13" runat="server" />
                                        </Editor>
                                    </ext:Column>
                                    <ext:Column DataIndex="CREA_PER_NAME" Header="?????" Sortable="True">
                                        <Editor>
                                            <ext:TextField ID="TextField12" runat="server" />
                                        </Editor>
                                    </ext:Column>
                                    <ext:Column DataIndex="CREA_TIME" Header="????" Sortable="True">
                                        <Renderer Fn="Ext.util.Format.dateRenderer('Y-m-d H:i:s')" />
                                        <Editor>
                                            <ext:TextField ID="TextField10" runat="server" />
                                        </Editor>
                                    </ext:Column>
                                    <ext:Column DataIndex="JINDU" Header="??" Sortable="True">
                                        <Editor>
                                            <ext:TextField ID="TextField11" runat="server" />
                                        </Editor>
                                    </ext:Column>
                                </Columns>
                            </ColumnModel>
                            <SelectionModel>
                                <ext:RowSelectionModel ID="RowSelectionModel1" runat="server" SingleSelect="true">
                                </ext:RowSelectionModel>
                            </SelectionModel>
                            
                            <BottomBar>
                                <ext:PagingToolBar ID="PagingToolBar1" runat="server" PageSize="10" RefreshText="??"
                                 StoreID="DStore1"/>
                            </BottomBar>
    
                            </ext:GridPanel>                
                            </ext:FitLayout>         
                        </Body>
                    </ext:Panel>
                </Center>            
            </ext:BorderLayout>
        </Body>
        <Buttons>
            <ext:Button ID="Button1" runat="server" Text="????" />
            <ext:Button ID="Button2" runat="server" Text="??">
    
                <Listeners>
                    <Click Handler="#{dGridPanel}.save();" />
                </Listeners>
      
    
            </ext:Button>
            <ext:Button ID="Button3" runat="server" Text="??">
                <AjaxEvents>
                    <Click OnEvent="Cancel_Click">
                    </Click>
                </AjaxEvents>
            </ext:Button>
        </Buttons>
       
    </ext:Window>
    
    BillInAdd.ascx.cs
    
    using System;
    using System.Collections;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    using System.Data.OracleClient;
    using Coolite.Ext.Web;
    using System.IO;
    
    public partial class BillInAdd : System.Web.UI.UserControl
    {
        //Page Load
        protected void Page_Load(object sender, AjaxEventArgs e)
        {
    
        }
    
        SqlCon sqlCon = new SqlCon();
    
        public static string conn;
       
        public string id;
    
        public string test;
    
        public string sCon = GetConn();
    
        public static string GetConn()
        {
            if (conn == null)
            {
                string sPath = System.Web.HttpContext.Current.Request.MapPath("conn.txt");
                StreamReader objReader = new StreamReader(sPath);
                conn = objReader.ReadToEnd();
                objReader.Close();
            }
            return conn;
        }
     
    
        protected void Add_Click(object sender, AjaxEventArgs e)
        {
         }
    
        public void Store1_Refresh(object sender, StoreRefreshDataEventArgs e)
        {
              DStore1.DataBind();
        }
    
        protected void Cancel_Click(object sender, AjaxEventArgs e)
        {
            Mtnwindow.Hide();
        }
    
    
        public void Show(string id1)
        {
            TextField1.Text = id1;
            SqlDataSource2.SelectParameters["BILL_IN_NO3"].DefaultValue = id1 ?? "-1";
            DStore1.DataBind();
            Mtnwindow.Show();
        }
      
    
        public void Show()
        {
           
            string[] restring = OKGETNO("BILL_IN_NO");
            string oid = restring[0];
            DStore1.DataBind();
            TextField1.Text = oid;
            Mtnwindow.Show();
        }
    
        public string[] OKGETNO(string s_OKnofieldname)
        {
            string[] ReStringList = new string[1];
            int i = 0;
            try
            {
                OracleConnection OraMyConnection = new OracleConnection();
                OraMyConnection.ConnectionString = sCon;
                OraMyConnection.Open();
                OracleCommand oraCommand = new OracleCommand("OKGETNO", OraMyConnection);
                oraCommand.CommandType = CommandType.StoredProcedure;
    
                oraCommand.Parameters.Add("s_OKnofieldname", OracleType.VarChar, 20).Value = s_OKnofieldname;
                oraCommand.Parameters[i++].Direction = ParameterDirection.Input;
    
                oraCommand.Parameters.Add("s_value", OracleType.VarChar, 300);
                oraCommand.Parameters[i++].Direction = ParameterDirection.Output;
    
                oraCommand.ExecuteNonQuery();
                OraMyConnection.Close();
                ReStringList[0] = Convert.ToString(oraCommand.Parameters[i - 1].Value);//??code <0???          
            }
            catch (Exception E)
            {
                ReStringList[1] = Convert.ToString(E);
                return ReStringList;
            }
            return ReStringList;
        }
    
    
        protected void Store1_BeforekRecordInserted(object sender, BeforeRecordInsertedEventArgs e)
        {
            object region = e.NewValues["BILL_IN_DT_ID"];
            if (region == null)
            {
                e.Cancel = true;
                this.cancel = true;
                this.message = "The BILL_IN_DT_ID must be 'AL'";
            }
        }
    
        protected void Store1_AfterRecordInserted(object sender, AfterRecordInsertedEventArgs e)
        {
            //The deleted and updated records confirms automatic (depending AffectedRows field)
            //But you can override this in AfterRecordUpdated and AfterRecordDeleted event
            //For insert we should set new id for refresh on client
            //If we don't set new id then old id will be used
            if (e.Confirmation.Confirm &amp;&amp; !string.IsNullOrEmpty(insertedValue))
            {
                e.Confirmation.ConfirmRecord(insertedValue);
    
                insertedValue = "";
            }
        }
    
        protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
        {
        }
        protected void Store1_AfterAjaxEvent(object sender, AfterAjaxEventArgs e)
        {
            if (e.Response.Success)
            {
                // set to .Success to false if we want to return a failure
                e.Response.Success = !cancel;
                e.Response.Msg = message;
            }
        }
    
        protected void Store1_BeforeAjaxEvent(object sender, BeforeAjaxEventArgs e)
        {
            string emulError = e.Parameters["EmulateError"];
            if (emulError == "1")
            {
                throw new Exception("Emulating error");
            }
        }
    
     
    
        protected void Store1_RefershData(object sender, StoreRefreshDataEventArgs e)
        {
            string id = e.Parameters["BILL_IN_NO3"].ToString();
            SqlDataSource2.SelectParameters["BILL_IN_NO3"].DefaultValue = id ?? "-1";
            this.DStore1.DataBind();
        }
    
    
        //private
    
        private bool cancel;
        private string message;
    
        private string insertedValue;
        
    }
    Sorry for the long and messy code.

    BTW, I found something which might be the clue. I used sqlmonitor from TOAD to monitor the real time sql query and I noticed that when I inserted a new record into the grid and I clicked 'Save', the monitored sql test was:

    INSERT INTO BILL_IN_DTT(BILL_IN_DT_ID, BILL_IN_NO,MATE_ID,PLAN_IN_NUM,LOT_NO,MAKE_DATE,REG_NUM,MEMO,BILL_IN_DT_STATUS,UP_NUM,
    CREA_PER_NO,CREA_PER_NAME,CREA_TIME,JINDU) 
     VALUES (
     :BILL_IN_DT_ID
     :BILL_IN_NO,
     :MATE_ID,
     :PLAN_IN_NUM
     )
    
    :1 = '9'
    :2 = 'R'
    :3 = 21
    Runtime error occurred: 1036 (ORA-01036.....);
    See, one of the parameters was lost. I tried this several times and have not yet figured out. I will keep looking into the problem and waiting for your help. Big thanksssssssssssssssssss!

    Michael

  2. #2

    RE: sqldatasource insert into oracle

    I got it! Did anyone find where the problem is? :)

    Michael
  3. #3

    RE: sqldatasource insert into oracle

    I got it! Did anyone find where the problem is? :)
    No idea. Your original post was a bit hard to follow.


    What was the solution?


    Geoffrey McGill
    Founder
  4. #4

    RE: sqldatasource insert into oracle

    Hi,

    This problem is in fact a hybrid of oracle database driver and sqldatasource.

    I tried many times to insert a record from a Gridpanel into an oracle 11g database and I always got 'ORA-01036 : illegal variable name/number' Issue. Originallly I was thinking of a probelm in gridpanel, however after I read this article

    http://weblogs.asp.net/kencox/archiv...ber-issue.aspx

    I realized that there might be something very tricky about the oracle database driver. After I installed ODT11 with ODAC, I noticed that select/update/insert/delete command must be changed. And that is where I made my last mistake, finally I noticed that there was a typo like this:

    InsertCommand="BEGIN INSERT INTO BILL_IN_DTT(BILL_IN_DT_ID, BILL_IN_NO,MATE_ID,PLAN_IN_NUM,LOT_NO,MAKE_DATE,REG_NUM,MEMO,BILL_IN_DT_STATUS,UP_NUM,CREA_PER_NO,CREA_PER_NAME,CREA_TIME,JINDU)
    
    VALUES (
    
    :BILL_IN_DT_ID
    
    :BILL_IN_NO,
    
    :MATE_ID,
    
    :PLAN_IN_NUM,
    
    :LOT_NO,
    
    :MAKE_DATE,
    
    :REG_NUM,
    
    :MEMO,
    
    :BILL_IN_DT_STATUS,
    
    :UP_NUM,
    
    :CREA_PER_NO,
    
    :CREA_PER_NAME,
    
    :CREA_TIME,
    
    :JINDU
    
    );
    See, the comma was omitted and no error message was prompted!

    ODT can be found here:
    http://www.oracle.com/technology/sof...net/index.html

    Cheers

    Michael

  5. #5

    RE: sqldatasource insert into oracle

    You can mark this topic as 'CLOSED'. Many thanks!

    Michael

Similar Threads

  1. Replies: 4
    Last Post: Oct 07, 2011, 10:49 AM
  2. Replies: 12
    Last Post: Oct 03, 2011, 9:59 PM
  3. Form processing with sqlDataSource and Oracle
    By preussmd in forum 1.x Help
    Replies: 0
    Last Post: Jun 18, 2010, 7:00 PM
  4. Replies: 0
    Last Post: Jun 10, 2010, 1:18 AM
  5. for SqlDataSource
    By lindgrenm in forum 1.x Help
    Replies: 0
    Last Post: Sep 23, 2009, 5:47 PM

Posting Permissions