Hi every1, I have trying to learn datagrid to do simple things but
since day 1 running into problems after problems for really simple
things.

Problem on hand: Display a datagrid containing records from the
database & let user to update few fields. Out of those fields one
should be multi-line (so user can scroll up & down).

So for that particular field I've used textbox (since boundcolumn
doesn't have any property like that...).

After running into problems one after another I finally able to figure
it out how to update the database (SQL Server) for that textbox when
user clicks on the update link.

However when I clicked on the update link 2 things are not happening
first the datagrid is not showing up after the page load.  User has to
click on a button to see the datagrid again (not a good design...) &
the values (text) is not displaying in that particular column.
When I open up the database I can see the updated text but why I can't
see it in the datagrid?

I'm posting the aspx code & HTML for datagrid.

Thanks in advance for your help.

Sub dgdSearch_Update(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)
'Read in the values of the updated row
Dim DemoNumber As Integer = dgdSearch.DataKeys(e.Item.ItemIndex)
'Dim strCustomerName As String = CType(e.Item.Cells(1).Controls(0),
TextBox).Text
'Dim strCustomerNumber As String = CType(e.Item.Cells(2).Controls(0),
TextBox).Text
Dim strSerialNumber As String = CType(e.Item.Cells(3).Controls(0),
TextBox).Text
'Dim strModelNumber As String = CType(e.Item.Cells(4).Controls(0),
TextBox).Text
'Dim strRMANumber As String = CType(e.Item.Cells(5).Controls(0),
TextBox).Text
'Dim strConditionNumber As String = CType(e.Item.Cells(6).Controls(0),
TextBox).Text
'Dim strLocationNumber As String = CType(e.Item.Cells(7).Controls(0),
TextBox).Text
Dim strStatus As String = CType(e.Item.Cells(8).Controls(0),
TextBox).Text
'Dim strRevisedDate As Date = CType(e.Item.Cells(9).Controls(0),
TextBox).Text
Dim strNote As String = CType(e.Item.Cells(10).Controls(1),
TextBox).Text


'Construct the SQL statement using Parameters
Dim strSQL As String = "UPDATE [Equip] SET [SerialNumber] =
@SerialNumber, " & _
                       "[Status] = @Status, [Note] = @Note " & _
                       "WHERE [DemoNumber] = @DemoNumber"

