[02/03] How to work data binding with dataset.
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.
http://img214.imageshack.us/img214/3696/pic1dn5.jpg
FORM OPEN FROM PARENT FORM WHEN RECORD SAVED, PARENT FORM WILL REFRESH THE COMBOBOX.
http://img466.imageshack.us/img466/8307/pic2fw9.jpg
ABOVE JUST A EXAMPLE.
Hope anyone of here can give me some advice, your advice are appreciated.
Re: [02/03] How to work data binding with dataset.
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.
Re: [02/03] How to work data binding with dataset.
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.
Code:
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
Code:
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
Code:
'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 :-)
Re: [02/03] How to work data binding with dataset.
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.
Code:
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
Code:
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
Re: [02/03] How to work data binding with dataset.
Re: [02/03] How to work data binding with dataset.
Nobody can help me ?
Thanks :-)