Visual Basic

sql_query.CommandText = "insert into tablelabel02 with (tablockx) (nome_posto, ordemfabrico, serialnumber, referencia,dataetiqueta,qrgerado,data,operador,posx,posy,impressora,controlo)output inserted.qrgerado
    values (@posto, @ordemfabrico, next value for '" & sequenceID & "' , @refcode, replace(cast(convert(varchar,getdate(),5) as varchar(10)),'-','/'),
    @refcode+replace(cast(convert(varchar,getdate(),5)as varchar(10)),'-','')+right('000'+cast((next value for '" & sequenceID & "' )as varchar (4)),4), getdate(), @operador,@labelx,@labely, @impressora, 'xx' );"

next value for seq_a 

next value for '" & sequenceID & "'

Private Sub btnCount_Click(sender As Object, e As EventArgs) Handles btnCount.Click
        Dim arrsize = rtbDHCP_lease.Lines.Length - 1
        Dim MyNum(arrsize)

        For I = 1 To arrsize
            MyNum(I) = rtbDHCP_lease.Lines(I).Substring(41, (rtbDHCP_lease.Lines(I).IndexOf(".", 41) - 41))
            ProgressBar1.Value = (I / arrsize) * 100
        Next

        Dim query = From item In MyNum
                    Order By item
                    Group By vlans = item
                        Into vlans_count = Group, Count()

        For Each result In query
            dgvDHCPcount.Rows.Add(New Integer() {result.vlans, result.Count})
        Next
End Sub

<pre>    Private Sub Gera2d()

        Try
            data = DateAndTime.Now
            Dim con As New SqlConnection
            Dim sql_query As New SqlCommand
            '
            Dim sql_result As SqlDataReader
            '
            con.ConnectionString = "server='" & SqlServer & "';Database=etiquetasKLC;Uid='" & SqlUser & "';PWD='" & Sqlpassword & "';"
            sql_query.Connection = con
            con.Open()
            sql_query.Parameters.Add("@posto", SqlDbType.NVarChar).Value = posto
            sql_query.Parameters.Add("@data", SqlDbType.NVarChar).Value = data
            sql_query.Parameters.Add("@lote", SqlDbType.NVarChar).Value = ordemFabrico
            sql_query.Parameters.Add("@versao", SqlDbType.NVarChar).Value = status
            sql_query.Parameters.Add("@indexnumber", SqlDbType.NVarChar).Value = cavidade + indexnumber
            sql_query.Parameters.Add("@semana", SqlDbType.NVarChar).Value = weekyear
            sql_query.Parameters.Add("@qrgerado", SqlDbType.NVarChar).Value = codigoQr
            sql_query.Parameters.Add("@operador", SqlDbType.NVarChar).Value = op
            sql_query.Parameters.Add("@partnumber", SqlDbType.NVarChar).Value = partnumber
            sql_query.Parameters.Add("posx", SqlDbType.NVarChar).Value = labelx
            sql_query.Parameters.Add("posy", SqlDbType.NVarChar).Value = labely
            sql_query.Parameters.Add("ip_impressora", SqlDbType.NVarChar).Value = enderecoimpressora
            sql_query.CommandText = "INSERT INTO etiquetas with (tablockx)(nome_posto, data, lote, serialnumber,partnumber,versao,indexnumber,semana, qrgerado,operador,snpn,posx,posy,impressora) select top (1) @posto,
            @data,@lote,T.x,@partnumber,@versao, @indexnumber,@semana, T.x+@partnumber+@versao+@indexnumber+@semana, @operador, T.x+@partnumber, @posx,@posy,@ip_impressora
            FROM [etiquetas] x CROSS APPLY (VALUES(format(convert(int,x.serialnumber)+1,'0000000')))T(x) where partnumber like @partnumber  order by id desc ;"

            sql_query.Parameters.AddWithValue("@sn", serialnumber)

            serialnumber = sql_query.ExecuteScalar()

            Try

                If sql_query.ExecuteScalar() <> 0 Then
                    serialnumber = serialnumber
                End If

            Catch ex As Exception
                IO.File.AppendAllText("C:\ti\Log.txt", String.Format("{0}{1}", Environment.NewLine, Now() + " - " + ex.ToString()))

            End Try
            con.Close()
        Catch ex As Exception

            MsgBox(ex.Message)
        End Try
    End Sub

