Answer / ramyab.mca@gmail.com

Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Dim da, da1 As SqlDataAdapter
Dim cmd As SqlCommand
Dim qstring, gropupname1, subgroupame1, groupname,
subgroupname, itemname As String
Dim rowcount1 As Integer
Dim dset As New DataSet
Dim stock As Decimal
Dim row1 As Integer = 0

'for displaying groupnames in group combobox

'Function for loading groupnames
Private Sub LoadGroup()
da = New SqlDataAdapter("select g_id,g_name from
groups order by g_id", Con)
Dim ds As New DataSet
da.Fill(ds, "groups")
cmb_group.DataSource = ds.Tables("groups")
cmb_group.DisplayMember = "g_name"
cmb_group.ValueMember = "g_id"
Dim newrow As DataRow = ds.Tables
("groups").NewRow 'adding ALL item dynamically to combo box
newrow("g_name") = "ALL"
newrow("g_id") = "-1"
ds.Tables("groups").Rows.InsertAt(newrow, 0)
cmb_group.SelectedIndex = 0
End Sub

'for displaying subgroupnames corresponding to
groupname in subgroup combobox
Private Sub LoadSubGroup()
'If cmb_group.Text = "ALL" Then
' da = New SqlDataAdapter("select * from
subgroup", Con)
'Else
da = New SqlDataAdapter("select s.sg_id,s.sg_name
from subgroup s,groups g where s.g_id=g.g_id and s.g_id=" &
cmb_group.SelectedValue & "", Con)
Dim ds As New DataSet
da.Fill(ds, "subgroup")
cmb_Subgroup.DataSource = ds.Tables("subgroup")
cmb_Subgroup.DisplayMember = "sg_name"
cmb_Subgroup.ValueMember = "sg_id"
Dim newrow As DataRow = ds.Tables
("subgroup").NewRow 'adding ALL item dynamically to combo
box
newrow("sg_name") = "ALL"
newrow("sg_id") = "-1"
ds.Tables("subgroup").Rows.InsertAt(newrow, 0)
cmb_Subgroup.SelectedIndex = 0
End Sub

Private Sub clear_Toolstrip()
'purpose:clearing the toolstrip menu
'note:
Stl_text.Text = ""
Stl_Image.Image = Nothing
End Sub

'adding columns to datagridview
Private Sub gridcolumns()
Dim checkbox1 As New DataGridViewCheckBoxColumn
checkbox1.Name = "Select"
checkbox1.HeaderText = "Select"
dgv_view.Columns.Add(checkbox1)
dgv_view.Columns.Add("ItemName", "ItemName")
dgv_view.Columns.Add("stock", "stock")
dgv_view.Columns(0).Width = 50
dgv_view.Columns(1).ReadOnly = True
dgv_view.Columns(2).ReadOnly = True
For Each column As DataGridViewColumn In
dgv_view.Columns
column.SortMode =
DataGridViewColumnSortMode.NotSortable 'making disable
sorting of all columns
Next
End Sub




Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
LoadGroup() 'Calling function to load groupnames to
group combobox
LoadSubGroup() 'calling function to load
subgroupnames to subgroup combobox
gridcolumns() 'adding datagridview columns
End Sub

'Load subgroup names to cmb_subgroup when selection
changed in groupname combobox
Private Sub ComboBox1_SelectionChangeCommitted(ByVal
sender As System.Object, ByVal e As System.EventArgs)
Handles cmb_group.SelectionChangeCommitted
LoadSubGroup() 'Calling function to load
subgroupnames to combobox
End Sub

'To show items stock for selected group and
subgroupnames
Private Sub btn_Show_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btn_Show.Click
dgv_view.Rows.Clear()
row1 = 0
dset = New DataSet
'select distinct
groupnames,subgroupnames,itemnames,stock values from
corrresponding tables.
qstring = "select distinct
g.g_name,s.sg_name,i.item_name,cs.stock from item_master
i,current_stock cs,groups g,subgroup s,group_items gi where
i.item_id=cs.item_id and g.g_id=s.g_id and gi.g_id=g.g_id
and gi.sg_id=s.sg_id and gi.item_id=i.item_id "
'qstring = "select distinct
g.g_name,s.sg_name,i.item_name,cs.stock from item_master
i,current_stock cs,groups g,subgroup s where
i.item_id=cs.item_id and g.g_id=s.g_id"
'for all subgroups and selected groupnames
If cmb_group.Text <> "ALL" And cmb_Subgroup.Text
= "ALL" Then
qstring = qstring & " and g.g_id=" &
cmb_group.SelectedValue
'for all groups and selected subgroupnames
'ElseIf cmb_group.Text = "ALL" And
cmb_Subgroup.Text <> "ALL" Then
' qstring = qstring & " and s.sg_name='" &
cmb_Subgroup.Text & "'"
'for selected groups and selected subgroupnames
ElseIf cmb_Subgroup.Text <> "ALL" And
cmb_group.Text <> "ALL" Then
qstring = qstring & " and g.g_id=" &
cmb_group.SelectedValue & " and s.sg_name='" &
cmb_Subgroup.Text & "'"
End If
qstring = qstring & " order by g.g_name"
da = New SqlDataAdapter(qstring, Con)
da.Fill(dset, "itemstock")
If dset.Tables("itemstock").Rows.Count = 0 Then 'if
dataset is empty then
Stl_text.Text = "No Matching Records"
Stl_Image.Image = My.Resources.Red
Stl_text.ForeColor = Color.Red
Exit Sub
End If
If dset.Tables("itemstock").Rows.Count > 0 Then 'if
dataset has records then
gropupname1 = dset.Tables("itemstock").Rows
(0).Item(0).ToString 'storing groupname in subgroupname1
variable
subgroupame1 = dset.Tables("itemstock").Rows
(0).Item(1).ToString 'storing subgroupname in subgroupname1
variable
'displaying groupname in datagridview as row
dgv_view.Rows.Insert(row1, False, "Group
Name :", gropupname1)
'Setting font as bold and size to 9
dgv_view.Rows(row1).DefaultCellStyle.Font = New
System.Drawing.Font("Arial", 9, FontStyle.Bold)
row1 += 1
'displaying subgroupname in datagridview as row
dgv_view.Rows.Insert(row1, False, "SubGroup
Name :", subgroupame1)
'Setting font as bold and size to 9
dgv_view.Rows(row1).DefaultCellStyle.Font = New
System.Drawing.Font("Arial", 9, FontStyle.Bold)
row1 += 1
End If

For rowcount = 0 To dset.Tables
("itemstock").Rows.Count - 1
groupname = dset.Tables("itemstock").Rows
(rowcount).Item(0).ToString
subgroupname = dset.Tables("itemstock").Rows
(rowcount).Item(1).ToString
'checking groupname with previous groupname for
finding duplication
If groupname <> gropupname1 Then 'if it is new
groupname then record will be added to datagridivew
gropupname1 = dset.Tables("itemstock").Rows
(rowcount).Item(0).ToString
subgroupame1 = dset.Tables("itemstock").Rows
(rowcount).Item(1).ToString
dgv_view.Rows.Insert(row1, False, "Group
Name :", gropupname1)
'Setting font as bold and size to 9
dgv_view.Rows(row1).DefaultCellStyle.Font =
New System.Drawing.Font("Arial", 9, FontStyle.Bold)
row1 += 1
dgv_view.Rows.Insert(row1, False, "SubGroup
Name :", subgroupame1)
'Setting font as bold and size to 9
dgv_view.Rows(row1).DefaultCellStyle.Font =
New System.Drawing.Font("Arial", 9, FontStyle.Bold)
row1 += 1
End If
'checking subgroupname with previous
subgroupname for duplication
If subgroupame1 <> subgroupname Then 'if it is
new subgroupname then record will be added to datagridview
subgroupame1 = dset.Tables("itemstock").Rows
(rowcount).Item(1).ToString
dgv_view.Rows.Insert(row1, False, "Group
Name :", gropupname1)
'Setting font as bold and size to 9
dgv_view.Rows(row1).DefaultCellStyle.Font =
New System.Drawing.Font("Arial", 9, FontStyle.Bold)
row1 += 1
dgv_view.Rows.Insert(row1, False, "SubGroup
Name :", subgroupame1)
'Setting font as bold and size to 9
dgv_view.Rows(row1).DefaultCellStyle.Font =
New System.Drawing.Font("Arial", 9, FontStyle.Bold)
row1 += 1
End If
itemname = dset.Tables("itemstock").Rows
(rowcount).Item(2).ToString()
stock = dset.Tables("itemstock").Rows
(rowcount).Item(3).ToString()
dgv_view.Rows.Insert(row1, False, itemname,
stock)
row1 += 1
'changing alternative gridivew rows color

dgv_view.AlternatingRowsDefaultCellStyle.BackColor =
Color.Bisque

For Each column As DataGridViewColumn In
dgv_view.Columns
column.SortMode =
DataGridViewColumnSortMode.NotSortable 'making disable
sorting of all columns
Next
For rowcounter As Integer = 1 To
dgv_view.ColumnCount - 1
dgv_view.Columns(rowcounter).ReadOnly = True
Next
Next
End Sub

'purpose:for selecting all checkboxes in datagridview
Private Sub lnk_selectall_LinkClicked(ByVal sender As
System.Object, ByVal e As
System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles
lnk_selectall.LinkClicked
Dim rows As Integer
For rows = 0 To dgv_view.Rows.Count - 1
dgv_view.Item(0, rows).Value = True ''to select
all checkboxes in gridview
Next
End Sub

'purpose: for deselecting all checkboxes in datagridview
Private Sub lnk_deselectall_LinkClicked(ByVal sender As
System.Object, ByVal e As
System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles
lnk_deselectall.LinkClicked
Dim rows As Integer
For rows = 0 To dgv_view.Rows.Count - 1
dgv_view.Item(0, rows).Value = False 'to
deselect all checkboxes in gridview
Next
End Sub

' Purpose: for Exporting Records to Excel
Private Sub lnk_export_LinkClicked(ByVal sender As
System.Object, ByVal e As
System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles
lnk_export.LinkClicked
Dim count As Integer = 0
Dim count1 As Integer = 0
For count = 0 To dgv_view.Rows.Count - 1
If dgv_view.Item(0, count).Value = True
Then 'counting no of records checked
count1 += 1
End If
Next
'if atleast one record is checked then exports
records to excel else exit
If count1 > 0 Then
dlg_save.Filter = "Excel files (*.xls)|*.xls"
dlg_save.ShowDialog()
Dim fn As String
fn = dlg_save.FileName
If fn = "" Then
Exit Sub
End If
exporttoexcel_fromdgv(Me, dgv_view, fn, "Item
Stock Groupwise Report")
Else
Stl_text.Text = "Select atleast one record"
Stl_text.ForeColor = Color.Red
Stl_Image.Image = My.Resources.Red
End If
End Sub

'For printing datagridview



Private Sub lnk_print_LinkClicked(ByVal sender As
System.Object, ByVal e As
System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles
lnk_print.LinkClicked
clear_Toolstrip() 'calling function to
clear toolstrip message
PrintDGV.Print_DataGridView(dgv_view)
End Sub

'To clear toolstrip message while leaving save button
Private Sub btn_Save_Leave(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btn_Show.Leave
clear_Toolstrip()
End Sub

'To clear toolstrip message while leaving selectall
link button
Private Sub lnk_selectall_Leave(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
lnk_selectall.Leave
clear_Toolstrip()
End Sub

'To clear toolstrip message while leaving deselectall
link button
Private Sub lnk_deselectall_Leave(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
lnk_deselectall.Leave
clear_Toolstrip()
End Sub

'To clear toolstrip message while leaving export link
button
Private Sub lnk_export_Leave(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
lnk_export.Leave
clear_Toolstrip()
End Sub

'To clear toolstrip message while leaving print link
button
Private Sub lnk_print_Leave(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
lnk_print.Leave
clear_Toolstrip()
End Sub
End Class

feilds:

Groups:g_id,g_name,description.
subgroup:sg_id,g_id,sg_name,description
item_master:item_refid,item_name,item_globalname,item_specif
ication,category_id,sub_cat_id,warehouse_id,storage_point_id
,min_stock_level,re_order_level,is_perishable,measuring_unit
,opening_stock,price_per_unit,eval_method,eval_price
current_stock:item_refid,stock

Explain internal keyword in .net framework?

0 Answers  

---

What is the difference between c# and vb.net?

0 Answers  

---

Tell us how many languages are supported by .net?

0 Answers  

---

what are the types of threading models in VB.net ?

1 Answers   Satyam,

---

Advantage of vb.net over vb ?

6 Answers   Digital GlobalSoft,

---

---

What is friend and protected-friend access specifier in vb.net?

1 Answers  

---

What is sorting in vb?

0 Answers  

---

What is misl code?

0 Answers  

---

difference between control and component more than one differences

0 Answers  

---

How many classes a dll can contain?

0 Answers  

---

Where would you use Abstract class and Interface?

4 Answers  

---

What do you mean by option strict on?

0 Answers  

---