none
having trouble updating a gridview using a VIEW - from 2 tables are left joined. RRS feed

  • Question

  • My ERROR:  - have no idea how to find this.

    Must declare the scalar variable "@UserName".

    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: System.Data.SqlClient.SqlException: Must declare the scalar variable "@UserName".

    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:

    [SqlException (0x80131904): Must declare the scalar variable "@UserName".] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +386 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +325 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +92 System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +907 System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +704 System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37 System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +123 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +118 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +166 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565 


    Must declare the scalar variable "@UserName".
    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: System.Data.SqlClient.SqlException: Must declare the scalar variable "@UserName".

    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:


    [SqlException (0x80131904): Must declare the scalar variable "@UserName".]
       System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
       System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
       System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
       System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
       System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
       System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
       System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
       System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +386
       System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +325
       System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +92
       System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +907
       System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +704
       System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95
       System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
       System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +123
       System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
       System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +118
       System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +166
       System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
       System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
       System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

     
    END ()

    MY Code - .aspx

    <body>

     

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

     

    <div>

     

    <asp:Image ID="imageLogo" runat="server" ImageAlign="Left" />

     

    &nbsp;&nbsp;

     

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"

     

    Width="600px" DataKeyNames="picNumber" OnRowDataBound="GridView1_RowDataBound" Height="800px" OnRowCommand="GridView1_RowCommand">

     

    <Columns>

     

    <asp:BoundField DataField="picName" HeaderText="Name" SortExpression="picName" ReadOnly="True" >

     

    <ItemStyle Width="20px" />

     

    </asp:BoundField>

     

    <asp:BoundField DataField="picDesc" HeaderText="Description" SortExpression="picDesc" ReadOnly="True" >

     

    <ItemStyle Width="200px" />

     

    </asp:BoundField>

     

    <asp:ImageField DataImageUrlField="URLThumbnail" ReadOnly="True" HeaderText="Small Image">

     

    <ItemStyle VerticalAlign="Middle" />

     

    </asp:ImageField>

     

    <asp:HyperLinkField HeaderText="View Big Image" Target="_blank" DataNavigateUrlFields="URLBigImage" Text="View" >

     

    <ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" />

     

    </asp:HyperLinkField>

     

    <asp:BoundField DataField="BidAmt" HeaderText="Current Bid" ReadOnly="True" SortExpression="BidAmt" >

     

    <ItemStyle Width="10px" />

     

    </asp:BoundField>

     

    <asp:TemplateField HeaderText="New Bid">

     

    <ItemTemplate>

     

    <asp:ListBox ID="gvlbNewBid" runat="server">

     

    </asp:ListBox>

     

    </ItemTemplate>

     

    </asp:TemplateField>

     

    <asp:ButtonField ButtonType="Button" Text="Bid" CausesValidation="True" CommandName="Update" HeaderText="Place Bid" ShowHeader="True" />

     

    </Columns>

     

    </asp:GridView>

     

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" OldValuesParameterFormatString="original_{0}" ConnectionString="<%$ ConnectionStrings:RelayLifeConnection %>"

     

    SelectCommand="SELECT [picName], [picDesc], [URLThumbnail], [URLBigImage], [BidAmt], [picNumber] FROM [vwRelayBid] WHERE ([Year] = @Year)"

    UpdateCommand="UPDATE vwRelayBid SET UserName = @UserName, BidAmt = @BidAmt WHERE (picNumber = @original_picNumber)"

    InsertCommand="INSERT INTO RelayLifeBid(UserName, Amt) VALUES (@UserName, @Amt)" OnInserting="SqlDataSource1_Inserting">

     

    <SelectParameters>

     

    <asp:QueryStringParameter Name="Year" QueryStringField="year" Type="Int32" />

     

    </SelectParameters>

     

    <InsertParameters>

     

    <asp:SessionParameter Name="@UserName" SessionField="sesUser" />

     

    <asp:SessionParameter Name="@Amt" SessionField="bidAmt" />

     

    </InsertParameters>

     

    <UpdateParameters>

     

    <asp:SessionParameter Name="@UserName" SessionField="sesUser" />

     

    <asp:SessionParameter Name="@BidAmt" SessionField="bidAmt" />

     

    </UpdateParameters>

     

    </asp:SqlDataSource>

     

    </div>

     

    </form>

    </body>


    -C# code

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)

    {

    if (e.Row.RowType == DataControlRowType.DataRow)

    {

    ListItem li = new ListItem();

    DataRowView rv = (DataRowView)e.Row.DataItem;

    decimal oldamt = Convert.ToDecimal(rv["BidAmt"]);

    ListBox lb = (ListBox)e.Row.FindControl("gvlbNewBid");

    for(int i=0;i<=50;i++)

    {

    decimal newAmt = oldamt + (decimal)i;

    ListItem z = new ListItem(newAmt.ToString(), i.ToString());

    lb.Items.Add(z);

    }

    }

    }

    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

    {

    if (e.CommandName.CompareTo("Update") == 0)

    {

    // Bid button was clicked

     

    int indexNbr = Convert.ToInt32(e.CommandArgument);

    GridViewRow row = GridView1.Rows[indexNbr];

    Int32 oldPicNbr = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value);

    ListBox lb = (ListBox)row.FindControl("gvlbNewBid");

    decimal newVal = Convert.ToDecimal(lb.Text);

    Session["picNbr"] = Convert.ToString(oldPicNbr);

    Session["bidAmt"] = Convert.ToString(newVal);

    }

    }


    my problem: notice that when I get to here, I can find the oldPicNbr = is a view.
    get my new value. Need to update. HOwever, the first time, there are no records in my view. Because it is LEFT JOINED.






    SQL STMT
    SELECT     TOP (100) PERCENT dbo.RelayLifeHdr.picNumber, dbo.RelayLifeHdr.picName,
                          dbo.RelayLifeYear.URL + dbo.RelayLifeHdr.picJPGThumb AS URLThumbnail,
                          dbo.RelayLifeYear.URL + dbo.RelayLifeHdr.picJPGName AS URLBigImage, dbo.RelayLifeHdr.picDesc, dbo.RelayLifeBid.UserName,
                          ISNULL(dbo.RelayLifeBid.Amt, 0) AS BidAmt, dbo.RelayLifeHdr.Year
    FROM         dbo.RelayLifeYear INNER JOIN
                          dbo.RelayLifeHdr ON dbo.RelayLifeYear.Year = dbo.RelayLifeHdr.Year LEFT OUTER JOIN
                          dbo.RelayLifeBid ON dbo.RelayLifeHdr.picNumber = dbo.RelayLifeBid.picNumber
    ORDER BY dbo.RelayLifeHdr.picNumber

    --
    I have tried everything. Now, I need some assistance. I'm a new c# developer. However, I've programmed on RPG,COBOL on IBM. So, I know logic.
    I have c# training in that I have gone to school. I have a book on my desk as a good reference.
    Up to this point, I have been struggling along. But, I have learned alot.

    --
    Your help is appreciated in advance.



    Thursday, March 5, 2009 9:35 PM

All replies