ASP.NET Update Datagrid Problem

I have just modified my existing datagrid to allow a user to add a record following the tutorial found at 4guysfromrolla. However, now that I have the add functionality working, I can no longer edit existing records as I get the error below:

[InvalidCastException: Specified cast is not valid.]
   ASP.budsales_aspx.dgSalesData_Update(Object sender, DataGridCommandEventArgs e) in c:\inetpub\wwwroot\WebJobFiles\u4217\budsales.aspx:273
   System.Web.UI.WebControls.DataGrid.OnUpdateCommand(DataGridCommandEventArgs e) +109
   System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) +507
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26
   System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) +100
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26
   System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +121
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +115
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1277

The code for my datagrid is here:

            <asp:DataGrid id="dgSalesData" runat="server"
            AutoGenerateColumns="False" CellPadding="4"
            HeaderStyle-BackColor="Blue"
            HeaderStyle-ForeColor="White"
            HeaderStyle-HorizontalAlign="Center"
            HeaderStyle-Font-Bold="True"
            DataKeyField="SaleID"
            OnItemCommand="doInsert"
              ShowFooter="True"

            EditItemStyle-BackColor="#eeeeee"

            OnEditCommand="dgSalesData_Edit"
            OnUpdateCommand="dgSalesData_Update"
            OnCancelCommand="dgSalesData_Cancel"
            OnDeleteCommand="dgSalesData_Delete">

            <Columns>
      <asp:TemplateColumn HeaderText="Week No">
        <FooterTemplate>
          <asp:TextBox ID="add_weekNo" Columns="4" Runat="Server" />
        </FooterTemplate>
        <ItemTemplate>
          <%# Container.DataItem("WkNo") %>
        </ItemTemplate>
        <EditItemTemplate>
          <asp:TextBox ID="wk_id" Columns="4"
              Text='<%# Container.DataItem("WkNo") %>' Runat="server" />
        </EditItemTemplate>
      </asp:TemplateColumn>

        <asp:TemplateColumn HeaderText="TechProbs">
        <FooterTemplate>
          <asp:TextBox ID="add_techprobs" Columns="4" Runat="Server" />
        </FooterTemplate>
        <ItemTemplate>
          <%# Container.DataItem("TechProbs") %>
        </ItemTemplate>
        <EditItemTemplate>
          <asp:TextBox ID="tech_id" Columns="4"
              Text='<%# Container.DataItem("TechProbs") %>' Runat="server" />
        </EditItemTemplate>
      </asp:TemplateColumn>

            <asp:TemplateColumn HeaderText="Service Line">
        <FooterTemplate>
          <asp:TextBox ID="add_service" Columns="4" Runat="Server" />
        </FooterTemplate>
        <ItemTemplate>
          <%# Container.DataItem("ServiceLine") %>
        </ItemTemplate>
        <EditItemTemplate>
          <asp:TextBox ID="service_id" Columns="4"
              Text='<%# Container.DataItem("ServiceLine") %>' Runat="server" />
        </EditItemTemplate>
      </asp:TemplateColumn>

            <asp:TemplateColumn HeaderText="Resolved">
        <FooterTemplate>
          <asp:TextBox ID="add_resolved" Columns="4" Runat="Server" />
        </FooterTemplate>
        <ItemTemplate>
          <%# Container.DataItem("Resolved") %>
        </ItemTemplate>
        <EditItemTemplate>
          <asp:TextBox ID="resolved_id" Columns="4"
              Text='<%# Container.DataItem("Resolved") %>' Runat="server" />
        </EditItemTemplate>
      </asp:TemplateColumn>

            <asp:TemplateColumn HeaderText="Report">
        <FooterTemplate>
          <asp:TextBox ID="add_report" Columns="4" Runat="Server" />
        </FooterTemplate>
        <ItemTemplate>
          <%# Container.DataItem("Report") %>
        </ItemTemplate>
        <EditItemTemplate>
          <asp:TextBox ID="report_id" Columns="4"
              Text='<%# Container.DataItem("Report") %>' Runat="server" />
        </EditItemTemplate>
      </asp:TemplateColumn>
      <asp:EditCommandColumn HeaderText ="Options" EditText="Edit Info" ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" />
      <asp:TemplateColumn HeaderText="Delete">
        <FooterTemplate>
          <asp:Button CommandName="Insert" Text="Add" ID="btnAdd" Runat="server" />
        </FooterTemplate>
        <ItemTemplate>
          <asp:Button CommandName="Delete" Text="Delete" ID="btnDel" Runat="server" ButtonType="PushButton"/>
        </ItemTemplate>
      </asp:TemplateColumn>

       </Columns>
        </asp:DataGrid>

