Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 878
  • Last Modified:

Create stored procedure

yes I have bounded a field within my datagrid. What I would like to change the item into a checkboxlist and when checked it will be active or none active.

ALTER PROCEDURE dbo.spA1SGetSalesTPR
 
 @UserID varchar(50)
 
AS
 
SELECT	ItemID,
		UPC, 
		[Description],  
		Quantity,
		Price,
		CreatedDate,
		ModifiedDate,
		SizeUnit,
		Stores,
		
		-- This was a check status image but a similar
-- funtion for the check box i need
		--Status =  '<img alt="Sale Item Added" src=..\Images\Add.png >' 
		Case	when Active = 1 Then  '<a><img alt="Active" src=..\Images\document_check.png></a>' 
				Else  '<a><img alt="Not Active" src=..\Images\document_delete.png ></a>' 
				
				 
			End  As 
		Stores
 
FROM A1S.dbo.SalesTPR
 
 
Where @UserID  =  UserID 
 
order by ItemID  DESC
 
DONE:
  RETURN 0
ERRORHANDLER:
  RETURN 1
 
 
 
--vb code
 
Imports System
Imports System.Data
Imports System.Data.SqlClient
 
Public Class SalesTPR
    Inherits System.Web.UI.Page
 
#Region " Web Form Designer Generated Code "
 
    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
 
    End Sub
    Protected WithEvents DoneButton As System.Web.UI.WebControls.Button
    Protected WithEvents lblMessage As System.Web.UI.WebControls.Label
    Protected WithEvents UPCCode As System.Web.UI.WebControls.TextBox
    Protected WithEvents Description As System.Web.UI.WebControls.TextBox
    Protected WithEvents Size As System.Web.UI.WebControls.TextBox
    Protected WithEvents Qty As System.Web.UI.WebControls.TextBox
    Protected WithEvents Price As System.Web.UI.WebControls.TextBox
    Protected WithEvents SizeMeasureList As System.Web.UI.WebControls.DropDownList
    Protected WithEvents WebImageButton1 As Infragistics.WebUI.WebDataInput.WebImageButton
    Protected WithEvents WebImageButton3 As Infragistics.WebUI.WebDataInput.WebImageButton
    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
    Protected WithEvents ListBox1 As System.Web.UI.WebControls.ListBox
    Protected WithEvents WebAsyncRefreshPanel1 As Infragistics.WebUI.Misc.WebAsyncRefreshPanel
 
    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object
 
    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub
 
