How to do a GUI base data-aware application?

Question

Hi,

I would like to ask how to do a GUI base data binding? without righting any lines of code in terms of adding,editing, deleting or searching records from the database to your application.

Answer 1

Use MS Office Access, but know that you than direct limited to the possibilities of that.

---

Success
Cor

Answer 2

If you want a WinForms application, you could follow  the instructions here:

http://msdn.microsoft.com/en-us/library/ms171884.aspx

Hope this helps.

---

www.insteptech.com ; msmvps.com/blogs/deborahk
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

Answer 3

@DeborahK and @Cor Ligthert

Thank you

is it possible to have a data-aware GUI base data binding using MySQL Server? or I need a third party components?

Thank you guys.

Answer 4

Yes. You should be able to follow the basic directions in the link I provided. Just set the connection to MySql instead of SQL Server.

Hope this helps.

---

www.insteptech.com ; msmvps.com/blogs/deborahk
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

Answer 5

Thank you DeborahK and Cor for your friendly help and support!

 

Hi Ton,

Welcome to MSDN forums!

Besides, here are four methods to make one basic Data Access application(Previous, Next, First. Last, Update, Delete, Insert, Select, Save) for you to check and follow.

Method 1:  Update (Insert/Update/Delete) data back into SQL Server database via a DataGridView.
http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/5980181e-f666-4f0a-ab50-c4ebecf96f02/

Imports System.Data.SqlClient     Public Class Form1         Dim myDA As SqlDataAdapter       Dim myDataSet As DataSet       Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load           Dim con As SqlConnection = New SqlConnection("Data Source=.;Integrated Security=True;AttachDbFilename=|DataDirectory|\SqlDatabase.mdf")           Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Table1", con)           con.Open()           myDA = New SqlDataAdapter(cmd)           'Automatically generates DeleteCommand, UpdateCommand and InsertCommand for DataAdapter object           Dim builder As SqlCommandBuilder = New SqlCommandBuilder(myDA)           myDataSet = New DataSet()           myDA.Fill(myDataSet, "MyTable")           DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView           con.Close()           con = Nothing      End Sub        ' Save data from DataGridView into Database       Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click           Me.Validate()           Me.myDA.Update(Me.myDataSet.Tables("MyTable"))           Me.myDataSet.AcceptChanges()       End Sub    End Class

Method 2. Using Data Wizard with a BindingNavigator control.
Please check the 11th post in this thread for detailed walkthrough:
http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/

Method 3. Using DataSet/DataTable/DataAdapter in VB.NET code
Please check the 12th post and 13th post in this thread for detailed code sample.

http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/

Method 4. 

Execute Select/Insert/Delete/Update T-SQL commands in VB.NET code
   Code sample: How to Select/Insert/Delete/Update records in MS Access database in VB.NET
   http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/87913f28-992d-4705-963b-cb0ffa53d8dd/
   Code sample: How to Select/Insert/Delete/Update records in SQL Server database in VB.NET
   http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/89e1067d-16e7-44e8-b12d-d78845bf255f/



Best regards,
Martin Xie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 6

About How to search records from database and filter them, here are three approaches:

1) Use T-SQL Select command to filter records

Prerequisites: DataGridView1 and TextBox1 on Form1.

Imports System.Data.OleDb Public Class Form1     ' Handle TextBox_TextChanged event     Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged         Dim keywords As String = TextBox1.Text         Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")         ' Use wildcard           Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1 WHERE Filed1 Like '%" & keywords & "%' ", con)         ' or Where Filed1='" & keywords & "'           con.Open()         Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)         Dim myDataSet As DataSet = New DataSet()         myDA.Fill(myDataSet, "MyTable")         DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView     End Sub End Class

 

2) Use DataView.RowFilter Property to filter records

Prerequisites: DataGridView1 and TextBox1 on Form1.

Imports System.Data.OleDb Public Class Form1     Dim ds As DataSet     ' Firstly binding all records to DataGridView       Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load         Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")         Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)         con.Open()         Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)         ds = New DataSet()         myDA.Fill(ds, "MyTable")         con.Close()         DataGridView1.DataSource = ds.Tables("MyTable").DefaultView     End Sub     'Then filter datatable view       Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged         Dim keywords As String = TextBox1.Text         ds.Tables("MyTable").DefaultView.RowFilter = "Field1 =" & keywords         ' or  = "Field1 Like '%" & keywords & "%' "       End Sub End Class

 

Related thread:

http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/bdd212be-f815-4023-9db1-582b2439987a

 

3) You can use LINQ to SQL to filter records if you use SQL Server database.

Dim db As DataClasses1DataContext = New DataClasses1DataContext()           ' Using Like wildcard in LING to SQL         Dim tableQuery = _         From t In db.Table1 Where t.Filed1 Like "%" & keywords & "%" _         Select t           ' Or using String.Contains method instead of Like wildcard in LING to SQL         Dim tableQuery = _         From t In db.Table1 Where t.Filed1.Contains(keywords) _         Select t           DataGridView1.DataSource = tableQuery

Some tutorials about LINQ to SQL:

http://msdn.microsoft.com/en-us/library/bb546190.aspx

http://blogs.msdn.com/charlie/archive/2007/11/19/connect-to-a-sql-database-and-use-the-sql-designer.aspx

http://blogs.msdn.com/mitsu/archive/2008/04/02/visual-linq-query-builder-for-linq-to-sql-vlinq.aspx

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 7

Wow. Thanks Martin Xie - MSFT

I don't have to hard code everything in my projects. wheeeew.. thank you very much. I'm getting tired of using visual studio until you help me with my problem.

Answer 8

Hi Martin,

Can you help me using GridView?

My problem is that I want to fill my custom Gridview columns

I have the following columns on my GridView

UserID
Name
Username -> this is hidden

My following code

    Private Sub LoadUsers()
        Dim UserCommand As MySqlCommand = New MySqlCommand("SELECT userid AS UserID,username as UserName, name as Name FROM users", DBConnection)
        DBDataAdapter = New MySqlDataAdapter(UserCommand)

        Dim DBBuilder As MySqlCommandBuilder = New MySqlCommandBuilder(DBDataAdapter)
        DBDataAdapter.Fill(UsersDataSet, "UsersTable")

        UsersGrid.DataSource = UsersDataSet.Tables("UsersTable").DefaultView
    End Sub

How to hide username column if I use dataset to fill the grid?

thank you.

Answer 9

Hi Ton,

I'm glad to hear that you got it working :) Cheers!


"How to hide username column if I use dataset to fill the grid?"
-> Please note: DataGridView's Column, Row and Cell have the Visible property.

Based on your scenario, the UserName will be filled as the second column in UsersGrid, so you can hide the UserName column like this:

UsersGrid.Columns(1).Visible = False
Or
UsersGrid.Columns("UserName").Visible = False



Best regards,
Martin Xie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.