and the offending line of code is here:

Dim intWkNo as integer = CType(e.Item.Cells(0).Controls(0), TextBox).Text

I am not sure why it wont convert the textbox values from the edit??

Many thanks in advance,
jamie

j_young_80Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

meet_zorrerCommented:
Hi !

kindly post all of your code behind, aspacially Form_Load

Regards,
Meet
0
David H.H.LeeCommented:
j_young_80,
You need to cast the existing datatype to integer type.
eg:
Instead of
Dim intWkNo as integer = CType(e.Item.Cells(0).Controls(0), TextBox).Text

Change to
Dim intWkNo as integer = Int32.Parse(CType(e.Item.Cells(0).Controls(0), TextBox).Text)

Regards
x_com
0
j_young_80Author Commented:
x_com,
I have replaced the line as you suggested and I get the same error on the same line....

Meet,
I have included the code here ....thanks..

                   Public Sub Page_Load(Sender As Object, E As EventArgs)

                      'Only load the dataset on the first time the page is loaded
                      if not Page.ispostback then

                       objOutletid =  CType(Context.Handler, budoutlets)

                       txtOutletID.Value = objOutletid.GetOutletid
                       txtOutletName.Value = objOutletid.GetOutletName

                       bindPageData(objOutletid.GetOutletid)
                       BindWeekData()

                      end if

                      showSaleID()
                      user_info.Text = ""
                      connection_error.Text = ""

                   End sub

                      Sub bindPageData(outletid)

                        Dim con as sqlConnection
                        Dim strSQL, ConStr as string

                               try
                                       conStr = "Data Source=xxxxx"
                                       con=new sqlConnection(conStr)

                                       con.open()

                                           strSQL = "SELECT * FROM Sales WHERE OutletID = '" & outletid & "'"

                                           Dim myDataSet As DataSet = new DataSet()
                                           Dim myDataAdapter As SqlDataAdapter = new SqlDataAdapter(strSQL, con)
                                           Dim myTable As DataTable

                                           myDataAdapter.Fill(myDataSet, "Sales")

                                           myTable = myDataSet.Tables("Sales") 'creates an instance of the table

                                           'Fill the drop down with the required batch no data
                                             dropWeekNo.datasource = myDataSet
                                             dropWeekNo.datatextfield = "WkNo"
                                             dropWeekNo.databind()

                                        'close the connection to the database
                                        con.close()

                               catch expMsg as exception

                                  connection_error.text = expMsg.Message 'debug the error to the user

                              end try

                    End Sub

             Sub BindWeekData()

                 Dim constr, strSQL as string
                 dim con as sqlconnection
                 Dim intOutletID as integer

                     '1. Create a connection
                         conStr = "Data Source=xxxxxx"

                         con=new sqlConnection(conStr)
                         intOutletID = txtOutletID.Value

                     '2. Create the command object, passing in the SQL string
                     strSQL = "SELECT WkNo, SaleID, TechProbs, ServiceLine, Resolved, Report FROM Sales WHERE (OutletID = " & intOutletID & ") ORDER BY WkNo DESC "
                     Dim myCommand as New SqlCommand(strSQL, con)

                 con.open()

                     'Set the datagrid's datasource to the datareader and databind

                     dgSalesData.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

                     dgSalesData.DataBind()
            End Sub
             Sub dgSalesData_Update(sender As Object, e As DataGridCommandEventArgs)

                     Dim constr as string
                     dim con as sqlconnection

                     'Read in the values of the updated row
                     Dim intWkNo as integer = Int32.Parse(CType(e.Item.Cells(0).Controls(0), TextBox).Text)
                     'Dim SaleID as Integer = e.item.Cells(2).Text

                     Dim strSaleID as String = dgSalesData.DataKeys(e.Item.ItemIndex)

                     Dim boolTechProbs as Boolean = CType(e.Item.Cells(2).Controls(0), TextBox).Text
                     Dim boolService as Boolean = CType(e.Item.Cells(3).Controls(0), TextBox).Text
                     Dim boolResolved as Boolean = CType(e.Item.Cells(4).Controls(0), TextBox).Text
                     Dim boolReport as Boolean = CType(e.Item.Cells(5).Controls(0), TextBox).Text


                     'Construct the SQL statement using Parameters
                    Dim strSQL as String = "UPDATE [Sales] SET [TechProbs] = @TechProbs,[ServiceLine] = @ServiceLine,[Resolved] = @Resolved, [Report] = @REport WHERE [SaleID] = " & strSaleID & ""

                     '1. Create a connection
                         conStr = "Data Source=xxxxxx"

                         con=new sqlConnection(conStr)

                    '2. Create the command object, passing in the SQL string
                    Dim myCommand as New SqlCommand(strSQL, con)

                   'open the connection
                   con.open()

                      ' Add Parameters to the SQL query
                      myCommand.Parameters.Add("@WkNo", SqlDbType.int, 4).Value = intWkNo
                      'myCommand.Parameters.Add("@SaleID", SqlDbType.int, 4).Value = SaleID
                      myCommand.Parameters.Add("@TechProbs", SqlDbType.bit, 1).Value = boolTechProbs
                      myCommand.Parameters.Add("@ServiceLine", SqlDbType.bit, 1).Value = boolService
                      myCommand.Parameters.Add("@Resolved", SqlDbType.bit, 1).Value = boolResolved
                      myCommand.Parameters.Add("@Report", SqlDbType.bit, 1).Value = boolReport

                      'Execute the UPDATE query
                      myCommand.ExecuteNonQuery()

                      'Close the connection
                      con.Close()

                      connection_error.text = "Records updated on the database."

                      'Finally, set the EditItemIndex to -1 and rebind the DataGrid
                      dgSalesData.ShowFooter = True
                      dgSalesData.EditItemIndex = -1
                      BindWeekData()
                      BindPageData(txtOutletID.Value)


         End Sub

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