#End Region
    Dim strselecteditems As String = Nothing
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            RetrieveSalesTPRItems()
            SetFocus(UPCCode, Me)
            LoadSizeMeasure()
 
            With lblMessage
                lblMessage.Visible = True
                .CssClass = "labelGreen"
                .Text = "Total Pages: " & DataGrid1.PageCount.ToString
            End With
 
            Dim StoreList() As String
            Dim I As Integer = 0
 
            StoreList = Session("Stores")
 
            For I = 0 To UBound(StoreList)
                'ListBox1.Items.Add(AddIntoDataStructureofStorelist(StoreList(I)).StoreId.ToString)
                ListBox1.Items.Add(StoreList(I).ToString)
            Next
 
            Me.UPCCode.BackColor = System.Drawing.Color.White
            Me.Description.BackColor = System.Drawing.Color.White
            Me.Size.BackColor = System.Drawing.Color.White
            Me.Qty.BackColor = System.Drawing.Color.White
            Me.Price.BackColor = System.Drawing.Color.White
        End If
    End Sub
 
    Private Sub UPCCode_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UPCCode.TextChanged
        'Searching for UPC code
        If Me.UPCCode.Text <> "" And IsValidIntegerRequired(Me.UPCCode.Text) Then
 
            Dim NameValueOut As String
            Dim SizeValueOut As String
            Dim UOMValueOut As String
 
            Dim conn As New SqlConnection(Application("A1SConnString"))
            Dim cmd As New SqlCommand("spA1SGetUPCCode", conn)
            cmd.CommandType = CommandType.StoredProcedure
            Dim sqlDR As SqlDataReader
            Dim prm As SqlParameter
 
            conn.Open()
            prm = cmd.Parameters.Add("@UPC", SqlDbType.Text)
            prm.Value = UPCCode.Text
            sqlDR = cmd.ExecuteReader
 
            While sqlDR.Read
                NameValueOut = sqlDR("Name")
                SizeValueOut = sqlDR("Size")
                UOMValueOut = sqlDR("UOM")
            End While
 
            If sqlDR.HasRows Then
 
                Me.Description.Text = NameValueOut
                Me.Size.Text = SizeValueOut
                'SizeMeasureList.Items.Clear()
                'SizeMeasureList.Items.Add(UOMValueOut)
                LoadSizeMeasure(UOMValueOut) 'Giving the user option to change SizeMeasureID
 
 
                lblMessage.Visible = False
                Me.Description.ReadOnly = True
                Me.Size.ReadOnly = True
                Me.UPCCode.ReadOnly = True
                Me.UPCCode.BackColor = System.Drawing.Color.White
                Me.Description.BackColor = System.Drawing.Color.White
                Me.Size.BackColor = System.Drawing.Color.White
 
            Else
                With lblMessage
                    lblMessage.Visible = True
                    .CssClass = "labelRed"
                    .Text = "The UPC Code could not be found matching the search term  '" & UPCCode.Text & "' . Please Enter Item Description."
                End With
                Me.UPCCode.BackColor = System.Drawing.Color.White
                Me.Description.BackColor = System.Drawing.Color.PeachPuff
                Me.Size.BackColor = System.Drawing.Color.White
            End If
 
            'Clean Up
            If (Not sqlDR Is Nothing) Then
                If (sqlDR.IsClosed) Then
                    sqlDR.Close()
                End If
            End If
            If (Not conn Is Nothing) Then
                If conn.State <> ConnectionState.Closed Then conn.Close()
                conn.Dispose()
            End If
            If Not cmd Is Nothing Then cmd.Dispose()
            'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
            ' Searching for Manual(non-Krasdale ID) code
        ElseIf CheckIfManualID(Me.UPCCode.Text.Chars(0)) Then
 
            Dim conn As New SqlConnection(Application("A1SConnString"))
            Dim cmd As New SqlCommand("spA1SGetWHCode", conn)
            cmd.CommandType = CommandType.StoredProcedure
            Dim sqlDR As SqlDataReader
            Dim prm As SqlParameter
 
            conn.Open()
            prm = cmd.Parameters.Add("@WHCode", SqlDbType.Text)
            prm.Value = UPCCode.Text
 
            prm = cmd.Parameters.Add("@UserID", SqlDbType.VarChar, 15)
            prm.Value = Request.Cookies("A1S")("UID")
 
            sqlDR = cmd.ExecuteReader
            sqlDR.Read()
 
            If sqlDR.HasRows Then
 
                Me.Description.Text = SqlDR("Description")
                SizeMeasureList.Items.Clear()
                Me.SizeMeasureList.Enabled = False
                'Me.DeleteBtn.Enabled = False
 
                lblMessage.Visible = False
                Me.Description.ReadOnly = True
                Me.Size.ReadOnly = True
                Me.UPCCode.ReadOnly = True
                Me.UPCCode.BackColor = System.Drawing.Color.White
                Me.Description.BackColor = System.Drawing.Color.White
                Me.Size.BackColor = Nothing
 
            Else
                With lblMessage
                    lblMessage.Visible = True
                    .CssClass = "labelRed"
                    .Text = "The warehouse code could not be found matching the search term  '" & UPCCode.Text & "' . Please reenter your warehouse code."
                End With
                Me.UPCCode.BackColor = System.Drawing.Color.White
                Me.Description.BackColor = System.Drawing.Color.White
                Me.Size.BackColor = System.Drawing.Color.White
            End If
 
            'Clean Up
            If (Not sqlDR Is Nothing) Then
                If (sqlDR.IsClosed) Then
                    sqlDR.Close()
                End If
            End If
            If (Not conn Is Nothing) Then
                If conn.State <> ConnectionState.Closed Then conn.Close()
                conn.Dispose()
            End If
            If Not cmd Is Nothing Then cmd.Dispose()
            'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
            'If UPC code and WHCode code were not found display error message
        Else
            With lblMessage
                lblMessage.Visible = True
                .CssClass = "labelRed"
                .Text = "The UPC Code must have only whole numbers. Please reenter your UPC Code."
            End With
        End If
    End Sub
    Public Function LoadSizeMeasure(Optional ByVal SelectedVal As String = Nothing)
 
        Dim conn As New SqlConnection(Application("A1SConnString"))
        Dim cmd As SqlCommand
        Dim prm As SqlParameter
 
        cmd = New SqlCommand("spA1SGetSizeMeasure", conn)
        cmd.CommandType = CommandType.StoredProcedure
 
        Dim oRDR As SqlDataReader
        cmd.CommandTimeout = 120 'Number of seconds to wait for a response.
 
        conn.Open()
        oRDR = cmd.ExecuteReader(CommandBehavior.Default)
 
        SizeMeasureList.Items.Clear()
 
        While oRDR.Read
            SizeMeasureList.Items.Add(oRDR("ABRV"))
            If SelectedVal = oRDR("ABRV") Then SizeMeasureList.SelectedValue = oRDR("ABRV")
            SizeMeasureList.Items(SizeMeasureList.Items.Count - 1).Value = oRDR("SizeMeasureID")
        End While
 
        'Clean Up
        If (Not oRDR Is Nothing) Then
            If Not (oRDR.IsClosed) Then
                oRDR.Close()
            End If
        End If
        oRDR = Nothing
        If (Not conn Is Nothing) Then
            If conn.State <> ConnectionState.Closed Then conn.Close()
            conn.Dispose()
        End If
        If Not cmd Is Nothing Then cmd.Dispose()
 
    End Function
 
    Private Sub WebImageButton1_Click(ByVal sender As System.Object, ByVal e As Infragistics.WebUI.WebDataInput.ButtonEventArgs) Handles WebImageButton1.Click
        If Me.UPCCode.Text <> "" Then
            'If UPC Code is entered then validate and save
            If ValidatingInputForUPCCode() = True Then
                If ListBox1.SelectedIndex <> -1 Then
                    If VerifyStores() = 1 Then
                        UpdateSalesTPRTable()
                        RetrieveSalesTPRItems()
                        InitializeForm()
                        With lblMessage
                            lblMessage.Visible = True
                            .CssClass = "labelGreen"
                            .Text = "The Sale Item was inserted successfully."
                        End With
                        Me.UPCCode.BackColor = System.Drawing.Color.White
                        Me.Description.BackColor = System.Drawing.Color.White
                        Me.Size.BackColor = System.Drawing.Color.White
                        Me.Qty.BackColor = System.Drawing.Color.White
                        Me.Price.BackColor = System.Drawing.Color.White
                        Me.ListBox1.BackColor = System.Drawing.Color.White
                    Else
                        DisplayErrorMessage(7)
                    End If
                Else
                    DisplayErrorMessage(8)
                End If
            End If
        Else
            DisplayErrorMessage(0)
        End If
    End Sub
 
    Private Sub WebImageButton3_Click(ByVal sender As System.Object, ByVal e As Infragistics.WebUI.WebDataInput.ButtonEventArgs) Handles WebImageButton3.Click
        InitializeForm()
    End Sub
    Private Sub InitializeForm()
        Me.lblMessage.Text = Nothing
        Me.UPCCode.Text = Nothing
        Me.Description.Text = Nothing
        Me.Size.Text = Nothing
        Me.Qty.Text = Nothing
        Me.Price.Text = Nothing
        Me.UPCCode.ReadOnly = False
        Me.Description.ReadOnly = False
        Me.Size.ReadOnly = False
 
        Me.UPCCode.BackColor = System.Drawing.Color.White
        Me.Description.BackColor = System.Drawing.Color.White
        Me.Size.BackColor = System.Drawing.Color.White
        Me.Qty.BackColor = System.Drawing.Color.White
        Me.Price.BackColor = System.Drawing.Color.White
        Me.SizeMeasureList.Enabled = True
        SetFocus(UPCCode, Me)
        SizeMeasureList.Items.Clear()
        LoadSizeMeasure()
    End Sub
    Private Function ValidatingInputForUPCCode() As Boolean
 
        Dim bChecking(6) As Boolean
 
        If Me.UPCCode.Text <> "" And IsValidIntegerRequired(Me.UPCCode.Text) Then bChecking(0) = True Else bChecking(0) = False
        If Me.Description.Text <> "" Then bChecking(1) = True Else bChecking(1) = False
        If Me.Size.Text <> "" And IsValidDoubleRequired(Me.Size.Text) Then bChecking(2) = True Else bChecking(2) = False
        If Me.SizeMeasureList.SelectedValue <> "" Then bChecking(3) = True Else bChecking(3) = False
        If Me.Qty.Text <> "" And IsValidIntegerRequired(Me.Qty.Text) Then bChecking(4) = True Else bChecking(4) = False
        If Me.Price.Text <> "" And IsValidDoubleRequired(Me.Price.Text) Then bChecking(5) = True Else bChecking(5) = False
        If VerifyUnicUPCCode(Me.UPCCode.Text) Then bChecking(6) = True Else bChecking(6) = False
 
        Dim i As Integer
        For i = 0 To 6
 
            If bChecking(i) = True Then
                ValidatingInputForUPCCode = True
            Else : ValidatingInputForUPCCode = i
                DisplayErrorMessage(i)
                ValidatingInputForUPCCode = False
                Exit For
            End If
 
        Next
        Return ValidatingInputForUPCCode
    End Function
    Private Function DisplayErrorMessage(ByVal MsgIndex As Integer)
 
        Select Case MsgIndex
            Case 0
                With lblMessage
                    lblMessage.Visible = True
                    .CssClass = "labelRed"
                    .Text = "UPC Code is a required field and must be a whole number. Please reenter your UPC Code."
                End With
                Me.UPCCode.BackColor = System.Drawing.Color.PeachPuff
                Me.Description.BackColor = System.Drawing.Color.White
                Me.Size.BackColor = System.Drawing.Color.White
                Me.Qty.BackColor = System.Drawing.Color.White
                Me.Price.BackColor = System.Drawing.Color.White
                Me.ListBox1.BackColor = System.Drawing.Color.White
 
            Case 1
                With lblMessage
                    lblMessage.Visible = True
                    .CssClass = "labelRed"
                    .Text = "Description is a required field. Please reenter your Description."
                End With
                Me.UPCCode.BackColor = System.Drawing.Color.White
                Me.Description.BackColor = System.Drawing.Color.PeachPuff
                Me.Size.BackColor = System.Drawing.Color.White
                Me.Qty.BackColor = System.Drawing.Color.White
                Me.Price.BackColor = System.Drawing.Color.White
                Me.ListBox1.BackColor = System.Drawing.Color.White
 
            Case 2
                With lblMessage
                    lblMessage.Visible = True
                    .CssClass = "labelRed"
                    .Text = "Size is a required field and must be numeric. Please reenter your Size."
                End With
                Me.UPCCode.BackColor = System.Drawing.Color.White
                Me.Description.BackColor = System.Drawing.Color.White
                Me.Size.BackColor = System.Drawing.Color.PeachPuff
                Me.Qty.BackColor = System.Drawing.Color.White
                Me.Price.BackColor = System.Drawing.Color.White
                Me.ListBox1.BackColor = System.Drawing.Color.White
 
            Case 3
                With lblMessage
                    lblMessage.Visible = True
                    .CssClass = "labelRed"
                    .Text = "Size Measure is a required field. Please reenter your Size Measure."
                End With
                Me.UPCCode.BackColor = System.Drawing.Color.White
                Me.Description.BackColor = System.Drawing.Color.White
                Me.Size.BackColor = System.Drawing.Color.White
                Me.Qty.BackColor = System.Drawing.Color.White
                Me.Price.BackColor = System.Drawing.Color.White
                Me.ListBox1.BackColor = System.Drawing.Color.White
 
            Case 4
                With lblMessage
                    lblMessage.Visible = True
                    .CssClass = "labelRed"
                    .Text = "Sale Quantity is a required field and must be a whole number other than 0. Please reenter your Sale Quantity."
                End With
                Me.UPCCode.BackColor = System.Drawing.Color.White
                Me.Description.BackColor = System.Drawing.Color.White
                Me.Size.BackColor = System.Drawing.Color.White
                Me.Qty.BackColor = System.Drawing.Color.PeachPuff
                Me.Price.BackColor = System.Drawing.Color.White
                Me.ListBox1.BackColor = System.Drawing.Color.White
 
            Case 5
                With lblMessage
                    lblMessage.Visible = True
                    .CssClass = "labelRed"
                    .Text = "Sale Price is a required field and must be a number other than 0. Please reenter your Sale Price."
                End With
                Me.UPCCode.BackColor = System.Drawing.Color.White
                Me.Description.BackColor = System.Drawing.Color.White
                Me.Size.BackColor = System.Drawing.Color.White
                Me.Qty.BackColor = System.Drawing.Color.White
                Me.Price.BackColor = System.Drawing.Color.PeachPuff
                Me.ListBox1.BackColor = System.Drawing.Color.White
 
            Case 6
                With lblMessage
                    lblMessage.Visible = True
                    .CssClass = "labelRed"
                    .Text = "The UPCCode that was entered already exists. Please reenter your UPCCode."
                End With
                Me.UPCCode.BackColor = System.Drawing.Color.PeachPuff
                Me.Description.BackColor = System.Drawing.Color.White
                Me.Size.BackColor = System.Drawing.Color.White
                Me.Qty.BackColor = System.Drawing.Color.White
                Me.Price.BackColor = System.Drawing.Color.White
                Me.ListBox1.BackColor = System.Drawing.Color.White
 
            Case 7
                With lblMessage
                    lblMessage.Visible = True
                    .CssClass = "labelRed"
                    .Text = "The Stores that you have selected do not belong to the same Program. Please reselect your Stores."
                End With
 
                Me.UPCCode.BackColor = System.Drawing.Color.White
                Me.Description.BackColor = System.Drawing.Color.White
                Me.Size.BackColor = System.Drawing.Color.White
                Me.Qty.BackColor = System.Drawing.Color.White
                Me.Price.BackColor = System.Drawing.Color.White
                Me.ListBox1.BackColor = System.Drawing.Color.PeachPuff
 
            Case 8
                With lblMessage
                    lblMessage.Visible = True
                    .CssClass = "labelRed"
                    .Text = "Please select at least one Store from the list."
                End With
 
                Me.UPCCode.BackColor = System.Drawing.Color.White
                Me.Description.BackColor = System.Drawing.Color.White
                Me.Size.BackColor = System.Drawing.Color.White
                Me.Qty.BackColor = System.Drawing.Color.White
                Me.Price.BackColor = System.Drawing.Color.White
                Me.ListBox1.BackColor = System.Drawing.Color.PeachPuff
        End Select
    End Function
    Public Function UpdateSalesTPRTable()
 
        Dim conn As New SqlConnection(Application("A1SConnString"))
        Dim cmd As New SqlCommand("spA1SInsertSalesTPRItems", conn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim prm As SqlParameter
 
        prm = New SqlParameter("@UPC", SqlDbType.VarChar, 50)
        prm.Direction = ParameterDirection.Input
        prm.Value = Me.UPCCode.Text 'If UPC is not found Adding to SalesTPR and upc.dbo.uProduct
        cmd.Parameters.Add(prm)
 
        prm = New SqlParameter("@Description", SqlDbType.VarChar, 60)
        prm.Direction = ParameterDirection.Input
        prm.Value = Me.Description.Text     'If UPC is not found Adding to SalesTPR and upc.dbo.uProduct
        cmd.Parameters.Add(prm)
 
        prm = New SqlParameter("@SizeUnit", SqlDbType.VarChar, 8)
        prm.Direction = ParameterDirection.Input
        prm.Value = Me.Size.Text            'If UPC is not found Adding to SalesTPR and upc.dbo.uProduct
        cmd.Parameters.Add(prm)
 
        prm = New SqlParameter("@SizeMeasureID", SqlDbType.BigInt)
        prm.Direction = ParameterDirection.Input
        prm.Value = CheckSizeMeasureID()
        cmd.Parameters.Add(prm)
 
        prm = New SqlParameter("@Quantity", SqlDbType.Decimal)
        prm.Direction = ParameterDirection.Input
        prm.Value = Convert.ToDecimal(Me.Qty.Text)
        cmd.Parameters.Add(prm)
 
        prm = New SqlParameter("@Price", SqlDbType.Decimal)
        prm.Direction = ParameterDirection.Input
        prm.Value = Convert.ToDecimal(Me.Price.Text)
        cmd.Parameters.Add(prm)
 
        prm = New SqlParameter("@Active", SqlDbType.TinyInt)
        prm.Direction = ParameterDirection.Input
        prm.Value = 1
        cmd.Parameters.Add(prm)
 
        prm = New SqlParameter("@CreatedDate", SqlDbType.DateTime)
        prm.Direction = ParameterDirection.Input
        prm.Value = Now.ToLongTimeString
        cmd.Parameters.Add(prm)
 
        prm = New SqlParameter("@ModifiedDate", SqlDbType.DateTime)
        prm.Direction = ParameterDirection.Input
        prm.Value = Now.ToLongTimeString
        cmd.Parameters.Add(prm)
 
        prm = New SqlParameter("@ActiveDate", SqlDbType.DateTime)
        prm.Direction = ParameterDirection.Input
        prm.Value = Now.ToLongTimeString
        cmd.Parameters.Add(prm)
 
        'prm = New SqlParameter("@UOM", SqlDbType.VarChar, 4)
        'prm.Direction = ParameterDirection.Input
        'prm.Value = Me.SizeMeasureList.SelectedItem.Text    'Adding to upc.dbo.uProduct if UPC doesn't exist
        'cmd.Parameters.Add(prm)
 
        prm = New SqlParameter("@UserID", SqlDbType.VarChar, 50)
        prm.Direction = ParameterDirection.Input
        prm.Value = ReadCookie(Tokens.UserId)
        cmd.Parameters.Add(prm)
 
        prm = New SqlParameter("@Stores", SqlDbType.VarChar, 150)
        prm.Direction = ParameterDirection.Input
        prm.Value = strselecteditems
        cmd.Parameters.Add(prm)
 
        conn.Open()
        cmd.ExecuteNonQuery()
 
        If conn.State <> ConnectionState.Closed Then conn.Close()
        If Not conn Is Nothing Then conn.Dispose()
        If Not cmd Is Nothing Then cmd.Dispose()
        prm = Nothing
 
    End Function
    Private Function CheckSizeMeasureID(Optional ByVal SizeMeasureID As String = Nothing) As Integer
 
        Dim SzMeasureID As Integer
 
        Dim conn As New SqlConnection(Application("A1SConnString"))
        Dim cmd As New SqlCommand("spA1SCheckSizeMeasure", conn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim sqlDR As SqlDataReader
        Dim prm As SqlParameter
 
        conn.Open()
        prm = cmd.Parameters.Add("@ABRV", SqlDbType.VarChar)
        If SizeMeasureID = Nothing Then
            prm.Value = Me.SizeMeasureList.SelectedItem.Text
        Else
            prm.Value = SizeMeasureID
        End If
        sqlDR = cmd.ExecuteReader
 
        While sqlDR.Read
            SzMeasureID = sqlDR("SizeMeasureID")
        End While
 
        If sqlDR.HasRows Then
            CheckSizeMeasureID = SzMeasureID
        Else
            CheckSizeMeasureID = -1
        End If
 
        'Clean Up
        If (Not sqlDR Is Nothing) Then
            If Not (sqlDR.IsClosed) Then
                sqlDR.Close()
            End If
        End If
        sqlDR = Nothing
        If (Not conn Is Nothing) Then
            If conn.State <> ConnectionState.Closed Then conn.Close()
            conn.Dispose()
        End If
        If Not cmd Is Nothing Then cmd.Dispose()
 
        Return CheckSizeMeasureID
 
    End Function
    Private Function RetrieveSalesTPRItems()
 
        Dim conn As New SqlConnection(Application("A1SConnString"))
        Dim cmd As New SqlCommand("spA1SGetSalesTPR", conn)
        Dim sqlDR As New SqlDataAdapter
        Dim ds As New DataSet
 
        Dim prm As New SqlParameter("@UserID", SqlDbType.VarChar, 50)
        cmd.CommandType = CommandType.StoredProcedure
        prm.Value = ReadCookie(Tokens.UserId)
        cmd.Parameters.Add(prm)
 
        conn.Open()
        sqlDR.SelectCommand = cmd
        sqlDR.Fill(ds)
 
        DataGrid1.DataSource = ds
        DataGrid1.DataBind()
 
        'Clean Up
        If (Not sqlDR Is Nothing) Then sqlDR = Nothing
        If (Not conn Is Nothing) Then
            If conn.State <> ConnectionState.Closed Then conn.Close()
            conn.Dispose()
        End If
 
    End Function
    Public Sub DeleteItems(ByVal sender As Object, ByVal e As CommandEventArgs)
 
        Dim conn As New SqlConnection(Application("A1SConnString"))
        Dim cmd As New SqlCommand("spA1SDeleteSalesTPRItem", conn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim sqlDR As SqlDataReader
 
        Dim p As New SqlParameter("@ItemID", SqlDbType.BigInt)
        p.Value = e.CommandArgument
        cmd.Parameters.Add(p)
 
        conn.Open()
        cmd.ExecuteNonQuery()
 
        'Clean Up
        If (Not sqlDR Is Nothing) Then
            If (sqlDR.IsClosed) Then
                sqlDR.Close()
            End If
        End If
        If (Not conn Is Nothing) Then
            If conn.State <> ConnectionState.Closed Then conn.Close()
            conn.Dispose()
        End If
        If Not cmd Is Nothing Then cmd.Dispose()
 
        RetrieveSalesTPRItems()
 
        With lblMessage
            lblMessage.Visible = True
            .CssClass = "labelGreen"
            .Text = "The Sale Item was successfully deleted ."
        End With
 
    End Sub
    Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged
        DataGrid1.CurrentPageIndex = e.NewPageIndex
        RetrieveSalesTPRItems()
 
        With lblMessage
            lblMessage.Visible = True
            .CssClass = "labelGreen"
            .Text = "Page: " & DataGrid1.CurrentPageIndex + 1 & " of " & DataGrid1.PageCount.ToString
        End With
    End Sub
 
    Private Sub DoneButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DoneButton.Click
        Response.Redirect("MainMenu.aspx")
    End Sub
    Private Function VerifyUnicUPCCode(ByVal UPCCode As String) As Boolean
 
        Dim conn As New SqlConnection(Application("A1SConnString"))
        Dim cmd As New SqlCommand("spA1SCheckUnicUPCforTPR", conn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim sqlDR As SqlDataReader
        Dim prm As SqlParameter
 
        conn.Open()
        sqlDR = cmd.ExecuteReader
 
        VerifyUnicUPCCode = True
 
        While sqlDR.Read
            If UPCCode = sqlDR("UPC") Then
                VerifyUnicUPCCode = False
                Exit While
            Else
                VerifyUnicUPCCode = True
            End If
        End While
 
        'Clean Up
        If (Not sqlDR Is Nothing) Then
            If Not (sqlDR.IsClosed) Then
                sqlDR.Close()
            End If
        End If
        sqlDR = Nothing
        If (Not conn Is Nothing) Then
            If conn.State <> ConnectionState.Closed Then conn.Close()
            conn.Dispose()
        End If
        If Not cmd Is Nothing Then cmd.Dispose()
 
        Return VerifyUnicUPCCode
 
    End Function
    Public Function VerifyStores() As Integer
        Dim i As Integer = 0
        Dim Count As Integer = 0
        Dim TempChain As Integer = -1
        Dim TempRegion As Integer = -1
        Dim TempCustomerLoyalty As Integer = -1
        Try
            For i = 0 To ListBox1.Items.Count - 1
                If ListBox1.Items(i).Selected Then
                    If Count = 0 Then
 
                        TempChain = GetChainNumber(ListBox1.Items(i).ToString)
                        TempRegion = GetRegionNumber(ListBox1.Items(i).ToString)
                        TempCustomerLoyalty = GetCustomerLoyaltyNumber(ListBox1.Items(i).ToString)
                        strselecteditems = ListBox1.Items(i).Text
                        Count = Count + 1
 
                    Else
                        If TempChain = GetChainNumber(ListBox1.Items(i).ToString) And TempRegion = GetRegionNumber(ListBox1.Items(i).ToString) And TempCustomerLoyalty = GetCustomerLoyaltyNumber(ListBox1.Items(i).ToString) Then
                            strselecteditems = strselecteditems & "," & ListBox1.Items(i).Text
                        Else
                            Return 0
                        End If
                    End If
                End If
            Next
            Return 1
        Catch ex As ApplicationException
 
        End Try
 
    End Function
    Public Sub ChangeItemStatus(ByVal sender As Object, ByVal e As CommandEventArgs)
        Dim conn As New SqlConnection(Application("A1SConnString"))
        Dim cmd As New SqlCommand("spA1SChangeTPRItemStatus", conn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim sqlDR As SqlDataReader
 
        Dim p As New SqlParameter("@ItemID", SqlDbType.BigInt)
        p.Value = e.CommandArgument
        cmd.Parameters.Add(p)
 
        conn.Open()
        cmd.ExecuteNonQuery()
 
        'Clean Up
        If (Not sqlDR Is Nothing) Then
            If (sqlDR.IsClosed) Then
                sqlDR.Close()
            End If
        End If
        If (Not conn Is Nothing) Then
            If conn.State <> ConnectionState.Closed Then conn.Close()
            conn.Dispose()
        End If
        If Not cmd Is Nothing Then cmd.Dispose()
 
        RetrieveSalesTPRItems()
 
    End Sub
    'Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged
    ' Dim conn As New SqlConnection(Application("A1SConnString"))
    ' Dim cmd As New SqlCommand("spA1SGetSalesTPR", conn)
    ' Dim sqlDR As SqlDataReader
    ' Dim RadioButton1 As System.Web.UI.WebControls.RadioButton
    '  I
    '  conn.Open()
    ' cmd.ExecuteNonQuery()
 
 
    'Clean(Up)
    '  If (Not sqlDR Is Nothing) Then
    '     If (sqlDR.IsClosed) Then
    '         sqlDR.Close()
    '     End If
    ' End If
    '  If (Not conn Is Nothing) Then
    '     If conn.State <> ConnectionState.Closed Then conn.Close()
    '     conn.Dispose()
    ' End If
    ' If Not cmd Is Nothing Then cmd.Dispose()
 
    ' RetrieveSalesTPRItems()
    ' End Sub
 
    ' Private Sub RadioButton2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton2.CheckedChanged
    'Dim conn As New SqlConnection(Application("A1SConnString"))
    'Dim cmd As New SqlCommand("spA1SGetSalesTPR", conn)
    'cmd.CommandType = CommandType.StoredProcedure
    'Dim sqlDR As SqlDataReader
    ' Dim RadioButton2 As System.Web.UI.WebControls.RadioButton
    't = new RadioButton2();
    't.Itm2 = false;
    'RadioButton2.DataBindings.Add(new Binding("Checked", t, "Itm2")) ;
    ' conn.Open()
    ' cmd.ExecuteNonQuery()
 
 
    'Clean(Up)
 
End Class
 
 
-- .net code
 
</asp:BoundColumn>
											<asp:BoundColumn  HeaderStyle-HorizontalAlign="Center" DataField="Stores" >
									<ItemStyle Width="190px"></ItemStyle>
										</asp:BoundColumn>
<asp:CheckBoxList id="CheckBoxList1" runat="server"></asp:CheckBoxList>

Open in new window

0
Seven price
Asked:
Seven price
  • 129
  • 99
6 Solutions
 
DotNetThinkerCommented:
I'm not trying to be offensive but you need to be a little more descriptive in your question and only include code that's REALLY important to the problem. Seeing 800+ lines of code will deter most experts from trying to solve your problem. I'm not sure where to even start with this.
0
 
Seven priceFull StackAuthor Commented:
Ok lets start with the stored procedure.

I want to populate a checkboxlist when checked this will turn the active on and when unchecked turn the active off similar to the case statement image below.
ALTER PROCEDURE dbo.spA1SGetStoreTPR
 
 @Store char(6)
 
AS
 
SELECT	TPRStoreID,
		ItemId, 
		[Store],  
		Active,
		Store,
		
		
		--Status =  '<img alt="Sale Item Added" src=..\Images\Add.png >' 
		Case	when Active = 1 Then  '<a><img alt="Active" src=..\Images\document_check.png></a>' 
				Else  '<a><img alt="Not Active" src=..\Images\document_delete.png ></a>' 
				
				 
			End  As 
		Store
 
FROM A1S.dbo.StoreTPR
 
 
Where @Store  =  Store 
 
order by TPRStoreID  DESC
 
DONE:
  RETURN 0
ERRORHANDLER:
  RETURN 1

Open in new window

0
 
DotNetThinkerCommented:
ok, so you want to do an update on the StoreTPR table when a checkbox is checked/unchecked?

Another question, what do you mean by populating a checkboxlist when checked? Do you want to populate a cbl when a different checkbox is checked? What are you doing from a process standpoint? In other words just walk me through it high level...."When a user clicks a store location I want to show xyz"... that kind of stuff.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Seven priceFull StackAuthor Commented:
Well the user might have more than one store for example store 43566; 43552
If he has more than one store number then both of these will populate when he logs in.
So I need to bound the store number to my Datagrid displaying the user who logs in his store numbers.
0
 
DotNetThinkerCommented:
Are you already retrieving the store numbers for the user?
0
 
DotNetThinkerCommented:
if so where are you storing them (session, viewstate, etc.)?
0
 
Seven priceFull StackAuthor Commented:
Yes I am displaying them using
<%# DataBinder.Eval(Container.DataItem, "Stores", "{0:D}") %> they are display with syntax or below which is totally wrong below because it displays every number with a checkbox
									<asp:CheckBoxList id="MyChecklist" DataSource='<%# DataBinder.Eval(Container.DataItem, "Stores") %>' AutoPostBack="True" RepeatDirection="Horizontal"  DataMember='<%# DataBinder.Eval(Container.DataItem, "ItemId") %>' runat="server"></asp:CheckBoxList>
									

Open in new window

0
 
DotNetThinkerCommented:
How do you want them displayed? If you'd like just a list of the store ids you should use a ListBox rather than the CheckBoxList.
0
 
Seven priceFull StackAuthor Commented:
well the client wants a checkbox I can use  regular check box like but only populates the check box not the store id
<asp:CheckBox id="Store"  DataSource='<%# DataBinder.Eval(Container.DataItem, "Stores") %>' AutoPostBack="True" RepeatDirection="Horizontal"  DataMember='<%# DataBinder.Eval(Container.DataItem, "Stores") %>' runat="server"></asp:CheckBox>
									

Open in new window

0
 
DotNetThinkerCommented:
ok, so you have your list of store ids. Assuming they're all unchecked what should happen when I check one?
0
 
Seven priceFull StackAuthor Commented:
then it will tell the database it is active which is 1 uncheck inaactive which is 2 field name is Active.
0
 
Seven priceFull StackAuthor Commented:
sorry inactive which is 0
0
 
DotNetThinkerCommented:
Ok, first off you'll want to define the OnSelectedIndexChanged event handler for the CheckBoxList. Something like this...
<asp:CheckBoxList id="MyChecklist" DataSource='<%# DataBinder.Eval(Container.DataItem, "Stores") %>' 
AutoPostBack="True" RepeatDirection="Horizontal"  DataMember='<%# DataBinder.Eval(Container.DataItem, "ItemId") %>'
runat="server" OnSelectedIndexChanged="MyCheckList_SelectedIndexChanged"></asp:CheckBoxList>
 
...
 
 
protected void MyCheckList_SelectedIndexChanged(object sender, EventArgs e)
{
 
}

Open in new window

0
 
Seven priceFull StackAuthor Commented:
I rather use a checkboxlist if possible but what ever works at this point
0
 
Seven priceFull StackAuthor Commented:
Ok sounds good whats next
0
 
Seven priceFull StackAuthor Commented:
And end of statement error is required please proceed
0
 
Seven priceFull StackAuthor Commented:
sure you rather me use something like this.
 Private Sub MyCheckList_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyCheckList.SelectedIndexChanged
 
    End Sub

Open in new window

0
 
DotNetThinkerCommented:
sorry, that was C# I was using.
0
 
Seven priceFull StackAuthor Commented:
Also here is the store procedure for the change and the store procedure for the load.

But I guess we need to change the image status for the checkbox instead of image in which i do not know how to do.

--Status =  '<img alt="Sale Item Added" src=..\Images\Add.png >'
            Case      when Active = 1 Then  '<a><img alt="Active" src=..\Images\document_check.png></a>'
                        Else  '<a><img alt="Not Active" src=..\Images\document_delete.png ></a>'
ALTER PROCEDURE dbo.spA1SChangeTPRItemStatus
 
 @ItemID AS BigInt
 
AS
 
Begin
 
UPDATE StoreTPR
 
SET Active = Case
		
When Active=1 Then 0 else 1
 
End
 
WHERE ItemID = @ItemID	
 
End
	
DONE:
  RETURN 0
ERRORHANDLER:
  RETURN 1
 
 
second stored procedure
 
ALTER PROCEDURE dbo.spA1SGetStoreTPR
 
 @Store char(6)
 
AS
 
SELECT	TPRStoreID,
		ItemId, 
		[Store],  
		Active,
		Store,
		
		
		--Status =  '<img alt="Sale Item Added" src=..\Images\Add.png >' 
		Case	when Active = 1 Then  '<a><img alt="Active" src=..\Images\document_check.png></a>' 
				Else  '<a><img alt="Not Active" src=..\Images\document_delete.png ></a>' 
				
				 
			End  As 
		Store
 
FROM A1S.dbo.StoreTPR
 
 
Where @Store  =  Store 
 
order by TPRStoreID  DESC
 
DONE:
  RETURN 0
ERRORHANDLER:
  RETURN 1

Open in new window

0
 
DotNetThinkerCommented:
Private Sub MyCheckList_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyCheckList.SelectedIndexChanged
    Dim StoreId As Integer = Integer.Parse(MyCheckList.SelectedItem.Value) 'This will give you your selected store Id

End Sub
0
 
Seven priceFull StackAuthor Commented:
LOL no problem
0
 
DotNetThinkerCommented:
Are you storing the image in the DB?
0
 
Seven priceFull StackAuthor Commented:
So we hav eto Declare  MycheckList correct?
0
 
Seven priceFull StackAuthor Commented:
well no that was for something else that he decieded he did not want when you click the image changes the 1 to 0 click again and 0 becomes 1 which would change the image to a check box or x box image.

Everything is coming from the stored procedure and i guess declared in vb
0
 
DotNetThinkerCommented:
If the store is active you want one image displayed, if it's inactive you want another. Is that the jist of it?
0
 
Seven priceFull StackAuthor Commented:
Yes but instead of an image it must be a checkbox
0
 
Seven priceFull StackAuthor Commented:
You can just keep going I thought we were getting some where
Description Quantity Price Created Date Store 
4150880012 S.PELLEGRINO SPARKLING MINERAL 1 19.99 12/6/2007 2:12:20 PM 14018 
Prev Next 
 

Open in new window

0
 
DotNetThinkerCommented:
ok, I hate to keep bugging you with these questions but I just want to clear a few things up...

Right now all you want is a CheckBoxList that shows all the Stores and whether or not they're active (checked if they are, unchecked if not)?

Where does the DataGrid come into play?
0
 
Seven priceFull StackAuthor Commented:
I tell you this if you can figure this out I wish I could give you 3000 points because there isn't a listing anywhere on the internet
0
 
Seven priceFull StackAuthor Commented:
The Datagrid displays all of the
Description  Quantity    Price    Created Date    Store

Store is the store number but a owner might have 2 or more stores in this field so when displayed the store owner chooses what product they need the quantity but if he has 2 stores he might only need it for 1 so he will check which one is active for that Quantity he needs.

So this was half way built from another developer but I am trying to finish it and I got just about everything except for this for the last 2 days.
0
 
Seven priceFull StackAuthor Commented:
This database is not displaying the The store which is the store number I am populating it through another database that has the store number but I need to populate this store in which is a inner join
0
 
Seven priceFull StackAuthor Commented:
We need to declare MyCheckList correct but how?
Private Sub MyCheckList_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyCheckList.SelectedIndexChanged
        Dim Store As Integer = Integer.Parse(MyCheckList.SelectedItem.Value) 'This will give you your selected store Id
 
    End Sub

Open in new window

0
 
Seven priceFull StackAuthor Commented:
Would this help for the Checkboxlist active function ?
Public Sub ChangeItemStatus(ByVal sender As Object, ByVal e As CommandEventArgs)
        Dim conn As New SqlConnection(Application("A1SConnString"))
        Dim cmd As New SqlCommand("spA1SGetStoreTPR", conn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim sqlDR As SqlDataReader
 
        Dim p As New SqlParameter("@TPRStoreID", SqlDbType.BigInt)
        p.Value = e.CommandArgument
        cmd.Parameters.Add(p)
 
        conn.Open()
        cmd.ExecuteNonQuery()
 
        'Clean Up
        If (Not sqlDR Is Nothing) Then
            If (sqlDR.IsClosed) Then
                sqlDR.Close()
            End If
        End If
        If (Not conn Is Nothing) Then
            If conn.State <> ConnectionState.Closed Then conn.Close()
            conn.Dispose()
        End If
        If Not cmd Is Nothing Then cmd.Dispose()
 
        RetrieveSalesTPRItems()
 
    End Sub

Open in new window

0
 
Seven priceFull StackAuthor Commented:
The Data grid just displays the description of the items they care to order
0
 
DotNetThinkerCommented:
ok, tell you what.... I'm actually headed home right now but I'll pick this back up with you tomorrow. Sorry I couldn't be more help tonight. Hopefully we'll have a breakthrough tomorrow.
0
 
DotNetThinkerCommented:
do me a favor and give me some sort of diagram of the tables you're querying from. Doesn't really matter how it's formatted. If you want, go ahead and sent a screenshot to me (dotnet.thinker@gmail.com). Maybe I'll take a look when I get home.
0
 
Seven priceFull StackAuthor Commented:
ok cool no problem.
0
 
Seven priceFull StackAuthor Commented:
hey I just got home from the office what time we can start this tommorrow because I have no connection to the database from home.
0
 
DotNetThinkerCommented:
do ahead and send me what you have when you get in. I'm not going to be able to spend all day on it only because I need to get some stuff done at work too but hopefully we'll get it figured out by the end of the day.
0
 
Seven priceFull StackAuthor Commented:
I sent it along with the files last night
0
 
DotNetThinkerCommented:
Ok, go ahead and add this to the SelectedIndexChanged handler...
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
 
        Dim YourConnectionString As String = "ConnectionString" 'this should be your connection string text
 
        'Create your Connection String
        Dim Connection As SqlClient.SqlConnection = New SqlClient.SqlConnection(YourConnectionString)
 
        'Open the connection
        Connection.Open()
 
        'Create your Update Sql Command
        Dim UpdateCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("Update YourTable set Active = @Active" + _
            " where ItemId = @ItemId", Connection)
 
        'Add the parameters to your Update Command
        UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("Active", CType(CBL.SelectedItem.Selected, Integer)))
        UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("ItemId", CBL.SelectedItem.Value))
 
        'Execute the Update Command
        UpdateCommand.ExecuteNonQuery()
 
        'Close the connection
        Connection.Close()

Open in new window

0
 
Seven priceFull StackAuthor Commented:
Ok telling Declaration expected
0
 
Seven priceFull StackAuthor Commented:
ok
0
 
DotNetThinkerCommented:
where is it saying a declaration is expected?
0
 
Seven priceFull StackAuthor Commented:
Where can I declare  CheckBoxList1 i already add it as an ID

 Private Sub CheckBoxList1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBoxList1.SelectedIndexChanged
 
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
 
    Dim YourConnectionString As String = "A1SConnString" 'this should be your connection string text
 
    'Create Connection String
    Dim conn As New SqlConnection(Application("spA1SGetStore"))
 
    'Open the connection
        Connection.Open()
 
    'Create  Update Sql Command
        Dim UpdateCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("Update YourTable set Active = @Active" + _
        " where ItemId = @ItemId", Connection)
 
    'Add the parameters to  Update Command
        UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("Active", CType(CBL.SelectedItem.Selected, Integer)))
        UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("ItemId", CBL.SelectedItem.Value))
 
    'Execute the Update Command
        UpdateCommand.ExecuteNonQuery()
 
    'Close the connection
        Connection.Close()
 
    End Sub

Open in new window

0
 
Seven priceFull StackAuthor Commented:
Also Connection.Open() not declared

  Connection.Open()
 
    'Create  Update Sql Command
        Dim UpdateCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("Update YourTable set Active = @Active" + _
        " where ItemId = @ItemId", Connection)

Open in new window

0
 
Seven priceFull StackAuthor Commented:
This is what I have on the front end
<asp:CheckBoxlist id="sender" DataSource='<%# DataBinder.Eval(Container.DataItem, "Stores") %>' OnSelectedIndexChanged='<%# Container.DataItem("ItemId") %>'  AutoPostBack="True" RepeatDirection="Horizontal" Runat="server"  DataMember='<%# DataBinder.Eval(Container.DataItem, "store") %>' ></asp:CheckBoxlist>
									

Open in new window

0
 
DotNetThinkerCommented:
change Connection.Open() and Connection.Close() to conn.Open() and conn.Close()
0
 
Seven priceFull StackAuthor Commented:
cool Next,
0
 
DotNetThinkerCommented:
change that to this... (note: the ID of the cbl doesn't really matter)
<asp:CheckBoxlist id="cblStores" DataSource='<%# DataBinder.Eval(Container.DataItem, "Stores") %>' OnSelectedIndexChanged="CheckBoxList1_SelectedIndexChanged"  AutoPostBack="True" RepeatDirection="Horizontal" Runat="server"  DataMember='<%# DataBinder.Eval(Container.DataItem, "store") %>' DataTextField="StoreId" DataTextField="StoreId"></asp:CheckBoxlist>

Open in new window

0
 
Seven priceFull StackAuthor Commented:
and this where do or how do I declare this checkBoxList
CheckBoxList1.SelectedIndexChanged

Open in new window

0
 
Seven priceFull StackAuthor Commented:
you already one step ahead
0
 
Seven priceFull StackAuthor Commented:
This requires a withevent handler
Handles CheckBoxList1.SelectedIndexChanged

Open in new window

0
 
DotNetThinkerCommented:
let me know if I'm jumping around too much or you need clarification on some of this.
0
 
Seven priceFull StackAuthor Commented:
I get this error below

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30456: 'CheckBoxList1_SelectedIndexChanged' is not a member of 'ASP.SalesTPR_aspx'.

Source Error:

 


Line 102:                                                      <asp:CheckBoxlist id="cblStores" DataSource='<%# DataBinder.Eval(Container.DataItem, "Stores") %>' OnSelectedIndexChanged="CheckBoxList1_SelectedIndexChanged" Runat="server"  AutoPostBack="True" ></asp:CheckBoxlist>
Line 103:                                    
 

<asp:CheckBoxlist id="cblStores" DataSource='<%# DataBinder.Eval(Container.DataItem, "Stores") %>' OnSelectedIndexChanged="CheckBoxList1_SelectedIndexChanged" Runat="server"  AutoPostBack="True" ></asp:CheckBoxlist>
									

Open in new window

0
 
DotNetThinkerCommented:
it should be "Handles WhateverYour_CBL_ID_Is.SelectedIndexChanged"

ie. if your CBL's Id is cblStores it should be "Handles cblStores.SelectedIndexChanged"
0
 
DotNetThinkerCommented:
the error sounds like it relates to my previous post, correct that and let me know what happens.
0
 
Seven priceFull StackAuthor Commented:
Just keep it coming I think we are moving just right sorry to be such a boomer
0
 
Seven priceFull StackAuthor Commented:
Still telling me Handle clause requires withevents variable
<asp:CheckBoxlist id="cblStores" DataSource='<%# DataBinder.Eval(Container.DataItem, "Stores") %>' OnSelectedIndexChanged="cblStores.SelectedIndexChanged" Runat="server"  AutoPostBack="True" ></asp:CheckBoxlist>
 
 
 
Private Sub CheckBoxList1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.SelectedIndexChanged
 
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
 
        Dim YourConnectionString As String = "A1SConnString" 'this should be your connection string text
 
        'Create Connection String
        Dim conn As New SqlConnection(Application("spA1SGetStore"))
 
        'Open the connection
        conn.Open()
 
        'Create  Update Sql Command
        Dim UpdateCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("Update YourTable set Active = @Active" + _
        " where ItemId = @ItemId", conn)
 
        'Add the parameters to  Update Command
        UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("Active", CType(CBL.SelectedItem.Selected, Integer)))
        UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("ItemId", CBL.SelectedItem.Value))
 
        'Execute the Update Command
        UpdateCommand.ExecuteNonQuery()
 
        'Close the connection
        conn.Close()
 
    End Sub
 
									

Open in new window

0
 
DotNetThinkerCommented:
ok, try getting rid of the OnSelectedIndexChanged="..." part of the CBL declaration
0
 
Seven priceFull StackAuthor Commented:
ok
<asp:CheckBoxlist id="cblStores" DataSource='<%# DataBinder.Eval(Container.DataItem, "Stores") %>'  Runat="server"  AutoPostBack="True" ></asp:CheckBoxlist>
									

Open in new window

0
 
Seven priceFull StackAuthor Commented:
Still get that error

Private Sub CheckBoxList1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ((((error here --- cblStores.SelectedIndexChanged)))))
0
 
