[02/03] How to work data binding with dataset.

[nUflAvOrS]

Hi All,
I would like make a question against data binding with dataset.
I’m new of vb.net. Before I used .net I’m vb6 programmer. When I’m using vb6 I can’t easily call another form function to refresh combobox follow record added to database table.

Example,

I got a department table, table contains only 1 field. The field stores all unique department name of company. The department record can retrieve through some of the window form. When User has to update current record or add new record into database table. The user needs to open a parent form, through the parent form the user need to open a new form to do the transaction of adding or updating.

Here is my QUESTION; when the user updated or added new record. The parent form combobox will refresh the database record. In VB6 this task is quite simple but IN vb.net I really very new of .net. I search all the solution from internet and vbforum. I read some articles and thread such as jmcilhinney and others post. I get the information with data binding and dataset can solve my PROBLEM. However, I tried all the step and code but can’t work because I’m noobiez of .net. I ever used dataset when university assignment by vs 2005.I can create dataset easily but now I’m using vs 2003. How can I create the dataset and binding with my combobox. When record updated or added, another activated form will auto refresh the data with this no matter in combo box or listview or dvg.

PARENT FORM CLICK THE ADD BUTTON WILL SHOWDIALOG OF ANOTHER FORM TO ADD RECORD.



FORM OPEN FROM PARENT FORM WHEN RECORD SAVED, PARENT FORM WILL REFRESH THE COMBOBOX.




ABOVE JUST A EXAMPLE.
Hope anyone of here can give me some advice, your advice are appreciated.

[jmcilhinney]

The whole point of data-binding is that you don't have to do anything to update bound controls. If you have the same DataTable bound to 10 controls and you change the data in one of them, the data-binding mechanism will propagate the change to the Datatable and then to the other controls... all automatically.

Now, to bind a DataTable to a list control, like a grid, ListBox or ComboBox, you assign it to the control's DataSource property. To bind it to a simple control you use the control's DataBindings collection and its Add method.

If you're only binding a DataTable to a single control or one group of controls that will all show the same record at the same time then you can bind the DataTable directly. If you want the same DataTable bound to multiple controls and be able to select records independently then you should create multiple DataViews first and then bind them to the controls.

[nUflAvOrS]

Thanks Sir to give me a clear concept of data bindings.

Can you point out my problem in my code thanks "=)


This is my SQL Class.

Option Strict On
Imports System.Data.SqlClient
Public Class SQL
    Protected Const SQL_CONNECTION_STRING As String = _
            "Data Source=SHLIM\SQLEXPRESS;Initial Catalog=Maintenance;User Id=;Password=;Trusted_Connection=True"
    Protected connectionString As String = SQL_CONNECTION_STRING
    Public da As SqlDataAdapter
    Public cnSQL As SqlConnection
    Public cmSQL As SqlCommand
    Public ds As New System.Data.DataSet
    Public Mode As String
Private drSQL1 As SqlDataReader

Public Sub SQLSELECT(ByVal StrSelect As String, ByVal strTable As String, ByVal strWHERE As String, ByVal strGROUP As String, ByVal strOrderBy As String, ByVal Blnds As Boolean)
        Dim strSQL As String
        Try

