This is a migrated thread and some comments may be shown as answers.

RadGrid, SQLDataSource, and Oracle database

2 Answers 307 Views

Grid

This is a migrated thread and some comments may be shown as answers.

Keith asked on 08 Sep 2016, 09:01 PM

I have an ASP.Net page with a RadGrid which is populated by the SQLDataSource.

I am trying to set up the RadGrid so the user edit some fields in the RadGrid for a record and click on update. But I cannot get it to work. When clicking on update, I get "ORA-01036: illegal variable name/number" error.

I am connecting to an Oracle database.

In looking at your demos, none of them use EditMode = "InPlace". They either use an Edit Form or some custom things like textboxes, comboboxes, etc.

I have tried having a simple update command in SQLDataSource like the following:

UpdateCommand="UPDATE ADMIN.RSDC_SCRAP_CREDIT_RATE SET LAST_UPDATE = CURRENT_DATE"

That statement should update the LAST_UPDATE field with the Current Date/Time for all the records but it is causing the error as well. That statement was just a test to see if the parameters in the SQLDataSource is causing the error and/or the Update statement is causing the error.

Usually I set up a RadGrid to use NeedDataSource but alot of your demos and documentation for editing and updating records in the RadGrid indicate using SQLDataSource instead.

Either way I am trying to find a way for the user to edit fields in the RadGrid and update the record.

Please help!

Here is my ASP.net page:

<%@ Page Language="VB" AutoEventWireup="false" Debug="true" CodeFile="ScrapRateBySupplier.aspx.vb" Inherits="ScrapRateBySupplier" %>

<%@ Register assembly="Telerik.Web.UI" namespace="Telerik.Web.UI" tagprefix="telerik" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title></title>

<style type="text/css">

.auto-style1 {

width: 960px;

height: 220px;

}

.auto-style4 {

width: 355px;

}

.modal

{

position: fixed;

top: 0;

left: 0;

background-color: black;

z-index: 99;

opacity: 0.8;

filter: alpha(opacity=80);

-moz-opacity: 0.8;

min-height: 100%;

width: 100%;

}

.loading

{

font-family: Arial;

font-size: 10pt;

border: 5px solid #67CFF5;

width: 200px;

height: 100px;

display: none;

position: fixed;

background-color: White;

z-index: 999;

}

.auto-style5 {

width: 186px;

}

.auto-style6 {

width: 264px;

}

html .RadMenu .rmLink,

html .RadMenu .rmHorizontal .rmText {

float: none;

padding: 0;

text-align: center;

}

</style>

<script type="text/javascript" src="/scripts/jquery.min.js"></script>

<script type = "text/javascript" >

function preventBack() { window.history.forward(); }

setTimeout("preventBack()", 3);

window.onunload = function () { null };

</script>

<script type="text/javascript">

function ShowProgress() {

setTimeout(function () {

var modal = $('<div />');

modal.addClass("modal");

$('body').append(modal);

var loading = $(".loading");

loading.show();

var top = Math.max($(window).height() / 2 - loading[0].offsetHeight / 2, 0);

var left = Math.max($(window).width() / 2 - loading[0].offsetWidth / 2, 0);

loading.css({ top: top, left: left });

}, 200);

}

$('form').live("submit", function () {

ShowProgress();

});

