Jul 28, 2009, 6:07 AM
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
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:
Michael
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" Showonload="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 && !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