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)
    {

    }

Open in new window

GY1680Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

guru_samiCommented:
Try the following modifications...
1: Set SqlDataSource like below:

       <asp:SqlDataSource ID="sqlAdsDetails" runat="server"
                ConnectionString="<%$ ConnectionStrings:AdsConnection %>"
                onselecting="sqlAdsDetails_Selecting"
                ProviderName="<%$ ConnectionStrings:AdsConnection.ProviderName %>"
                SelectCommand = "Select * from Employee Where Number =@number">
                <SelectParameters>
                      <asp:Parameter Name="Number" Type="Int32" />
                 </SelectParameters>
        </asp:SqlDataSource>


2: Add variable number global to the class and modify your SelectedIndex for the gridviews like below:

private int number =0;

 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");

        number = Convert.ToInt32(gv1.SelectedValue);
        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");

        number = Convert.ToInt32(gv2.SelectedValue);
        fv1.DataBind();
    }

3: Modify the sqlAdsDetails_Selecting like below:

protected void sqlAdsDetails_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        e.Command.Parameters["@number"].Value = number;
    }

4:  Set datasourceID for Fv inside the markup like:
<asp:FormView ID="FormView1" runat="server" CellPadding="4"
                ForeColor="#333333" DefaultMode="ReadOnly"
            DataSourceID="sqlAdsDetails" >
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GY1680Author Commented:
I implemented the changes you advise, but I received the following error on page load (sqlAdsDetails_Selecting):

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
0
guru_samiCommented:
can't read the error message by ....change two things as below:

protected void sqlAdsDetails_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        e.Command.Parameters["Number"].Value = number;
    }

Change made to SqlCommand...

 <asp:SqlDataSource ID="sqlAdsDetails" runat="server"
                ConnectionString="<%$ ConnectionStrings:AdsConnection %>"
                onselecting="sqlAdsDetails_Selecting"
                ProviderName="<%$ ConnectionStrings:AdsConnection.ProviderName %>"
                SelectCommand = "Select * from Employee Where Number =@Number">
                <SelectParameters>
                      <asp:Parameter Name="Number" Type="Int32" />
                 </SelectParameters>
        </asp:SqlDataSource>
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

GY1680Author Commented:
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.AdsException: 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.AdsException.CheckACE(UInt32 ulRet) +61
   Advantage.Data.Provider.AdsCommand.SetParameters() +7737
   Advantage.Data.Provider.AdsCommand.ExecuteStatement(IntPtr& hCursor, String& strIndex) +1305
   Advantage.Data.Provider.AdsCommand.ExtecuteReaderInternal(CommandBehavior behavior, Boolean bExtendedReader) +78
   Advantage.Data.Provider.AdsCommand.ExecuteReader(CommandBehavior behavior) +38
   Advantage.Data.Provider.AdsCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +36
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +287
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +92
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1297
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +19
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +142
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +73
   System.Web.UI.WebControls.FormView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
   System.Web.UI.WebControls.FormView.EnsureDataBound() +166
   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +72
   System.Web.UI.Control.EnsureChildControls() +87
   System.Web.UI.Control.PreRenderRecursiveInternal() +44
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +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.
0
guru_samiCommented:
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(object sender, SqlDataSourceSelectingEventArgs e)
    {
        e.Command.Parameters["@Number"].Value = 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_SelectedIndexChanged(object sender, EventArgs e)
    {
        GridView gv2 = (GridView)LoginView1.FindControl("GridView2");
        SqlDataSource sqlAdsDetails = (SqlDataSource)LoginView1.FindControl("sqlAdsDetails");
        FormView fv1 = (FormView)LoginView1.FindControl("FormView1");

        number = Convert.ToInt32(gv2.SelectedValue);
        fvDatabind(number);
    }

protected void fvDataBind(int num)
{
  string query ="Select * from Employee Where Number=" + num;
  //do db query...

fv1.DataSource=//DBresultset..
fv1.DataBind();
}

0
GY1680Author Commented:
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.
0
guru_samiCommented:
yes...that's correct....you need to assign DataSourceID="sqlAdsDetails" for the FormView.
0
GY1680Author Commented:
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.
0
GY1680Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.