DotNetThinkerCommented:
OK, I think I found the problem. In your code you'll see a section labeled Windows Forms Generated Code (or something like that). You should also see a list of declarations (see below for an example). What you want to do is add your CBL in there (seel below).
'Existing code....
 
    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
    Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Protected WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand
 
'Add this...
 
    Protected WithEvents cblStores As System.Web.UI.WebControls.CheckBoxList

Open in new window

0
 
Seven priceFull StackAuthor Commented:
When I run the application I still have and error clStores.SelectedIndexChanged.

but I see the checkbox but the thing is the checkboxes are showing up on every number.

For example if I have 14089 instead of checkbox 14089 it shows up
checkbox 1  checkbox  4 checkbox 0 checkbox  8 checkbox  9
Private Sub CheckBoxList1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.SelectedIndexChanged
 
 
<asp:CheckBoxlist id="cblStores" DataSource='<%# DataBinder.Eval(Container.DataItem, "Stores") %>' Runat="server"   AutoPostBack="True" ></asp:CheckBoxlist>
									

Open in new window

0
 
DotNetThinkerCommented:
OK, do me a favor and post the entire code for your DataGrid, from <asp:DataGrid...> to </asp:DataGrid>
0
 
Seven priceFull StackAuthor Commented:
ok done that
Protected WithEvents cblStores As System.Web.UI.WebControls.CheckBoxList

