Datagrid ... pain .... HELP!

[aspnetpal]

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>

 

[Roy]

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

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

Good luck!

 

[Elton Wang]

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
(e-mail address removed)

-----Original Message-----
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"

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

<AlternatingItemStyle

<ItemStyle ForeColor="#4A3C8C"

 

[aspnetpal]

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.

 

[Elton Wang]

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

 

[aspnetpal]

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.

 

[Elton Wang]

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

 

[aspnetpal]

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.