master-detail in tab control

[patelheenah]

hi,
Can anyone help me out. Its very urgent to solve the following problem

i am using tab control to display the master-detail table.
I am using sql server 2000 as a back end

In my database i am having master i.e Purchase and detail i.e Purchase_Detail

The field of master are:
1) Voucher_No Pk
2) Date
.
.
.
.
Vendo_Name
Sub_total
Expense
Other Expense
Total Amount


In Detail table i am having
1) Voucher_No FK
2) Product Descritption
3) Quantity
4) Rate
5) Amount

I am having the tab control with two tab pages tabpage1 and tabpage2.

In tabpage1 i am to have show master table textboxes and in tabpage 2 i have datagridview. Can you please go through the code:

Private Sub Form6_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim con As New SqlConnection(str)
Dim ds As New DataSet
Dim dt1 As New DataTable
Dim dt2 As New DataTable
ds.Tables.Add(dt1)
ds.Tables.Add(dt2)
Dim da1 As SqlDataAdapter
da1 = New SqlDataAdapter("Select * from Purchase", con)
Dim da2 As SqlDataAdapter
da2 = New SqlDataAdapter("Select* from purchase_Detail", con)
da1.FillSchema(dt1, SchemaType.Mapped)
da2.FillSchema(dt2, SchemaType.Mapped)
da1.Fill(dt1)
da2.Fill(dt2)

Dim rel As DataRelation
rel = New DataRelation("FK__Purchase___Vouch__276EDEB3", ds.Tables(0).Columns("Voucher_No"), ds.Tables(1).Columns("Voucher_No"))
ds.Relations.Add(rel)
Dim PBindingSource As BindingSource
PBindingSource = New BindingSource(Me.components)

Me.PBindingSource.DataMember = ds.Tables(0).TableName
Me.PBindingSource.DataSource = ds

Me.TextBox1.DataBindings.Add(New Binding("Text", Me.PBindingSource, "Voucher_No", True))
Me.TextBox2.DataBindings.Add(New Binding("Text", Me.PBindingSource, "Bill_No", True))
Me.DateTimePicker1.DataBindings.Add(New Binding("Value", Me.PBindingSource, "Bill_Date", True))
Me.TextBox4.DataBindings.Add(New Binding("Text", Me.PBindingSource, "Challan_No", True))
Me.DateTimePicker2.DataBindings.Add(New Binding("Value", Me.PBindingSource, "Challan_Date", True))
Me.TextBox6.DataBindings.Add(New Binding("Text", Me.PBindingSource, "Sub_Total", True))
Me.TextBox7.DataBindings.Add(New Binding("Text", Me.PBindingSource, "VAT_Charges", True))
Me.TextBox8.DataBindings.Add(New Binding("Text", Me.PBindingSource, "Vat_percent", True))
Me.TextBox9.DataBindings.Add(New Binding("Text", Me.PBindingSource, "Other_Charges", True))
Me.TextBox3.DataBindings.Add(New Binding("Text", Me.PBindingSource, "Total_Amount", True))
Dim bspurchasedetail As New BindingSource
'bspurchasedetail = New BindingSource(Me.components)

Dim datagridview1 As New DataGridView
Me.bspurchasedetail.DataSource = PBindingSource
'Me.bspurchasedetail.DataMember = "FK__Purchase___Vouch__276EDEB3"

Me.DataGridView2.DataSource = ds.Tables("Purchase_Detail")

Me.DataGridView3.DataMember = "FK__Purchase___Vouch__1367E606"

end sub
end class


When i compile the then its not working . To display all the data of master in respective textbox and all the information of particular voucherno in datagridview

Secondly, if i want this to be in edit mode then how its possible.

A Quick response will be awaited please help me out.

 

[cjard]

I'd love to go through the code, if it didnt look like a pile of **** slammed up against the left edge of the screen. Take a read on what a code tag is:

http://www.vbdotnetforums.com/misc.php?do=bbcode

 

[patelheenah]

Ok, i got it but can you please favor me in solving the problem. It would be great help for me

 

[cjard]

Ok, it looks like you dont got it but can you please favor me in solving the problem of your code being all slammed up against the left edge of your post. It would be great help for me if you would notice the EDIT button

 

[patelheenah]

hi,

Can anyone help me out. Its very urgent to solve the following problem

i am using tab control to display the master-detail table.
I am using sql server 2000 as a back end

In my database i am having master i.e Purchase and detail i.e Purchase_Detail

The field of master are:
1) Voucher_No Pk
2) Date
.
.
.
.
Vendo_Name
Sub_total
Expense
Other Expense
Total Amount


In Detail table i am having
1) Voucher_No FK
2) Product Descritption
3) Quantity
4) Rate
5) Amount

I am having the tab control with two tab pages tabpage1 and tabpage2.​

In tabpage1 i am to have show master table textboxes and in tabpage 2 i have datagridview. Can you please go through the code:

 Private Sub Form6_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim con As New SqlConnection(str)
