Question how to insert a query in each datagridview row value

evolet10000

Active member
Joined
Nov 22, 2012
Messages
40
Programming Experience
Beginner
ok here's the problem, i have 2 datagridview in my form, namely datagridview1 and datagridview2

vb.png


what im trying to accomplish is to execute a query in each row(datagridview1), and the result will be displayed also in each row of datagridview2(with column name BALANCE_QTY AND CONSUME).
(note: with corresponding row or the same row)


for example i want to get the balance_QTY(datagridview2) of a stock(in my 1st row) where stock_No(datagridview1) = 179000 and will display the result on datagridview2(1st row)
i want the program to do looping and execute query in each datagridview1 row,. is this possible?if yes please take some time to give suggestions, i really need it..


thanks in advance and god bless you...
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,378
Location
Sydney, Australia
Programming Experience
10+
Unless the number of rows in the first grid is very large, I would suggest querying to get all the data for both grids in one go. You can populate two DataTables and then bind them in a master/detail arrangement. The good thing there is that the child grid is filtered automatically whenever you select a parent record. Here's one I prepared earlier:

Master/Detail (Parent/Child) Data-binding
 

evolet10000

Active member
Joined
Nov 22, 2012
Messages
40
Programming Experience
Beginner
thanks for the quick reply and example, ill try to grasp the concept of those codes...

anyway here's my code for dgv1,dgv2 and form_load...

#Region "LOAD_REQUISITION_ITEM"
'here's my code for dgv1
Public Sub LoadRequisitionItem()
Try
con = New SqlClient.SqlConnection(My.Settings.PSMSConnectionString)
con.Open()

ds = New DataSet
dt = New DataTable
ds.Tables.Add(dt)
adptr = New SqlClient.SqlDataAdapter("SELECT RIS_NO, STOCK_NO, UNIT, DESCRIPTION, QUANTITY FROM tblREQUISITION WHERE RIS_NO = '" & listviewItemMC1 & "'", con)
adptr.Fill(dt)

DataGridViewMC1_RIS.DataSource = dt.DefaultView
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

'code for dgv2(the problem is here, it seems that there's no problem on my looping structure(i guess) but on how i display the query result set... )

Public Sub LoadBalance()
Try
con = New SqlClient.SqlConnection(My.Settings.PSMSConnectionString)
con.Open()

For m As Integer = 0 To DataGridViewMC1_RIS.Rows.Count - 1

Dim value As Integer = DataGridViewMC1_RIS.Rows(0).Cells(1).Value.ToString

ds = New DataSet
dt = New DataTable

ds.Tables.Add(dt)
adptr = New SqlClient.SqlDataAdapter("SELECT BALANCE_QTY,DAYS_TO_CONSUME FROM tblSTOCK_CARD WHERE STOCK_NO = '" & DataGridViewMC1_RIS.Rows(0).Cells(1).Value.ToString & "'", con)
adptr.Fill(dt)

DataGridViewMC1_RIS_STATUS.DataSource = dt.DefaultView
Next

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

Private Sub frmMonitoringLoadData_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If TabControlMainContent.SelectedIndex = 0 Then
LoadRISData()
LoadRequisitionItem()
LoadBalance()

Dim no As Integer
no = DataGridViewMC1_RIS.Rows.Count.ToString
DataGridViewMC1_RIS_ISSUANCE.Rows.Clear()
DataGridViewMC1_RIS_ISSUANCE.Rows.Add(no)
DataGridViewMC1_RIS_ISSUANCE.ReadOnly = False

With Me.DataGridViewMC1_RIS
.RowsDefaultCellStyle.BackColor = Color.White
.RowsDefaultCellStyle.ForeColor = Color.Black
.AlternatingRowsDefaultCellStyle.BackColor = Color.LightSteelBlue
End With

With Me.DataGridViewMC1_RIS_ISSUANCE
.RowsDefaultCellStyle.BackColor = Color.White
.RowsDefaultCellStyle.ForeColor = Color.Black
.AlternatingRowsDefaultCellStyle.BackColor = Color.LightSteelBlue
End With

With Me.DataGridViewMC1_RIS_STATUS
.RowsDefaultCellStyle.BackColor = Color.White
.RowsDefaultCellStyle.ForeColor = Color.Black
.AlternatingRowsDefaultCellStyle.BackColor = Color.LightSteelBlue
End With

ElseIf TabControlMainContent.SelectedIndex = 1 Then
LoadSCData()
End If
End Sub

Private Sub DataGridViewMC1_RIS_STATUS_Scroll(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ScrollEventArgs) Handles DataGridViewMC1_RIS_STATUS.Scroll
If e.ScrollOrientation = ScrollOrientation.VerticalScroll Then
Dim i As Integer = DataGridViewMC1_RIS_STATUS.FirstDisplayedScrollingRowIndex
DataGridViewMC1_RIS.FirstDisplayedScrollingRowIndex = i
DataGridViewMC1_RIS_ISSUANCE.FirstDisplayedScrollingRowIndex = i
End If
End Sub

#End Region
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
You know.. looking at your screenshot, this should be so easy it's untrue

In your shot, the top right, you se ethe Data Sources window?

make a new form in your project
drag the tblREQUISITION onto the form
expand the tblREQUISITION node to reveal another tblSTOCK_CARD node
drag this new tblSTOCK_CARD node to the form

Visual studio will set up 2 grids, one the child of the other
DO NOT drag the main tblSTOCK_CARD node to the form. This will NOT generate a parent/child relationship

If you don't see a tblSTOCK_CARD listed under the tblREQUISITION, open the dataset in the dataset designer and add a DataRelation between the two datatables (drag the ID column out of one and into another, check the parent and child ar ethe right way round)

Dont forget, that only creates the relation, you still have to fill the data into the datatables


-

Delete all that code you wrote in the LoadXXX methods; it's nasty
 
Top Bottom