8 Replies - 1726 Views - Last Post: 08 May 2013 - 11:08 PM Rate Topic: -----

#1 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Datareade with Datatable to display data in datagridview.

Posted 08 May 2013 - 08:09 AM

 Dim dt As New DataTable
        dt.Columns.Add("UOM ID", GetType(System.Int32))
        dt.Columns.Add("Item ID", GetType(System.Int32))
        dt.Columns.Add("Item_Name", GetType(System.String))
        dt.Columns.Add("BATCH NO", GetType(System.String))
        dt.Columns.Add("sale_id", GetType(System.String))
        dt.Columns.Add("MRP", GetType(System.Decimal))
        dt.Columns.Add("EP Rate", GetType(System.Decimal))
        dt.Columns.Add("Return Qty", GetType(System.Int32))   '9
        dt.Columns.Add("Amount", GetType(System.String))
        dt.Columns.Add("Disc Per", GetType(System.String))
        dt.Columns.Add("Discount Amount", GetType(System.Decimal))
        dt.Columns.Add("Vat Per", GetType(System.String))
        dt.Columns.Add("Vat Amt", GetType(System.Decimal))
        dt.Columns.Add("Surcharge %", GetType(System.Decimal))
        dt.Columns.Add("Add.Tax Amt", GetType(System.Decimal))
        dt.Columns.Add("Cst", GetType(System.Decimal))
        dt.Columns.Add("Cst Amount", GetType(System.Decimal))
        dt.Columns.Add("Expiry Dt", GetType(System.DateTime))
        dt.Columns.Add("Item Type", GetType(System.String))
        dt.Columns.Add("Total Amt", GetType(System.Decimal))
        dt.Columns.Add("Stock_Quantity", GetType(System.Decimal))
        Try
            q = "select iwh.sale_id,iwh.customer_name,iwh.customer_address,iwh.dl_number,iwh.tin_number,iwh.phone_number,iwd.item_name,iwd.expiry_date,iwd.quantity,iwd.item_vat,iwd.item_add_tax,iwd.total_amount,iwd.item_id,iwd.Item_Discount,iwd.grn_id,iwd.item_mrp,iwd.uom,iwd.vat_per,iwd.add_tax_per,iwd.cst_per,iwd.cst_amount,iwd.batch_no,iwd.item_type,iwd.disc_per,ium.Quantity as unitnumber  from Item_WholeSale_Header iwh inner join Item_Wholesale_Sale_Details iwd on iwd.Sale_Id=iwh.Sale_Id  INNER JOIN Item_Unit_Master ium on iwd.UOM =ium.Unit_ID    where iwd.sale_id='WS-" & (Trim(TxtNo.Text)) & "'"
            cn = New SqlConnection(My.Settings.PharmacyConnectionString)
            cn.Open()
            '  ad = New SqlDataAdapter(q, cn)
            ' ds = New DataSet
            '  ad.Fill(ds, "Item_WholeSale_Header")
            '  Sale_Return.DgvSR.DataSource = ds.Tables(0)



            cmd = New SqlCommand(q, cn)
            dr = cmd.ExecuteReader
            While dr.Read

                Dim r As DataRow = dt.NewRow
                r("UOM ID") = dr.Item("uom")      'uom
                r("Item ID") = dr.Item("item_id")
                r("Item_Name") = dr.Item("item_name")
                r("BATCH NO") = dr.Item("batch_no")
                r("sale_id") = dr.Item("sale_d") '
                r("MRP") = dr.Item("item_mrp")
                r("EP Rate") = dr.Item("ep_rate")
                r("Return Qty") = dr.Item("quantity")
                r("Amount") = dr.Item("total_amount")
                r("Disc Per") = dr.Item("disc_per")
                r("Discount Amount") = dr.Item("Item_Discount")
                r("Vat Per") = dr.Item("vat_per")
                r("Vat Amt") = dr.Item("item_vat")
                r("Surcharge %") = dr.Item("add_tax_per")
                r("Add.Tax Amt") = dr.Item("item_add_tax")
                r("Cst") = dr.Item("cst_per")
                r("Cst Amount") = dr.Item("cst_amount")
                r("Expiry Dt") = dr.Item("expiry_date")
                r("Item Type") = dr.Item("item_type")
                r("Total Amt") = dr.Item("total_amount")
                r("Stock_Quantity") = Val(dr.Item("uom")) * Val(dr.Item("quantity"))
                dt.Rows.Add(r)
            End While

            Sale_Return.DgvSR.DataSource = dt
            'dr.Close()
            '  ad.Dispose()
            cn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Me.Close()


