• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2126
  • Last Modified:

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

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
Dovberman
Asked:
Dovberman
  • 3
  • 2
  • 2
1 Solution
 
Rog DManager Inforamtion SystemsCommented:
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
 
Rog DManager Inforamtion SystemsCommented:


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

Roger
0
 
raja_ind82Commented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
raja_ind82Commented:
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
 
DovbermanAuthor Commented:
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
 
DovbermanAuthor Commented:
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
 
DovbermanAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now