add row in datagridview that is bounded to datatable and save to database

EDN Admin · Sep 16, 2011

hi guys,
Add row in datagridview that is bounded to datatable and save to database

i am newbie in c#.net and I am sure you know exactly what to do and help on my concern. Well, I was on my way to finish my project of an simple ordering system. There will be time that the user would like to add a new row to the grid that was
already in the database. I would like to ask your if you have a simple source code that will guide me on how to do this.
Well below is the code that I have.
I have 2 forms and 1 BAL Class
Here is my BALClass
public class BLLClass 
{ 
#region VARIABLE 
clsDataAccess dbAccess = new clsDataAccess(); 
string _selectedID; 
 
#endregion 
 
#region OBJECT 
public DataTable dtClass { get; set; } 
public DataRow drClass { get; set; } 
#endregion 
 
public BLLClass() 
{ 
FillGrid(); 
} 
 
public DataTable FillGrid() 
{ 
try 
{ 
dtClass = dbAccess.ExecuteQuery("select * from productClass", CommandType.Text); 
return dtClass; 
} 
catch { throw; } 
} 
 
public string CurrentID 
{ 
get { return _selectedID; } 
 
set 
{ 
_selectedID = value; 
DataTable dt; 
try 
{ 
 
dbAccess.ClearParameters(); 
dbAccess.CreateParamater("@ProductClassID", _selectedID); 
dt = dbAccess.ExecuteQuery("select * from productclass where productClassID = @ProductClassID"); 
 
if (dt.Rows.Count > 0) 
drClass = dt.Rows[0]; 
 
} 
catch { throw; } 
} 
 
} 
 
public void NewRecord() 
{ 
drClass = dtClass.NewRow(); 
} 
 
public void AddItem() 
{ 
try 
{ 
dtClass.Rows.Add(drClass); 
} 
catch { throw; } 
} 
 
public void Save() 
{ 
try 
{ 
dbAccess.ClearParameters(); 
dbAccess.CreateParamater("@ProductClassID", drClass["ProductClassID"]); 
dbAccess.CreateParamater("@Description", drClass["Description"]); 
//dbAccess.CreateParamater("@UpdateDate", drClass["UpdateDate"]); 
dbAccess.CreateParamater("@ActiveUser", drClass["UpdateUser"]); 
 
dbAccess.ExecuteQuery("UpdateProductClass", CommandType.StoredProcedure); 
 
updateHeader(); 
} 
catch { throw; } 
} 
 
public void updateHeader() 
{ 
try 
{ 
DataRow[] fRow = dtClass.Select("ProductClassID = " + _selectedID + ""); 
if (fRow.Length > 0) 
for (int i = 0; i < dtClass.Columns.Count; i++) 
fRow[0] = drClass; 
} 
catch (Exception ex) { new Exception("Error in updateHeader() :/n" + ex.Message); } 
} 
} 
 
} 
 