Dim ds As New DataSet
Dim dt1 As New DataTable
Dim dt2 As New DataTable
ds.Tables.Add(dt1)
ds.Tables.Add(dt2)
Dim da1 As SqlDataAdapter
da1 = New SqlDataAdapter("Select * from Purchase", con)
Dim da2 As SqlDataAdapter
da2 = New SqlDataAdapter("Select* from purchase_Detail", con)
da1.FillSchema(dt1, SchemaType.Mapped)
da2.FillSchema(dt2, SchemaType.Mapped)
da1.Fill(dt1)
da2.Fill(dt2)

Dim rel As DataRelation
rel = New DataRelation("FK__Purchase___Vouch__276EDEB3", ds.Tables(0).Columns("Voucher_No"), ds.Tables(1).Columns("Voucher_No"))
ds.Relations.Add(rel)
Dim PBindingSource As BindingSource
PBindingSource = New BindingSource(Me.components)

Me.PBindingSource.DataMember = ds.Tables(0).TableName
Me.PBindingSource.DataSource = ds

Me.TextBox1.DataBindings.Add(New Binding("Text", Me.PBindingSource, "Voucher_No", True))
Me.TextBox2.DataBindings.Add(New Binding("Text", Me.PBindingSource, "Bill_No", True))
Me.DateTimePicker1.DataBindings.Add(New Binding("Value", Me.PBindingSource, "Bill_Date", True))
Me.TextBox4.DataBindings.Add(New Binding("Text", Me.PBindingSource, "Challan_No", True))
Me.DateTimePicker2.DataBindings.Add(New Binding("Value", Me.PBindingSource, "Challan_Date", True))
Me.TextBox6.DataBindings.Add(New Binding("Text", Me.PBindingSource, "Sub_Total", True))
Me.TextBox7.DataBindings.Add(New Binding("Text", Me.PBindingSource, "VAT_Charges", True))
Me.TextBox8.DataBindings.Add(New Binding("Text", Me.PBindingSource, "Vat_percent", True))
Me.TextBox9.DataBindings.Add(New Binding("Text", Me.PBindingSource, "Other_Charges", True))
Me.TextBox3.DataBindings.Add(New Binding("Text", Me.PBindingSource, "Total_Amount", True))
Dim bspurchasedetail As New BindingSource
'bspurchasedetail = New BindingSource(Me.components)

Dim datagridview1 As New DataGridView
Me.bspurchasedetail.DataSource = PBindingSource
'Me.bspurchasedetail.DataMember = "FK__Purchase___Vouch__276EDEB3"

Me.DataGridView2.DataSource = ds.Tables("Purchase_Detail")

Me.DataGridView3.DataMember = "FK__Purchase___Vouch__1367E606"

end sub 
end class

When i compile the then its not working . To display all the data of master in respective textbox and all the information of particular voucherno in datagridview. I have three button one for 1) Add, 2)Update & 3) delete.

Addbtn is to insert the data in database. One Voucher_No can have many products purchased But the problem is that when i add data in datagridview first record gets saved in database and makes blank the datagridview actually i want the sum of Amount of all the particular voucher_No of datagridview get into the Sub_total Textbox.

Secondly, if i want this to be in edit mode then how its possible.

A Quick response will be awaited please help me out.​

 

[cjard]

thats a lot of hard work you wrote, when the visual studio designer can write it for you..

take a read of the dw2 link in my signature, section on displaying related data

 

[patelheenah]

Thank you very much for your help... Its really really working without using a bit of coding.

But Still i have a problem in it. Its gets compiled without any error. But my thing is that when i start using inserting data in textbox (master table in detail desinger) its fine working but as soon when i start entering in datagridview (Showing Detail Table) when end up with first row its automatically get saved in database and making datagridview empty. I don't want that. As in Detail table it can have multiple entry so i don't datagridview get empty as soon as a row is filled. I want multiple entry and when i click on savebutton then only all the multiple entry must get into database.
Secondly i want the multiplication of Quantity * Rate Automatically generated in datagridview Amount column. For that i have coded as below
​

Private Sub Purchase_DetailDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles Purchase_DetailDataGridView.CellContentClick
        If Me.Purchase_DetailDataGridView.Columns(e.ColumnIndex).Name = "Rate" Then
            Me.Purchase_DetailDataGridView.Rows(e.RowIndex).Cells("Amount").Value = Me.Purchase_DetailDataGridView.Rows(e.RowIndex).Cells("Quantity").Value * Me.Purchase_DetailDataGridView.Rows(e.RowIndex).Cells("Rate").Value
        End If
    End Sub

This doesn't work as i want it automatically generated in Amount Column in Datagridview.​

Thirdly, i want the sum of all Amount column of datagridview must displayed in SubtotalTextbox. Code for the same is :

 Private Sub Sub_Total_GotFocus(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles   Sub_Total.GotFocus 

        Dim isubtotal As Integer = 0
        Dim dgvr As New DataGridView

        For Each dgvr In Purchase_DetailDataGridView.Rows
            isubtotal += dgvr.Cells("Amount").Value

        Next
        Me.Sub_TotalTextBox.Text = isubtotal
    End Sub

This also does not work. I think this won't work unless problems would be solved..

I Hope you are getting my problem now...​