strSQL = "SELECT " & StrSelect.Trim & " FROM " & strTable.Trim _
                     & " " & strWHERE.Trim _
                     & " " & strGROUP.Trim _
                     & " " & strOrderBy.Trim

            cnSQL = New SqlConnection(connectionString)
            cnSQL.Open()
            If Blnds = False Then
                cmSQL = New SqlCommand(strSQL, cnSQL)

                drSQL = cmSQL.ExecuteReader()

            Else
                Me.drSQL.Close()
                da = New SqlDataAdapter(strSQL, cnSQL)
                da.Fill(ds, strTable)

            End If
        Catch e As SqlException
            MsgBox(e.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch e As Exception
            MsgBox(e.Message, MsgBoxStyle.Critical, "General Error")
        End Try
    End Sub



Public Property drSQL() As SqlDataReader
        Get
            Return drSQL1
        End Get
        Set(ByVal Value As SqlDataReader)
            drSQL1 = Value
        End Set
End Property
Public Property intRowsAffected() As Integer
        Get
            Return intRowsAffected1
        End Get
        Set(ByVal Value As Integer)
            intRowsAffected1 = Value
        End Set
End Property

End class

This is my Parent form which will load when record updated

Public Class frmAddNewMac
Inherits System.Windows.Forms.Form
Private WithEvents frmadd As frmadd
Private SQL As New SQL


Private Sub frmAddNewMac_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Top = 40
        Me.Left = 150
        '0 Loc
        '1 Vendor
        '2 Machine Type
        '3 Machine Status
        

        Me.LoadCombo(Me.cboLoc, "Location", "Location_", 0)
        Me.LoadCombo(Me.cboVendor, "VendorType", "Ven_Type", 1)
        Me.LoadCombo(Me.cmdMacType, "Machinetype", "Mac_Type", 2)
        Me.LoadCombo(Me.cboStatus, "MachineStatus", "Mac_Status", 3)
        Me.LoadYear()
End Sub

Public Sub LoadCombo(ByVal ComboxBoxName As ComboBox, ByVal strField As String, ByVal strTable As String, ByVal TableSelect As Integer)

'I did a lot of method in this section


	
	
        'Dim ComboBoxBinder As CurrencyManager
        'ComboxBoxName.Items.Clear()


        SQL.SQLSELECT(strField, strTable, "", "", "", True)
        ComboxBoxName.DataSource = SQL.ds
        ComboxBoxName.DisplayMember = strTable + "." + strField

        'ComboBoxBinder = Me.BindingContext(SQL.ds, strTable)

'ComboxBoxName.DataBindings.Add("Text", SQL.ds, strTable + "."         + strField)


'For count As Integer = 0 To SQL.ds.Tables(TableSelect).Rows.Count - 1
'    ComboxBoxName.Items.Add(SQL.ds.Tables(TableSelect).Rows(count)(0))
        'Next


        'Do While SQL.drSQL.Read
        '    ComboxBoxName.Items.Add(SQL.drSQL.Item(0))
        'Loop
   End Sub
End Class

Furthermore, When user add a new record example form like my last post second picture attachment. How can i indicate the system to refresh the combo box. Izzit the data bindings will automatically refresh it as "jmc Master" said. Else what shld i do ?

Thanks :-)

[nUflAvOrS]

I succeed to bind combobox with dataset, however when I added record to database table. The combobox binded with dataset can’t refresh the combobox item compatible with dataset value. I tested with integer i, I obtained the actual row count value. However, I could not refresh the combo list item.

Public Sub SQLSELECT(ByVal StrSelect As String, ByVal strTable As String, ByVal strWHERE As String, ByVal strGROUP As String, ByVal strOrderBy As String, ByVal Blnds As Boolean)
        Dim strSQL As String
        Try

            strSQL = "SELECT " & StrSelect.Trim & " FROM " & strTable.Trim _
                     & " " & strWHERE.Trim _
                     & " " & strGROUP.Trim _
                     & " " & strOrderBy.Trim

            cnSQL = New SqlConnection(connectionString)
            cnSQL.Open()
            If Blnds = False Then
                cmSQL = New SqlCommand(strSQL, cnSQL)

                drSQL = cmSQL.ExecuteReader()

            Else
                da = New SqlDataAdapter(strSQL, cnSQL)
               Dim drCommBuilder As SqlCommandBuilder = _
               New SqlCommandBuilder(da)     
                da.MissingSchemaAction = MissingSchemaAction.Add
                da.Fill(ds, strTable)
                dr = ds.Tables(strTable).NewRow
                ds.Tables(strTable).Rows.Add(dr)
            End If
        Catch e As SqlException
            MsgBox(e.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch e As Exception
            MsgBox(e.Message, MsgBoxStyle.Critical, "General Error")
        End Try
End Sub

Public Sub LoadCombo(ByVal ComboxBoxName As ComboBox, ByVal strField As String, ByVal strTable As String, ByVal TableSelect As Integer)
        
        Dim i As Integer

        SQL.SQLSELECT(strField, strTable, "", "", "", True)

        i = SQL.ds.Tables(strTable).Rows.Count()
        ComboxBoxName.DataSource = SQL.ds.Tables(strTable)
        ComboxBoxName.DisplayMember = strField
End sub

Please Help me to solve the problem ..... Thx

[nUflAvOrS]

Pls help@!!! T_T

[nUflAvOrS]

Nobody can help me ?

Thanks :-)