Open in new window

0
 
Seven priceFull StackAuthor Commented:
ok
<asp:datagrid id=DataGrid1 runat="server" Width="1004px" PageSize="7" AllowPaging="True" cssclass="prgdata" AlternatingItemStyle-BackColor="white" ItemStyle-CssClass="prgdata" ItemStyle-Height="25px" CellPadding="1" ShowHeader="false" AutoGenerateColumns="False">
								<AlternatingItemStyle BackColor="White"></AlternatingItemStyle>
								<ItemStyle Height="25px" CssClass="prgdata"></ItemStyle>
								<HeaderStyle Height="0px"></HeaderStyle>
								<Columns>
									<asp:TemplateColumn>
										<ItemTemplate>
											<asp:ImageButton id="ImageButton1" runat="server" Width="16px" AlternateText="Delete Item From The List" ImageUrl="../Images/delete2.png" OnCommand="DeleteItems" CommandArgument='<%# Container.DataItem("ItemId") %>'>
											</asp:ImageButton>
										</ItemTemplate>
									</asp:TemplateColumn>
									<asp:BoundColumn Visible="False" DataField="ItemID"></asp:BoundColumn>
									<asp:BoundColumn DataField="UPC">
										<ItemStyle Width="100px"></ItemStyle>
									</asp:BoundColumn>
									<asp:BoundColumn DataField="Description">
										<ItemStyle HorizontalAlign="Left" Width="400px"></ItemStyle>
									</asp:BoundColumn>
									<asp:BoundColumn DataField="Quantity">
										<ItemStyle Width="185px"></ItemStyle>
									</asp:BoundColumn>
									<asp:BoundColumn DataField="Price">
										<ItemStyle Width="250px"></ItemStyle>
									</asp:BoundColumn>
									<asp:BoundColumn DataField="CreatedDate">
										<ItemStyle Width="470px"></ItemStyle>
										</asp:BoundColumn>
											<asp:BoundColumn  HeaderStyle-HorizontalAlign="Center" DataField="Stores" >
									<ItemStyle Width="120px"></ItemStyle>
										</asp:BoundColumn>
									<asp:TemplateColumn>
										<ItemTemplate>
										<%--# DataBinder.Eval(Container.DataItem, "Stores", "{0:D}")--%>
									<asp:CheckBoxlist id="sender" DataSource='<%# DataBinder.Eval(Container.DataItem, "Stores") %>' OnSelectedIndexChanged='<%# Container.DataItem("ItemId") %>'  AutoPostBack="True" RepeatDirection="Horizontal" DataMember='<%# DataBinder.Eval(Container.DataItem, "Price") %>' ></asp:CheckBoxlist>
									<asp:CheckBox ID="CheckBoxList1" OnDataBinding="<%# DataBinder.Eval(Container.DataItem, "ItemID") %>" OnCheckedChanged='<%# Container.DataItem("ItemId") %>' AutoPostBack="True"></asp:CheckBox>
									<asp:CheckBoxlist id="cblStores" DataSource='<%# DataBinder.Eval(Container.DataItem, "Stores") %>' Runat="server"   AutoPostBack="True" ></asp:CheckBoxlist>
									
									</ItemTemplate>
									</asp:TemplateColumn>
									</Columns>
							 <PagerStyle Width="1004px" NextPageText="Next" Height="20px" Font-Size="X-Small" Font-Bold="True"
									PrevPageText="Prev" ForeColor="White" PageButtonCount="7" CssClass="Headercell"></PagerStyle>
							</asp:datagrid>

Open in new window

0
 
Seven priceFull StackAuthor Commented:
you can ignore these 2 they are not active
<asp:CheckBoxlist id="sender" DataSource='<%# DataBinder.Eval(Container.DataItem, "Stores") %>' OnSelectedIndexChanged='<%# Container.DataItem("ItemId") %>'  AutoPostBack="True" RepeatDirection="Horizontal" DataMember='<%# DataBinder.Eval(Container.DataItem, "Price") %>' ></asp:CheckBoxlist>
									<asp:CheckBox ID="CheckBoxList1" OnDataBinding="<%# DataBinder.Eval(Container.DataItem, "ItemID") %>" OnCheckedChanged='<%# Container.DataItem("ItemId") %>' AutoPostBack="True"></asp:CheckBox>
									

Open in new window

0
 
DotNetThinkerCommented:
Great, could you post the code where you're binding the DataGrid?
0
 
Seven priceFull StackAuthor Commented:
not sure what you mean the vb code
Dim strselecteditems As String = Nothing
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            RetrieveSalesTPRItems()
            SetFocus(UPCCode, Me)
            LoadSizeMeasure()
 
            With lblMessage
                lblMessage.Visible = True
                .CssClass = "labelGreen"
                .Text = "Total Pages: " & DataGrid1.PageCount.ToString
            End With
 
            Dim StoreList() As String
            Dim I As Integer = 0
 
            StoreList = Session("Stores")
 
            For I = 0 To UBound(StoreList)
                'ListBox1.Items.Add(AddIntoDataStructureofStorelist(StoreList(I)).StoreId.ToString)
                ListBox1.Items.Add(StoreList(I).ToString)
            Next
 
            Me.UPCCode.BackColor = System.Drawing.Color.White
            Me.Description.BackColor = System.Drawing.Color.White
            Me.Size.BackColor = System.Drawing.Color.White
            Me.Qty.BackColor = System.Drawing.Color.White
            Me.Price.BackColor = System.Drawing.Color.White
        End If
    End Sub
 
    Private Sub WebImageButton1_Click(ByVal sender As System.Object, ByVal e As Infragistics.WebUI.WebDataInput.ButtonEventArgs) Handles WebImageButton1.Click
        If Me.UPCCode.Text <> "" Then
            'If UPC Code is entered then validate and save
            If ValidatingInputForUPCCode() = True Then
                If ListBox1.SelectedIndex <> -1 Then
                    If VerifyStores() = 1 Then
                        UpdateSalesTPRTable()
                        RetrieveSalesTPRItems()
                        InitializeForm()
                        With lblMessage
                            lblMessage.Visible = True
                            .CssClass = "labelGreen"
                            .Text = "The Sale Item was inserted successfully."
                        End With
                        Me.UPCCode.BackColor = System.Drawing.Color.White
                        Me.Description.BackColor = System.Drawing.Color.White
                        Me.Size.BackColor = System.Drawing.Color.White
                        Me.Qty.BackColor = System.Drawing.Color.White
                        Me.Price.BackColor = System.Drawing.Color.White
                        Me.ListBox1.BackColor = System.Drawing.Color.White
                    Else
                        DisplayErrorMessage(7)
                    End If
                Else
                    DisplayErrorMessage(8)
                End If
            End If
        Else
            DisplayErrorMessage(0)
        End If
    End Sub
 
    Private Sub WebImageButton3_Click(ByVal sender As System.Object, ByVal e As Infragistics.WebUI.WebDataInput.ButtonEventArgs) Handles WebImageButton3.Click
        InitializeForm()
    End Sub
    Private Sub InitializeForm()
        Me.lblMessage.Text = Nothing
        Me.UPCCode.Text = Nothing
        Me.Description.Text = Nothing
        Me.Size.Text = Nothing
        Me.Qty.Text = Nothing
        Me.Price.Text = Nothing
        Me.UPCCode.ReadOnly = False
        Me.Description.ReadOnly = False
        Me.Size.ReadOnly = False
 
        Me.UPCCode.BackColor = System.Drawing.Color.White
        Me.Description.BackColor = System.Drawing.Color.White
        Me.Size.BackColor = System.Drawing.Color.White
        Me.Qty.BackColor = System.Drawing.Color.White
        Me.Price.BackColor = System.Drawing.Color.White
        Me.SizeMeasureList.Enabled = True
        SetFocus(UPCCode, Me)
        SizeMeasureList.Items.Clear()
        LoadSizeMeasure()
    End Sub
 Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged
        DataGrid1.CurrentPageIndex = e.NewPageIndex
        RetrieveSalesTPRItems()
 
        With lblMessage
            lblMessage.Visible = True
            .CssClass = "labelGreen"
            .Text = "Page: " & DataGrid1.CurrentPageIndex + 1 & " of " & DataGrid1.PageCount.ToString
        End With
    End Sub

Open in new window

0
 
Seven priceFull StackAuthor Commented:

 Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged
        DataGrid1.CurrentPageIndex = e.NewPageIndex
        RetrieveSalesTPRItems()
 
        With lblMessage
            lblMessage.Visible = True
            .CssClass = "labelGreen"
            .Text = "Page: " & DataGrid1.CurrentPageIndex + 1 & " of " & DataGrid1.PageCount.ToString
        End With
    End Sub

Open in new window

0
 
DotNetThinkerCommented:
I just mean the section of code where you're saying DataGrid1.DataSource = ...
0
 
Seven priceFull StackAuthor Commented:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            RetrieveSalesTPRItems()
            SetFocus(UPCCode, Me)
            LoadSizeMeasure()
 
            With lblMessage
                lblMessage.Visible = True
                .CssClass = "labelGreen"
                .Text = "Total Pages: " & DataGrid1.PageCount.ToString
            End With
 
            Dim StoreList() As String
            Dim I As Integer = 0
 
            StoreList = Session("Stores")
 
            For I = 0 To UBound(StoreList)
                'ListBox1.Items.Add(AddIntoDataStructureofStorelist(StoreList(I)).StoreId.ToString)
                ListBox1.Items.Add(StoreList(I).ToString)
            Next
 
            Me.UPCCode.BackColor = System.Drawing.Color.White
            Me.Description.BackColor = System.Drawing.Color.White
            Me.Size.BackColor = System.Drawing.Color.White
            Me.Qty.BackColor = System.Drawing.Color.White
            Me.Price.BackColor = System.Drawing.Color.White
        End If
    End Sub

Open in new window

0
 
Seven priceFull StackAuthor Commented:
sorry here
 Private Function RetrieveSalesTPRItems()
 
        Dim conn As New SqlConnection(Application("A1SConnString"))
        Dim cmd As New SqlCommand("spA1SGetSalesTPR", conn)
        Dim sqlDR As New SqlDataAdapter
        Dim ds As New DataSet
 
        Dim prm As New SqlParameter("@UserID", SqlDbType.VarChar, 50)
        cmd.CommandType = CommandType.StoredProcedure
        prm.Value = ReadCookie(Tokens.UserId)
        cmd.Parameters.Add(prm)
 
        conn.Open()
        sqlDR.SelectCommand = cmd
        sqlDR.Fill(ds)
 
        DataGrid1.DataSource = ds
        DataGrid1.DataBind()
 
        'Clean Up
        If (Not sqlDR Is Nothing) Then sqlDR = Nothing
        If (Not conn Is Nothing) Then
            If conn.State <> ConnectionState.Closed Then conn.Close()
            conn.Dispose()
        End If
 
    End Function