' Create Instance of Connection and Command Object
'1. Create a connection
Const strConnString As String = ("server=(local);Integrated
Security=SSPI;database=Equipment Log")

Dim objConn As New SqlConnection(strConnString)
objConn.Open()

Dim myCommand As SqlCommand = New SqlCommand(strSQL, objConn)
myCommand.CommandType = CommandType.Text

' Add Parameters to the SQL query
Dim parameterDemoNumber As SqlParameter = New
SqlParameter("@DemoNumber", SqlDbType.Int, 4)
parameterDemoNumber.Value = DemoNumber
myCommand.Parameters.Add(parameterDemoNumber)

Dim parameterSerialNumber As SqlParameter = New
SqlParameter("@SerialNumber", SqlDbType.VarChar, 10)
parameterSerialNumber.Value = strSerialNumber
myCommand.Parameters.Add(parameterSerialNumber)

Dim parameterStatus As SqlParameter = New SqlParameter("@Status",
SqlDbType.VarChar, 10)
parameterStatus.Value = strStatus
myCommand.Parameters.Add(parameterStatus)

Dim parameterNote As SqlParameter = New SqlParameter("@Note",
SqlDbType.VarChar, 500)
parameterNote.IsNullable = True
parameterNote.Value = strNote
myCommand.Parameters.Add(parameterNote)

'Execute the UPDATE query
myCommand.ExecuteNonQuery()

objConn.Close()

'Finally, set the EditItemIndex to -1 and rebind the DataGrid
dgdSearch.EditItemIndex = -1
'BindData()

'refresh the datagrid
dgdSearch.DataBind()
End Sub

*******************************************************************

Datagrid's HTML

<asp:datagrid id="dgdSearch"
style="Z-INDEX: 103; LEFT: 48px;
POSITION: absolute; TOP: 504px"
runat="server" Width="272px"
BackColor="White" Height="192px"
CellSpacing="1" AllowSorting="True"
DataMember="Equip" DataKeyField="DemoNumber"
AutoGenerateColumns="False" AllowCustomPaging="True"
AllowPaging="True" GridLines="Horizontal" CellPadding="3"
BorderWidth="1px" BorderStyle="None"
BorderColor="#E7E7FF" ShowFooter="True"
OnCancelCommand="dgdSearch_Cancel" OnEditCommand="dgdSearch_Edit"
OnUpdateCommand="dgdSearch_Update">

<FooterStyle ForeColor="#4A3C8C" BackColor="#B5C7DE"></FooterStyle>
<SelectedItemStyle Font-Bold="True" ForeColor="#F7F7F7"
BackColor="#738A9C"></SelectedItemStyle>

<AlternatingItemStyle BackColor="#F7F7F7"></AlternatingItemStyle>
<ItemStyle ForeColor="#4A3C8C" BackColor="#E7E7FF"></ItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="#F7F7F7"
BackColor="#4A3C8C"></HeaderStyle>
<PagerStyle HorizontalAlign="Right" ForeColor="#4A3C8C"
Position="TopAndBottom" BackColor="#E7E7FF"
Mode="NumericPages"></PagerStyle>

<Columns>
<asp:BoundColumn DataField="DemoNumber" ReadOnly="True"
HeaderText="Demo Number"></asp:BoundColumn>

<asp:BoundColumn DataField="CustomerName" ReadOnly="True"
HeaderText="Customer Name"></asp:BoundColumn>

<asp:BoundColumn DataField="CustomerNumber" ReadOnly="True"
HeaderText="Customer Number"></asp:BoundColumn>

<asp:BoundColumn DataField="SerialNumber" HeaderText="Serial
Number"></asp:BoundColumn>

<asp:BoundColumn DataField="ModelNumber" ReadOnly="True"
HeaderText="Model Number"></asp:BoundColumn>

<asp:BoundColumn DataField="RMANumber" ReadOnly="True" HeaderText="RMA
Number"></asp:BoundColumn>

<asp:BoundColumn DataField="ConditionNumber" ReadOnly="True"
HeaderText="Condition Number"></asp:BoundColumn>

<asp:BoundColumn DataField="LocationNumber" ReadOnly="True"
HeaderText="Location Number"></asp:BoundColumn>

<asp:BoundColumn DataField="Status"
HeaderText="Status"></asp:BoundColumn>

<asp:BoundColumn DataField="RevisedDate" ReadOnly="True"
HeaderText="Revised Date"></asp:BoundColumn>

<asp:TemplateColumn HeaderText="Notes">
<EditItemTemplate>
<asp:TextBox id="txtNote" TextMode="MultiLine" runat="server" Text='<%#
DataBinder.Eval(Container, "DataItem.Note") %>'>
</asp:TextBox>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update"
CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
</Columns>
</asp:datagrid>

http://www.datagridgirl.com/faq.aspx

http://www.datagridgirl.com/articles.aspx

Good luck!

Hi Riaz,

About your problems

1. after updating DataGrid not showing
You should redo bind data source to datagrid  rather than
simply calling datagrid.DataBind(). For example, you have
a method BindDataGrid like 

Private Sub BindDataGrid()
      Dim dap As New SqlDataAdapter("Select * from Equip",
connString)
        Dim tbl As New DataTable
        dap.Fill(tbl)
        Me.dgdSearch.DataSource = tbl
        Me.dgdSearch.DataBind()
End Sub

In the end of dgdSearch_UpdateCommand, Call the method. If
you don't do it, the datagrid will lose the data source,
so it will not be shown.

2. No change in database after updating (and no exception).
I don't see your whole code.

I don't read your whole code. I can only guess. Since no
exception, so updating executes successfully. But all
parameters are original values. If it's true, in Page_Load
method you bind data source to the datagrid every time no
matter it's postback or not. If that, simply make change
in Page_Load method to

        If Not IsPostBack Then
            BindDataGrid()
        End If

HTH,

Elton Wang
elton_w***@hotmail.com


Show quoteHide quoteable to figure
load.  User has to
but why I can't
(e.Item.ItemIndex)
(1).Controls(0),
(2).Controls(0),
(3).Controls(0),
(4).Controls(0),
(5).Controls(0),
(6).Controls(0),
(7).Controls(0),
(9).Controls(0),
@Note " & _
Show quoteHide quoteBackColor="#B5C7DE"></FooterStyle>
BackColor="#F7F7F7"></AlternatingItemStyle>
BackColor="#E7E7FF"></ItemStyle>
Show quoteHide quoterunat="server" Text='<%#
EditText="Edit"></asp:EditCommandColumn>
Show quoteHide quote

Hi Elton, thanks for your detail response.
In the page load I do have the BindData().

I have replace the BindData() with yours, I have also called this in
Update.
Problem solved.
When I clicked on the update after the page load I can see the
datagrid.

However, the field I just updated is not showing the text infact, is
not showing any text at all. The whole column is empty.
When open the database (SQL Sever) I can the see the updated text.
What do you think is the problem here.

Thanks for your help in advance.

Coding in update like this:

Sub dgdSearch_Update(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs)
    ' execute update functionality
     myCommand.ExecuteNonQuery()
     objConn.Close()
     dgdSearch.EditItemIndex = -1
     BindDataGrid() ' the method binding data source of
datagrid
End Sub


called this in
Show quoteHide quote

Hi Elton, thanks for your suggestion but unfortunately it didn't work.
I still have the same problem that has to do with datagrid display.

I will try to explain the problem as much as possible.
Lets say I have 11 columns nameds as column1, column2, ..... column10,
column11

Out of 11 I'm updating only 3 of them.(column4, column9, & column11).

For column4 & column9 I'm using  boundcolumn.
For column 11 I'm using textbox.

I can't post the picture of the datagrid here but I'll try to type it
so u can have some idea.


C1     C2      C3        C4          C5    C6  C7  C8  C9      C10
     C11
1001 Test     6t7899  564      Dsp4    7    2    4    Demo  1/1/1900
           Edit
1003 Tere54  890      456            6   1     2
1/1/1900               Edit
1011 Paul     890      tyu563  Integ   4     3    2
1/1/1900               Edit

Out of these 3 records as you can see that C11 is empty, however in the
database it is full. This part I don't understand.

And also when I click on the edit after a page load I can see the text
in datagrid's C11.

Any suggestion as how to solve this problem.

Thanks for your help.

Make following Change to column11

FROM
<asp:TemplateColumn HeaderText="Notes">
<EditItemTemplate>
<asp:TextBox id="txtNote" TextMode="MultiLine"
runat="server" Text='<%#
DataBinder.Eval(Container, "DataItem.Note") %>'>
</asp:TextBox>
</EditItemTemplate>

TO
<asp:TemplateColumn HeaderText="Notes">
<ItemTemplate>
    <asp:Label Text='<%# DataBinder.Eval
(Container, "DataItem.Note") %>' runat=server>
    </asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="txtNote" TextMode="MultiLine"
runat="server" Text='<%#
DataBinder.Eval(Container, "DataItem.Note") %>'>
</asp:TextBox>
</EditItemTemplate>

HTH,

Elton Wang


didn't work.
Show quoteHide quotetry to type it
C9      C10
Demo  1/1/1900
however in the
see the text
Show quoteHide quote

Thanks Elton, that did it. I highly appreciate that you helped me alot.
Thanks for your time.

I had a feeling that something is not right but I was unable to figure
it out.

Appreciate your help.

Problem with datagrid update
problem updating in datagrid ??
How to refresh a parent grid?
DataTable consisting of 2 other DataTables
Sorting underlying data
Hyperlink Column Event Handler...
Dynamically adding DataGrid columns
Datagrid Footer now showing up correctly
datagrid(master) with a datalist(detail)
Converting SQL Server "bit" field to a CLR boolean-- has to be a better way