Private Sub LoadChart()

        Dim oDT As New DataTable
        Dim sqlCmd As New SqlCommand

        Dim sSQLDateCTE As String = "WITH Date_CTE as ( " & _
                "SELECT {0} DDim.[Date] as SaleDate " & _
                "FROM [DateDimension] as DDim {1} "
        Dim sWHEREDateCTE As String = "WHERE [Date] BETWEEN DATEADD({0},-{1},GETDATE()) AND GETDATE() {2} "
        Dim sSQLSUM As String = "), SUM_CTE as ( " & _
                "SELECT {0}" & _
                "FROM {1} " & _
                "WHERE F1034 = 3 AND F64 <> 0 AND F01 = @UPCCode " & _
                "AND F254 IN (SELECT saledate FROM Date_CTE) " & _
                "GROUP BY {2}) "
        Dim sSQLFinalPart = "SELECT {0} DC.SaleDate ,ISNULL(SC.UnitsSold,0) as UnitsSold " & _
                "FROM Date_CTE as DC " & _
                "LEFT OUTER JOIN SUM_CTE as SC " & _
                "ON SC.SaleDate = DC.SaleDate {1}" & _
                "ORDER BY DC.SaleDate ASC "


        Dim sFieldList As String = ""
        Dim sGroupBy As String = ""
        Dim sStorelist As String = ""

        Dim sWhereIN As String = ""
        Dim sWhereINDateCTE As String = ""

        Dim sSQL As String = ""

        Try

            If oAppEnv.sSource = "H" Then
                For Each CheckedItem In clbStores.CheckedItems
                    If sStorelist <> "" Then
                        sStorelist = sStorelist & ", "
                    End If
                    sStorelist = sStorelist & CheckedItem.key
                Next
            End If

            If sStorelist <> "" Then
                sFieldList = "F01 as UPCCode, F1056 as Store, F254 as SaleDate, SUM(F64) as UnitsSold "
                sGroupBy = "F01,F1056,F254"
                sSQLDateCTE = String.Format(sSQLDateCTE, "SL.Store, ", "CROSS JOIN Newbuck.dbo.tblStoreList as SL ")
                sWhereIN = "AND {0} in ({1}) "
                sWhereINDateCTE = String.Format(sWhereIN, "SL.Store", sStorelist)
                sSQLFinalPart = String.Format(sSQLFinalPart, "DC.Store, ", "AND SC.Store = DC.Store ")
            Else
                sFieldList = "F01 as UPCCode, F254 as SaleDate, SUM(F64) as UnitsSold "
                sGroupBy = "F01,F254"
                sSQLDateCTE = String.Format(sSQLDateCTE, "", "")
                sWhereINDateCTE = ""
                sSQLFinalPart = String.Format(sSQLFinalPart, "", "")

            End If


            Dim PeriodRow As DataRow = oAppEnv.dtPeriods.Select(String.Format("DPPeriodID = {0}", cboPeriods.SelectedValue)).FirstOrDefault

            Dim DurationRow As DataRow = oAppEnv.dtDurations.Select(String.Format("DDDurationID = '{0}'", cboDuration.SelectedValue)).FirstOrDefault

            Select Case PeriodRow.Item("DPPeriodAbbrev")
                Case "D"
                    sWHEREDateCTE = String.Format(sWHEREDateCTE, "DD", DurationRow.Item("DDDurationLength"), sWhereINDateCTE)
                    sSQLSUM = String.Format(sSQLSUM, sFieldList, "RPT_ITM_D", sGroupBy)
                    sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM & sSQLFinalPart
                Case "W"
                    sWHEREDateCTE = String.Format(sWHEREDateCTE, "WW", DurationRow.Item("DDDurationLength"), sWhereINDateCTE) & " AND Weekday = 1 "
                    sSQLSUM = String.Format(sSQLSUM, sFieldList, "RPT_ITM_W", sGroupBy)
                    sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM & sSQLFinalPart
                Case "M"
                    sWHEREDateCTE = String.Format(sWHEREDateCTE, "MM", DurationRow.Item("DDDurationLength"), sWhereINDateCTE) & " AND [DAY] = DATEPART(Day,getdate()) "
                    sSQLSUM = String.Format(sSQLSUM, sFieldList, "RPT_ITM_M", sGroupBy)
                    sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM & sSQLFinalPart
                Case "Y"
                    sWHEREDateCTE = String.Format("WHERE [Date] BETWEEN '01-01-2009' AND GETDATE() AND DayOfYear = 1 {0} ", sWhereINDateCTE)
                    sSQLSUM = String.Format(sSQLSUM, sFieldList, "RPT_ITM_Y", sGroupBy)
                    sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM & sSQLFinalPart
            End Select

            sqlCmd.CommandText = sSQL

            sqlCmd.Parameters.AddWithValue("@UPCCode", sUPCCode)

            oDT = oAppEnv.oLogiDM.GetSQLData(sqlCmd)

            ctSalesData.ChartAreas.Clear()
            ctSalesData.ChartAreas.Add("Sales")
            ctSalesData.Series.Clear()

            If oDT IsNot Nothing Then

                '//Get the Date range.

                Dim oSortDV As DataView = oDT.DefaultView
                oSortDV.Sort = "UnitsSold DESC"

                'ctSalesData.DataSource = oDT
                Dim iCnt As Integer = 0

                ctSalesData.AlignDataPointsByAxisLabel()

                If oAppEnv.sSource = "H" And sStorelist <> "" Then
                    For Each CheckedItem In clbStores.CheckedItems
                        Dim oStoreRows() As DataRow = oDT.Select("Store = " & CheckedItem.key)
                        iCnt = 0
                        Dim cSeries As New Charting.Series(CheckedItem.key)
                        cSeries.ChartType = Charting.SeriesChartType.Bar
                        cSeries.IsXValueIndexed = True
                        cSeries.CustomProperties = "PixelPointWidth = 20"
                        cSeries.IsValueShownAsLabel = True
                        For Each row In oStoreRows
                            cSeries.Points.AddXY(iCnt, row.Item("UnitsSold"))
                            cSeries.Points(iCnt).AxisLabel = row.Item("SaleDate")
                            iCnt += 1
                        Next
                        ctSalesData.Series.Add(cSeries)
                    Next
                Else
                    Dim cSeries As New Charting.Series("Sales")
                    cSeries.ChartType = Charting.SeriesChartType.Bar
                    cSeries.IsXValueIndexed = True
                    cSeries.CustomProperties = "PixelPointWidth = 15"
                    cSeries.IsValueShownAsLabel = True

                    For Each row In oDT.Rows
                        cSeries.Points.AddXY(iCnt, row.item("UnitsSold"))
                        cSeries.Points(iCnt).AxisLabel = row.item("SaleDate")
                        iCnt += 1
                    Next
                    ctSalesData.Series.Add(cSeries)
                End If

                ctSalesData.ChartAreas(0).AxisX.Minimum = 0
                ctSalesData.ChartAreas(0).AxisX.Maximum = oDT.Rows.Count + 1

                If oDT.Rows.Count > 21 Then
                    Dim ZoomDate As Date = oDT.Rows(14).Item("SaleDate")
                    ctSalesData.ChartAreas(0).AxisX.ScaleView.Zoom(0, 22)
                End If

                ctSalesData.ChartAreas(0).AxisX.Interval = 1
                ctSalesData.ChartAreas(0).AxisY.Minimum = 0
                ctSalesData.ChartAreas(0).AxisY.Interval = 1

                ctSalesData.ChartAreas(0).AxisY.Maximum = Math.Ceiling(oSortDV(0).Item("UnitsSold") / 10) * 10

                If ctSalesData.ChartAreas(0).AxisY.Maximum = oSortDV(0).Item("UnitsSold") Then
                    ctSalesData.ChartAreas(0).AxisY.Maximum += 10
                End If

                ctSalesData.ChartAreas(0).CursorX.AutoScroll = True

                ctSalesData.ChartAreas(0).AxisX.ScaleView.SizeType = Charting.DateTimeIntervalType.Number
                ctSalesData.ChartAreas(0).AxisX.ScrollBar.ButtonStyle = Charting.ScrollBarButtonStyles.SmallScroll

                ctSalesData.ChartAreas(0).AxisX.ScaleView.SmallScrollSize = 21
                ctSalesData.ChartAreas(0).AxisX.ScrollBar.IsPositionedInside = False


            End If


        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
End Sub