How to add Running Total Field in Crystal Report?

In the Field Explorer, under Database Field,

1. Right Click on Running Total Fields and Click New
2. In the Create Running Total Field Dialog Box, under Report Fields, select the specific field which  you want to sum
3. Then, Click the ">" and Choose Sum for Type of summary.
4. You may change the name for Running Total Name.
5. Click OK button.
6. Then, you will see the running total field name under Running Total Fields.
7. After than, just drag to your report.

Grouping in Crystal Report

1. Right Click on Group Name Fields in Field Explorer
2. Click Group Expert
3. Choose a field which you want to group from Available Fields, Add to Group By box.

What is set Datasource location ?

Set Datasource Location is used to change or update the datasource of the report.

To use Set Datasource Location,
1. Right Click on Database Fields in Field Explorer.
2. Click Set Datasource Location
3. Choose the specific table or datasource in Current Data Source.
4. In Replace with box, you may create new connection for the updated table.
5. Click Update.

How to apply conditional formatting color in crystal report.

1. Right Click on the specific text object
2. Click Highlighting Expert
3. You may click New button
4. In Item editor box, you can set which field for which criteria and value.
5. Then for format, you can set Font Style, Font Color, Background and Border.

How to call stored procedure with parameters in VB.Net

cmd = New SqlCommand

        With cmd
            .Connection = con
            .CommandType = CommandType.StoredProcedure
            .CommandText = "ProProdDaily" //(StoreProcedure Name)
            .Parameters.Add("@vFDate", SqlDbType.DateTime).Value = frmRptProductionOpt.dtpProRptFDate.Text
            .Parameters.Add("@vTDate", SqlDbType.DateTime).Value = frmRptProductionOpt.dtpProRptTDate.Text
            .ExecuteNonQuery()
        End With

Croass Tab Query in Access

Original Table: Production

PDate ItemID ItemName Made_Item Left_Item
------------------------------------------------------------
06/24/15 1 Prod1 4 1
06/24/15 2 Prod2 6
06/25/15 1 Prod1 5
06/25/15 2 Prod2 5 2


CrossTab Production: For Made Items

ItemID ItemName 06/24/15 06/25/15
----------------------------------------------------------
1 Prod1 4 5
2 Prod2 6 5

SQL Query:

--------------

TRANSFORM Sum(Production.[Made_Item]) AS SumOfMade_Item
SELECT Production.[ITemID], Production.[ItemName], Sum(Production.[Made_Item]) AS [Total Of Made_Item]
FROM Production
GROUP BY Production.[ITemID], Production.[ItemName]
PIVOT Format([PDate],"Short Date");

How to import Data from access 2007 (accdb) to SQL Server 2008

Step by Step to import Data to SQL Server

1. Right Click on the specific database and Choose import from Tasks

2. 

3. Select Microsoft Office 12.0 Access Database Engine OLEDB Provider

4. Click Properties and enter Datasource

5. Choose the Destination

6. Then Specify Table Copy or Query and Select the specific tables and views.

7. After you may execute by choosing Execute immediately.

How to get Value of CurrentCell of Datagridview when user click

Dim s As System.Drawing.Point

s = me.dataGridView.currentCellAddress

dim id as string

'By Column index
id = me.dataGridView.item(0,s.Y).value.toString

OR

'By Column Name
id = me.dataGridView.item("ID",s.Y).value.toString

How to Sort Data by Column in Datagridview

 'sort by Column(1)
        Me.dataGridView.Sort(Me.dataGridView.Columns(1), System.ComponentModel.ListSortDirection.Ascending)

Save Error Dialog Box in SQL Server

When you get this error dialog box after you have made any changes into tables in sql server,

Just change this option,
on the Tools menu ---> Options --> Designers ---> Table and Database Designers --> Uncheck the Prevent saving changes that require the table to be re-created check box.

Combo box Autocomplete in vb.net

To be autocomplete combo box, Just do the following setting of the combo box.

How to add Bounded CombBox into Datagridview

Private Sub DisplayItems()
        Dim vSqlStr As String = "SELECT * FROM ItemMaster"
        Dim vSqlCat As String = "SELECT * FROM ItemCat"

        Using vDA As New OleDb.OleDbDataAdapter(vSqlStr, con)
            Using vDs As New DataSet
                vDA.Fill(vDs, "ItemMaster")

                Me.dgvItemList.DataSource = vDs.Tables("ItemMaster")
            End Using
        End Using

        'Formatting Columns

        Try
            If Not Me.dgvItemList Is Nothing AndAlso Not Me.dgvItemList.CurrentRow Is Nothing Then
                Me.dgvItemList.Columns(0).Visible = False
                Me.dgvItemList.Columns(3).Visible = False

                Me.dgvItemList.ColumnHeadersDefaultCellStyle.Font = New Font(Me.dgvItemList.Font, FontStyle.Bold)
                Me.dgvItemList.Columns(1).HeaderText = "Item Name"
                Me.dgvItemList.Columns(1).Width = 160
                Me.dgvItemList.Columns(2).HeaderText = "Item Price"

                'add combo box
                Dim cbo As New DataGridViewComboBoxColumn

                cbo.DataPropertyName = "ItemCatID"
                cbo.HeaderText = "Category"
                cbo.Name = "cboCatID"

                Using vDA As New OleDb.OleDbDataAdapter(vSqlCat, con)
                    Using vDs As New DataSet
                        vDA.Fill(vDs, "ItemCat")

                        cbo.DataSource = vDs.Tables("ItemCat")
                    End Using
                End Using

                cbo.DisplayMember = "ItemCatName"
                cbo.ValueMember = "ItemCatID"

                Me.dgvItemList.Columns.Add(cbo)
            End If

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

