Click here to Skip to main content
15,075,638 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
when click the button didn't show a data in datagridview, the respected data is available in database
Please help me;
VB
Private Sub load_acc()
       Try
            sqL = "SELECT DISTINCT [POSNo], [SubTotal], [Discount], [VAT], [Total], [Type] FROM tblPOS WHERE POSDate >= #" & dtpFrom.Text & "# AND POSDate <=#" & dtpTo.Text & "# ORDER BY POSNo"

           ConnDB()
           cmd = New OleDbCommand(sqL, conn)
           dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
           dgw.Rows.Clear()

           subtotal = 0
           disscount = 0
           tax = 0
           totalAmount = 0

           Do While dr.Read = True
               dgw.Rows.Add(dr(0), dr(1), dr(2), dr(3), dr(4), dr(5))
               subtotal += dr(1)
               'disscount += dr(2)
               ' tax += dr(3)
               totalAmount += dr(4)
           Loop

           lblSubTotal.Text = Format(subtotal, "#,##0.00")
           lblDiscount.Text = Format(disscount, "#,##0.00")
           lblTAX.Text = Format(tax, "#,##0.00")
           lblTotal.Text = Format(totalAmount, "#,##0.00")
       Catch ex As Exception
           MsgBox(ex.Message)
       Finally
           cmd.Dispose()
           conn.Close()
       End Try
   End Sub
Posted
Updated 4-Feb-16 14:12pm
v2
Comments
Richard Deeming 4-Feb-16 8:33am
   
Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

1 solution

Richard is correct, you should not use string concatenation but there is a much better way of doing this.

Firstly; Initialise your datagridview by adding the columns that you require, ensuring that you set the AutoGenerateColumns to false - see below
VB
dgw.DataSource = null
dgw.Columns.Clear()
dgw.AutoGenerateColumns = false

Then add in the columns you require, typically you only need DataGridViewTextBoxColumns but by adding them you get to set the data-type and display format which allows you to sort them correctly & set properties like column visibility, widths and headings.
Refer here for MSDN documentation on the DataGridViewTextBoxColumn

Secondly; Load the DataReader results to a DataTable and then bind the DataTable to the DataGridView - refer below;
VB
Dim dataTable as DataTable
dataTable.Load(dr)
dgw.DataSource = dataTable


Third; Re-use the datatable to get the label values you require using an Expression on a datacolumn
Refer here for MSDN documentation on DataColumn.Expression

This will allow you to get rid handling the data row by row & will be much more efficient

Kind Regards
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900