Problem With FormView Update Using Sybase DataProvider
I'm working on an ASP.NET 2.0 application in Visual Studio 2005 that
accesses a Sybase database back end. We're using Sybase SQL Anywhere
9.0.2.3228. I have installed and registered the Sybase .NET 2.0
DataProvider (iAnywhere.Data.AsaClient.dll) into the GAC so it can be
used in the ProviderName property of a SQLDataSource and loads properly
at run time.
The application I'm writing is a bit more complex than the example I'm
about to show, but this is a simplified example that demonstrates the
problem I'm encountering.
I have a SQLDataSource configured with a connection string and the
Sybase DataProvider. I am using a FormView to display and allow
editing of member records in the Members table. Connecting to and
issuing a Select query, and displaying the results in the ItemTemplate
and EditItemTemplate work without any problems.
The problem occurs when I try to issue an Update query. With the code
directly below I get the error "iAnywhere.Data.AsaClient.AsaException:
Column '@Unique_ID' not found." Here is the code that causes that
error:
(FormView.aspx)
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="FormView.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div style="text-align: center">
<asp:SqlDataSource ID="MembersDataSource" runat="server"
ProviderName="iAnywhere.Data.AsaClient" ConnectionString="<%$
ConnectionStrings:CSQL5 %>" SelectCommand="SELECT [Unique_ID],
[MemNum], [FName], [LName] FROM [Members] WHERE [Deleted] IS NULL AND
[Type] = 'M'" EnableCaching="True" UpdateCommand="UPDATE [Members] SET
[MemNum]=@MemNum, [FName]=@FName, [LName]=@LName WHERE
[Unique_ID]=@Unique_ID">
</asp:SqlDataSource>
<br />
<br />
<asp:FormView ID="FormView1" runat="server"
AllowPaging="True" BackColor="White"
BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px"
CellPadding="4" DataSourceID="MembersDataSource"
GridLines="Both" DataKeyNames="Unique_ID">
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<EditRowStyle BackColor="#009999" Font-Bold="True"
ForeColor="#CCFF99" />
<RowStyle BackColor="White" ForeColor="#003399" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399"
HorizontalAlign="Left" />
<ItemTemplate>
MemNum:
<asp:Label ID="Label1" runat="server"
Text='<%#Eval("MemNum")%>'></asp:Label><br />
Last Name:
<asp:Label ID="Label2" runat="server"
Text='<%#Eval("LName")%>'></asp:Label><br />
First Name:
<asp:Label ID="Label3" runat="server"
Text='<%#Eval("FName")%>'></asp:Label><br />
<br />
<asp:Button ID="Button1" runat="server"
CommandName="Edit" Text="Edit" />
</ItemTemplate>
<HeaderStyle BackColor="#003399" Font-Bold="True"
ForeColor="#CCCCFF" />
<EditItemTemplate>
MemNum:
<asp:TextBox ID="TextBox3" runat="server" Text='<%#
Bind("MemNum") %>' Width="135px"></asp:TextBox><br />
Last Name:
<asp:TextBox ID="TextBox1" runat="server" Text='<%#
Bind("LName") %>' Width="135px"></asp:TextBox><br />
First Name:
<asp:TextBox ID="TextBox2" runat="server" Text='<%#
Bind("FName") %>' Width="135px"></asp:TextBox><br />
UniqueID:
<asp:TextBox ID="MemID" runat="server" Text='<%#
Bind("Unique_ID") %>' ReadOnly="True" Width="135px"></asp:TextBox><br
/>
<br />
<asp:Button ID="Button2" runat="server"
CommandName="Update" Text="Update" />
<asp:Button ID="Button3" runat="server"
CommandName="Cancel" Text="Cancel" />
</EditItemTemplate>
</asp:FormView>
</div>
</form>
</body>
</html>
(FormView.aspx.cs)
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
}
So I figured I could assign the Unique_ID in the Where clause manually
and avoid the problem. That just caused the application to error in a
very similar way on a different column. Here is the error I get when
manually assigning the Unique_ID in the Update statement:
"iAnywhere.Data.AsaClient.AsaException: Column '@MemNum' not found."
Here is the code that causes this error:
(FormView.aspx)
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="FormView.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div style="text-align: center">
<asp:SqlDataSource ID="MembersDataSource" runat="server"
ProviderName="iAnywhere.Data.AsaClient" ConnectionString="<%$
ConnectionStrings:CSQL5 %>" SelectCommand="SELECT [Unique_ID],
[MemNum], [FName], [LName] FROM [Members] WHERE [Deleted] IS NULL AND
[Type] = 'M'" EnableCaching="True" UpdateCommand="UPDATE [Members] SET
[MemNum]=@MemNum, [FName]=@FName, [LName]=@LName WHERE [Unique_ID]=">
</asp:SqlDataSource>
<br />
<br />
<asp:FormView ID="FormView1" runat="server"
AllowPaging="True" BackColor="White"
BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px"
CellPadding="4" DataSourceID="MembersDataSource"
GridLines="Both" DataKeyNames="Unique_ID"
OnItemUpdating="MemberUpdating">
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<EditRowStyle BackColor="#009999" Font-Bold="True"
ForeColor="#CCFF99" />
<RowStyle BackColor="White" ForeColor="#003399" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399"
HorizontalAlign="Left" />
<ItemTemplate>
MemNum:
<asp:Label ID="Label1" runat="server"
Text='<%#Eval("MemNum")%>'></asp:Label><br />
Last Name:
<asp:Label ID="Label2" runat="server"
Text='<%#Eval("LName")%>'></asp:Label><br />
First Name:
<asp:Label ID="Label3" runat="server"
Text='<%#Eval("FName")%>'></asp:Label><br />
<br />
<asp:Button ID="Button1" runat="server"
CommandName="Edit" Text="Edit" />
</ItemTemplate>
<HeaderStyle BackColor="#003399" Font-Bold="True"
ForeColor="#CCCCFF" />
<EditItemTemplate>
MemNum:
<asp:TextBox ID="TextBox3" runat="server" Text='<%#
Bind("MemNum") %>' Width="135px"></asp:TextBox><br />
Last Name:
<asp:TextBox ID="TextBox1" runat="server" Text='<%#
Bind("LName") %>' Width="135px"></asp:TextBox><br />
First Name:
<asp:TextBox ID="TextBox2" runat="server" Text='<%#
Bind("FName") %>' Width="135px"></asp:TextBox><br />
UniqueID:
<asp:TextBox ID="MemID" runat="server" Text='<%#
Bind("Unique_ID") %>' ReadOnly="True" Width="135px"></asp:TextBox><br
/>
<br />
<asp:Button ID="Button2" runat="server"
CommandName="Update" Text="Update" />
<asp:Button ID="Button3" runat="server"
CommandName="Cancel" Text="Cancel" />
</EditItemTemplate>
</asp:FormView>
</div>
</form>
</body>
</html>
(FormView.aspx.cs)
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void MemberUpdating(object sender,
FormViewUpdateEventArgs e)
{
MembersDataSource.UpdateCommand +=
e.Keys["Unique_ID"].ToString();
}
}
As far as I can tell, the values for the columns are not getting
updated with the input from the fields that I've bound using
Bind(string). I've tested to make sure, and the new values do exist
properly in the FormViewUpdateEventArgs.NewValues collection.
My best guess is that there is a problem with the Sybase DataProvider,
but perhaps there is some other cause, or at least some way I can work
around this to get the field data to bind properly and not have to
manually code the UpdateCommand. Any ideas?
date: 19 Jan 2006 09:06:27 -0800
author: Metal2You
