Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004

    Unanswered: Update table based on check box state in DataGrid

    I basically copied this code from someone, replaced their table and column names with mine, and this still does not update my table. I can't seem to figure out what's wrong!! Here are the code parts that matter:

    Dim myconnection As SqlConnection
    Dim myda As SqlDataAdapter
    Dim mycmd As SqlCommand
    Dim ds As DataSet
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    If Not Page.IsPostBack Then
    End If
    End Sub
    Sub bindData()
            myconnection = New 
    SqlConnection("Server=localhost;UID=3532535r;PWD=345345;Database=  54325")
            myda = New SqlDataAdapter("SELECT dbo.AccountTransfers.AccountID, dbo.AccountTransfers.Approved, 
    dbo.Accounts.AccountName, dbo.Territories.TerritoryName, Territories_1.TerritoryName AS Expr1, 
    AccountTransfers.EffectiveDate, AccountTransfers.Explanation FROM dbo.AccountTransfers INNER JOIN 
    dbo.Territories ON dbo.AccountTransfers.FROMTerritoryID = dbo.Territories.TerritoryID INNER JOIN 
    dbo.Territories Territories_1 ON dbo.AccountTransfers.ToTerritoryID = Territories_1.TerritoryID INNER JOIN 
    dbo.Accounts ON dbo.AccountTransfers.AccountID = dbo.Accounts.AccountID WHERE (effectiveDate = 
    convert(datetime,convert(nvarchar,getdate(),101))-day(getdate())+1 AND (Approved = 0))", myconnection)
            ds = New DataSet()
            myda.Fill(ds, "AllTables")
            dg_approval.DataSource = ds
    End Sub
    Private Sub dg_approval_ItemCommand(ByVal source As Object, _
        ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)
    Dim prodchknew As CheckBox = e.Item.FindControl("chk")
    Dim updval As String
    If prodchknew.Checked Then
                updval = "1"
                updval = "0"
    End If
    If e.CommandName = "Update" Then
                myconnection = New 
    SqlConnection("Server=localhost;UID=EndoscopySqlUser;PWD=2er@9b76  m;Database=Endoscopy")
                Dim strsql As String = "Update AccountTransfers set Approved='" & updval & "'" & _
             " where AccountID=" & dg_approval.DataKeys(CInt(e.Item.ItemIndex))
                ' Response.Write(strsql)
                mycmd = New SqlCommand(strsql, myconnection)
    End If
    End Sub
    And the datagrid: 
    <asp:datagrid id="dg_approval" runat="server" AutoGenerateColumns="False" DataKeyField="AccountID"  Font-Names="Verdana" Font-Size="xx-small">
    	<asp:TemplateColumn HeaderText="Approve Account">
    	    <asp:CheckBox id=chk runat="server" Text =<%# DataBinder.Eval(Container.DataItem, "AccountID") %> checked=<%# DataBinder.Eval(Container.DataItem, "Approved") %> />
                        <asp:BoundColumn DataField="accountname" HeaderText="Account Name"></asp:BoundColumn>
                        <asp:BoundColumn DataField="TerritoryName" HeaderText="From Territory"></asp:BoundColumn>
                        <asp:BoundColumn DataField="Expr1" HeaderText="To Territory"></asp:BoundColumn>
    		    <asp:BoundColumn DataField="Explanation" HeaderText="Explanation"></asp:BoundColumn>
                  	    <asp:BoundColumn DataField="effectivedate" HeaderText="Effective Date"></asp:BoundColumn>
    		    <asp:BoundColumn DataField="approved" HeaderText="Approved"></asp:BoundColumn>	

  2. #2
    Join Date
    Nov 2003
    Christchurch, New Zealand
    When you say it won't update your table what do you mean? Is it raising an error or does it run find but the update doesn't change the record you are trying to change?

    I suspect it is the second option.

    I suspect it is because your update statement is using the wrong value in the where clause and becuase of this it is trying to update a record that doesn't exist (or it may exist but it's not the one your are looking at).

    Uncomment the line the writes out the sql statement and see what you are trying to execute against the database. If it looks right put it into the query analyzer and execute the sql statement and examine the results.

  3. #3
    Join Date
    Jul 2003
    This looks like VB not ASP... there are subtle differences between the two, especially when DIMing new objects...
    That which does not kill me postpones the inevitable.

  4. #4
    Join Date
    Nov 2003
    Christchurch, New Zealand
    Actually looks like asp.Net using VB.Net

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts