?
Solved

Gridview template column does not update. SQL Server 2005 Visual Studio 2005 ASP.NET

Posted on 2007-09-28
7
Medium Priority
?
2,121 Views
Last Modified: 2013-11-26
Gridview template column does not update.

The gridview shows the editable column (SecName). The text is changed by entering from the keyboard.
The update button is clicked.
The gridview refreshes with the original value.
No errors are generated.

What am I missing?
Do I need to write code for the ASPX.VB events.
I thought the update command and parameters handled the update.
Here are the important snippets:

ASPX
<asp:SqlDataSource ID="dscSymbols" runat="server" ConnectionString="<%$ ConnectionStrings:StockSelectConnectionString %>"
        DeleteCommand="DELETE FROM [Symbol] WHERE [SymbolID] = @SymbolID"
        InsertCommand="INSERT INTO [Symbol] ([SymbolName], [MarketID], [SecName]) VALUES (@SymbolName, @MarketID, @SecName)"
        SelectCommand="SELECT TOP 20 [SymbolName], [SymbolID], [MarketID],
        [SecName] FROM [Symbol]
        WHERE ([MarketID] = @MarketID)
        ORDER BY [SymbolName]"
       
        UpdateCommand="UPDATE [Symbol]
        SET [SymbolName] = @SymbolName,
        [MarketID] = @MarketID,
        [SecName] = @SecName
        WHERE [SymbolID] = @original_SymbolID">
        <UpdateParameters>
            <asp:Parameter Name="SymbolName" Type="String" />
            <asp:Parameter Name="MarketID" Type="Int32" />
            <asp:Parameter Name="SecName" Type="String" />
            <asp:Parameter Name="original_SymbolID" Type="Int32" />
        </UpdateParameters>    

etc .for asp:SqlDataSource

<asp:GridView ID="grdSymbols" runat="server" AllowPaging="True" PageSize=6 AllowSorting="True"
        AutoGenerateColumns="False" DataSourceID="dscSymbols"
        >
        <Columns>
            <asp:CommandField
            ShowSelectButton="True" SelectText="Details" />
            <asp:CommandField
             ButtonType="Button" ShowEditButton="True"/>
            <asp:BoundField DataField="SymbolName" HeaderText="SymbolName" SortExpression="SymbolName" ReadOnly="True" />
            <asp:TemplateField HeaderText="SecName" SortExpression="SecName">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("SecName") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("SecName") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="MarketID" HeaderText="MarketID" SortExpression="MarketID" ReadOnly="True" />
            <asp:BoundField DataField="SymbolID" HeaderText="SymbolID" InsertVisible="False"
                ReadOnly="True" SortExpression="SymbolID" />
            <asp:CommandField ShowDeleteButton="True"  ButtonType="Button" DeleteText="Remove" />
         
        </Columns>
    </asp:GridView>

 ASPX.VB

Partial Class Symbols
    Inherits System.Web.UI.Page

    Protected Sub optMarket_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles optMarket.SelectedIndexChanged

    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then 'First rendering
            optMarket.SelectedIndex = 0
 
        Else
   
        End If
    End Sub

    Protected Sub btnRefreshGrid_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRefreshGrid.Click
        'Show grdPickToWatch
        Dim intMarketID As Integer = Convert.ToInt32(optMarket.SelectedValue)
        Dim strMarketName As String = optMarket.SelectedItem.Text
        dscSymbols.SelectCommand = _
        "SELECT TOP 20 [SymbolName], [SymbolID], [MarketID], [SecName] " _
        & "FROM [Symbol] WHERE ([MarketID] = " & intMarketID & ") " _
        & "ORDER BY [SymbolName]"
    End Sub

   
End Class
0
Comment
Question by:Dovberman
  • 3
  • 2
  • 2
7 Comments
 
LVL 8

Expert Comment

by:Rog D
ID: 19981849
One thing to look at is the databinding of the template control.  Make sure it is bound to the data in the datasource of the datagrid.

Rog
0
 
