Solved

Simple question - using datagrids for data-entry

Posted on 2004-08-25
11
242 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now