How to group by Data group by in datagrid view in vb.net

I searched the solutions to group data in datagridview. Here is the best and easiest solution I found.

    Private Sub dgvItemList_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgvItemList.CellFormatting
        If e.RowIndex > 0 And e.ColumnIndex = 3 Then
            If Me.dgvItemList.Item(3, e.RowIndex - 1).Value = e.Value Then
                e.Value = ""
            End If
        End If

        If e.Value.ToString <> "" And e.ColumnIndex = 3 Then
            e.CellStyle.BackColor = Color.SkyBlue
        End If
    End Sub

Reference :

http://www.vb-tips.com/DataGridViewGrouping.aspx

how to write datagridview edited data to database in vb.net

Public vDA As OleDb.OleDbDataAdapter
Public vDs As DataSet

    Private Sub cmdGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGenerate.Click
       
        Dim vSelStr As String = "SELECT * FROM PRODUCTION WHERE PDATE = #" & vDate & "#"

        vDA = New OleDb.OleDbDataAdapter(vSelStr, con)
        vDs = New DataSet
        vDA.Fill(vDs, "Production")

        Me.dgvData.DataSource = vDs.Tables("PRODUCTION")

    End Sub


    Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click

        Dim vCb As OleDb.OleDbCommandBuilder

        vCb = New OleDb.OleDbCommandBuilder(vDA)

        vDA.Update(vDs, "PRODUCTION")
     
        MsgBox("Updated!")
    End Sub

Check Date in OLedb in Vb.Net

Dim vDate As Date = Format(Me.datetimepicker1.Value, "MM/dd/yyyy")
        Dim vDelStr As String = "DELETE * FROM PRODUCTION  _
WHERE PDATE =#" & vDate.ToString("MM/dd/yyyy") & "#"
     

        Dim Delcmd As New OleDb.OleDbCommand(vDelStr, con)
       
        Delcmd.ExecuteNonQuery()

How to set a value in datagridview in Vb.Net

If e.ColumnIndex = 5 Then


            If Me.dgvData IsNot Nothing AndAlso Me.dgvData.CurrentRow IsNot Nothing Then
Me.dgvData.CurrentRow.Cells("Made_Cost").Value = Me.dgvData.CurrentRow.Cells("ItemmPrice").Value  *    Me.dgvData.CurrentRow.Cells("Made_Item").Value
            End If

End If

How to create a Folder in local drive in vb.net

Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
       Dim dr As New DirectoryInfo("C:\createfolder")

        If dr.Exists Then
            MsgBox("exists")

            If MsgBox("Do you want to Delete?", MsgBoxStyle.YesNo, "Question") = MsgBoxResult.Yes Then
                dr.Delete()

                MsgBox("Deleted")
                Exit Sub
            End If
        End If

        dr.Create()
        MsgBox("Created")
    End Sub

The data types ntext and varchar are incompatible in the equal to operator

I got an error message from the following sentense.
As far as I studied, this error occured becuase I compared Nvarchar and text. It is not incompatible.

 Dim vSqlStr As String = "SELECT * FROM HRSUSERS WHERE HRSUSERNAME = '" & Me.txtUsername.Text & "' AND HRSPASSWORD = '" & Me.txtPassword.Text & "'"

After I have changed to
Dim vSqlStr As String = "SELECT * FROM HRSUSERS WHERE HRSUSERNAME LIKE '" & Me.txtUsername.Text & "' AND HRSPASSWORD LIKE'" & Me.txtPassword.Text & "'"

It works. :)

Currency format for listview items in vb.net

For k = 1 To DSDs.Tables(0).Columns.Count - 1
               If k = DSDs.Tables(0).Columns.Count - 1 Then
                 lstrow.SubItems.Add(CDbl(DSDs.Tables(0).Rows(j)k).ToString()).ToString("N2"))
                Else
                            lstrow.SubItems.Add(DSDs.Tables(0).Rows(j)(k).ToString)
                End If

Next

How to set listview column width in vb.net

For i As Integer = 0 To Me.lstList.Columns.Count - 1
            Me.lstList.Columns(i).Width = -2
 Next

If you set -2, the control resizes the columns to fit Data and Header.
If you set -1, it does to fit Only Data.

Date Format for Listview Items in vb.net

Using DSAd As New OleDb.OleDbDataAdapter(cmdStr, con)
            Using DSDs As New DataSet
                DSAd.Fill(DSDs, "TBLDAILYSALES")

                For i As Integer = 0 To DSDs.Tables(0).Columns.Count - 1
                    Me.lstList.Columns.Add(DSDs.Tables(0).Columns(i).ToString)

                Next

                For j As Integer = 0 To DSDs.Tables(0).Rows.Count - 1
                    Dim lstrow As New ListViewItem

                    lstrow.Text = FormatDateTime(DSDs.Tables(0).Rows(j)(0).ToString(), DateFormat.ShortDate)


                    For k = 1 To DSDs.Tables(0).Columns.Count - 1
                        lstrow.SubItems.Add(DSDs.Tables(0).Rows(j)(k).ToString)
                    Next
                    Me.lstList.Items.Add(lstrow)
                Next
            End Using