Solved

Simple question - using datagrids for data-entry

Posted on 2004-08-25
11
245 Views
Last Modified: 2012-05-05
I know ASP.NET very well and VB.NET, and am just starting with Windows Forms.  I know ADO.NET, but dislike using wizards provided (must be a control freak).  I want to create a simple datagrid for use for data-entry which will work just like Access or SQL Server table (ie data entered into datagrid is added immediately to underlying table).  

If I create a dataset/datatable and bind form to that, I'll have to write the records back to the underlying database.  Can I bind data directly to Access/SQL Server table to avoid user having to click on an Update button?  Failing that, should I be using a dataadapter?
0
Comment
Question by:AndyOwl
  • 7
  • 4
11 Comments
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11890338
No, you can't ado.net is disconnected . The easiest way to write data back to the database is indeed a dataadapter.
You don't really have to implement an update button you could to that without user interaction, in lets say the form deactivate event.
0
 

Author Comment

by:AndyOwl
ID: 11890409
Does a dataadapter build an update command in SQL?  Do you have a standard bit of simple VB.NET code to do this?  

I'm just trying to get into the philosophy of how best to do things in VB.NET.  I know everyone says you create a single-record data entry form, and I'm inclinded to agree with this, but occasionally I will want a simple grid for data-entry.  

Or perhaps the best way is to allow a user to make any changes they like then click on a WRITE CHANGES TO DATABASE button to write datatable records back to database?

Thanks
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11890495
Hi Andy, There are several ways to do this.

1. Just drag an dataadapter to your form and use the wizard to create a select statement, the wizard will than generate the update,delete and insert command for you, when you want to update to the database just use
yourdataadapter.update(yourdataset)

2. Create your own dataadapter in code and use the commandbuilder method, this will create the update,insert,delete commands at runtime for you

3. create your own dataadapter in code and build the update,insert,delete command yourself.

option 1 is obviously the one that requires you to write the least code, but it will only work with simple select statements (no joins)
and requires the table to have a primary key (to create the update and delete statements)

option 2 Is a bit more flexibel because you can create the statements during runtime (but the same restriction apply as in option 1)