$(document).ready(function () {

$("#txtActivity").focus(function () {

$(this).css("background-color", "#ffff33");

});

$("#txtActivity").blur(function () {

$(this).css("background-color", "#ffffff");

});

$("#ddlUnit").focus(function () {

$(this).css("background-color", "#ffff33");

});

$("#ddlUnit").blur(function () {

$(this).css("background-color", "#ffffff");

});

$("#txtNote").focus(function () {

$(this).css("background-color", "#ffff33");

});

$("#txtNote").blur(function () {

$(this).css("background-color", "#ffffff");

});

</script>

</head>

<body style="background-color: #3A4459">

<form id="form1" runat="server">

<telerik:RadScriptManager ID="RadScriptManager1" runat="server">

<Scripts>

<asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.Core.js">

</asp:ScriptReference>

<asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQuery.js">

</asp:ScriptReference>

<asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQueryInclude.js">

</asp:ScriptReference>

</Scripts>

</telerik:RadScriptManager>

<div>

<img alt="RSDC of Michigan" class="auto-style1" src="/images/RSDC_Logo2.jpg" />

<br />

<telerik:RadMenu ID="RadMenu1" Runat="server" Width="960px" Style="z-index: 50">

<Items>

<telerik:RadMenuItem runat="server" Font-Bold="True" Font-Size="14pt" NavigateUrl="CustomerPortal" Text="Home" Width="475px">

</telerik:RadMenuItem>

<telerik:RadMenuItem runat="server" IsSeparator="True" Text="Sep">

</telerik:RadMenuItem>

<telerik:RadMenuItem runat="server" Font-Bold="True" Font-Size="14pt" ForeColor="Red" Text="Logout" Width="475px">

</telerik:RadMenuItem>

</Items>

</telerik:RadMenu>

<br />

<asp:Label ID="Label2" runat="server" Font-Bold="True" ForeColor="White" Text="Scrap Rate By Supplier"></asp:Label>

<table style="width:960px;">

<tr>

<td style="text-align:right;"><asp:Label ID="lblTable" runat="server" ForeColor="White"></asp:Label></td>

</tr>

</table>

<asp:Panel ID="Panel1" runat="server" Height="600px" Wrap="False" BorderWidth="3px" BorderColor="Black" BorderStyle="Ridge" ForeColor="White" Width="955px" style="margin-right: 17px">

<br />

<telerik:RadButton ID="rbtnAdd" runat="server" Text="ADD" RenderMode="Classic" Font-Size="11pt">

<Icon PrimaryIconCssClass="rbAdd" PrimaryIconLeft="4" PrimaryIconTop="3"></Icon>

</telerik:RadButton>

<asp:Label ID="Label1" runat="server"></asp:Label>

<br />

<telerik:RadGrid ID="rgScrapRateBySupplier" runat="server" GroupPanelPosition="Top" Height="400px" Width="955px" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" AllowAutomaticUpdates="True">

<ExportSettings>

<Pdf AllowPrinting="False">

</Pdf>

</ExportSettings>

<ClientSettings>

<Selecting AllowRowSelect="True" />

<Scrolling AllowScroll="True" UseStaticHeaders="True" />

</ClientSettings>

<MasterTableView EditMode="InPlace" DataSourceID="SqlDataSource1">

<CommandItemSettings ShowAddNewRecordButton="False" ShowRefreshButton="False" />

<Columns>

<telerik:GridEditCommandColumn UniqueName="EditCommandColumn">

</telerik:GridEditCommandColumn>

<telerik:GridBoundColumn DataField="SUPPLIER" FilterControlAltText="Filter column column" HeaderText="Supplier" ReadOnly="True" UniqueName="Supplier">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="PRIME" FilterControlAltText="Filter column column" HeaderText="Prime" UniqueName="Prime">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="RSDC_SV" FilterControlAltText="Filter column1 column" HeaderText="RSDC SV" UniqueName="RSDCSv">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="MILL" FilterControlAltText="Filter column2 column" HeaderText="Mill" UniqueName="Mill">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="ENG" FilterControlAltText="Filter column3 column" HeaderText="Eng" UniqueName="Eng">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="LAST_UPDATE" FilterControlAltText="Filter column4 column" HeaderText="Last Update" ReadOnly="True" UniqueName="LastUpdate">

</telerik:GridBoundColumn>

</Columns>

</MasterTableView>

</telerik:RadGrid>

<br />

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="OverwriteChanges"

ConnectionString="<%$ ConnectionStrings:OracleConnectionStringS01 %>" ProviderName="<%$ ConnectionStrings:OracleConnectionStringS01.ProviderName %>"

SelectCommand="SELECT * FROM ADMIN.RSDC_SCRAP_CREDIT_RATE">

</asp:SqlDataSource>

</asp:Panel>

<br />

</div>

<div class="loading" align="center" style="z-index:100000">

Loading. Please wait.<br />

<br />

<img src="loader.gif" alt="" />

</div>

</form>

</body>

</html>

Here is the Code-behind in vb.net:

Imports System.Data

Imports Telerik.Web.UI

Partial Class ScrapRateBySupplier

Inherits System.Web.UI.Page

Private myDSData As DataSet

Private sql As String

Private bHasError As Boolean = False, strErrorMsg As String

Private Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

Try

Dim strUser As String = ""

Dim strDecUser As String = ""

Dim strAppName As String = "ACMENUSCRAPRATEBYSUPP"

If Request.IsAuthenticated Then

If Not Session("UserName") Is Nothing Then

strUser = CType(Session("UserName"), String)

strDecUser = DataEncryption.base64Decode(strUser)

strDecUser = DataEncryption.base64Decode(strDecUser)

strUser = strDecUser

End If

If Not LoginValidation.IsValidAppAccess(strUser, strAppName) Then

Response.Redirect("CustomerPortal")

End If

Else

FormsAuthentication.RedirectToLoginPage()

End If

Catch ex As Exception

strErrorMsg = "Procedure: Page_Init - ScrapRateBySupplier" & vbCrLf & "Error Message: " & ex.Message & vbCrLf & "Source: " & ex.Source

UserFunctions.UserMsgBox(Me, strErrorMsg)

End Try

End Sub

End Class

2 Answers, 1 is accepted

answered on 13 Sep 2016, 12:52 PM

Hi,

Please note that using SqlDataSource with Oracle database is not directly related to the RadGrid control. This is a general programming issue.

Nevertheless, check out the following thread that discusses similar error and how it can be resolved.

http://stackoverflow.com/questions/21375288/ora-01036-illegal-variable-name-number-when-running-query-through-c-sharp

Also, examine the following article that discusses how you can use the SqlDataSource with Oracle database.

https://msdn.microsoft.com/en-us/library/92ceczx1(v=vs.85).aspx

In case you would like additional information on how to use automatic CRUD operations in RadGrid you would find the article below interesting.

http://docs.telerik.com/devtools/aspnet-ajax/controls/grid/data-editing/automatic-datasource-operations

Regards,
Viktor Tachev
Telerik by Progress

Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.