im not getting my data in datagridview ..is there anything im missing here.if i use data adapter directly with dataset to datagridview im getting data.but i don't want this way

Is This A Good Question/Topic? 0
  • +

Replies To: Datareade with Datatable to display data in datagridview.

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,199
  • Joined: 25-September 09

Re: Datareade with Datatable to display data in datagridview.

Posted 08 May 2013 - 08:27 AM

May I first ask why your not just filling a datatable and setting it to your datagridview datasource instead of iterating through a reader.

It would reduce that down to about 5 lines of code.

This post has been edited by CharlieMay: 08 May 2013 - 08:27 AM

Was This Post Helpful? 0
  • +
  • -

#3 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Datareade with Datatable to display data in datagridview.

Posted 08 May 2013 - 08:32 AM

View PostCharlieMay, on 08 May 2013 - 08:27 AM, said:

May I first ask why your not just filling a datatable and setting it to your datagridview datasource instead of iterating through a reader.

It would reduce that down to about 5 lines of code.

do you mean i fill my dt datatable with data-adptor
Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,199
  • Joined: 25-September 09

Re: Datareade with Datatable to display data in datagridview.

Posted 08 May 2013 - 08:34 AM

yea
Was This Post Helpful? 0
  • +
  • -

#5 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Datareade with Datatable to display data in datagridview.

Posted 08 May 2013 - 08:40 AM

View PostCharlieMay, on 08 May 2013 - 08:34 AM, said:

yea

well that is the case which i can't use. actually i have to perform few calculation on my datagridview with different tables.so i can't give different names to my columns everytimes my table changed and it will lead more to make a calculation in table..

i have used datareader with datatable to sort this kind of problem that worked but i don't know why isn't it working here..

Hope so i explained well here ..it is all due to i have to work with few tables and i don't want everytime my column name get changed with change of datasource of my gridview..

This post has been edited by vks.gautam1: 08 May 2013 - 08:43 AM

Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,199
  • Joined: 25-September 09

Re: Datareade with Datatable to display data in datagridview.

Posted 08 May 2013 - 08:42 AM

You do know that you can perform calculation on data in the row through the query right?
Was This Post Helpful? 0
  • +
  • -

#7 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Datareade with Datatable to display data in datagridview.

Posted 08 May 2013 - 08:46 AM

View PostCharlieMay, on 08 May 2013 - 08:42 AM, said:

You do know that you can perform calculation on data in the row through the query right?


hmmm yes i know lol ok i wil surely try and amanha i will be back if there will be any further query
Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,199
  • Joined: 25-September 09

Re: Datareade with Datatable to display data in datagridview.

Posted 08 May 2013 - 08:49 AM

where you're doing this:
r("Stock_Quantity") = Val(dr.Item("uom")) * Val(dr.Item("quantity"))

would be a part of your select

SELECT .....all your columns, iwd.UOM * ium.Quantity As Stock_Quantity ..... INNER JOIN..... WHERE


Oh, and shame on you for not using a parameter in that statement ;)/>/>


So as far as what you have... :D

After the executereader, check dr.hasrows with a messagebox or something and see if it returns true. If not, the query didn't produce a result.

This post has been edited by CharlieMay: 08 May 2013 - 08:53 AM

Was This Post Helpful? 1
  • +
  • -

#9 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Datareade with Datatable to display data in datagridview.

Posted 08 May 2013 - 11:08 PM

View PostCharlieMay, on 08 May 2013 - 08:49 AM, said:

where you're doing this:
r("Stock_Quantity") = Val(dr.Item("uom")) * Val(dr.Item("quantity"))

would be a part of your select

SELECT .....all your columns, iwd.UOM * ium.Quantity As Stock_Quantity ..... INNER JOIN..... WHERE


Oh, and shame on you for not using a parameter in that statement ;)/>/>/>


So as far as what you have... :D/>

After the executereader, check dr.hasrows with a messagebox or something and see if it returns true. If not, the query didn't produce a result.

:whistling: yes thankx for keeping me on my toes...ya i should use that parameters their and it's all working thanks for all of this. :rockon: :P sometimes i become quite lazy to think in broader way
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1