Open in new window

0
 
Seven priceFull StackAuthor Commented:
Also the stored procedure could have somthing to do with this
I thing we need to also change this case statement

--Status =  '<img alt="Sale Item Added" src=..\Images\Add.png >'
            Case      when Active = 1 Then  '<a><img alt="Active" src=..\Images\document_check.png></a>'
                        Else  '<a><img alt="Not Active" src=..\Images\document_delete.png ></a>'
ALTER PROCEDURE dbo.spA1SGetSalesTPR
 
 @UserID varchar(50)
 
AS
 
SELECT	ItemID,
		UPC, 
		[Description],  
		Quantity,
		Price,
		CreatedDate,
		ModifiedDate,
		SizeUnit,
		Stores,
		
		
		--Status =  '<img alt="Sale Item Added" src=..\Images\Add.png >' 
		Case	when Active = 1 Then  '<a><img alt="Active" src=..\Images\document_check.png></a>' 
				Else  '<a><img alt="Not Active" src=..\Images\document_delete.png ></a>' 
				
				 
			End  As 
		Stores
 
FROM A1S.dbo.SalesTPR
 
 
Where @UserID  =  UserID 
 
order by ItemID  DESC
 
DONE:
  RETURN 0
ERRORHANDLER:
  RETURN 1

Open in new window

0
 
DotNetThinkerCommented:
ok, here we go. Change your checkboxlist's DataSource to look like this...

<asp:CheckBoxList id="cblStores" DataSource='<%# (DataBinder.Eval(Container.DataItem, "Stores").ToString().Split(",")) %>' Runat="server" AutoPostBack="True"></asp:CheckBoxList>
0
 
DotNetThinkerCommented:
Sorry this has been taking so long but I haven't programmed in VB or .NET 1.1 for quite some time.
0
 
Seven priceFull StackAuthor Commented:
yes that worked now we have to bind the checkbox when it is clicked it will stay clicked
0
 
Seven priceFull StackAuthor Commented:
Or how about this can we make the checkbox the same function as the image control. Where when clicked it will tell me wheather a store owner is Active and if you uncheck it its active.
0
 
DotNetThinkerCommented:
Yeah, just define a Command and CommandArgument and you can use it just like the image
0
 
Seven priceFull StackAuthor Commented:
Like this is the image change when clicked, when this is clicked this will change the image button wheather its active click again and its not active maybe can we do the same function with the checkbox
or what ever you think right now is best.
<asp:ImageButton id="ChangeLbls" runat="server" Width="25px" AlternateText="Change Status" ImageUrl="../Images/selection_replace.png" OnCommand="ChangeItemStatus" CommandArgument='<%# Container.DataItem("ItemId") %>'>
											</asp:ImageButton>
 
 
 
 