option 3 requires you to write all the code yourself but has the greatest flexibilty and allows for more complicated statements
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:AndyOwl
ID: 11890631
Option 3 sounds best, particularly as you could put it all in a class and just pass in SQL.  Presumably I'd then have a button at the bottom which would write changes back to database.  I still don't quite get it - what would happen if someone else had deleted a record in between?  Any sample VB.NET code to make an update work would be appreciated - just needs to be simple to give me the idea.
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11890681
I'll have an example for you in a minute
0
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 250 total points
ID: 11890796
This example uses an access database

    Dim con As New OleDb.OleDbConnection("Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
    "ocking Mode=1;Jet OLEDB:Database Password=;Data Source=""C:\databases\checking.md" & _
    "b"";Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provid" & _
    "er=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;Exte" & _
    "nded Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB" & _
    ":Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet O" & _
    "LEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Encrypt Databa" & _
    "se=False")

    Dim da As OleDb.OleDbDataAdapter
    Dim ds As DataSet

    Private Sub Form5_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        da = New OleDb.OleDbDataAdapter("select * from checking", con)
        ds = New DataSet
        da.Fill(ds)
        DataGrid1.DataSource = ds.Tables(0)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        da.UpdateCommand = New OleDb.OleDbCommand("UPDATE checking SET item = ?, need_scan = ?, orderID = ?, processed = ?, qty = ? " & _
        "WHERE (item = ?) AND (orderID = ?) AND (need_scan = ? OR ? IS NULL AND need_scan" & _
        " IS NULL) AND (processed = ? OR ? IS NULL AND processed IS NULL) AND (qty = ? OR" & _
        " ? IS NULL AND qty IS NULL)")
        da.UpdateCommand.Connection = con
        With da.UpdateCommand.Parameters
            .Add(New System.Data.OleDb.OleDbParameter("item", System.Data.OleDb.OleDbType.VarWChar, 50, "item"))
            .Add(New System.Data.OleDb.OleDbParameter("need_scan", System.Data.OleDb.OleDbType.Integer, 0, "need_scan"))
            .Add(New System.Data.OleDb.OleDbParameter("orderID", System.Data.OleDb.OleDbType.Integer, 0, "orderID"))
            .Add(New System.Data.OleDb.OleDbParameter("processed", System.Data.OleDb.OleDbType.Integer, 0, "processed"))
            .Add(New System.Data.OleDb.OleDbParameter("qty", System.Data.OleDb.OleDbType.Integer, 0, "qty"))
            .Add(New System.Data.OleDb.OleDbParameter("Original_item", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "item", System.Data.DataRowVersion.Original, Nothing))
            .Add(New System.Data.OleDb.OleDbParameter("Original_orderID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "orderID", System.Data.DataRowVersion.Original, Nothing))
            .Add(New System.Data.OleDb.OleDbParameter("Original_need_scan", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "need_scan", System.Data.DataRowVersion.Original, Nothing))
            .Add(New System.Data.OleDb.OleDbParameter("Original_need_scan1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "need_scan", System.Data.DataRowVersion.Original, Nothing))
            .Add(New System.Data.OleDb.OleDbParameter("Original_processed", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "processed", System.Data.DataRowVersion.Original, Nothing))
            .Add(New System.Data.OleDb.OleDbParameter("Original_processed1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "processed", System.Data.DataRowVersion.Original, Nothing))
            .Add(New System.Data.OleDb.OleDbParameter("Original_qty", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "qty", System.Data.DataRowVersion.Original, Nothing))
            .Add(New System.Data.OleDb.OleDbParameter("Original_qty1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "qty", System.Data.DataRowVersion.Original, Nothing))
        End With

        da.DeleteCommand = New OleDb.OleDbCommand("DELETE FROM checking WHERE (item = ?) AND (orderID = ?) AND (need_scan = ? OR ? I" & _
        "S NULL AND need_scan IS NULL) AND (processed = ? OR ? IS NULL AND processed IS N" & _
        "ULL) AND (qty = ? OR ? IS NULL AND qty IS NULL)")

        da.DeleteCommand.Connection = con

        With da.DeleteCommand.Parameters
            .Add(New System.Data.OleDb.OleDbParameter("Original_item", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "item", System.Data.DataRowVersion.Original, Nothing))
            .Add(New System.Data.OleDb.OleDbParameter("Original_orderID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "orderID", System.Data.DataRowVersion.Original, Nothing))
            .Add(New System.Data.OleDb.OleDbParameter("Original_need_scan", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "need_scan", System.Data.DataRowVersion.Original, Nothing))
            .Add(New System.Data.OleDb.OleDbParameter("Original_need_scan1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "need_scan", System.Data.DataRowVersion.Original, Nothing))
            .Add(New System.Data.OleDb.OleDbParameter("Original_processed", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "processed", System.Data.DataRowVersion.Original, Nothing))
            .Add(New System.Data.OleDb.OleDbParameter("Original_processed1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "processed", System.Data.DataRowVersion.Original, Nothing))
            .Add(New System.Data.OleDb.OleDbParameter("Original_qty", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "qty", System.Data.DataRowVersion.Original, Nothing))
            .Add(New System.Data.OleDb.OleDbParameter("Original_qty1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "qty", System.Data.DataRowVersion.Original, Nothing))
        End With


        da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO checking(item, need_scan, orderID, processed, qty) VALUES (?, ?, ?, ?, ?)")
        da.InsertCommand.Connection = con
        With da.InsertCommand.Parameters
            .Add(New System.Data.OleDb.OleDbParameter("item", System.Data.OleDb.OleDbType.VarWChar, 50, "item"))
            .Add(New System.Data.OleDb.OleDbParameter("need_scan", System.Data.OleDb.OleDbType.Integer, 0, "need_scan"))
            .Add(New System.Data.OleDb.OleDbParameter("orderID", System.Data.OleDb.OleDbType.Integer, 0, "orderID"))
            .Add(New System.Data.OleDb.OleDbParameter("processed", System.Data.OleDb.OleDbType.Integer, 0, "processed"))
            .Add(New System.Data.OleDb.OleDbParameter("qty", System.Data.OleDb.OleDbType.Integer, 0, "qty"))
        End With
        da.Update(ds)
        ds.AcceptChanges()
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11890818
This would be the code if you would use the command builder

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
   
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        da.Update(ds)
        ds.AcceptChanges()

end sub
0
 

Author Comment

by:AndyOwl
ID: 11890923
Thanks - have awarded points.  Now I'll try to get this to work.

Am I correct in thinking you have to build an update, insert and delete command just in case the changes you have made in the datagrid need them?

I'm sure I'll become a fan of datagrids in VB.NET soon ...
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11890951
Yep,

If you are using sql server a lot, then download this

http://www.microsoft.com/downloads/details.aspx?familyid=76fe2b16-3271-42c2-b138-2891102590ad&displaylang=en

this download contains a lot of overloaded functions that will greatly reduce the code you have to write when handling
dataadapters, datasets etc.. very powerfull.





0
 

Author Comment

by:AndyOwl
ID: 11891662
Thanks - I confess I tried just adding a data adapter from the toolbar, and now see where your sample code came from.  Now have to struggle with other things - but they can be the subject of another question ...
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11891745
There's nothing against using the designer, I use it a lot, just not when I need to do something really complicated
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question