none
binding problem to classes and stored procedues RRS feed

  • Question

  • hi i have a stored procedure in my db that returns me a sql select command. i use this command to fill a table class as below.  now i have a class that i want to map to each field of this datatable. how do i go about or am i going the wrong way?  i could basically have a list of detailedbills(see detailed bills) on next post now. which i will bind to a manager. but how i map these two?

     

    Imports System.Data.SqlClient

    Public Class bills

    Inherits DataTable

    Private Sub FillBill()

    Dim myConnection As New SqlConnection(My.Settings.CostingConnectionString)

    Dim myCommand As New SqlCommand("DetailedBill", myConnection)

    myCommand.CommandType = CommandType.StoredProcedure

    myConnection.Open()

    Dim s As String = myCommand.ExecuteScalar.ToString

    Dim conn As New SqlClient.SqlConnection(My.Settings.CostingConnectionString)

    Dim da1 As New SqlClient.SqlDataAdapter(s, conn)

    da1.FillSchema(Me, SchemaType.Source)

    da1.Fill(Me)

    End Sub

    Public Sub New()

    MyBase.New()

    FillBill()

    End Sub

    End Class

    Tuesday, September 5, 2006 8:17 AM

Answers

  • I am not sure I really understand the question.  I would bind the master table to a bindingsource and make the datagridview's datasource the bindingsource.  In the bindingsource's position change event you can call your stored procedure.
    Tuesday, September 5, 2006 6:06 PM

All replies

  • Public Class DetailedBill

    Dim BillID As New Guid

    Dim Pono As String

    Dim _Date As Date

    Dim SupplierCode As String

    Dim Reference As String

    Dim BillType As String

    Dim ROE As Double

    Dim Currency As String

    Dim LinkBill As Guid

    Dim multiplicityID As Guid

    Dim payment_or_bill As Guid

    Dim TypeCode As String

    Dim AMOUNT As Double

    Dim VAT As Double

    Dim DUTY As Double

    Public Property myPayment_or_bill() As Guid

    Get

    Return Me.payment_or_bill

    End Get

    Set(ByVal value As Guid)

    Me.payment_or_bill = value

    End Set

    End Property

    Public Property myMultiplicityID() As Guid

    Get

    Return Me.multiplicityID

    End Get

    Set(ByVal value As Guid)

    Me.multiplicityID = value

    End Set

    End Property

    Public Property myLinkBill() As Guid

    Get

    Return Me.LinkBill

    End Get

    Set(ByVal value As Guid)

    Me.LinkBill = value

    End Set

    End Property

    Public Property myCurrency() As Double

    Get

    Return Me.Currency

    End Get

    Set(ByVal value As Double)

    Me.Currency = value

    End Set

    End Property

    Public Property myRoe() As Double

    Get

    Return Me.ROE

    End Get

    Set(ByVal value As Double)

    Me.ROE = value

    End Set

    End Property

    Public Property myTypeCode() As String

    Get

    Return Me.TypeCode

    End Get

    Set(ByVal value As String)

    Me.TypeCode = value

    End Set

    End Property

    Public Property myBillType() As String

    Get

    Return Me.BillType

    End Get

    Set(ByVal value As String)

    Me.BillType = value

    End Set

    End Property

    Public Property myReference() As String

    Get

    Return Me.Reference

    End Get

    Set(ByVal value As String)

    Me.Reference = value

    End Set

    End Property

    Public Property mySupplierCode() As String

    Get

    Return Me.SupplierCode

    End Get

    Set(ByVal value As String)

    Me.SupplierCode = value

    End Set

    End Property

    Public Property myDate() As Date

    Get

    Return _Date

    End Get

    Set(ByVal value As Date)

    _Date = value

    End Set

    End Property

    Public Property myPono() As String

    Get

    Return Me.Pono

    End Get

    Set(ByVal value As String)

    Me.Pono = value

    End Set

    End Property

    Public Property myBillId() As Guid

    Get

    Return Me.BillID

    End Get

    Set(ByVal value As Guid)

    Me.BillID = value

    End Set

    End Property

    Public Property myAmount() As Double

    Get

    Return Me.AMOUNT

    End Get

    Set(ByVal value As Double)

    Me.AMOUNT = value

    End Set

    End Property

    Public Property myVat() As Double

    Get

    Return Me.VAT

    End Get

    Set(ByVal value As Double)

    Me.VAT = value

    End Set

    End Property

    Public Property myDuty() As Double

    Get

    Return Me.DUTY

    End Get

    Set(ByVal value As Double)

    Me.DUTY = value

    End Set

    End Property

    End Class

    Tuesday, September 5, 2006 8:18 AM
  • So the idea is to have a mapping between my two classes., the datatable and the class detailed bill., I am a bit confused about what i am doing here.

    the idea is .

    1.    my grid is binded to the class (list of)  or the table  //am not sure by now how it will work

    2. my class bills should execute the select command returned by the stored procedure. this is used to fill values as shown in the datatable class.

    so how do i map these/??

    is it there is some other way of doing this??

    Tuesday, September 5, 2006 8:21 AM
  • and to get the idea, this is the stored procedure, not as yet defined to match the fields in my class.. will be done when the above is solved.

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    ALTER PROCEDURE [dbo].[DetailedBill]

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @fieldname varchar(10) DECLARE @stmt varchar(4000)

    DECLARE Fields CURSOR FOR SELECT Amounttype

    FROM Amounttypes

    SET @stmt = 'select pono, billtype,reference' OPEN Fields FETCH Next

    FROM Fields INTO @fieldname

    WHILE @@Fetch_Status = 0

    BEGIN

    if exists(Select * From bill Where Amountfc <> 0 And AmountType = @fieldname)

    SET @stmt = @stmt + ', sum(amountfc * Case When amounttype = ''' + @fieldname + ''' Then 1 Else 0 End) As ' + @fieldname

    FETCH Next

    FROM Fields

    INTO @fieldname END

    CLOSE Fields

    DEALLOCATE Fields

    SET @stmt = @stmt + ' From bill '

    set @stmt = @stmt + ' Group By pono, billtype,reference '

    --exec(@stmt)

    select (@stmt)

    END

    Tuesday, September 5, 2006 8:23 AM
  • I am not sure I really understand the question.  I would bind the master table to a bindingsource and make the datagridview's datasource the bindingsource.  In the bindingsource's position change event you can call your stored procedure.
    Tuesday, September 5, 2006 6:06 PM
  • Eye Opener once again

    ken thanks.

    I'm going full time designing my forms with dgv's and always binding to a bindingsource  that then be bound.

    on top of that i could design the system with a common binding navigator that works too lovely bra it was so easy actually:)  It was only about the save that i implement for each grid that needs saving and the save button is shared with events, implemented on each form. hehe i'm back to events reality. it was a bit hard from java to vb  where i was full into listeners and the methodology was a bit bit different but not that much variation though .

    p.s the concept of me designing a control with a dgv with navigator a wrong way,

    thanks for your precious time,

    have a good day.

    Thursday, September 7, 2006 9:12 AM