GY1680
asked on
Advantage Database record in FormView
I'm new to Advantage Databases, and I'm able to successfully connect and query against them with asp.net gridviews, but I'm unable to get a FormView to populate with data that I know is in the sqldatasource (debugging shows me the record is there). Please advise if you have any ideas as to why my formview simply does not display on the page after selecting one of the gridview items (as works with MS SQL just fine).
ASP code:
<%@ Page Title="" Language="C#" MasterPageFile="~/Master.master" AutoEventWireup="true" CodeFile="AdsTest.aspx.cs" Inherits="AdsTest" %>
<%@ Register assembly="System.Web.Entity, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" namespace="System.Web.UI.WebControls" tagprefix="asp" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="content" Runat="Server">
<asp:LoginView ID="LoginView1" runat="server">
<Rolegroups>
<asp:RoleGroup Roles="Managers">
<ContentTemplate>
</ContentTemplate>
</asp:RoleGroup>
<asp:RoleGroup Roles="Users>
<ContentTemplate>
<asp:TextBox ID="tbNumber" runat="server"></asp:TextBox>
<br /><br />
<asp:GridView ID="GridView1" runat="server"
OnLoad="GridView1_Load" DataKeyNames="Number"
OnSelectedIndexChanged="GridView1_SelectedIndexChanged"
AutoGenerateSelectButton="true">
</asp:GridView>
<br /><br />
<asp:GridView ID="GridView2" runat="server"
OnLoad="GridView2_Load" DataKeyNames="Clinician"
onselectedindexchanged="GridView2_SelectedIndexChanged"
AutoGenerateSelectButton="True">
</asp:GridView>
<br />
<asp:FormView ID="FormView1" runat="server" CellPadding="4"
ForeColor="#333333" DefaultMode="ReadOnly" >
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#EFF3FB" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#2461BF" />
</asp:FormView>
<asp:SqlDataSource ID="sqlAdsDetails" runat="server"
ConnectionString="<%$ ConnectionStrings:AdsConnection %>"
onselecting="sqlAdsDetails_Selecting"
ProviderName="<%$ ConnectionStrings:AdsConnection.ProviderName %>"
SelectCommand="">
<SelectParameters>
</SelectParameters>
</asp:SqlDataSource>
</ContentTemplate>
</asp:RoleGroup>
</Rolegroups>
<AnonymousTemplate>
</AnonymousTemplate>
</asp:LoginView>
</asp:Content>
C# code:
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Advantage.Data.Provider;
using System.Data;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
public partial class AdsTest : System.Web.UI.Page
{
private Model.EmpModel empModel;
protected void Page_Load(object sender, EventArgs e)
{
if (User.Identity.IsAuthenticated)
{
empModel = new Model.EmpModel();
}
else
{
FormsAuthentication.RedirectToLoginPage();
}
}
protected void GridView1_Load(object sender, EventArgs e)
{
GridView gv1 = (GridView)LoginView1.FindControl("GridView1");
TextBox tbNumber = (TextBox)LoginView1.FindControl("tbNumber");
AdsConnection conn = new AdsConnection();
conn.ConnectionString = "Data Source=c:\\******\\dd1.add;User ID=******;Password=******;ServerType=LOCAL;TrimTrailingSpaces=True";
DataSet ds1 = new DataSet();
AdsDataAdapter ada1 = new AdsDataAdapter();
//ObjectQuery<Model.EMPLOYEE> EmployeeQuery = empModel.EMPLOYEE;
try
{
conn.Open();
AdsCommand com = new AdsCommand("Select * From EMPLOYEE Where Number = 2", conn);
ada1.SelectCommand = com;
ada1.Fill(ds1, "EMPLOYEE");
gv1.DataSource = ds1;
//gv1.DataSource = EmployeeQuery;
gv1.DataBind();
}
catch (Exception ex)
{
Response.Write(ex);
}
finally
{
conn.Close();
}
}
protected void GridView2_Load(object sender, EventArgs e)
{
GridView gv2 = (GridView)LoginView1.FindControl("GridView2");
TextBox tbNumber = (TextBox)LoginView1.FindControl("tbNumber");
AdsConnection conn = new AdsConnection();
conn.ConnectionString = "Data Source=c:\\ahh-live\\dd1.add;User ID=adssys;ServerType=LOCAL;TrimTrailingSpaces=True";
DataSet ds1 = new DataSet();
AdsDataAdapter ada1 = new AdsDataAdapter();
try
{
conn.Open();
AdsCommand com = new AdsCommand("Select * From EMPAVAIL Where Clinician = 2", conn);
ada1.SelectCommand = com;
ada1.Fill(ds1, "EMPAVAIL");
gv2.DataSource = ds1;
gv2.DataBind();
}
catch (Exception ex)
{
Response.Write(ex);
}
finally
{
conn.Close();
}
}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
GridView gv1 = (GridView)LoginView1.FindControl("GridView1");
SqlDataSource sqlAdsDetails = (SqlDataSource)LoginView1.FindControl("sqlAdsDetails");
FormView fv1 = (FormView)LoginView1.FindControl("FormView1");
sqlAdsDetails.SelectCommand = "Select * from Employee Where Number = " + gv1.SelectedValue;
fv1.DataSourceID = "sqlAdsDetails";
//sqlAdsDetails.Select(DataSourceSelectArguments.Empty);
fv1.DataBind();
}
protected void GridView2_SelectedIndexChanged(object sender, EventArgs e)
{
GridView gv2 = (GridView)LoginView1.FindControl("GridView2");
SqlDataSource sqlAdsDetails = (SqlDataSource)LoginView1.FindControl("sqlAdsDetails");
FormView fv1 = (FormView)LoginView1.FindControl("FormView1");
sqlAdsDetails.SelectCommand = "Select * from Employee Where Number = " + gv2.SelectedValue;
fv1.DataSourceID = "sqlAdsDetails";
sqlAdsDetails.Select(DataSourceSelectArguments.Empty);
fv1.DataBind();
}
protected void sqlAdsDetails_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
can't read the error message by ....change two things as below:
protected void sqlAdsDetails_Selecting(ob ject sender, SqlDataSourceSelectingEven tArgs e)
{
e.Command.Parameters["Numb er"].Value = number;
}
Change made to SqlCommand...
<asp:SqlDataSource ID="sqlAdsDetails" runat="server"
ConnectionString="<%$ ConnectionStrings:AdsConne ction %>"
onselecting="sqlAdsDetails _Selecting "
ProviderName="<%$ ConnectionStrings:AdsConne ction.Prov iderName %>"
SelectCommand = "Select * from Employee Where Number =@Number">
<SelectParameters>
<asp:Parameter Name="Number" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
protected void sqlAdsDetails_Selecting(ob
{
e.Command.Parameters["Numb
}
Change made to SqlCommand...
<asp:SqlDataSource ID="sqlAdsDetails" runat="server"
ConnectionString="<%$ ConnectionStrings:AdsConne
onselecting="sqlAdsDetails
ProviderName="<%$ ConnectionStrings:AdsConne
SelectCommand = "Select * from Employee Where Number =@Number">
<SelectParameters>
<asp:Parameter Name="Number" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
ASKER
Tried the above changes and received the following error:
Error 5111: The parameter name specified was invalid for the statement.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Advantage.Data.Provider.Ad sException : Error 5111: The parameter name specified was invalid for the statement.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[AdsException: Error 5111: The parameter name specified was invalid for the statement.]
Advantage.Data.Provider.Ad sException .CheckACE( UInt32 ulRet) +61
Advantage.Data.Provider.Ad sCommand.S etParamete rs() +7737
Advantage.Data.Provider.Ad sCommand.E xecuteStat ement(IntP tr& hCursor, String& strIndex) +1305
Advantage.Data.Provider.Ad sCommand.E xtecuteRea derInterna l(CommandB ehavior behavior, Boolean bExtendedReader) +78
Advantage.Data.Provider.Ad sCommand.E xecuteRead er(Command Behavior behavior) +38
Advantage.Data.Provider.Ad sCommand.S ystem.Data .IDbComman d.ExecuteR eader(Comm andBehavio r behavior) +36
System.Data.Common.DbDataA dapter.Fil lInternal( DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130
System.Data.Common.DbDataA dapter.Fil l(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +287
System.Data.Common.DbDataA dapter.Fil l(DataSet dataSet, String srcTable) +92
System.Web.UI.WebControls. SqlDataSou rceView.Ex ecuteSelec t(DataSour ceSelectAr guments arguments) +1297
System.Web.UI.DataSourceVi ew.Select( DataSource SelectArgu ments arguments, DataSourceViewSelectCallba ck callback) +19
System.Web.UI.WebControls. DataBoundC ontrol.Per formSelect () +142
System.Web.UI.WebControls. BaseDataBo undControl .DataBind( ) +73
System.Web.UI.WebControls. FormView.D ataBind() +4
System.Web.UI.WebControls. BaseDataBo undControl .EnsureDat aBound() +82
System.Web.UI.WebControls. FormView.E nsureDataB ound() +166
System.Web.UI.WebControls. CompositeD ataBoundCo ntrol.Crea teChildCon trols() +72
System.Web.UI.Control.Ensu reChildCon trols() +87
System.Web.UI.Control.PreR enderRecur siveIntern al() +44
System.Web.UI.Control.PreR enderRecur siveIntern al() +171
System.Web.UI.Control.PreR enderRecur siveIntern al() +171
System.Web.UI.Control.PreR enderRecur siveIntern al() +171
System.Web.UI.Control.PreR enderRecur siveIntern al() +171
System.Web.UI.Control.PreR enderRecur siveIntern al() +171
System.Web.UI.Control.PreR enderRecur siveIntern al() +171
System.Web.UI.Page.Process RequestMai n(Boolean includeStagesBeforeAsyncPo int, Boolean includeStagesAfterAsyncPoi nt) +842
-------------------------- ---------- ---------- ---------- ---------- ---------- ----
Version Information: Microsoft .NET Framework Version:2.0.50727.4927; ASP.NET Version:2.0.50727.4927
Additionally, the select statement for the sqlAdsDetails looks like this:
Select * from Employee Where Number =@Number
It's almost as if the parameter has no effect.
Error 5111: The parameter name specified was invalid for the statement.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Advantage.Data.Provider.Ad
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[AdsException: Error 5111: The parameter name specified was invalid for the statement.]
Advantage.Data.Provider.Ad
Advantage.Data.Provider.Ad
Advantage.Data.Provider.Ad
Advantage.Data.Provider.Ad
Advantage.Data.Provider.Ad
Advantage.Data.Provider.Ad
System.Data.Common.DbDataA
System.Data.Common.DbDataA
System.Data.Common.DbDataA
System.Web.UI.WebControls.
System.Web.UI.DataSourceVi
System.Web.UI.WebControls.
System.Web.UI.WebControls.
System.Web.UI.WebControls.
System.Web.UI.WebControls.
System.Web.UI.WebControls.
System.Web.UI.WebControls.
System.Web.UI.Control.Ensu
System.Web.UI.Control.PreR
System.Web.UI.Control.PreR
System.Web.UI.Control.PreR
System.Web.UI.Control.PreR
System.Web.UI.Control.PreR
System.Web.UI.Control.PreR
System.Web.UI.Control.PreR
System.Web.UI.Page.Process
--------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.4927; ASP.NET Version:2.0.50727.4927
Additionally, the select statement for the sqlAdsDetails looks like this:
Select * from Employee Where Number =@Number
It's almost as if the parameter has no effect.
I am not sure as I never tried advantag DB...and this is what they have:
http://devzone.advantagedatabase.com/dz/content.aspx?Key=17&RefNo=070813-1930
Which is already taken care above and also the bug should have been fixed by now...
may be trial/error with different combination like:
protected void sqlAdsDetails_Selecting(ob ject sender, SqlDataSourceSelectingEven tArgs e)
{
e.Command.Parameters["@Num ber"].Valu e = number;
}
Or
Select * from Employee Where Number =?
Or
Can you do the querying part entirely in code-behind.....and not use SDS...
i.e.
protected void GridView2_SelectedIndexCha nged(objec t sender, EventArgs e)
{
GridView gv2 = (GridView)LoginView1.FindC ontrol("Gr idView2");
SqlDataSource sqlAdsDetails = (SqlDataSource)LoginView1. FindContro l("sqlAdsD etails");
FormView fv1 = (FormView)LoginView1.FindC ontrol("Fo rmView1");
number = Convert.ToInt32(gv2.Select edValue);
fvDatabind(number);
}
protected void fvDataBind(int num)
{
string query ="Select * from Employee Where Number=" + num;
//do db query...
fv1.DataSource=//DBresults et..
fv1.DataBind();
}
http://devzone.advantagedatabase.com/dz/content.aspx?Key=17&RefNo=070813-1930
Which is already taken care above and also the bug should have been fixed by now...
may be trial/error with different combination like:
protected void sqlAdsDetails_Selecting(ob
{
e.Command.Parameters["@Num
}
Or
Select * from Employee Where Number =?
Or
Can you do the querying part entirely in code-behind.....and not use SDS...
i.e.
protected void GridView2_SelectedIndexCha
{
GridView gv2 = (GridView)LoginView1.FindC
SqlDataSource sqlAdsDetails = (SqlDataSource)LoginView1.
FormView fv1 = (FormView)LoginView1.FindC
number = Convert.ToInt32(gv2.Select
fvDatabind(number);
}
protected void fvDataBind(int num)
{
string query ="Select * from Employee Where Number=" + num;
//do db query...
fv1.DataSource=//DBresults
fv1.DataBind();
}
or may be check if this is of any help:
http://devzone.advantagedatabase.com/dz/content.aspx?key=48&Relatives=%3C49af9612@solutions.advantagedatabase.com%3E
http://devzone.advantagedatabase.com/dz/content.aspx?key=48&Relatives=%3C49af9612@solutions.advantagedatabase.com%3E
ASKER
Tried the suggestion of the colon (:) in the query as the parameter, and it debugs all the way through the formview databind method with no errors. In fact, I can view he formview data item (one record selected) while debugging. It looks like the formview is acquiring the record - which should mean that the query and parameter are working just fine - but it's not showing the formview. I'm not sure why. I don't have the formview disabled and the visible flag is not set to false. I've looked all over and I can't find anyone having this specific problem and am beginning to think it might be a bug. Another possibility is that some of the data is formatted as such that the formview cannot display properly. The only other thing I can think of is that since the formview doesn't have a datasourceid, it has not pre-built the structure (with all of the labels and text boxes) and therefore cannot populate them correctly. I'll try to set the datasourceid, then remove it in code and assign the datasource before databinding and see what happens. Please let me know if you have any other ideas.
Thanks.
Thanks.
yes...that's correct....you need to assign DataSourceID="sqlAdsDetail s" for the FormView.
ASKER
The "sqlAdsDetails" is aligned to the Formview1 as the DataSourceID - but the structure wasn't created. I tried to "refresh schema" but it fails because of the parameter in the query. This usually works with SQL parameters (@ instead of :) but alas not with Advantage. I may just forget about the formview and create my own "form" in textboxes in a table.
Appreciate the help and insight.
Appreciate the help and insight.
ASKER
Didn't get the formview working, but can see that the data is there - so the issue is not with the solution, but the formview and the data.
ASKER
Specified argument was out of the range of valid values.
So I created a string to capture the SelectCommand, which debugging showed not to change regardless of what parameter and @ or ? value I used in the select statement of the sqlAdsDetails datasource.
I've attached a screenshot of debugging through the _Selecting function to show that the datasource parameter "Number" is considered null by the application at the point of the error.
Please advise if you have any thoughts.
0402001136.jpg