David H.H.LeeCommented:
j_young_80 ,
I saw this line, please change it :
** If table field - "saleID" is string type, should be put single quotes around it, missing 1 quotes here :
Original :
 Dim strSQL as String = "UPDATE [Sales] SET [TechProbs] = @TechProbs,[ServiceLine] = @ServiceLine,[Resolved] = @Resolved, [Report] = @REport WHERE [SaleID] = " & strSaleID & ""

Change to
 Dim strSQL as String = "UPDATE [Sales] SET [TechProbs] = @TechProbs,[ServiceLine] = @ServiceLine,[Resolved] = @Resolved, [Report] = @REport WHERE [SaleID] = '" & strSaleID & ""

** If table field - "saleID" is integer type, change to this :
 Dim strSQL as String = "UPDATE [Sales] SET [TechProbs] = @TechProbs,[ServiceLine] = @ServiceLine,[Resolved] = @Resolved, [Report] = @REport WHERE [SaleID] = " & Int32.Parse(strSaleID)

If all the solutions not your main problems, and your problems is related with
Dim intWkNo as integer = Int32.Parse(CType(e.Item.Cells(0).Controls(0), TextBox).Text)
-you can use others method to get the control.
eg:
Dim intWkNo as integer = Int32.Parse(CType(e.Item.FindControl("add_weekNo"), TextBox).Text)

If that still not solve your problems, please paste your full source code here and state it the ERROR details - points with current error lines. This will more easy for me to figure out the problems here.

Regards
x_com
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
j_young_80Author Commented:
x_com,
I used the findcontrol method to reference the textbox and it worked fine.
thanks for all your help!
jamie
0
David H.H.LeeCommented:
Glad to help, j_young_80

Regards
x_com
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

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.