How to show/display saved data on data grid view-CodePudding

I'm currently trying to code a material booking module for an IMS (Inventory Management System) using vb.net. Here's my part of code that has already been done

 Public Class frmINBKG
   Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
   Me.Close()
 End Sub
 Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
   Call ClearDetails()
 End Sub


  Private Function SaveRecord() As Boolean
    Dim strSQL As String

    strSQL = "INSERT INTO INBKG_TBL ("
    strSQL &= "INBKG_BKGID, INBKG_BKGDT, INBKG_USRID, INBKG_MATCD, "
    strSQL &= "INBKG_BKGQT, INBKG_STSFG) "
    strSQL &= "VALUES ('" & txtBKGID.Text & "', '" & gfSQLDate(dtBKGDT.Value) & "', '" & txtUSRID.Text & "', '" & txtBKGMAT.Text & "', "
    strSQL &= "" & numBKGQT.Value & ",  '1')"
    Call DBExecute(strSQL)
 End Function

 Private Sub ClearDetails()
   txtBKGID.Text = String.Empty
   dtBKGDT.Value = Date.Today
   txtUSRID.Text = String.Empty
   txtBKGMAT.Text = String.Empty
   numBKGQT.Value = 0
   numRECID.Value = 0
 End Sub

 Private Sub RefreshGrid()
   Dim strSQL As String
   Dim dt As DataTable
   Dim i As Integer

   strSQL = "SELECT INBKG_BKGID, INBKG_BKGDT, INBKG_USRID, INBKG_MATCD, "
   strSQL &= "INBKG_BKGQT, INBKG_STSFG "
   strSQL &= "FROM INBKG_TBL "
   strSQL &= "WHERE INBKG_STSFG IN ('1', '2')"
   dt = ExecProc(strSQL)

 *show grid

 End Sub
  Private Sub frmINEADJ_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
  Dim blnChildFormExists As Boolean

  For Each ChildForm As Form In Me.ParentForm.MdiChildren
     If ChildForm.Name <> Me.Name Then
        blnChildFormExists = True
        Exit For
     End If
  Next
   If Not blnChildFormExists Then Me.ParentForm.Controls("Panel1").Visible = True
End Sub

 Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
   Call SaveRecord()
   Call RefreshGrid()
 End Sub

 Private Sub btnRefresh_Click(sender As Object, e As EventArgs) Handles btnRefresh.Click
   Call RefreshGrid()
 End Sub
End Class

The *show grid line is where I suppose to have a code that allows me to show any saved data on the data grid view. I'm new to vb.net, so I've been told to refer to the other modules for examples but most of them are just not what I need or aren't compatible. I'm currently using Visual Studio 2019 and Microsoft SQL Server Management Studio, any answers or suggestion is appreciated.

CodePudding user response：

I would avoid DataTables and create an object class with properties that represent the table. Add each object to a BindingList and use that as the datasource for the grid. The grid then automatically updates when anything is changed.

Also, to avoid SQL injection attacks, always use SqlParameters.

CodePudding user response：

My suggestion would be to use Entity Framework (Tools -> Manage NuGet package -> manage NuGet package for the solution.. 6.4.4 should be the latest version). Entity treats your Database tables like Classes. I'll give you an example

'Create your entity object
Dim context as New MyProjectEntity
'Query
Dim myGridviewDataSource = context.myTable.Tolist
Me.gridview1.Properties.DataSource = myGridviewDataSource

This is a simple example. Your gridview will have in the header the column names of myTable, the data will be shown automatically and the ID column will be hidden. You can eventually set the columns from the designer window (SHIFT F7). It's really nice cause you can query simply using lambda expressions. Let's say you have a table called Students and a column called studentName and you want to get only the Students with studentName = John. You can simply query like this:

Dim myGridviewDataSource = context.Students.Where(function(x) x.Name.equals("John")).Tolist

Also, if you have foreign keys just go on the designer window, run the gridview designer, and in the column fieldname you do the trick. For example, for each Student you have a Teacher assigned, so you have the teacherID column as foreign key in Students, which is the primary key for the table Teacher but you want to see the name of the Teacher on the gridview. So go on the designer window, on the fieldname of the column targeted for the Teacher name you give it the value

Teacher.teacherName

teacherName has to be the name of the column in the Teacher table of course. Remember, you need to specify the foreign key in Microsoft SQL Server Management Studio! Entity will do the rest