Updating table based on checkbox

Question

Can anyone tell whats wrong that is not updating the records. right now when a checkbox is selected and the submit button is clicked, no changes are made to the database table. Dim connstr as string = 'Server=localhost;UID=User;PWD=;Database=' Dim cancel_page as string = 'bscireal.aspx' Dim return_page as string = 'approval.aspx' Dim myda As SqlDataAdapter Dim mycmd As SqlCommand Sub Page_Load(sender As Object, e As EventArgs) Dim rightNow as DateTime = DateTime.Now Dim sql as string if not Page.IsPostBack then sql = 'SELECT RegionID, FirstName + ' ' + LastName + ' - ' + RegionID AS Region FROM dbo.Employees WHERE (JobID = '5') AND (DateLeft IS NULL) AND (EmployeeID IS NOT NULL) and (RegionID is not NuLL) ORDER BY LastName' FillDropDownList(sql,ddlreps) BindDataToGrid('') end if End Sub Private Sub dg_approval_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Dim approval As CheckBox = e.Item.FindControl('app') Dim updval As String Dim myconnection As SqlConnection If Approval.Checked Then updval = '1' Else updval = '0' End If If e.CommandName = 'Update' Then myconnection = New SqlConnection('Server=localhost;UID=;PWD=;Database=') Dim strsql As String = 'UPDATE AccountTransfers SET AccountTransfers.Approved='' & updval & ''' & 'where AccountID=' & dg_approval.DataKeys(CInt(e.Item.ItemIndex)) ' Response.Write(strsql) mycmd = New SqlCommand(strsql, myconnection) mycmd.commandType = CommandType.storedProcedure myconnection.Open() mycmd.ExecuteNonQuery() End If End Sub Sub FillDropDownList(sql as string, ddl as DropDownList) ' Create Connection Object, Command Object, Connect to the database Dim conn as New SQLConnection(connstr) Dim cmd as New SQLCommand(sql,conn) conn.open() ' Execute the SQL, Bind the results to the Dropdown List Control ddl.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection) ddl.DataBind() End Sub Sub BindDataToGrid(rep as string) Dim sql as string Dim conn as New SQLConnection(connstr) Dim cmd as New SQLCommand(sql,conn) conn.open() if rep = '' then cmd = New SQLCommand( 'SELECT dbo.AccountTransfers.AccountID, 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))', conn ) else cmd = New SQLCommand( 'SELECT AccountTransfers.AccountID, 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)) AND @RegionId in (FromRegionId,ToRegionId)', conn ) cmd.Parameters.Add( New SqlParameter( '@RegionID', ddlreps.selectedValue)) end if ' Execute the SQL, Bind the results to the DataGrid Control dg_approval.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection) dg_approval.DataBind() End Sub Sub cmdFilter_Click(sender As Object, e As EventArgs) BindDataToGrid(Request('ddlreps')) End Sub Sub cmdCancel_Click(sender As Object, e As EventArgs) response.redirect(return_page) End Sub Account Transfers Filter by Region: You Have transfered these accounts:

jinwolf · Answer

mycmd.commandType = CommandType.storedProcedure should be: mycmd.commandType = CommandType.Text

Updating table based on checkbox

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (1)