LVL 8

Expert Comment

by:Rog D
ID: 19981880


Also check your Update statement to see if the parameter is there for that column.

Roger
0
 
LVL 14

Expert Comment

by:raja_ind82
ID: 19982097
Please copy this both code. i hope this will help you.
   
 <asp:SqlDataSource ID="dscSymbols" runat="server" ConnectionString="<%$ ConnectionStrings:StockSelectConnectionString %>"
        DeleteCommand="DELETE FROM [Symbol] WHERE [SymbolID] = @SymbolID"
        InsertCommand="INSERT INTO [Symbol] ([SymbolName], [MarketID], [SecName]) VALUES (@SymbolName, @MarketID, @SecName)"
        SelectCommand="SELECT TOP 20 [SymbolName], [SymbolID], [MarketID],[SecName] FROM [Symbol] WHERE ([MarketID] =1) ORDER BY [SymbolName]"
        UpdateCommand="UPDATE [Symbol] SET [SecName] = @SecName WHERE [SymbolID] =1">
        <UpdateParameters>
            <asp:Parameter Name="SymbolName" Type="String" />
            <asp:Parameter Name="MarketID" Type="Int32" />
            <asp:Parameter Name="SecName" Type="String" />
            <asp:Parameter Name="original_SymbolID" Type="Int32" />
        </UpdateParameters>  
               <InsertParameters>
            <asp:Parameter Name="SymbolName" Type="String" />
            <asp:Parameter Name="MarketID" Type="Int32" />
            <asp:Parameter Name="SecName" Type="String" />            
        </InsertParameters>
        <SelectParameters >
            <asp:Parameter Name="MarketID" Type="Int32"  DefaultValue="1"/>
        </SelectParameters>
        <DeleteParameters >
            <asp:Parameter Name="SymbolID" Type="Int32" />
        </DeleteParameters>
</asp:SqlDataSource>
    <asp:GridView ID="grdSymbols" runat="server" AllowPaging="True" PageSize=6 AllowSorting="True"
        AutoGenerateColumns="False" DataSourceID="dscSymbols"
        >
        <Columns>
            <asp:CommandField
            ShowSelectButton="True" SelectText="Details" />
            <asp:CommandField
             ButtonType="Button" ShowEditButton="True"/>
            <asp:BoundField DataField="SymbolName" HeaderText="SymbolName" SortExpression="SymbolName" ReadOnly="True" />
            <asp:TemplateField HeaderText="SecName" SortExpression="SecName">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("SecName") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("SecName") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="MarketID" HeaderText="MarketID" SortExpression="MarketID" ReadOnly="True" />
            <asp:BoundField DataField="SymbolID" HeaderText="SymbolID" InsertVisible="False"
                ReadOnly="True" SortExpression="SymbolID" />
            <asp:CommandField ShowDeleteButton="True"  ButtonType="Button" DeleteText="Remove" />
         
        </Columns>
    </asp:GridView>
************************************************************************************
Protected Sub grdSymbols_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles grdSymbols.RowEditing
        EditIndex = e.NewEditIndex
    End Sub

    Protected Sub grdSymbols_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles grdSymbols.RowUpdating
        If (e.RowIndex > 0 Or grdSymbols.PageIndex > 0) Then
            Exit Sub ' only RowIndex 0 on Page 0 is the row we want to insert
        End If

        'find the DataSource for the GridView control
        Dim ds As New System.Web.UI.WebControls.SqlDataSource
        Dim conn As New System.Data.SqlClient.SqlConnection

        ds = FindControl(grdSymbols.DataSourceID)
        Dim aaa As String
        aaa = ds.ConnectionString
        ' Get the DataSource's connection string
        conn.ConnectionString = ds.ConnectionString

        ' open the connection
        conn.Open()
        ' get the Insert command string
        Dim s As String
        s = ds.UpdateCommand

        ' create the new command
        Dim c As New System.Data.SqlClient.SqlCommand
        c.Connection = conn
        c.CommandText = s

        Dim p As New System.Data.SqlClient.SqlParameter
        ' the NewValues collection contains the name value pairs that need to be inserted, make them parameters to the command
        Dim ss As String
        Dim x As System.Collections.DictionaryEntry
        ss = ""

        For Each x In e.NewValues
            ss += "@" + x.Key + "=" + x.Value + ";"
            c.Parameters.Add("@" + x.Key, x.Value)
        Next
        ' execute the command
        c.ExecuteNonQuery()
        ' the GridView framework will execute an update on a row where the identity column=0
        ' since none exists it will silently do nothing, but that is ok
    End Sub

Regards,
M.Raja
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Accepted Solution

by:
raja_ind82 earned 750 total points
ID: 19982111
For my testing i have changed your code and post with the following
[SymbolID] =1
([MarketID] =1

So please check this and change this as you want. i hope you understand...

Thanks
M.Raja
0
 

Author Comment

by:Dovberman
ID: 19982254
raja_ind82:

I am not sure why you set specific default values for MarketID and SymbolID.

I will try the solution you proposed in the morning and give you feedback.

Thanks,

Dovberman
0
 

Author Comment

by:Dovberman
ID: 19982273
Roq
Does the following bind the template control?

<asp:TemplateField HeaderText="SecName" SortExpression="SecName">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("SecName") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("SecName") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>

Is the following a correct Update Statement?

    UpdateCommand="UPDATE [Symbol]
        SET [SymbolName] = @SymbolName,
        [MarketID] = @MarketID,
        [SecName] = @SecName
        WHERE [SymbolID] = @original_SymbolID">
        <UpdateParameters>
            <asp:Parameter Name="SymbolName" Type="String" />
            <asp:Parameter Name="MarketID" Type="Int32" />
            <asp:Parameter Name="SecName" Type="String" />
            <asp:Parameter Name="original_SymbolID" Type="Int32" />
        </UpdateParameters>  

If the only column I want to update is SecName, then will the following work ?

UpdateCommand="UPDATE [Symbol]
        SET   [SecName] = @SecName
        WHERE [SymbolID] = @original_SymbolID">
        <UpdateParameters>
             <asp:Parameter Name="SecName" Type="String" />
            <asp:Parameter Name="original_SymbolID" Type="Int32" />
        </UpdateParameters>  

Thanks,

Dovberman



0
 

Author Comment

by:Dovberman
ID: 19983401
raja ind82 provided the most usefull answer by noting that the grdSymbols_RowEditing event must be coded to insure the update process.
The datasource update properties defined in the aspx file appear to be useless. I have always said "if you want to make sure something happens, then write code"

This is what worked:

Protected Sub grdSymbols_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles grdSymbols.RowUpdating
        Dim intCtr As Integer = 1
        intCtr = 1
        Dim intRowIndex As Integer = e.RowIndex 'Index of row being edited
        'Edited value of the 1st template column is e.NewValues.Item(0)
        Dim strSecName As String = Convert.ToString(e.NewValues.Item(0))
        Dim intSymbolID As Integer = Convert.ToInt32(grdSymbols.Rows(intRowIndex).Cells(5).Text)
        Dim intUpdateType As Integer = 0
        Dim strUpdateSQL As String
        Dim cmdSQL As SqlCommand
        Dim conStockSelector As SqlConnection
        Dim strConnection As String = "Data Source=BURCEL05;Initial Catalog=StockSelectSQL;" _
        & "Integrated Security=SSPI"
        conStockSelector = New SqlConnection(strConnection)

        strUpdateSQL = "UPDATE Symbol " _
          & "SET SecName='" & strSecName & "' " _
          & "WHERE SymbolID=" & intSymbolID & ";"

        'Update the symbol table
        conStockSelector.Open()
        cmdSQL = New SqlCommand(strUpdateSQL, conStockSelector)
        cmdSQL.ExecuteNonQuery()
        conStockSelector.Close()
        Me.dscSymbols.DataBind()

    End Sub
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question