Form 1
public partial class Form1 : Form 
{ 
#region VARIABLE 
BLLClass balClass = new BLLClass(); 
#endregion 
 
public Form1() 
{ 
InitializeComponent(); 
} 
 
private void Form1_Load(object sender, EventArgs e) 
{ 
dgv.DataSource = balClass.FillGrid(); 
} 
 
private void dgv_DoubleClick(object sender, EventArgs e) 
{ 
try 
{ 
string value = dgv.CurrentRow.Cells[0].Value.ToString(); 
balClass.CurrentID = value; 
Form2 frm2 = new Form2(balClass); 
frm2.ShowDialog(); 

 
} 
catch (Exception ex) { MessageBox.Show(ex.Message); } 
} 
 
private void btnRemove_Click(object sender, EventArgs e) 
{ 
DialogResult res = MessageBox.Show("Buburahin mo ba talaga ito? Sure ka?", "Reminder", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation); 
 
if (res == DialogResult.Yes) 
dgv.Rows.RemoveAt(dgv.CurrentRow.Index);
 
} 
 
private void btnProcess_Click(object sender, EventArgs e) 
{ 
if (txtDescription.Text.Trim() == string.Empty) 
{ 
MessageBox.Show("Please enter Description", "Required Input", MessageBoxButtons.OK, MessageBoxIcon.Information); 
txtDescription.Focus(); 
return; 
}
 
 
int b = 0; 
while (b <= dgv.Rows.Count - 1) 
{ 
for (int a = 0; a <= (dgv.Columns.Count - 1); a++) 
{ 
balClass.drClass["ProductClassID"] = code.ToInt32(dgv.CurrentRow.Cells["ProductClassID"].Value.ToString()); 
balClass.drClass["Description"] = dgv.CurrentRow.Cells["Description"].Value.ToString(); 
//balClass.drClass["UpdateDate"] = dgv.CurrentRow.Cells["UpdateDate"].Value.ToString(); 
balClass.drClass["UpdateUser"] = dgv.CurrentRow.Cells["UpdateUser"].Value.ToString(); 
 
balClass.Save(); 
b = b + 1; 
 
if (b == dgv.Rows.Count) 
return; 
} 
} 
//balClass.FillGrid(); 
} 
 
private void btnAdd_Click(object sender, EventArgs e) 
{ 
if (txtDescription.Text.Trim() == string.Empty) 
{ 
MessageBox.Show("Please enter Description", "Required Input", MessageBoxButtons.OK, MessageBoxIcon.Information); 
txtDescription.Focus(); 
return; 
} 
 
balClass.NewRecord(); 
try 
{ 
balClass.drClass["Description"] = txtDescription.Text.Trim(); 
balClass.drClass["UpdateDate"] = DateTime.Now; 
balClass.drClass["UpdateUser"] = "anthrow"; 
 
balClass.AddItem(); 
} 
catch (Exception ex) { MessageBox.Show(ex.Message); } 
} 
} 
}
= ==== ==
this is my FORM2
public partial class Form2 : Form 
{ 
#region VARIBLE 
BLLClass balClass; 
 
#endregion 
 
public Form2(object clsFrm1) 
{ 
InitializeComponent(); 
balClass = (BLLClass)clsFrm1; 
} 
 
private void Form2_Load(object sender, EventArgs e) 
{ 
SetForm(); 
} 
 
private void SetForm() 
{
 
txtID.Text = balClass.drClass["ProductClassID"].ToString(); 
txtDescription.Text = balClass.drClass["Description"].ToString(); 
txtUpdateDate.Text = balClass.drClass["UpdateDate"].ToString(); 
txtUpdateUser.Text = balClass.drClass["UpdateUser"].ToString();
 
} 
 
private void btnSave_Click(object sender, EventArgs e) 
{ 
if (txtDescription.Text.Trim() == string.Empty) 
{ 
MessageBox.Show("Description is required!"); 
return; 
} 
 
try 
{ 
balClass.drClass["ProductClassID"] = txtID.Text; 
balClass.drClass["Description"] = txtDescription.Text.Trim(); 
balClass.drClass["UpdateDate"] = DateTime.Now; 
balClass.drClass["UpdateUser"] = "anthrow"; 
 
balClass.Save(); 
this.Close(); 
} 
catch (Exception ex) { MessageBox.Show(ex.Message); } 
} 
} 
}
= = = =
SCRIPT OF MY TABLE
USE [DUMMY] 
GO 
 
/****** Object: Table [dbo].[ProductClass] Script Date: 09/16/2011 14:30:12 ******/ 
SET ANSI_NULLS ON 
GO 
 
SET QUOTED_IDENTIFIER ON 
GO 
 
SET ANSI_PADDING ON 
GO 
 
CREATE TABLE [dbo].[ProductClass]( 
[ProductClassID] [int] IDENTITY(1,1) NOT NULL, 
[Description] [varchar](50) NULL, 
[UpdateDate] [datetime] NULL, 
[UpdateUser] [varchar](50) NULL, 
[ObsoleteDate] [date] NULL 
) ON [PRIMARY] 
 
GO 
 
SET ANSI_PADDING OFF 
GO 
 
 
= = ==
here is my update SP
ALTER PROCEDURE [dbo].[UpdateProductClass] 
-- Add the parameters for the stored procedure here 
( 
@ProductClassID int OUTPUT, 
@Description varchar(20), 
@ObsoleteDate datetime = NULL, 
@ActiveUser varchar(30) 
) 
 
AS 
SET NOCOUNT ON; 
 
IF EXISTS(SELECT 1 FROM ProductClass WHERE ProductClassID = @ProductClassID) 
BEGIN 
UPDATE ProductClass SET 
 
Description = @Description, 
ObsoleteDate = @ObsoleteDate,
 
UpdateDate = GETDATE(), 
UpdateUser = @ActiveUser 
 
WHERE ProductClassID = @ProductClassID 
END 
 
ELSE 
 
BEGIN 
INSERT INTO ProductClass 
(
 
Description, 
ObsoleteDate, 
UpdateDate, 
UpdateUser 
) 
VALUES 
(
 
@Description, 
NULL, 
GETDATE(), 
@ActiveUser
 
) 
 
SET @ProductClassID = SCOPE_IDENTITY() 
 
END 
SET NOCOUNT OFF 
SELECT @ProductClassID as ProductClassID
= = ==

There is problem when saving new row in database.

If you could provide me a sample working code same like this.

<
Christian 

View the full article

add row in datagridview that is bounded to datatable and save to database

EDN Admin

Well-known member

Similar threads