Updating datatable

Nikos · October 28th, 2004, 07:39 PM

Hi

I am building a Windows application using VSNet 2003.

I am using a DataTable in a dataset and I am binding the fields to a datagrid and a text box.
When moving to another row the text box displays the field named description.

When I add a new record or update one I fire the sub with the update,delete or insert command with a button.

If the last control before the click is the textbox the changes in the textbox are not saved unless I move to another record or click in a datagrids cell first.The changes in the datagrid are saved fine.

What I have to do to be able to insert or update properly?

here is my ins command
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        Dim insert_sqc As New SqlCommand
        With insert_sqc
            .CommandText = _
                    " INSERT INTO AccidentExtraBenefits_T(Description, AsfPoso, Rate, Asfalistro, PolicyID)" & _
                    " VALUES (@Description, @AsfPoso, @Rate, @Asfalistro, @PolicyID);" & _
                    " SELECT ID, Description, AsfPoso, Rate, Asfalistro, PolicyID" & _
                    " FROM AccidentExtraBenefits_T WHERE (ID = @@IDENTITY)"
            .Connection = Conn
            .Parameters.Add(New System.Data.SqlClient.SqlParameter("@Description", System.Data.SqlDbType.VarChar, 50, "Description"))
            .Parameters.Add(New System.Data.SqlClient.SqlParameter("@AsfPoso", System.Data.SqlDbType.Money, 8, "AsfPoso"))
            .Parameters.Add(New System.Data.SqlClient.SqlParameter("@Rate", System.Data.SqlDbType.Real, 4, "Rate"))
            .Parameters.Add(New System.Data.SqlClient.SqlParameter("@Asfalistro", System.Data.SqlDbType.Money, 8, "Asfalistro"))
            .Parameters.Add(New System.Data.SqlClient.SqlParameter("@PolicyID", System.Data.SqlDbType.Int, 4)).Value = PolicyID
        End With

.........similar for update and delete commands and then.......

        Dim DAPosaAsfalisis As New SqlDataAdapter
        With DAPosaAsfalisis
            .DeleteCommand = delete_sqc
            .InsertCommand = insert_sqc
            .UpdateCommand = update_sqc
        End With
        Try
            DAPosaAsfalisis.Update(DSBase.Tables("AccidentExtr aBenefits"))
            DAPosaAsfalisis.Dispose()
            insert_sqc.Dispose()
            update_sqc.Dispose()
            delete_sqc.Dispose()
        Catch
            DAPosaAsfalisis.Dispose()
            insert_sqc.Dispose()
            update_sqc.Dispose()
            delete_sqc.Dispose()
            MsgBox(Err.Description, MsgBoxStyle.Critical)
        End Try
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thank you in advance
Best regards

Nikos

mehdi62b · November 4th, 2004, 04:43 PM

Hello,
Take a look at this example and follow the steps,

Code:

<%@ Import Namespace="System.Data.SqlClient" %>

<Script Runat="Server">

Dim conNorthwind As SqlConnection
Dim cmdSql As SqlCommand
Dim strSql As String

Sub Page_Load
  conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )
  If Not IsPostBack Then
    BindDataGrid
  End If
End Sub

Sub BindDataGrid
  cmdSql = New SqlCommand( "Select * From Products", conNorthwind )
  conNorthwind.Open()
  dgrdProducts.DataSource = cmdSql.ExecuteReader()
  dgrdProducts.DataBind()
  conNorthwind.Close()
End Sub

Sub dgrdProducts_EditCommand( s As Object, e As DataGridCommandEventArgs )
  dgrdProducts.EditItemIndex = e.Item.ItemIndex
  BindDataGrid
End Sub

Sub dgrdProducts_UpdateCommand( s As Object, e As DataGridCommandEventArgs )
  Dim intProductID As Integer
  Dim txtUnitPrice As TextBox
  Dim decUnitPrice As Decimal

  If IsValid Then
    intProductID = dgrdProducts.DataKeys( e.Item.ItemIndex )
    txtUnitPrice = e.Item.FindControl( "txtUnitPrice" )
    decUnitPrice = txtUnitPrice.Text
    strSql = "Update Products Set UnitPrice=@UnitPrice " _
     & "Where ProductID=@ProductID"
    cmdSql = New SqlCommand( strSql, conNorthwind )
    cmdSql.Parameters.Add( "@UnitPrice", decUnitPrice )
    cmdSql.Parameters.Add( "@ProductID", intProductID )
    conNorthwind.Open()
    cmdSql.ExecuteNonQuery()
    conNorthwind.Close()
    dgrdProducts.EditItemIndex = -1
    BindDataGrid
  End If
End Sub

Sub dgrdProducts_CancelCommand( s As Object, e As DataGridCommandEventArgs )
  dgrdProducts.EditItemIndex = -1
  BindDataGrid
End Sub

</Script>

<html>
<head><title>DataGridEditTemplate.aspx</title></head>
<body>
<form Runat="Server">

<asp:DataGrid
  ID="dgrdProducts"
  OnEditCommand="dgrdProducts_EditCommand"
  OnUpdateCommand="dgrdProducts_Updatecommand"
  OnCancelCommand="dgrdProducts_CancelCommand"
  DataKeyField="ProductID"
  AutoGenerateColumns="False"
  CellPadding="10"
  HeaderStyle-BackColor="Salmon"
  Runat="Server">
<Columns>
  <asp:BoundColumn
    HeaderText="Product Name"
    DataField="ProductName"
    ReadOnly="True" />
  <asp:TemplateColumn>
  <HeaderTemplate>
    Price
  </HeaderTemplate>
  <ItemTemplate>
    <%# Container.DataItem( "UnitPrice" ) %>
  </ItemTemplate>
  <EditItemTemplate>
    <asp:TextBox
      ID="txtUnitPrice"
      Text='<%# Container.DataItem( "UnitPrice" )%>'
      Runat="Server" />
    <asp:RequiredFieldValidator
      ControlToValidate="txtUnitPrice"
      Display="Dynamic"
      Text="Required!"
      Runat="Server" />
    <asp:CompareValidator
      ControlToValidate="txtUnitPrice"
      Display="Dynamic"
      Text="Must be Currency!"
      Operator="DataTypeCheck"
      Type="Currency"
      Runat="Server" />
  </EditItemTemplate>
  </asp:TemplateColumn>
  <asp:EditCommandColumn
    EditText="Edit!"
    UpdateText="Update!"
    CancelText="Cancel!" />
</Columns>
</asp:DataGrid>

</form>
</body>
</html>

HtH.

--------------------------------------------
Mehdi.:)