How to save unbound Datagrid to database table

Question

i have created a datagridview with unbound columns where has two comboboxcolumns. i want to save data to access database table from this DG.

Kirtan Patel · Answer

Here is Your Corrected Code Take a Look private void MakeTable() { DataColumn dc0 = new DataColumn ( "AccountNo" , typeof ( int )); dc0.Unique = true ; dt_saveTrans.Columns.Add(dc0); DataColumn dc1 = new DataColumn ( "AccountType" , typeof ( string )); dt_saveTrans.Columns.Add(dc1); DataColumn dc2 = new DataColumn ( "TransactionName" , typeof ( string )); dt_saveTrans.Columns.Add(dc2); DataColumn dc3 = new DataColumn ( "TransactionAmount" , typeof ( Decimal )); dt_saveTrans.Columns.Add(dc3); DataColumn dc4 = new DataColumn ( "PaymentType" , typeof ( string )); dt_saveTrans.Columns.Add(dc4); DataColumn dc5 = new DataColumn ( "TransactionDate" , typeof ( DateTime )); //dc5.DateTimeMode = dt_saveTrans.Columns.Add(dc5); for ( int i = 0; i { DataRow dr = dt_saveTrans.NewRow(); dr[0] = i; dr[1] = "AccountType" + i.ToString(); dr[2] = "TransactionName" + i.ToString(); dr[3] = Convert.ToDecimal( i); dr[4] = "PaymentType" + i.ToString(); dr[5] = DateTime.Now; dt_saveTrans.Rows.Add(dr); } dt_saveTrans.AcceptChanges(); }

Kirtan Patel · Answer

Friend, please upload your project Files(zip) file on http://mediafire.com and tell me the Download Link they provide you . and let me know what exactly you are trying to do i will correct the code and send you back :) Thank you :)

j k · Answer

hi KIRTAN thanks for your help. but the error msg again occur after update my code . what should i do. i need your help.

Kirtan Patel · Answer

Friend, Modifiy your Code like below try { con = new OleDbConnection (conStr); da = new OleDbDataAdapter ( "Select * from DailyTransaction" , con); ds_saveTable = new DataSet (); //Add below Two lines in Code OleDbCommandBuilder commBuild = new OleDbCommandBuilder (da); commBuild.GetUpdateCommand(); da.Update(ds_saveTable.Tables[ "dt_saveTrans" ]); } catch ( Exception ex) { MessageBox .Show(ex.Message); }

j k · Answer

Hi Kirtan, Thanks for your reply.i have made the table as you wrote. and in btn_Save i have added this code private void btn_save_Click( object sender, EventArgs e) { try { con = new OleDbConnection (conStr); da = new OleDbDataAdapter ( "Select * from DailyTransaction" , con); ds_saveTable = new DataSet (); //dt_saveTrans = null ; da.Update(ds_saveTable.Tables[ "dt_saveTrans" ]); } catch ( Exception ex) { MessageBox .Show(ex.Message); } } when click on button , error msg: value cannot be null,parameter dataTable. thanks

j k · Answer

Thank you.....I have created method as you advised private void MakeTable() { DataColumn dc0 = new DataColumn ( "AccountNo" , typeof ( int )); dc0.Unique = true ; dt_saveTrans.Columns.Add(dc0); DataColumn dc1 = new DataColumn ( "AccountType" , typeof ( string )); dt_saveTrans.Columns.Add(dc1); DataColumn dc2 = new DataColumn ( "TransactionName" , typeof ( string )); dt_saveTrans.Columns.Add(dc2); DataColumn dc3 = new DataColumn ( "TransactionAmount" , typeof ( Decimal )); dt_saveTrans.Columns.Add(dc3); DataColumn dc4 = new DataColumn ( "PaymentType" , typeof ( string )); dt_saveTrans.Columns.Add(dc4); DataColumn dc5 = new DataColumn ( "TransactionDate" , typeof ( DateTime )); //dc5.DateTimeMode = dt_saveTrans.Columns.Add(dc5); for ( int i = 0; i { DataRow dr = dt_saveTrans.NewRow(); dr[0] = "AccounNo" + int .Parse(i.ToString()); dr[1] = "AccountType" + i.ToString(); dr[2] = "TransactionName" + i.ToString(); dr[3] = "TransactionAmount" + Decimal .Parse(i.ToString()); dr[4] = "PaymentType" + i.ToString(); dr[5] = "TransactionDate" + i.ToString(); dt_saveTrans.Rows.Add(dr); } dt_saveTrans.AcceptChanges(); } But an error msg occurred after debug Error msg:"Input string was not in a correct format.Couldn't store in AccountNo Column. Expected type is Int32."

Roei Bar · Answer

here is a better sample, with an option to set the values in the grid Combo per each row and column: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; namespace WindowsFormsApplication4 { public partial class Form1 : Form { private DataTable dt = new DataTable(); public Form1() { InitializeComponent(); maketable(); dgv.DataSource = dt.DefaultView; dgv.Columns.Add("New", "New"); dgv.Columns.Add(new DataGridViewColumn(new DataGridViewComboBoxCell())); } private DataGridViewComboBoxCell setCellComboBoxItems(DataGridView dataGrid, int rowIndex, int colIndex, object[] itemsToAdd) { DataGridViewComboBoxCell dgvcbc = (DataGridViewComboBoxCell)dataGrid.Rows[rowIndex].Cells[colIndex]; // You might pass a boolean to determine whether to clear or not. dgvcbc.Items.Clear(); foreach (object itemToAdd in itemsToAdd) { dgvcbc.Items.Add(itemToAdd); } return dgvcbc; } private void maketable() { DataColumn dc0 = new DataColumn("ID", typeof(int)); dc0.AutoIncrement = true; dt.Columns.Add(dc0); DataColumn dc1 = new DataColumn("Name", typeof(String)); dt.Columns.Add(dc1); DataColumn dc2 = new DataColumn("Item", typeof(String)); dt.Columns.Add(dc2); for (int i = 0; i { DataRow dr = dt.NewRow(); dr[1] = "Name" + i.ToString(); dr[2] = "Item" + i.ToString(); dt.Rows.Add(dr); } dt.AcceptChanges(); } private void Button1_Click(object sender, System.EventArgs e) { foreach (DataGridViewRow dgvr in dgv.Rows) { dgvr.Cells["New"].Value = dgvr.Cells["Name"].Value; } } } }

Roei Bar · Answer

here is a sample for this: Public Class Form1 Private dt As New DataTable Private Sub maketable() Dim dc0 As New DataColumn("ID", GetType(Integer)) dc0.AutoIncrement = True dt.Columns.Add(dc0) Dim dc1 As New DataColumn("Name", GetType(String)) dt.Columns.Add(dc1) Dim dc2 As New DataColumn("Item", GetType(String)) dt.Columns.Add(dc2) For i As Integer = 0 To 9 Dim dr As DataRow = dt.NewRow dr(1) = "Name" & i.ToString dr(2) = "Item" & i.ToString dt.Rows.Add(dr) Next dt.AcceptChanges() End Sub Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load maketable() dgv.DataSource = dt.DefaultView dgv.Columns.Add("New", "New") End Sub Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click For Each dgvr As DataGridViewRow In dgv.Rows If CInt(dgvr.Cells("ID").Value) Mod 2 = 0 Then dgvr.Cells("New").Value = dgvr.Cells("Name").Value Else dgvr.Cells("New").Value = dgvr.Cells("Item").Value End If Next End Sub End Class

j k · Answer

hi Roei bar thanks for your answer. but i need more details........

j k · Answer

Hi Kirtan, Thanks for your reply. I have added some unbound Textbox columns and Comboboxcolumn in Datagridview control.The fields name of my Databasetable are name(text), address(text), gender (male/female), Amount (currency), date(date time), UserType(Administrator/Guest). Here I want to mention that "gender" and " UserType" column is ComboBoxColumn…… Now I want to input data in this datagrid and want to save new data to my Access database table by clicking on Save button. How could it possible? plz…..need your help

Roei Bar · Answer

well my friend you can do that by running on the DataGriDRows as follows foreach (DataGridViewRow row in dgv1.Rows) { //one you have the row you can run on the Columns foreach(DataGridViewColumn col in row) { } } and then once you have all the col text or in the columns of the Combo the col.selectedValue you can either directly run an update to the db, or build a datatable and then do a bulk update on the db here is a nice post on GridView and unbounded ComboBox http://homepage.ntlworld.com/herring1/datagrid.html

Kirtan Patel · Answer

Friend , What fields are there in database to store that values ??? and What Fields Are in DataGridView Can you tell me please :)

How to save unbound Datagrid to database table

Answers (12)