Solved

Create stored procedure

Posted on 2007-12-06
228
860 Views
Last Modified: 2013-11-26
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
Comment
Question by:sevensnake77
  • 129
  • 99
228 Comments
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
Are you already retrieving the store numbers for the user?
0
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
if so where are you storing them (session, viewstate, etc.)?
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
ok, so you have your list of store ids. Assuming they're all unchecked what should happen when I check one?
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
then it will tell the database it is active which is 1 uncheck inaactive which is 2 field name is Active.
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
sorry inactive which is 0
0
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
I rather use a checkboxlist if possible but what ever works at this point
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
Ok sounds good whats next
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
And end of statement error is required please proceed
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
sorry, that was C# I was using.
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
LOL no problem
0
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
Are you storing the image in the DB?
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
So we hav eto Declare  MycheckList correct?
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
0
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
If the store is active you want one image displayed, if it's inactive you want another. Is that the jist of it?
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
Yes but instead of an image it must be a checkbox
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
The Data grid just displays the description of the items they care to order
0
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
ok cool no problem.
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
I sent it along with the files last night
0
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
Ok telling Declaration expected
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
ok
0
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
where is it saying a declaration is expected?
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
change Connection.Open() and Connection.Close() to conn.Open() and conn.Close()
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
cool Next,
0
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
and this where do or how do I declare this checkBoxList
CheckBoxList1.SelectedIndexChanged

Open in new window

0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
you already one step ahead
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
This requires a withevent handler
Handles CheckBoxList1.SelectedIndexChanged

Open in new window

0
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
let me know if I'm jumping around too much or you need clarification on some of this.
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
it should be "Handles WhateverYour_CBL_ID_Is.SelectedIndexChanged"

ie. if your CBL's Id is cblStores it should be "Handles cblStores.SelectedIndexChanged"
0
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
the error sounds like it relates to my previous post, correct that and let me know what happens.
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
Just keep it coming I think we are moving just right sorry to be such a boomer
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
ok, try getting rid of the OnSelectedIndexChanged="..." part of the CBL declaration
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
ok
<asp:CheckBoxlist id="cblStores" DataSource='<%# DataBinder.Eval(Container.DataItem, "Stores") %>'  Runat="server"  AutoPostBack="True" ></asp:CheckBoxlist>

									

Open in new window

0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
Still get that error

Private Sub CheckBoxList1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ((((error here --- cblStores.SelectedIndexChanged)))))
0
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
OK, do me a favor and post the entire code for your DataGrid, from <asp:DataGrid...> to </asp:DataGrid>
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
ok done that
Protected WithEvents cblStores As System.Web.UI.WebControls.CheckBoxList

Open in new window

0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
Great, could you post the code where you're binding the DataGrid?
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility

 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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
I just mean the section of code where you're saying DataGrid1.DataSource = ...
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility

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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
Sorry this has been taking so long but I haven't programmed in VB or .NET 1.1 for quite some time.
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
yes that worked now we have to bind the checkbox when it is clicked it will stay clicked
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
Yeah, just define a Command and CommandArgument and you can use it just like the image
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
but how does that work with a checkbox.
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
But either way the check box is not binding to the Active state. when clicked and I refresh or go back its gone.
0
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
yea, Well even if its binded we can tell if the check box stay checked when clicked
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
Also it should look like this...
OnSelectedIndexChanged="CheckBoxList1_SelectedIndexChanged"

Open in new window

0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
Ok sorry lets scratch that, last
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
do you want to checkboxlist inside the datagrid or where the listbox is currently?
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
never mind scratch that...

how is the list box displaying the store id's
0
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
understand
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
Are you using this... Application("A1SConnString")... as the connection string?
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
Yes
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility

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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
Dim Connection As New SqlConnection("A1SConnString") should be Dim Connection As New SqlConnection(Application("A1SConnString"))
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
when i remove this this seems brink up the checkbox again but now 2 just one
OnLoad="cblStores_Load"

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
No that change was there with the connection string but I still get that error,
0
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
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
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
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
 
LVL 11

Expert Comment

by:DotNetThinker
Comment Utility
I'm not sure I do. I thou