Public Sub ChangeItemStatus(ByVal sender As Object, ByVal e As CommandEventArgs)
        Dim conn As New SqlConnection(Application("A1SConnString"))
        Dim cmd As New SqlCommand("spA1SChangeTPRItemStatus", conn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim sqlDR As SqlDataReader
 
        Dim p As New SqlParameter("@ItemID", SqlDbType.BigInt)
        p.Value = e.CommandArgument
        cmd.Parameters.Add(p)
 
        conn.Open()
        cmd.ExecuteNonQuery()
 
        'Clean Up
        If (Not sqlDR Is Nothing) Then
            If (sqlDR.IsClosed) Then
                sqlDR.Close()
            End If
        End If
        If (Not conn Is Nothing) Then
            If conn.State <> ConnectionState.Closed Then conn.Close()
            conn.Dispose()
        End If
        If Not cmd Is Nothing Then cmd.Dispose()
 
        RetrieveSalesTPRItems()
 
    End Sub

Open in new window

0
 
Seven priceFull StackAuthor Commented:
but how does that work with a checkbox.
0
 
Seven priceFull StackAuthor Commented:
But either way the check box is not binding to the Active state. when clicked and I refresh or go back its gone.
0
 
DotNetThinkerCommented:
Yeah, that's what I've been thinking about. I'm honestly not too sure how to go about binding it to the Active field. We'll probably need to use the OnLoad handler to go through each checkbox and query to see if it's active or not.
0
 
Seven priceFull StackAuthor Commented:
yea, Well even if its binded we can tell if the check box stay checked when clicked
0
 
Seven priceFull StackAuthor Commented:
And it could not bind because this is not working OnSelectedIndexChanged for some reason
<asp:CheckBoxList id="cblStores" DataSource='<%# (DataBinder.Eval(Container.DataItem, "Stores").ToString().Split(",")) %>' OnSelectedIndexChanged='<%# Container.DataItem("ItemId") %>' Font-Size="10px"  Runat="server" AutoPostBack="True" ></asp:CheckBoxList>
									

Open in new window

0
 
DotNetThinkerCommented:
Ok, the reason the SelectedIndexChanged property isn't being called is because the DataGrid is being re-bound on every postback so here's what I did...


    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            RetrieveSalesTPRItems()
        End If
    End Sub

Open in new window

0
 
DotNetThinkerCommented:
Also it should look like this...
OnSelectedIndexChanged="CheckBoxList1_SelectedIndexChanged"

Open in new window

0
 
Seven priceFull StackAuthor Commented:
Ok sorry lets scratch that, last
0
 
Seven priceFull StackAuthor Commented:
what I am trying to do is more like what populates the list box but I want same thing that populate the list box to populate the checkboxlist.

So if there is one or 2 stores listed there all we want to do is if 3 stores are listed 2 are active so we check both stores  that are active and they stay checked which is bounded to that datagrid.

Similar to the check box we have.
understand if not please i will try to clarify by email if i can.
0
 
Seven priceFull StackAuthor Commented:
So basically this list box function that bounds the store id but with the checkbox.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            RetrieveSalesTPRItems()
            SetFocus(UPCCode, Me)
            LoadSizeMeasure()
 
            With lblMessage
                lblMessage.Visible = True
                .CssClass = "labelGreen"
                .Text = "Total Pages: " & DataGrid1.PageCount.ToString
            End With
 
            Dim StoreList() As String
            Dim I As Integer = 0
 
            StoreList = Session("Stores")
 
            For I = 0 To UBound(StoreList)
                'ListBox1.Items.Add(AddIntoDataStructureofStorelist(StoreList(I)).StoreId.ToString)
                ListBox1.Items.Add(StoreList(I).ToString)
            Next
 
            Me.UPCCode.BackColor = System.Drawing.Color.White
            Me.Description.BackColor = System.Drawing.Color.White
            Me.Size.BackColor = System.Drawing.Color.White
            Me.Qty.BackColor = System.Drawing.Color.White
            Me.Price.BackColor = System.Drawing.Color.White
        End If
    End Sub
 
 Public Function VerifyStores() As Integer
        Dim i As Integer = 0
        Dim Count As Integer = 0
        Dim TempChain As Integer = -1
        Dim TempRegion As Integer = -1
        Dim TempCustomerLoyalty As Integer = -1
        Try
            For i = 0 To ListBox1.Items.Count - 1
                If ListBox1.Items(i).Selected Then
                    If Count = 0 Then
 
                        TempChain = GetChainNumber(ListBox1.Items(i).ToString)
                        TempRegion = GetRegionNumber(ListBox1.Items(i).ToString)
                        TempCustomerLoyalty = GetCustomerLoyaltyNumber(ListBox1.Items(i).ToString)
                        strselecteditems = ListBox1.Items(i).Text
                        Count = Count + 1
 
                    Else
                        If TempChain = GetChainNumber(ListBox1.Items(i).ToString) And TempRegion = GetRegionNumber(ListBox1.Items(i).ToString) And TempCustomerLoyalty = GetCustomerLoyaltyNumber(ListBox1.Items(i).ToString) Then
                            strselecteditems = strselecteditems & "," & ListBox1.Items(i).Text
                        Else
                            Return 0
                        End If
                    End If
                End If
            Next
            Return 1
        Catch ex As ApplicationException
 
        End Try
 
    End Function

Open in new window

0
 
Seven priceFull StackAuthor Commented:
So I if I can populate the checkboxlist the same as the listbox and when clicked to make it active ((which means stay clicked)) I have reached my goal.
0
 
DotNetThinkerCommented:
do you want to checkboxlist inside the datagrid or where the listbox is currently?
0
 
Seven priceFull StackAuthor Commented:
No vice versa but populated as a checkbox, So for instance both stores will show up. or if they add 5 stores they will populate where the checkbox list goes.

Because the listbox is going to be removed that is the purpose for the checkboxlist, to have the same function as the listbox but as checkboxes but you can click to keep it checked to stay active.
Public Function VerifyStores() As Integer
        Dim i As Integer = 0
        Dim Count As Integer = 0
        Dim TempChain As Integer = -1
        Dim TempRegion As Integer = -1
        Dim TempCustomerLoyalty As Integer = -1
        Try
            For i = 0 To ListBox1.Items.Count - 1
                If ListBox1.Items(i).Selected Then
                    If Count = 0 Then
 
                        TempChain = GetChainNumber(ListBox1.Items(i).ToString)
                        TempRegion = GetRegionNumber(ListBox1.Items(i).ToString)
                        TempCustomerLoyalty = GetCustomerLoyaltyNumber(ListBox1.Items(i).ToString)
                        strselecteditems = ListBox1.Items(i).Text
                        Count = Count + 1
 
                    Else
                        If TempChain = GetChainNumber(ListBox1.Items(i).ToString) And TempRegion = GetRegionNumber(ListBox1.Items(i).ToString) And TempCustomerLoyalty = GetCustomerLoyaltyNumber(ListBox1.Items(i).ToString) Then
                            strselecteditems = strselecteditems & "," & ListBox1.Items(i).Text
                        Else
                            Return 0
                        End If
                    End If
                End If
            Next
            Return 1
        Catch ex As ApplicationException
 
        End Try

Open in new window

0
 
DotNetThinkerCommented:
OK, this is the basic jist of what we need to do I'm just not sure how to get the appropriate ItemId for each row...


    Protected Sub cblStores_DataBinding(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.SelectedIndexChanged
        Dim Connection As New SqlConnection(YourConnectionString)
        Dim cmd As New SqlCommand("select active from Stores where ItemId = @ItemId and StoreId = @StoreId", Connection)
 
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
 
        Dim i As Integer
 
 
        For i = 0 To CBL.Items.Count - 1
            Dim cb As ListItem = CBL.Items(i)
 
            'Need to get ItemID associated w/ this row
            Dim ItemId As String = "?"
 
            ''Open the connection
            Connection.Open()
 
            cmd.Parameters.Add(New SqlClient.SqlParameter("ItemId", ItemId))
            cmd.Parameters.Add(New SqlClient.SqlParameter("StoreId", CType(cb.Text, Integer)))
 
            cb.Selected = Convert.ToBoolean(cmd.ExecuteScalar())
 
        Next i
 
        'Close the connection
        Connection.Close()
 
    End Sub

Open in new window

0
 
Seven priceFull StackAuthor Commented:
I think the way the list box works, A store id is assign to that user, like if i had a store i will send a screen shot it appears because i am that logged in user.
0
 
Seven priceFull StackAuthor Commented:
never mind scratch that...

how is the list box displaying the store id's
0
 
DotNetThinkerCommented:
you have a list of stores in the session variable "Stores". The program walks through that session variable and adds a new item for each store number it sees. That's basically what this is doing....


            Dim StoreList() As String
            Dim I As Integer = 0
 
            StoreList = Session("Stores")
 
            For I = 0 To UBound(StoreList)
                ListBox1.Items.Add(StoreList(I).ToString)
            Next

Open in new window

0
 
DotNetThinkerCommented:
The problem is that not only do we need the store number but we also need the Item number in order to get the correct Active field
0
 
Seven priceFull StackAuthor Commented:
Trying to see if we can find out where the listbox is binding the information then maybe we can see how it is pulling the appropriate item id
0
 
Seven priceFull StackAuthor Commented:
But whats weird about that I do not see the same store numbers in that database only the one we add when you click to add upc and description. But where is this pulling the storeids thats what i need to find then maybe that wil lhelp you on where is this pulling from
0
 
Seven priceFull StackAuthor Commented:
understand
0
 
Seven priceFull StackAuthor Commented:
So what you are saying is when the user logins in the correct item number shows up that is what displays the correct stores. So we need to do the same for the checkboxlits
0
 
Seven priceFull StackAuthor Commented:
there is a session variable that applies the list box stating how the users are  pulled in.  and there is a table called userid that i think identitfies with the itemid
0
 
DotNetThinkerCommented:
Ok, I think I have it figured out. You'll need to tweak the code a little bit as far as using the correct table names but other than that this should be a pretty good start...


HTML...
 
<asp:TemplateColumn>
	<ItemTemplate>
		<asp:CheckBoxList id="cblStores" DataSource='<%# (DataBinder.Eval(Container.DataItem, "Stores").ToString().Split(",")) %>' 
			Runat="server" AutoPostBack="True" OnSelectedIndexChanged="cblStores_SelectedIndexChanged" 
			OnLoad="cblStores_Load">
		</asp:CheckBoxList>
		<asp:Label ID="lblItemId" Runat="server" Visible="False" Text='<%# DataBinder.Eval(Container, "DataItem.ItemId") %>'>
		</asp:Label>
	</ItemTemplate>
</asp:TemplateColumn>
 
 
Code Behind...
 
    Protected Sub cblStores_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.Load
        Dim Connection As New SqlConnection(YourConnectionString)
        Dim cmd As New SqlCommand("select active from Stores where ItemId = @ItemId and StoreId = @StoreId", Connection)
 
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
        Dim Lbl As Label
        Dim ctrl As Control
 
        For Each ctrl In CBL.Parent.Controls
            If TypeOf ctrl Is Label Then
                Lbl = CType(ctrl, Label)
            End If
        Next
 
 
 
        Dim i As Integer
 
        For i = 0 To CBL.Items.Count - 1
            Dim cb As ListItem = CBL.Items(i)
            'Need to get ItemID associated w/ this row
            Dim ItemId As String = "?"
 
            'Open the connection
            Connection.Open()
 
            'Add the parameters to your Update Command
            cmd.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
            cmd.Parameters.Add(New SqlClient.SqlParameter("@StoreId", CType(cb.Text, Integer)))
 
            cb.Selected = Convert.ToBoolean(cmd.ExecuteScalar())
 
        Next i
 
        'Close the connection
        Connection.Close()
 
    End Sub

Open in new window

0
 
Seven priceFull StackAuthor Commented:
received this error
occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30456: 'cblStores_SelectedIndexChanged' is not a member of 'ASP.SalesTPR_aspx'.

Source Error:

 

Line 99:                                                       <asp:TemplateColumn>
Line 100:      <ItemTemplate>
Line 101:            <asp:CheckBoxList id="cblStores" DataSource='<%# (DataBinder.Eval(Container.DataItem, "Stores").ToString().Split(",")) %>'
Line 102:                  Runat="server" AutoPostBack="True" OnSelectedIndexChanged="cblStores_SelectedIndexChanged"
Line 103:                  OnLoad="cblStores_Load">
 

 Protected Sub cblStores_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.Load
        Dim Connection As New SqlConnection("A1SConnString")
        Dim cmd As New SqlCommand("select active from Stores where ItemId = @ItemId and StoreId = @StoreId", Connection)
 
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
        Dim Lbl As Label
        Dim ctrl As Control
 
        For Each ctrl In CBL.Parent.Controls
            If TypeOf ctrl Is Label Then
                Lbl = CType(ctrl, Label)
            End If
        Next
 
 
 
        Dim i As Integer
 
        For i = 0 To CBL.Items.Count - 1
            Dim cb As ListItem = CBL.Items(i)
            'Need to get ItemID associated w/ this row
            Dim ItemId As String = "?"
 
            'Open the connection
            Connection.Open()
 
            'Add the parameters to your Update Command
            cmd.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
            cmd.Parameters.Add(New SqlClient.SqlParameter("@StoreId", CType(cb.Text, Integer)))
 
            cb.Selected = Convert.ToBoolean(cmd.ExecuteScalar())
 
        Next i
 
        'Close the connection
        Connection.Close()
 
    End Sub
End Class
\
 
 
////aspnet
 
 
<asp:CheckBoxList id="cblStores" DataSource='<%# (DataBinder.Eval(Container.DataItem, "Stores").ToString().Split(",")) %>' 
			Runat="server" AutoPostBack="True" OnSelectedIndexChanged="cblStores_SelectedIndexChanged" 
			OnLoad="cblStores_Load">
		</asp:CheckBoxList>
		<asp:Label ID="lblItemId" Runat="server" Visible="False" Text='<%# DataBinder.Eval(Container, "DataItem.ItemId") %>'>
		</asp:Label>

Open in new window

0
 
DotNetThinkerCommented:
ok, go ahead and get rid of the OnSelectedIndexChanged for now. Lets just get it displaying correctly and we'll go from there. This is the Load function that's working for me....


    Protected Sub cblStores_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.Load
        Dim Connection As New SqlConnection(YourConnectionString)
        Dim cmd As New SqlCommand("select active from Stores where ItemId = @ItemId and StoreId = @StoreId", Connection)
        
        'Insert your table name into the above query
 
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
        Dim Lbl As Label
        Dim ctrl As Control
 
        For Each ctrl In CBL.Parent.Controls
            If TypeOf ctrl Is Label Then
                Lbl = CType(ctrl, Label)
            End If
        Next
 
 
 
        Dim i As Integer
        Connection.Open()
 
        For i = 0 To CBL.Items.Count - 1
            Dim cb As ListItem = CBL.Items(i)
            'Need to get ItemID associated w/ this row
            Dim ItemId As String = "?"
 
            'Open the connection
 
            'Add the parameters to your Update Command
            cmd.Parameters.Clear()
            cmd.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
            cmd.Parameters.Add(New SqlClient.SqlParameter("@StoreId", cb.Text))
 
            cb.Selected = Convert.ToBoolean(cmd.ExecuteScalar())
 
        Next i
 
        'Close the connection
        Connection.Close()
 
    End Sub

Open in new window

0
 
Seven priceFull StackAuthor Commented:
I did and this is the error i am receiving.
Format of the initialization string does not conform to specification starting at index 0. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
 
Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.
 
Source Error: 
 
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  
 
Stack Trace: 
 
 
[ArgumentException: Format of the initialization string does not conform to specification starting at index 0.]
   System.Data.Common.DBConnectionString.GetKeyValuePair(Char[] connectionString, Int32 currentPosition, String& key, Char[] valuebuf, Int32& vallength, Boolean& isempty) +942
   System.Data.Common.DBConnectionString.ParseInternal(Char[] connectionString, UdlSupport checkForUdl, NameValuePair& keychain) +116
   System.Data.Common.DBConnectionString..ctor(String connectionString, UdlSupport checkForUdl) +114
   System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +13
   System.Data.SqlClient.SqlConnectionString.ParseString(String connectionString) +96
   System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +11
   System.Data.SqlClient.SqlConnection..ctor(String connectionString) +158
   A1S.SalesTPR.cblStores_Load(Object sender, EventArgs e) in \\10.52.90.1\a1s\store\SalesTPR.aspx.vb:842
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Page.ProcessRequestMain() +739
 
 

Open in new window

0
 
DotNetThinkerCommented:
Are you using this... Application("A1SConnString")... as the connection string?
0
 
Seven priceFull StackAuthor Commented:
Yes
0
 
Seven priceFull StackAuthor Commented:

Protected Sub cblStores_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.Load
        Dim Connection As New SqlConnection("A1SConnString")
        Dim cmd As New SqlCommand("select Active from Stores where ItemId = @ItemId and Store = @Store", Connection)
 
        'Insert your table name into the above query
 
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
        Dim Lbl As Label
        Dim ctrl As Control
 
        For Each ctrl In CBL.Parent.Controls
            If TypeOf ctrl Is Label Then
                Lbl = CType(ctrl, Label)
            End If
        Next
 
 
 
        Dim i As Integer
        Connection.Open()
 
        For i = 0 To CBL.Items.Count - 1
            Dim cb As ListItem = CBL.Items(i)
            'Need to get ItemID associated w/ this row
            Dim ItemId As String = "?"
 
            'Open the connection
 
            'Add the parameters to your Update Command
            cmd.Parameters.Clear()
            cmd.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
            cmd.Parameters.Add(New SqlClient.SqlParameter("@StoreId", cb.Text))
 
            cb.Selected = Convert.ToBoolean(cmd.ExecuteScalar())
 
        Next i
 
        'Close the connection
        Connection.Close()
 
    End Sub
End Class

Open in new window

0
 
DotNetThinkerCommented:
Dim Connection As New SqlConnection("A1SConnString") should be Dim Connection As New SqlConnection(Application("A1SConnString"))
0
 
Seven priceFull StackAuthor Commented:
when i remove this this seems brink up the checkbox again but now 2 just one
OnLoad="cblStores_Load"

Open in new window

0
 
DotNetThinkerCommented:
You don't want to get rid of the OnLoad. Just make the change to the connection string that I mentioned above and you should be good to go.
0
 
Seven priceFull StackAuthor Commented:
No that change was there with the connection string but I still get that error,
0
 
DotNetThinkerCommented:
how about the query below? Be sure the table name and column names are correct.
Dim cmd As New SqlCommand("select Active from Stores where ItemId = @ItemId and Store = @Store", Connection)

Open in new window

0
 
Seven priceFull StackAuthor Commented:
No I tried it for the large database and the smaller no matter what that error comes up
Protected Sub cblStores_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.Load
        Dim Connection As New SqlConnection("A1SConnString")
        Dim cmd As New SqlCommand("select Active from Stores where ItemId = @ItemId and Stores = @Stores", Connection)
        ' Dim cmd As New SqlCommand("select active from Stores where ItemId = @ItemId and StoreId = @StoreId", Connection)
 
        'Insert your table name into the above query
 
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
        Dim Lbl As Label
        Dim ctrl As Control
 
        For Each ctrl In CBL.Parent.Controls
            If TypeOf ctrl Is Label Then
                Lbl = CType(ctrl, Label)
            End If
        Next
 
 
 
        Dim i As Integer
        Connection.Open()
 
        For i = 0 To CBL.Items.Count - 1
            Dim cb As ListItem = CBL.Items(i)
            'Need to get ItemID associated w/ this row
            Dim ItemId As String = "?"
 
            'Open the connection
 
            'Add the parameters to your Update Command
            cmd.Parameters.Clear()
            cmd.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
            cmd.Parameters.Add(New SqlClient.SqlParameter("@Stores", cb.Text))
 
            cb.Selected = Convert.ToBoolean(cmd.ExecuteScalar())
 
        Next i
 
        'Close the connection
        Connection.Close()
 
    End Sub
End Class

Open in new window

0
 
DotNetThinkerCommented:
ok, here's what your cblStores_Load Sub should look like only you should be querying the table that consists of TPRStoreId, ItemId, Store, and Active.
    Protected Sub cblStores_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.Load
        Dim Connection As New SqlConnection(Application("A1SConnString"))
        Dim cmd As New SqlCommand("select Active from Stores where ItemId = @ItemId and Store = @Store", Connection)
        '***************
        'In the above query: Query the table with the columns TPRStoreId, ItemId, Store, Active
        '***************
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
        Dim Lbl As Label
        Dim ctrl As Control
 
        For Each ctrl In CBL.Parent.Controls
            If TypeOf ctrl Is Label Then
                Lbl = CType(ctrl, Label)
            End If
        Next
 
 
 
        Dim i As Integer
        Connection.Open()
 
        For i = 0 To CBL.Items.Count - 1
            Dim cb As ListItem = CBL.Items(i)
            'Need to get ItemID associated w/ this row
            Dim ItemId As String = "?"
 
            'Open the connection
 
            'Add the parameters to your Update Command
            cmd.Parameters.Clear()
            cmd.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
            cmd.Parameters.Add(New SqlClient.SqlParameter("@Store", cb.Text))
 
            cb.Selected = Convert.ToBoolean(cmd.ExecuteScalar())
 
        Next i
 
        'Close the connection
        Connection.Close()
 
    End Sub

Open in new window

0
 
DotNetThinkerCommented:
I'm not sure what the table's name is that I'm referring to in the above but it's probably "Items" or something like that.
0
 
Seven priceFull StackAuthor Commented:
correct but when I tested it, it is not bounded to the datagrid I think thats why I was just using the old data that was pulling to make sure everything at least works. But do you understand me about the list box.
0
 
DotNetThinkerCommented:
I'm not sure I do. I thought you already had the DataGrid bound to the correct data?
0
 
DotNetThinkerCommented:
What's the spA1SGetSalesTPR stored procedure look like?
0
 
Seven priceFull StackAuthor Commented:

ALTER PROCEDURE dbo.spA1SGetStoreTPR
 
 @Store char(6)
 
AS
 
SELECT	TPRStoreID,
		ItemId, 
		Active,
		Store,
		
		
			Case	when Active = 1 Then  '<a></a>' 
				Else  '<a></a>' 
				
				 
			End  As 
		Store
 
FROM A1S.dbo.StoreTPR
 
 
Where @Store  =  Store 
 
order by TPRStoreID  DESC
 
DONE:
  RETURN 0
ERRORHANDLER:
  RETURN 1

Open in new window

0
 
DotNetThinkerCommented:
Didn't we already bind the checkboxlist with this...
<asp:CheckBoxList id="cblStores" DataSource='<%# (DataBinder.Eval(Container.DataItem, "Stores").ToString().Split(",")) %>'
Runat="server" AutoPostBack="True" OnSelectedIndexChanged="cblStores_SelectedIndexChanged" OnLoad="cblStores_Load">
</asp:CheckBoxList>

Open in new window

0
 
Seven priceFull StackAuthor Commented:
and this one is I think gets the userid which is the table i sent you earlier combine with the StoreID
ALTER PROCEDURE dbo.spA1SGetUserStores 
  @UserID	VARCHAR(15)
AS
  SET NOCOUNT ON
select StoreID from UserStore where UserID IN (SELECT UserKey from Users WHERE UserID = @UserID) ORDER BY StoreID
		
DONE:
  RETURN 0
ERRORHANDLER:
  RETURN 1

Open in new window

0
 
Seven priceFull StackAuthor Commented:
but instead of Stores the actuall database he created is store. but what i was told that the listbox display has to display the same way in the datagrid, everything must be mis understood when they first told me.
So the listbox into the datagrid as checkboxes is what we need to focus on.
0
 
DotNetThinkerCommented:
OK, let's start from the beginning...
Other than the checkboxes, is everything being bound in the datagrid as it should be?
0
 
Seven priceFull StackAuthor Commented:
yes
0
 
DotNetThinkerCommented:
ok, so you have your items displayed correctly. Do you want every store id shown in every row?
0
 
Seven priceFull StackAuthor Commented:
yes just like the list box correct
0
 
DotNetThinkerCommented:
ok, first add this to the HTML for you datagrid...
OnItemDataBound="DataGrid1_ItemDataBound"

Open in new window

0
 
DotNetThinkerCommented:
Then add this to your code...
Protected Sub dgParents_ItemDataBound(ByVal sender As System.Object, ByVal e as DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
{
 
}

Open in new window

0
 
DotNetThinkerCommented:
sorry that should be Protected Sub DataGrid1_ItemDataBound
0
 
Seven priceFull StackAuthor Commented:

Protected Sub DataGrid1_ItemDataBound(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.Load
    

Open in new window

0
 
Seven priceFull StackAuthor Commented:
ok sorry
Protected Sub dgParents_ItemDataBound(ByVal sender As System.Object, ByVal e as DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
{
 
}

Open in new window

0
 
Seven priceFull StackAuthor Commented:
like this correct i think the brackets is c#
Protected Sub dgParents_ItemDataBound(ByVal sender As System.Object, ByVal e As DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
 
    End Sub

Open in new window

0
 
DotNetThinkerCommented:
this is what it should look like...
Protected Sub DataGrid1_ItemDataBound(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGrid1.ItemDataBound
 
'... code will go here to bind the checkbox
 
End Sub

Open in new window

0
 
Seven priceFull StackAuthor Commented:
oh yeah lol no problem understood
0
 
Seven priceFull StackAuthor Commented:

<asp:CheckBoxList id="cblStores" DataSource='<%# (DataBinder.Eval(Container.DataItem, "Stores").ToString().Split(",")) %>' 
			Runat="server" AutoPostBack="True" Font-Size="10px" OnItemDataBound="DataGrid1_ItemDataBound">
		</asp:CheckBoxList>

Open in new window

0
 
DotNetThinkerCommented:
no no no... in the datagrid definition like this...




<asp:datagrid id="DataGrid1" runat="server" AutoGenerateColumns="False" ShowHeader="False" CellPadding="1"
ItemStyle-Height="25px" ItemStyle-CssClass="prgdata" AlternatingItemStyle-BackColor="white"
OnItemDataBound="DataGrid1_ItemDataBound" cssclass="prgdata" AllowPaging="True" PageSize="7" Width="1004px"
DataKeyField="ItemID">

Open in new window

0
 
DotNetThinkerCommented:
then copy & paste this as the Sub declaration (notice the change in the definition of 'e')...


Protected Sub DataGrid1_ItemDataBound(ByVal sender As System.Object, ByVal e As DataGridItemEventArgs) Handles DataGrid1.ItemDataBound

Open in new window

0
 
Seven priceFull StackAuthor Commented:
got it
0
 
DotNetThinkerCommented:
Ok, now comment out your current CheckBoxList and we'll start fresh...


<asp:CheckBoxList id="cblStores" runat="server"></asp:CheckBoxList>

Open in new window

0
 
Seven priceFull StackAuthor Commented:

Compilation Error 
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. 
 
Compiler Error Message: BC30456: 'DataGrid1_ItemDataBound' is not a member of 'ASP.SalesTPR_aspx'.
 
Source Error:
 
 
 
Line 68:   <TR style="WIDTH: 1004px; HEIGHT: 200px">
Line 69:     <TD style="WIDTH: 1004px; HEIGHT: 197px" vAlign=top colSpan=7>
Line 70: <asp:datagrid id=DataGrid1 OnItemDataBound="DataGrid1_ItemDataBound" runat="server" Width="1004px" PageSize="7" AllowPaging="True" cssclass="prgdata" AlternatingItemStyle-BackColor="white" ItemStyle-CssClass="prgdata" ItemStyle-Height="25px" CellPadding="1" ShowHeader="false" AutoGenerateColumns="False">
Line 71: 								<AlternatingItemStyle BackColor="White"></AlternatingItemStyle>
Line 72: 								<ItemStyle Height="25px" CssClass="prgdata"></ItemStyle>
 

Open in new window

0
 
Seven priceFull StackAuthor Commented:
never mind i compiled it no problem ok on the to vb
0
 
Seven priceFull StackAuthor Commented:
ok ready
0
 
DotNetThinkerCommented:
ok, the first step is to add this to the Sub we just created...
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
 
        End If

Open in new window

0
 
Seven priceFull StackAuthor Commented:

 Protected Sub DataGrid1_ItemDataBound(ByVal sender As System.Object, ByVal e As DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
        Dim Connection As New SqlConnection(Application("A1SConnString"))
 
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
 
        End If
 
    End Sub

Open in new window

0
 
DotNetThinkerCommented:
Ok, I'm back. The next thing you want to do is grab your CheckBoxList....
        Dim Connection As New SqlConnection(Application("A1SConnString"))
 
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
 
            Dim CBL As CheckBoxList = CType(e.Item.FindControl("cblStores"), CheckBoxList)
 
        End If

Open in new window

0
 
Seven priceFull StackAuthor Commented:
ok cool then we add a control correct
0
 
DotNetThinkerCommented:
let's not jump ahead. I want to take this one step at a time so we don't lose focus. Right now you should have the old CheckBoxList commented out and replaced with a generic one like this...
<asp:CheckBoxList ID="cblStores" runat="server"></asp:CheckBoxList>

Open in new window

0
 
DotNetThinkerCommented:
Do you know which query you're planning on using to get the store numbers we want?
0
 
Seven priceFull StackAuthor Commented:
that is correct
0
 
Seven priceFull StackAuthor Commented:
this is where the listbox is pulling the information I think/
ALTER PROCEDURE dbo.spA1SGetUserStores 
  @UserID	VARCHAR(15)
AS
  SET NOCOUNT ON
select StoreID from UserStore where UserID IN (SELECT UserKey from Users WHERE UserID = @UserID) ORDER BY StoreID
		
DONE:
  RETURN 0
ERRORHANDLER:
  RETURN 1

Open in new window

0
 
DotNetThinkerCommented:
ok, we'll go ahead and use it then...


        Dim Connection As New SqlConnection(Application("A1SConnString"))
 
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
 
            Dim CBL As CheckBoxList = CType(e.Item.FindControl("cblStores"), CheckBoxList)
            Dim Command As New SqlCommand("spA1SGetUserStores", Connection)
            Command.CommandType = CommandType.StoredProcedure
            Dim Param As New SqlParameter("@UserID", SqlDbType.VarChar, 50)
            Param.Value = ReadCookie(Tokens.UserId)
            Command.Parameters.Add(Param)
 
        End If

Open in new window

0
 
Seven priceFull StackAuthor Commented:
ok that is in place
0
 
DotNetThinkerCommented:
Now the final touch; this should create the checkboxes we want so go ahead and try it out to see if it works....
        Dim Connection As New SqlConnection(Application("A1SConnString"))
 
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
 
            Dim CBL As CheckBoxList = CType(e.Item.FindControl("cblStores"), CheckBoxList)
            Dim Command As New SqlCommand("spA1SGetUserStores", Connection)
            Command.CommandType = CommandType.StoredProcedure
            Dim Param As New SqlParameter("@UserID", SqlDbType.VarChar, 50)
            Param.Value = ReadCookie(Tokens.UserId)
            Command.Parameters.Add(Param)
 
            If Connection.State <> ConnectionState.Open Then
                Connection.Open()
            End If
            CBL.DataSource = Command.ExecuteReader()
            CBL.DataBind()
            Connection.Close()
 
        End If

Open in new window

0
 
DotNetThinkerCommented:
try this...
        Dim Connection As New SqlConnection(Application("A1SConnString"))
 
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
 
            Dim CBL As CheckBoxList = CType(e.Item.FindControl("cblStores"), CheckBoxList)
            Dim Command As New SqlCommand("spA1SGetUserStores", Connection)
            Command.CommandType = CommandType.StoredProcedure
            Dim Param As New SqlParameter("@UserID", SqlDbType.VarChar, 50)
            Param.Value = ReadCookie(Tokens.UserId)
            Command.Parameters.Add(Param)
            Dim SDA As New SqlDataAdapter(Command)
            Dim StoreTable As DataTable
 
            If Connection.State <> ConnectionState.Open Then
                Connection.Open()
            End If
            SDA.Fill(StoreTable)
 
            CBL.DataSource = StoreTable
            CBL.DataTextField = "StoreID"
            CBL.DataBind()
            Connection.Close()
 
        End If

Open in new window

0
 
Seven priceFull StackAuthor Commented:
sent you a screen shot
0
 
Seven priceFull StackAuthor Commented:

Value cannot be null. Parameter name: dataTable 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
 
Exception Details: System.ArgumentNullException: Value cannot be null. Parameter name: dataTable
 
Source Error: 
 
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Open in new window

0
 
DotNetThinkerCommented:
Try setting a breakpoint in the Sub to see where it's crashing. I don't really understand that because we're never using a parameter named dataTable
0
 
DotNetThinkerCommented:
ok, i got it...
'Dim StoreTable as DataTable - OLD
Dim StoreTable As New DataTable("Stores") ' - NEW

Open in new window

0
 
Seven priceFull StackAuthor Commented:
better
0
 
DotNetThinkerCommented:
ok, on to setting the checkboxes to checked or unchecked...
0
 
Seven priceFull StackAuthor Commented:
lol cool
0
 
DotNetThinkerCommented:
go ahead and change the CBL to look like this....
<asp:CheckBoxList id="cblStores" Runat="server" OnLoad="cblStores_Load"></asp:CheckBoxList>

Open in new window

0
 
DotNetThinkerCommented:
Then, if it's not already there, create the Sub in the code...
Protected Sub cblStores_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.Load
 
End Sub

Open in new window

0
 
Seven priceFull StackAuthor Commented:

Object reference not set to an instance of an object. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
 
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
 
Source Error: 

Open in new window

0
 
Seven priceFull StackAuthor Commented:
oh ok one sec
0
 
Seven priceFull StackAuthor Commented:
The sub for onItemDataBound="DataGrid1_ItemDataBound" is there do you want me to change it.
Protected Sub DataGrid1_ItemDataBound(ByVal sender As System.Object, ByVal e As DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
        Dim Connection As New SqlConnection(Application("A1SConnString"))
 
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
 
            Dim CBL As CheckBoxList = CType(e.Item.FindControl("cblStores"), CheckBoxList)
            Dim Command As New SqlCommand("spA1SGetUserStores", Connection)
            Command.CommandType = CommandType.StoredProcedure
            Dim Param As New SqlParameter("@UserID", SqlDbType.VarChar, 50)
            Param.Value = ReadCookie(Tokens.UserId)
            Command.Parameters.Add(Param)
            Dim SDA As New SqlDataAdapter(Command)
 
            Dim StoreTable As New DataTable("Stores")
 
            If Connection.State <> ConnectionState.Open Then
                Connection.Open()
            End If
            SDA.Fill(StoreTable)
 
            CBL.DataSource = StoreTable
            CBL.DataTextField = "StoreID"
            CBL.DataBind()
            Connection.Close()
 
        End If

Open in new window

0
 
DotNetThinkerCommented:
don't do anything to the DataGrid1_ItemDataBound Sub. We have that working just like we want it.
0
 
Seven priceFull StackAuthor Commented:
ok but when i add the

<asp:CheckBoxList ID="cblStores" runat="server" Font-Size="10px" RepeatDirection="Horizontal"   OnLoad="cblStores_Load"
Object reference not set to an instance of an object. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
 
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Open in new window

0
 
DotNetThinkerCommented:
did you add the "Protected Sub cblStores_Load..." to the code?
0
 
DotNetThinkerCommented:
if so be sure there's nothing inside the Sub for now.
0
 
Seven priceFull StackAuthor Commented:

Protected Sub cblStores_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.Load
 
    End Sub
End Class

Open in new window

0
 
Seven priceFull StackAuthor Commented:
should we use this to determine the active and inactive
Protected Sub cblStores_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.Load
        Dim Connection As New SqlConnection(Application("A1SConnString"))
        Dim cmd As New SqlCommand("spA1SGetStoreTpr")
        cmd.CommandType = CommandType.StoredProcedure
        'Dim cmd As New SqlCommand("select Active from Stores where ItemId = @ItemId and Store = @Store", Connection)
        '***************
 
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
        Dim Lbl As Label
        Dim ctrl As Control
 
        For Each ctrl In CBL.Parent.Controls
            If TypeOf ctrl Is Label Then
                Lbl = CType(ctrl, Label)
            End If
        Next
 
 
 
        Dim i As Integer
        Connection.Open()
 
        For i = 0 To CBL.Items.Count - 1
            Dim cb As ListItem = CBL.Items(i)
            'Need to get ItemID associated w/ this row
            Dim ItemId As String = "?"
 
            'Open the connection
 
            'Add the parameters to Update Command
            cmd.Parameters.Clear()
            cmd.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
            cmd.Parameters.Add(New SqlClient.SqlParameter("@Store", cb.Text))
 
            cb.Selected = Convert.ToBoolean(cmd.ExecuteScalar())
 
        Next i
 
        'Close the connection
        Connection.Close()
 
    End Sub

Open in new window

0
 
DotNetThinkerCommented:
right now I have this...


    Protected Sub cblStores_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.Load
        Dim Connection As New SqlConnection(Application("A1SConnString"))
        Dim cmd As New SqlCommand("select Active from StoreTPR where ItemId = @ItemId and Store = @Store", Connection)
 
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
        Dim Lbl As Label
        Dim ctrl As Control
 
        For Each ctrl In CBL.Parent.Controls
            If TypeOf ctrl Is Label Then
                Lbl = CType(ctrl, Label)
            End If
        Next
 
        Dim i As Integer
        Connection.Open()
 
        For i = 0 To CBL.Items.Count - 1
            Dim cb As ListItem = CBL.Items(i)
            'Need to get ItemID associated w/ this row
            Dim ItemId As String = "?"
 
            'Open the connection
 
            'Add the parameters to your Update Command
            cmd.Parameters.Clear()
            cmd.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
            cmd.Parameters.Add(New SqlClient.SqlParameter("@Store", cb.Text))
 
            cb.Selected = Convert.ToBoolean(cmd.ExecuteScalar())
 
        Next i
 
        'Close the connection
        Connection.Close()
 
    End Sub

Open in new window

0
 
Seven priceFull StackAuthor Commented:
Correct I have that come up with this
Object reference not set to an instance of an object. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
 
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
 
Source Error: 
 
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  
 
Stack Trace: 
 
 
[NullReferenceException: Object reference not set to an instance of an object.]
   A1S.SalesTPR.cblStores_Load(Object sender, EventArgs e) in \\10.52.90.1\a1s\store\SalesTPR.aspx.vb:893
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Page.ProcessRequestMain() +739
 
 

Open in new window

0
 
DotNetThinkerCommented:
ok, that's why I want to go ahead and comment everything out and start new. There's no way to tell where the error is happening right now.
0
 
Seven priceFull StackAuthor Commented:
ok
0
 
DotNetThinkerCommented:
Go ahead and comment all of it out and uncomment it in the sequence below. If the program compiles after you uncomment a section, leave it uncommented. Let me know where you get the error.
    Protected Sub cblStores_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.Load
    ' **FIRST**
    '    Dim Connection As New SqlConnection(Application("A1SConnString"))
    '    Dim cmd As New SqlCommand("select Active from StoreTPR where ItemId = @ItemId and Store = @Store", Connection)
 
    ' **SECOND**
    '    Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
    '    Dim Lbl As Label
    '    Dim ctrl As Control
 
    ' **THIRD**
    '    For Each ctrl In CBL.Parent.Controls
    '        If TypeOf ctrl Is Label Then
    '            Lbl = CType(ctrl, Label)
    '        End If
    '    Next
 
    ' **FOURTH**
    '    Dim i As Integer
    '    Connection.Open()
 
    ' **FIFTH**
    '    For i = 0 To CBL.Items.Count - 1
    '        Dim cb As ListItem = CBL.Items(i)
 
    '        cmd.Parameters.Clear()
    '        cmd.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
    '        cmd.Parameters.Add(New SqlClient.SqlParameter("@Store", cb.Text))
 
    '        cb.Selected = Convert.ToBoolean(cmd.ExecuteScalar())
 
    '    Next i
 
    '    Connection.Close()
 
    End Sub

Open in new window

0
 
Seven priceFull StackAuthor Commented:
Ok when i comment out Dim cb As ListItem = CBL.Items(i)
there is no error just tells me cb is not declared but when i uncomment this i get .

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace:


[NullReferenceException: Object reference not set to an instance of an object.]
   A1S.SalesTPR.cblStores_Load(Object sender, EventArgs e) in \\10.52.90.1\a1s\store\SalesTPR.aspx.vb:893
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Page.ProcessRequestMain() +739

 

Dim cb As ListItem = CBL.Items(i)
 
 
 
 ' **FIFTH**
        For i = 0 To CBL.Items.Count - 1
            Dim cb As ListItem = CBL.Items(i)
 
            cmd.Parameters.Clear()
            cmd.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
            cmd.Parameters.Add(New SqlClient.SqlParameter("@Store", cb.Text))
 
            cb.Selected = Convert.ToBoolean(cmd.ExecuteScalar())
 
        Next i
 
        Connection.Close()
 
    End Sub

Open in new window

0
 
DotNetThinkerCommented:
ok try replacing the For statement with this. It should tell us a little about what's going on.
        For i = 0 To CBL.Items.Count - 1
            'Dim cb As ListItem = CBL.Items(i)
            Dim s As String = CBL.Items(i).Text
 
            'cmd.Parameters.Clear()
            'cmd.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
            'cmd.Parameters.Add(New SqlClient.SqlParameter("@Store", cb.Text))
 
            'cb.Selected = Convert.ToBoolean(cmd.ExecuteScalar())
 
        Next i

Open in new window

0
 
DotNetThinkerCommented:
Also, do me a favor and post your code for the whole cblStores_Load Sub
0
 
Seven priceFull StackAuthor Commented:

 Protected Sub cblStores_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.Load
        ' **FIRST**
        Dim Connection As New SqlConnection(Application("A1SConnString"))
        Dim cmd As New SqlCommand("select Active from StoreTPR where ItemId = @ItemId and Store = @Store", Connection)
 
        ' **SECOND**
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
        Dim Lbl As Label
        Dim ctrl As Control
 
        ' **THIRD**
        For Each ctrl In CBL.Parent.Controls
            If TypeOf ctrl Is Label Then
                Lbl = CType(ctrl, Label)
            End If
        Next
 
        ' **FOURTH**
        Dim i As Integer
        Connection.Open()
 
        ' **FIFTH**
        For i = 0 To CBL.Items.Count - 1
            'Dim cb As ListItem = CBL.Items(i)
            Dim s As String = CBL.Items(i).Text
 
            'cmd.Parameters.Clear()
            'cmd.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
            'cmd.Parameters.Add(New SqlClient.SqlParameter("@Store", cb.Text))
 
            'cb.Selected = Convert.ToBoolean(cmd.ExecuteScalar())
 
        Next i
        '    Connection.Close()
 
    End Sub
End Class

Open in new window

0
 
DotNetThinkerCommented:
OK, forget what I posted. Put the code back to the way it was when you got the error. The following line of the error tells us where the problem is...

A1S.SalesTPR.cblStores_Load(Object sender, EventArgs e) in \\10.52.90.1\a1s\store\SalesTPR.aspx.vb:893

If you go into your code page and click ctrl+g you can go to a specific line, go to line 893, that's where the error is.
0
 
Seven priceFull StackAuthor Commented:
Yes this one what I said before is it the Item ID
    cmd.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))

Open in new window

0
 
DotNetThinkerCommented:
ok, I'm guessing you probably don't have the label in there like this...
<asp:TemplateColumn>
    <ItemTemplate>
	<asp:CheckBoxList id="cblStores" Runat="server" OnLoad="cblStores_Load">
	</asp:CheckBoxList>
	<asp:Label ID="lblItemId" Runat="server" Visible="False" Text='<%# DataBinder.Eval(Container, "DataItem.UPC") %>'>
	</asp:Label>
    </ItemTemplate>
</asp:TemplateColumn>

Open in new window

0
 
Seven priceFull StackAuthor Commented:
ok everything is fine I had the label without run-at server but everything is the same it loads the listbox but
this is not for the check function correct.
0
 
DotNetThinkerCommented:
Are the checkboxes checked as they should be?
0
 
Seven priceFull StackAuthor Commented:
no they are not
0
 
DotNetThinkerCommented:
OK, in the StoreTPR table there is a column for ItemID. Those Ids need to exactly match either the ItemID or UPC from the large table we're using to bind to the datagrid. Does that make sense?
0
 
Seven priceFull StackAuthor Commented:
sent you an email also these are assigned in which when you login, the store id numbers.

refer to email last sent.
0
 
DotNetThinkerCommented:
OK, that makes sense. I guess my question is what's the primary key for the SalesTPR table?
0
 
Seven priceFull StackAuthor Commented:
there is no primary key set
0
 
Seven priceFull StackAuthor Commented:
stored procedure for salesTPR if you might want to see it
ALTER PROCEDURE dbo.spA1SGetSalesTPR
 
 @UserID varchar(50)
 
AS
 
SELECT	ItemID,
		UPC, 
		[Description],  
		Quantity,
		Price,
		CreatedDate,
		ModifiedDate,
		SizeUnit,
		Stores,
		Active,
		
		
		
		--Status =  '<img alt="Sale Item Added" src=..\Images\Add.png >' 
		Case	when Active = 1 Then  '<a><img alt="Active" src=..\Images\document_check.png></a>' 
				Else  '<a><img alt="Not Active" src=..\Images\document_delete.png ></a>' 
				
				 
			End  As 
		Active
 
FROM A1S.dbo.SalesTPR
 
 
Where @UserID  =  UserID 
 
order by ItemID  DESC
 
DONE:
  RETURN 0
ERRORHANDLER:
  RETURN 1

Open in new window

0
 
DotNetThinkerCommented:
OK, that's a huge no-no when it comes to data. The fact that there is no primary key (combined or not) means there's no way for us to update the table without possibly affecting data unintentionally. I guess my suggestion would be that whenever (with a few exceptions) you create a table you should create an ID field for that table. The ID field can then be set as the primary key. That's something you're going to have to add to this table before we can really move forward. Every table you ever create needs a primary key in order to maintain data integrity.
0
 
DotNetThinkerCommented:
Ok, so how should we proceed? You should probably go ahead and update your StoresTPR table to reflect the correct "active" values.
0
 
Seven priceFull StackAuthor Commented:
i see i guess we can use that table then i emailed it to you.
0
 
DotNetThinkerCommented:
Sorry, I didn't mean to jump all over you on that, it's just a pretty important method to practice.
0
 
Seven priceFull StackAuthor Commented:
oh yea understandable
0
 
DotNetThinkerCommented:
did you add the row I emailed you to the StoreTPR table? If so, try running the program again & see if one of the checkboxes is checked.
0
 
Seven priceFull StackAuthor Commented:
replyied by email
0
 
DotNetThinkerCommented:
OK, first off add the OnSelectedIndexChanged property to your CBL (also set AutoPostBack to true if it isn't already) so it looks like so...
<asp:CheckBoxList id="cblStores" Runat="server" AutoPostBack="True" OnLoad="cblStores_Load" OnSelectedIndexChanged="cblStores_SelectedIndexChanged"></asp:CheckBoxList>

Open in new window

0
 
DotNetThinkerCommented:
Then, if it's not already there create the Sub in your code. If it is there comment out everything inside it and recompile to check for errors.
Protected Sub cblStores_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.SelectedIndexChanged
 
End Sub

Open in new window

0
 
Seven priceFull StackAuthor Commented:
got it
0
 
Seven priceFull StackAuthor Commented:
I jumped i thought this will work
Protected Sub cblStores_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.SelectedIndexChanged
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
 
        Dim YourConnectionString As String = "A1SConnString" 'this should be connection string text
 
        'Create Connection String
        Dim conn As New SqlConnection(Application("StoreTPR"))
 
        'Open the connection
        conn.Open()
 
        'Create  Update Sql Command
        Dim UpdateCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("Update Store set Active = @Active" + _
        " where ItemId = @ItemId", conn)
 
        'Add the parameters to Update Command
        UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("Active", CType(CBL.SelectedItem.Selected, Integer)))
        UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("ItemId", CBL.SelectedItem.Value))
 
        'Execute the Update Command
        UpdateCommand.ExecuteNonQuery()
 
        'Close the connection
        conn.Close()
    End Sub
End Class

Open in new window

0
 
DotNetThinkerCommented:
OK, I have it working but before we add code to the new Sub you'll need to add an If Not IsPostBack around the cblStores_Load and DataGrid1_ItemDataBound Subs so they look like this...
    Protected Sub cblStores_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.Load
        If Not IsPostBack Then
 
            Dim Connection As New SqlConnection(Application("A1SConnString"))
            Dim cmd As New SqlCommand("select Active from StoreTPR where ItemId = @ItemId and Store = @Store", Connection)
 
            Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
            Dim Lbl As Label
            Dim ctrl As Control
 
            For Each ctrl In CBL.Parent.Controls
                If TypeOf ctrl Is Label Then
                    Lbl = CType(ctrl, Label)
                End If
            Next
 
            Dim i As Integer
            Connection.Open()
 
            For i = 0 To CBL.Items.Count - 1
 
                Dim cb As ListItem = CBL.Items(i)
 
                cmd.Parameters.Clear()
                cmd.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
                cmd.Parameters.Add(New SqlClient.SqlParameter("@Store", cb.Text))
 
                cb.Selected = Convert.ToBoolean(cmd.ExecuteScalar())
 
            Next i
 
            Connection.Close()
 
        End If
    End Sub
 
    Protected Sub DataGrid1_ItemDataBound(ByVal sender As System.Object, ByVal e As DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
        If Not IsPostBack Then
 
            Dim Connection As New SqlConnection(Application("A1SConnString"))
 
            If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
 
                Dim CBL As CheckBoxList = CType(e.Item.FindControl("cblStores"), CheckBoxList)
                Dim Command As New SqlCommand("spA1SGetUserStores", Connection)
                Command.CommandType = CommandType.StoredProcedure
                Dim Param As New SqlParameter("@UserID", SqlDbType.VarChar, 50)
                Param.Value = 1 'ReadCookie(Tokens.UserId)
                Command.Parameters.Add(Param)
                Dim SDA As New SqlDataAdapter(Command)
                Dim StoreTable As New DataTable("Stores")
 
                If Connection.State <> ConnectionState.Open Then
                    Connection.Open()
                End If
                SDA.Fill(StoreTable)
 
                CBL.DataSource = StoreTable
                CBL.DataTextField = "StoreID"
                CBL.DataBind()
                Connection.Close()
 
            End If
 
        End If
 
    End Sub

Open in new window

0
 
DotNetThinkerCommented:
And here's the final piece to your puzzle, let me know if you get any errors...
    Protected Sub cblStores_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.SelectedIndexChanged
        Dim Connection As New SqlConnection(Application("A1SConnString"))
        Dim UpdateCommand As New SqlCommand("update StoreTPR set active = @Active where ItemId = @ItemId and Store = @Store", Connection)
 
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
        Dim Lbl As Label
        Dim ctrl As Control
 
        For Each ctrl In CBL.Parent.Controls
            If TypeOf ctrl Is Label Then
                Lbl = CType(ctrl, Label)
            End If
        Next
 
        If Connection.State <> ConnectionState.Open Then
            Connection.Open()
        End If
 
        Dim ActiveValue As Integer
        If CType(CType(CBL.Controls(0), Control), CheckBox).Checked() Then
            ActiveValue = 0
        Else
            ActiveValue = 1
        End If
 
        UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("@Active", ActiveValue))
        UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
        UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("@Store", CBL.SelectedItem.Text))
 
        UpdateCommand.ExecuteNonQuery()
 
        Connection.Close()
 
    End Sub

Open in new window

0
 
DotNetThinkerCommented:
Also let me know if you want me to walk you through the Sub but at this point I think you have a pretty good idea as to what's going on.
0
 
Seven priceFull StackAuthor Commented:
ok ran it but nothing happens to the checkboxes
Protected Sub DataGrid1_ItemDataBound(ByVal sender As System.Object, ByVal e As DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
        'Add if not IsPostBack here
        If Not IsPostBack Then
            Dim Connection As New SqlConnection(Application("A1SConnString"))
 
            If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
 
                Dim CBL As CheckBoxList = CType(e.Item.FindControl("cblStores"), CheckBoxList)
                Dim Command As New SqlCommand("spA1SGetUserStores", Connection)
                Command.CommandType = CommandType.StoredProcedure
                Dim Param As New SqlParameter("@UserID", SqlDbType.VarChar, 50)
                Param.Value = ReadCookie(Tokens.UserId)
                Command.Parameters.Add(Param)
                Dim SDA As New SqlDataAdapter(Command)
 
                Dim StoreTable As New DataTable("Stores")
 
                If Connection.State <> ConnectionState.Open Then
                    Connection.Open()
                End If
                SDA.Fill(StoreTable)
 
                CBL.DataSource = StoreTable
                CBL.DataTextField = "StoreID"
                CBL.DataBind()
                Connection.Close()
 
            End If
        End If
 
 
 
 
        'Dim Connection As New SqlConnection(Application("A1SConnString"))
        ' this sets up our Checkboxlist telling us to grab it
        'If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
 
        'Dim CBL As CheckBoxList = CType(e.Item.FindControl("cblStores"), CheckBoxList)
        'Dim Command As New SqlCommand("spA1SGetStoreTPR", Connection)
        'Command.CommandType = CommandType.StoredProcedure
        'Dim Param As New SqlParameter("@UserID", SqlDbType.VarChar, 50)
        'Param.Value = ReadCookie(Tokens.UserId)
        ' Command.Parameters.Add(Param)
 
        'If Connection.State <> ConnectionState.Open Then
        'Connection.Open()
        'End If
        'CBL.DataSource = Command.ExecuteReader()
        ' CBL.DataBind()
        'Connection.Close()
 
        'End If
    End Sub
    Protected Sub cblStores_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.Load
        If Not IsPostBack Then
            ' **FIRST**
            Dim Connection As New SqlConnection(Application("A1SConnString"))
            Dim cmd As New SqlCommand("select Active from StoreTPR where ItemId = @ItemId and Store = @Store", Connection)
 
            ' **SECOND**
            Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
            Dim Lbl As Label
            Dim ctrl As Control
 
            ' **THIRD**
            For Each ctrl In CBL.Parent.Controls
                If TypeOf ctrl Is Label Then
                    Lbl = CType(ctrl, Label)
                End If
            Next
 
            ' **FOURTH**
            Dim i As Integer
            Connection.Open()
 
            ' **FIFTH**
            For i = 0 To CBL.Items.Count - 1
                Dim cb As ListItem = CBL.Items(i)
 
                cmd.Parameters.Clear()
                cmd.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
                cmd.Parameters.Add(New SqlClient.SqlParameter("@Store", cb.Text))
 
                cb.Selected = Convert.ToBoolean(cmd.ExecuteScalar())
 
            Next i
 
            Connection.Close()
        End If
    End Sub
 
    Protected Sub cblStores_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblStores.SelectedIndexChanged
        Dim Connection As New SqlConnection(Application("A1SConnString"))
        Dim UpdateCommand As New SqlCommand("update StoreTPR set active = @Active where ItemId = @ItemId and Store = @Store", Connection)
 
        Dim CBL As CheckBoxList = CType(sender, CheckBoxList)
        Dim Lbl As Label
        Dim ctrl As Control
 
        For Each ctrl In CBL.Parent.Controls
            If TypeOf ctrl Is Label Then
                Lbl = CType(ctrl, Label)
            End If
        Next
 
        If Connection.State <> ConnectionState.Open Then
            Connection.Open()
        End If
 
        Dim ActiveValue As Integer
        If CType(CType(CBL.Controls(0), Control), CheckBox).Checked() Then
            ActiveValue = 0
        Else
            ActiveValue = 1
        End If
 
        UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("@Active", ActiveValue))
        UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("@ItemId", Lbl.Text))
        UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("@Store", CBL.SelectedItem.Text))
 
        UpdateCommand.ExecuteNonQuery()
 
        Connection.Close()
 
    End Sub

Open in new window

0
 
DotNetThinkerCommented:
So the checkboxes don't stay checked/unchecked when you click them?
0
 
Seven priceFull StackAuthor Commented:
No when clicked then refresh back to they way they were
0
 
Seven priceFull StackAuthor Commented:
I add an autopostback
<asp:CheckBoxList id="cblStores" AutoPostBack="True" Runat="server" Font-Size="10px"  OnLoad="cblStores_Load" OnSelectedIndexChanged="cblStores_SelectedIndexChanged">
	</asp:CheckBoxList>

Open in new window

0
 
Seven priceFull StackAuthor Commented:
Ok when i added the autopost back and i change the database Active to 0 then go back to the site and click it changes the checkbox in the database to active but when i click again I GET THIS ERROR BELOW
Object reference not set to an instance of an object. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
 
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
 
Source Error: 
 
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Open in new window

0
 
DotNetThinkerCommented:
Is it showing you the stack trace? If so could you post the first couple lines?
0
 
Seven priceFull StackAuthor Commented:
this line
UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("@Store", CBL.SelectedItem.Text))

Open in new window

0
 
DotNetThinkerCommented:
OK, I think that means that somewhere the checkboxes or the whole grid is being re-bound before the SelectedIndexChanged function is fired. This may be a pain because you can't debug but you'll need to find where this is happening. It's most likely going to be the DataGrid so do a search (ctrl+f) for DataGrid1.DataBind() and see if it's in a section of code that's not surrounded by an "If Not IsPostBack" statement.
0
 
Seven priceFull StackAuthor Commented:
just this but i added the If not idpostback
 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            RetrieveSalesTPRItems()
 
            SetFocus(UPCCode, Me)
            LoadSizeMeasure()
 
            With lblMessage
                lblMessage.Visible = True
                .CssClass = "labelGreen"
                .Text = "Total Pages: " & DataGrid1.PageCount.ToString
            End With
 
            Dim StoreList() As String
            Dim I As Integer = 0
 
            StoreList = Session("Stores")
 
            For I = 0 To UBound(StoreList)
                'ListBox1.Items.Add(AddIntoDataStructureofStorelist(StoreList(I)).StoreId.ToString)
                ListBox1.Items.Add(StoreList(I).ToString)
            Next
 
            Me.UPCCode.BackColor = System.Drawing.Color.White
            Me.Description.BackColor = System.Drawing.Color.White
            Me.Size.BackColor = System.Drawing.Color.White
            Me.Qty.BackColor = System.Drawing.Color.White
            Me.Price.BackColor = System.Drawing.Color.White
        End If
    End Sub